How do I link a database in SQL

I am working on a databse on my local box, my source data is on
another. How can I link the database and table from one server to
another? Currently I am using DTS to just transfer the records!(rzito@.si.rr.com) writes:

Quote:

Originally Posted by

I am working on a databse on my local box, my source data is on
another. How can I link the database and table from one server to
another? Currently I am using DTS to just transfer the records!

You can set up a linked server. If the stars align, it's as simple as

sp_addlinkedserver THATSERVER

then you can query the remote tables with four-part notation:

select col1, col2 from THATSERVER.db.dbo.tbl

If you are on SQL 2005, you can use synonyms to make your code cleaner:

CREATE SYNONYM remotebl FOR THATSERVER.db.dbo.tlb

and then go:

select col1, col2 FROM remotetbl

Linked servers are a bit tricky to work with, and one obstacle is to
get authentication to work. I can't say that I have fully understood the
rules, but I have not dug very hard into it. You can use
sp_addlinkedsrvlogin to specify how you are to connect to the remote server.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I actually found a much easier way, I connected to my SQL database
using MS Access ADP project, and just clicked on the link and the
wizard walked me through it, thanks for the help!

How do I insert a string value with quotes into a nvarchar column

I am reading data from another data source and storing it in the sqlce database. Some of the string values I'm trying to insert into the database have single quotes in the string (i.e. Johnny's Company). When I try to insert the values with the single quotes, it throws an exception. The code I use to insert the records is as follows:

cmd.CommandText = "INSERT sy_company " +

" (company_id, company, co_name, companyid) " +

"VALUES(" +

"'" + dtSYCompany.Rows[x]["company_id"] + "'," +

"N'" + dtSYCompany.Rows[x]["company"] + "'," +

"N'" + dtSYCompany.Rows[x]["co_name"] + "'," +

"'" + dtSYCompany.Rows[x]["companyid"] + "')";

cmd.ExecuteNonQuery();

When the company name (co_name) has a single quote in it, I get the error. How do I write the insert statement so it will work even though the value being inserted into co_name has a single quote in it?

Thanks so much!

Use parameters. It’s also great for performance and security.

By the way, it appears you’re using DataTable. In that case you could use DataAdapter.Update() instead of running commands manually. CommandBuilder can generate parametrized command for you.

|||That did it!!! Thanks so much.

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

how do i import repeatable but non pre-defined fields?

hello all,

I'm strugeling for quite soem time and hoping to get your assistance:

facts:

We have a flat file source each record has the same set of columns, let's call it "identifying columns" each row can have after the identifying columns (but not all the records have it) a second set of columns, let's call it "detailed info". the "detailed info" set can repeat itself several time for each record We have a sql table destination The row in the table should represent an "indetifying columns" record

sample record 1:

David | 1 | Rodeo Drive | 456 |

sample record 2:

Jeff | 2 |

sample record 3:

John | 3 | Sunny rd | 111 | Marvell str | 6

expected output in the table:

David | 1 | Rodeo Drive | 456

Jeff | 2 | null | null

John | 3 | Sunny rd | 111

John | 3 | Marvell str | 6

question: How do i import the data from the source file into the table, when i dont know in advance how many columns each record has? and how do i divide the sets of "detailed info" into different records in the db?

Thanks,

Eric

From your example, it looks like the table represents a "detailed info" record. Regardless, your problem is that you have a varying number of columns and the SSIS flat file connection manager won't support that. There are several threads here about the problem.

To quickly describe my preferred solution, I think you should read the file as a single column and use a script component to parse it into the columns you need. Others may recommend the Derived Column for the parsing.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/08/handling-flat-files-with-varying-numbers-of-columns.aspx
|||

re the number of columns - i've used a script that adds empty columns to each row, based on the max number of columns in the file,

so my file will actualy look like this:

John | 123 | B str | 2 | | |

Jeff | 444 | | | | |

Amy | 555 | A str | 1 | C Str | 12

my issue still remains with splitting the columns into rows. meaning - i need my 3rd & 4th columns to represent 1 row in the table and i need my 5th & 6th columns to represent a second row in the destination table.

(the 2 columns are just an example in order to simplify, i actualy have a set of 10 columns that repeat itself and need to be separated into rows).

how would you suggest splitting each set of columns into rows?

thanks in advance !

|||Have you looked at the Unpivot transform? That is designed to move columns to rows.|||

yes, this is what I am trying to do now.

the question i have around this one is - whether we can do some kind of dynamic un-pivot (or via script task?) cause the number of columns that we have is not defined in advance, so i need some kind of looping on all the columns and insert every 4th column in destination column A and every 5th column into destination column B, etc.

is there such a thing, a dynamic unpivot? can you direct me to examples?

thanks!

|||The Unpivot does not support a dynamic number of columns to pivot on, so you'd need to use a script component transform with an asynchronous output. For each input row, you would output one or more rows. Take a look at "Creating an Asynchronous Transformation with the Script Component" in Books Online for some guidance in doing this.|||

I posted an example of this on my blog - hope it's helpful.

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/18/dynamically-pivoting-columns-to-rows.aspx

How do I find the source of mystery backups?

I went into the Activity Log to look for one thing, and found something else
very troubling. Starting one week ago, something is triggering weekly full
backups and daily transaction log backups, but they are not on my backup
plan, nor can I tell from the log where the backup files are being dumped.
The regularity tells me that this is something that's been scheduled, but I
have checked all maintenance plans, and all jobs, and I see nothing that
correlates to the times of these backups or to the specific databases.
Can something outside of Sql run these backups?
I have changed the server property to record all logins and not just failed
logins, but what else can I do to trace what is happening?
Our tech just said it could be nothing more than the tape backups of the Sql
server, but I don't understand how the copying of mdf and ldf files to tape
could generate entries in the sql activity log that are identical in wording
to the entries made from my regularly scheduled backups.
Oh, and the puzzle that first drew me to the activity log: Starting last
week, the same time that this all started, my differential backups were the
same size as full backups. Is there a connection?
On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
<BevKaufman@.discussions.microsoft.com> wrote:

>I went into the Activity Log to look for one thing, and found something else
>very troubling. Starting one week ago, something is triggering weekly full
>backups and daily transaction log backups, but they are not on my backup
>plan, nor can I tell from the log where the backup files are being dumped.
A great deal of information on every BACKUP and RESTORE command is
logged in the msdb database. I haven't played with it recently - and
if you mentioned the version of SQL Server you are using I missed it -
but I am pretty sure you will find the destination of each BACKUP
somewhere there.
Roy Harvey
Beacon Falls, CT
|||Yes, to add to Roy's comment, all the information is on some msdb tables.
The user who performed the backup is on the user_name field on the backupset
table. Some other information about the backups is on the backupfile and
backupmediafamily system tables.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Roy Harvey (SQL Server MVP)" wrote:

> On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
> <BevKaufman@.discussions.microsoft.com> wrote:
>
> A great deal of information on every BACKUP and RESTORE command is
> logged in the msdb database. I haven't played with it recently - and
> if you mentioned the version of SQL Server you are using I missed it -
> but I am pretty sure you will find the destination of each BACKUP
> somewhere there.
> Roy Harvey
> Beacon Falls, CT
>
|||Hi,
Try this under msdb database:
select distinct * from backupset,backupfile,backupmediafamily where
backupset.backup_set_id=backupfile.backup_set_id
and backupset.media_set_id=backupmediafamily.media_set _id
order by backup_start_date desc
Manu
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Yes, to add to Roy's comment, all the information is on some msdb tables.
> The user who performed the backup is on the user_name field on the backupset
> table. Some other information about the backups is on the backupfile and
> backupmediafamily system tables.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Roy Harvey (SQL Server MVP)" wrote:

How do I find the source of mystery backups?

I went into the Activity Log to look for one thing, and found something else
very troubling. Starting one week ago, something is triggering weekly full
backups and daily transaction log backups, but they are not on my backup
plan, nor can I tell from the log where the backup files are being dumped.
The regularity tells me that this is something that's been scheduled, but I
have checked all maintenance plans, and all jobs, and I see nothing that
correlates to the times of these backups or to the specific databases.
Can something outside of Sql run these backups?
I have changed the server property to record all logins and not just failed
logins, but what else can I do to trace what is happening?
Our tech just said it could be nothing more than the tape backups of the Sql
server, but I don't understand how the copying of mdf and ldf files to tape
could generate entries in the sql activity log that are identical in wording
to the entries made from my regularly scheduled backups.
Oh, and the puzzle that first drew me to the activity log: Starting last
week, the same time that this all started, my differential backups were the
same size as full backups. Is there a connection?> Can something outside of Sql run these backups?
SQL Server doesn't backup something by itself. It is always something outside SQL Server that
initiates the backup (logs on and executes the backup command). Most often, this is an SQL Server
Agent job (like for instance a maint plan).
> Our tech just said it could be nothing more than the tape backups of the Sql
> server, but I don't understand how the copying of mdf and ldf files to tape
> could generate entries in the sql activity log that are identical in wording
> to the entries made from my regularly scheduled backups.
I'm confused? Are you copying the database files directly? Or are you using some kind of snapshot
technology (VSS)? You could try to stop the SQL Server VSS writer service to find out, but be aware
that your snapshot backups (if such are done) will not be proper while the service is off.
Also, you can use Profiler to log what is happening a these times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F3E1830-A4D2-41E9-945C-86C2C30338A4@.microsoft.com...
>I went into the Activity Log to look for one thing, and found something else
> very troubling. Starting one week ago, something is triggering weekly full
> backups and daily transaction log backups, but they are not on my backup
> plan, nor can I tell from the log where the backup files are being dumped.
> The regularity tells me that this is something that's been scheduled, but I
> have checked all maintenance plans, and all jobs, and I see nothing that
> correlates to the times of these backups or to the specific databases.
> Can something outside of Sql run these backups?
> I have changed the server property to record all logins and not just failed
> logins, but what else can I do to trace what is happening?
> Our tech just said it could be nothing more than the tape backups of the Sql
> server, but I don't understand how the copying of mdf and ldf files to tape
> could generate entries in the sql activity log that are identical in wording
> to the entries made from my regularly scheduled backups.
> Oh, and the puzzle that first drew me to the activity log: Starting last
> week, the same time that this all started, my differential backups were the
> same size as full backups. Is there a connection?
>|||On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
<BevKaufman@.discussions.microsoft.com> wrote:
>I went into the Activity Log to look for one thing, and found something else
>very troubling. Starting one week ago, something is triggering weekly full
>backups and daily transaction log backups, but they are not on my backup
>plan, nor can I tell from the log where the backup files are being dumped.
A great deal of information on every BACKUP and RESTORE command is
logged in the msdb database. I haven't played with it recently - and
if you mentioned the version of SQL Server you are using I missed it -
but I am pretty sure you will find the destination of each BACKUP
somewhere there.
Roy Harvey
Beacon Falls, CT|||Yes, to add to Roy's comment, all the information is on some msdb tables.
The user who performed the backup is on the user_name field on the backupset
table. Some other information about the backups is on the backupfile and
backupmediafamily system tables.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Roy Harvey (SQL Server MVP)" wrote:
> On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
> <BevKaufman@.discussions.microsoft.com> wrote:
> >I went into the Activity Log to look for one thing, and found something else
> >very troubling. Starting one week ago, something is triggering weekly full
> >backups and daily transaction log backups, but they are not on my backup
> >plan, nor can I tell from the log where the backup files are being dumped.
> A great deal of information on every BACKUP and RESTORE command is
> logged in the msdb database. I haven't played with it recently - and
> if you mentioned the version of SQL Server you are using I missed it -
> but I am pretty sure you will find the destination of each BACKUP
> somewhere there.
> Roy Harvey
> Beacon Falls, CT
>|||Hi,
Try this under msdb database:
select distinct * from backupset,backupfile,backupmediafamily where
backupset.backup_set_id=backupfile.backup_set_id
and backupset.media_set_id=backupmediafamily.media_set_id
order by backup_start_date desc
Manu
"Ben Nevarez" wrote:
> Yes, to add to Roy's comment, all the information is on some msdb tables.
> The user who performed the backup is on the user_name field on the backupset
> table. Some other information about the backups is on the backupfile and
> backupmediafamily system tables.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Roy Harvey (SQL Server MVP)" wrote:
> > On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
> > <BevKaufman@.discussions.microsoft.com> wrote:
> >
> > >I went into the Activity Log to look for one thing, and found something else
> > >very troubling. Starting one week ago, something is triggering weekly full
> > >backups and daily transaction log backups, but they are not on my backup
> > >plan, nor can I tell from the log where the backup files are being dumped.
> >
> > A great deal of information on every BACKUP and RESTORE command is
> > logged in the msdb database. I haven't played with it recently - and
> > if you mentioned the version of SQL Server you are using I missed it -
> > but I am pretty sure you will find the destination of each BACKUP
> > somewhere there.
> >
> > Roy Harvey
> > Beacon Falls, CT
> >

How do I find the source of mystery backups?

I went into the Activity Log to look for one thing, and found something else
very troubling. Starting one week ago, something is triggering weekly full
backups and daily transaction log backups, but they are not on my backup
plan, nor can I tell from the log where the backup files are being dumped.
The regularity tells me that this is something that's been scheduled, but I
have checked all maintenance plans, and all jobs, and I see nothing that
correlates to the times of these backups or to the specific databases.
Can something outside of Sql run these backups?
I have changed the server property to record all logins and not just failed
logins, but what else can I do to trace what is happening?
Our tech just said it could be nothing more than the tape backups of the Sql
server, but I don't understand how the copying of mdf and ldf files to tape
could generate entries in the sql activity log that are identical in wording
to the entries made from my regularly scheduled backups.
Oh, and the puzzle that first drew me to the activity log: Starting last
week, the same time that this all started, my differential backups were the
same size as full backups. Is there a connection?> Can something outside of Sql run these backups?
SQL Server doesn't backup something by itself. It is always something outsid
e SQL Server that
initiates the backup (logs on and executes the backup command). Most often,
this is an SQL Server
Agent job (like for instance a maint plan).

> Our tech just said it could be nothing more than the tape backups of the S
ql
> server, but I don't understand how the copying of mdf and ldf files to tap
e
> could generate entries in the sql activity log that are identical in wordi
ng
> to the entries made from my regularly scheduled backups.
I'm confused? Are you copying the database files directly? Or are you using
some kind of snapshot
technology (VSS)? You could try to stop the SQL Server VSS writer service to
find out, but be aware
that your snapshot backups (if such are done) will not be proper while the s
ervice is off.
Also, you can use Profiler to log what is happening a these times.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Bev Kaufman" <BevKaufman@.discussions.microsoft.com> wrote in message
news:6F3E1830-A4D2-41E9-945C-86C2C30338A4@.microsoft.com...
>I went into the Activity Log to look for one thing, and found something els
e
> very troubling. Starting one week ago, something is triggering weekly ful
l
> backups and daily transaction log backups, but they are not on my backup
> plan, nor can I tell from the log where the backup files are being dumped.
> The regularity tells me that this is something that's been scheduled, but
I
> have checked all maintenance plans, and all jobs, and I see nothing that
> correlates to the times of these backups or to the specific databases.
> Can something outside of Sql run these backups?
> I have changed the server property to record all logins and not just faile
d
> logins, but what else can I do to trace what is happening?
> Our tech just said it could be nothing more than the tape backups of the S
ql
> server, but I don't understand how the copying of mdf and ldf files to tap
e
> could generate entries in the sql activity log that are identical in wordi
ng
> to the entries made from my regularly scheduled backups.
> Oh, and the puzzle that first drew me to the activity log: Starting last
> week, the same time that this all started, my differential backups were th
e
> same size as full backups. Is there a connection?
>|||On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
<BevKaufman@.discussions.microsoft.com> wrote:

>I went into the Activity Log to look for one thing, and found something els
e
>very troubling. Starting one week ago, something is triggering weekly full
>backups and daily transaction log backups, but they are not on my backup
>plan, nor can I tell from the log where the backup files are being dumped.
A great deal of information on every BACKUP and RESTORE command is
logged in the msdb database. I haven't played with it recently - and
if you mentioned the version of SQL Server you are using I missed it -
but I am pretty sure you will find the destination of each BACKUP
somewhere there.
Roy Harvey
Beacon Falls, CT|||Yes, to add to Roy's comment, all the information is on some msdb tables.
The user who performed the backup is on the user_name field on the backupset
table. Some other information about the backups is on the backupfile and
backupmediafamily system tables.
Hope this helps,
Ben Nevarez
Senior Database Administrator
AIG SunAmerica
"Roy Harvey (SQL Server MVP)" wrote:

> On Mon, 12 Nov 2007 07:56:08 -0800, Bev Kaufman
> <BevKaufman@.discussions.microsoft.com> wrote:
>
> A great deal of information on every BACKUP and RESTORE command is
> logged in the msdb database. I haven't played with it recently - and
> if you mentioned the version of SQL Server you are using I missed it -
> but I am pretty sure you will find the destination of each BACKUP
> somewhere there.
> Roy Harvey
> Beacon Falls, CT
>|||Hi,
Try this under msdb database:
select distinct * from backupset,backupfile,backupmediafamily where
backupset.backup_set_id=backupfile.backup_set_id
and backupset.media_set_id=backupmediafamily.media_set_id
order by backup_start_date desc
Manu
"Ben Nevarez" wrote:
[vbcol=seagreen]
> Yes, to add to Roy's comment, all the information is on some msdb tables.
> The user who performed the backup is on the user_name field on the backups
et
> table. Some other information about the backups is on the backupfile and
> backupmediafamily system tables.
> Hope this helps,
> Ben Nevarez
> Senior Database Administrator
> AIG SunAmerica
>
> "Roy Harvey (SQL Server MVP)" wrote:
>

How do I find out what query someone ran?

Hello All,

We have an app that we do not have the source code for that is behaving badly. I'd like to find out what queries it is running in order to possibly fix the issue form the SQL server side of things. Anyone know what table/view I should select off of to find the queries that have been run recently?

Thanks in advance!

Kenny, your best bet is to fire up SQL Profiler and do some filtering so you only see the queries being executed by the application.

Thanks,
Sam Lester (MSFT)

How do I extract data from selected rows from my excel source file?

The columns in my excel source contain data of different types with the column name being a string and the data in those columns being integers. Is there any way to only extract numeric data , in short I want column names to be omitted. Also the data is distributed unevenly , beggining at various rows in each column.

Thanking in advance :)

You can try to create a named range in the Excel sheet and then import only the range.

HTH.

|||Is there any other way to do it , like writing a query or so? I need help as i'm new to SSIS , any help would be appreciated

How do i Edit the Package Source file name Dynamically

Hi

I have created a package using DTS Designer to import the data from flat file to the SQL server.
i have sheduled the package to execute at every 1st day of the month and saved the the package to the local server.

i want to do the following steps dynamically from the Visual Basic

1. i am loading all the packages from the local server and also from the metadata services into a combo box. select one package from the combo box and i want to change the package shedule time from vb and i want to save the package back to the sql server.

2. when the package is executing at the schedule time at that time i want to change the source of the package that means flat filename(to different filename) and i need to execute the package and save the package.

how do i do this dynamically.

Thanks in advance

Regards
GandhiThe package schedule time is really a job schedule time - so you would use the sp_update_jobschedule stored procedure. For the 2nd part, when would the filename change and what would change it ?