Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner jo
in
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on th
e
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get s
ome
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resourc
e
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/defaul...kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:

> Thanks for the inputs everyone. After looking at your examples, I did get
some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resou
rce
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, look
s
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was ab
le
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BT
W,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if thi
s
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:

> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However
if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveK
Could you please post your join query ?
-jens Suessmeyer.
|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner join
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:

> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK
|||Thanks for the inputs everyone. After looking at your examples, I did get some
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resource
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.
|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/default...b;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:

> Thanks for the inputs everyone. After looking at your examples, I did get some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resource
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, looks
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was able
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BTW,
> the linked server is 2,000 miles away, but we have a good connection.
>
|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?
|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if this
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:

> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multi-Table Linked Query?

I can access a single table in a query, but I cannot seem to make a
multi-table query work. The following works:
SELECT order_number from
remote.staging.dbo.orders
where
company_code = 'Company1' and
order_number = '005579974620040304'
However, I cannot get joins to work when both sides of the joins are on the
remote server. I always get "column prefix does not match" errors. Is a
linked server good only for one table?
Can someone give me an example ? Just make up an additional table in the
same DB with a join. Thank you.
DaveKCould you please post your join query ?
-jens Suessmeyer.|||Hello Dave,
I belive that linked tables arn't so much for queries but for server
operaitons (backups etc).
As an example of a joined table on the same server, it is often useful to
see it another way - as a simple join using a WHERE statement so you can see
the function of joining:
SELECT name_table.first_name, address_table.street_number
FROM name_table, address_table
WHERE name_table.address_id = address_table.address_id
In this example I am linking the two tables by the address_id field.
So using a JOIN you would use:
SELECT name_table.first_name, address_table.street_number
FROM name_table
INNER JOIN
address_table ON name_table.address_id = address_table.address_id
Does this help you a little bit?
"DaveK" <DaveK@.discussions.microsoft.com> wrote in message
news:509461C5-1A63-4979-AA81-212139DB467A@.microsoft.com...
>I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on
> the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||If you use table identifiers, that should work:
ie:
select top 1* from [Test1].[MyDB].[dbo].[Table1] a inner join
[Test1].[MyDB].[dbo].[Table2] b
on b.Col1=a.Col1
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Hi Dave
You should be fine with more than one table, but without seeing the query or
the exact error text or number it is hard to say exactly which is wrong. It
sounds like you have a typo.
John
"DaveK" wrote:
> I can access a single table in a query, but I cannot seem to make a
> multi-table query work. The following works:
> SELECT order_number from
> remote.staging.dbo.orders
> where
> company_code = 'Company1' and
> order_number = '005579974620040304'
> However, I cannot get joins to work when both sides of the joins are on the
> remote server. I always get "column prefix does not match" errors. Is a
> linked server good only for one table?
> Can someone give me an example ? Just make up an additional table in the
> same DB with a join. Thank you.
> DaveK|||Thanks for the inputs everyone. After looking at your examples, I did get some
progress. Here is the exact query:
select c.test_code, d.result_code
from server1.staging.dbo.tests c
right join server1.staging.dbo.results d
on c.company_code = d.company_code and
c.order_number = d.order_number and
c.test_code = d.test_code
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and d.result_code = '35645AA1'
Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
provider because a resource limit was reached.
[OLE/DB provider returned message: Timeout expired]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
returned 0x80040e31: Execution terminated by the provider because a resource
limit was reached.].
These tables have several million rows. The error, after 15 minutes, looks
to me like the linked server was trying to send the whole tables. This
should not be.
A single query with no join, like this one:
select c.test_code, c.result_code, c.value
from server1.staging.dbo.results c
where c.company_code = 'Company'
and c.order_number like '00557997462004____'
and c.result_code = '35645AA1'
normally takes 3 seconds
Am I on the right track with the limitations of a linked server? I was able
to get OPENQUERY style querys to work with my join syntax, so I am pretty
sure I am using the correct syntax. They only took 3 seconds as well. BTW,
the linked server is 2,000 miles away, but we have a good connection.|||Hi Dave
Your error is probably the remote query timing out
http://support.microsoft.com/default.aspx?scid=kb;en-us;314530
As OPENQUERY is working so well I assume that you are going to use that?
John
"DaveK" wrote:
> Thanks for the inputs everyone. After looking at your examples, I did get some
> progress. Here is the exact query:
> select c.test_code, d.result_code
> from server1.staging.dbo.tests c
> right join server1.staging.dbo.results d
> on c.company_code = d.company_code and
> c.order_number = d.order_number and
> c.test_code = d.test_code
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and d.result_code = '35645AA1'
> Now what I get is:Server: Msg 7399, Level 16, State 1, Line 1
> OLE DB provider 'SQLOLEDB' reported an error. Execution terminated by the
> provider because a resource limit was reached.
> [OLE/DB provider returned message: Timeout expired]
> OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ICommandText::Execute
> returned 0x80040e31: Execution terminated by the provider because a resource
> limit was reached.].
> These tables have several million rows. The error, after 15 minutes, looks
> to me like the linked server was trying to send the whole tables. This
> should not be.
> A single query with no join, like this one:
> select c.test_code, c.result_code, c.value
> from server1.staging.dbo.results c
> where c.company_code = 'Company'
> and c.order_number like '00557997462004____'
> and c.result_code = '35645AA1'
> normally takes 3 seconds
>
> Am I on the right track with the limitations of a linked server? I was able
> to get OPENQUERY style querys to work with my join syntax, so I am pretty
> sure I am using the correct syntax. They only took 3 seconds as well. BTW,
> the linked server is 2,000 miles away, but we have a good connection.
>|||I think I have narrowed down the problem some more.
If I use a search string in single quotes, it runs pretty fast. However if
I built the search string as a variable (declare @.search_string ...,) then
use that in my where clause, everything goes to pot timewise.
forinstance:
select c.order_number, c.value
from remote1.staging.dbo.orders c
where c.company_code = 'Company1'
and c.order_number = @.accession_string
this takes forever and times out.
Why would one form of the same data (literals in single quotes vs. string
variable change the query so much?|||Hi Dave
I am not sure why it does this, you may want to try using profiler on the
local and remote machines to see what gets sent to them, to determine if this
is the query optimiser or the OLEDB driver that is having problems.
Assuming @.accession_string is a char(10), you may want to try:
EXEC remote1.master.dbo.sp_executesql
N'select c.order_number, c.value from staging.dbo.orders c
where c.company_code = ''Company1''
and c.order_number = @.accession',
N'@.accession char(10)',
@.accession_string
John
"DaveK" wrote:
> I think I have narrowed down the problem some more.
> If I use a search string in single quotes, it runs pretty fast. However if
> I built the search string as a variable (declare @.search_string ...,) then
> use that in my where clause, everything goes to pot timewise.
> forinstance:
> select c.order_number, c.value
> from remote1.staging.dbo.orders c
> where c.company_code = 'Company1'
> and c.order_number = @.accession_string
> this takes forever and times out.
> Why would one form of the same data (literals in single quotes vs. string
> variable change the query so much?
>

Multiple-step OLE DB operation generated errors

Hello....

I have two linked server... (ServerB and ServerC) which reside on ServerA. I am able to connect to the remote database using "Select" statements without any issues.

When I run this query, It is successful:

delete [SERVERB].MyDatabase.dbo.TableName
from [SERVERB].MyDatabase.dbo.TableName t1
Left join MyDatabase.dbo.TableName t2 on ( t1.ID = t2.ID and
t1.EmployeeNumber = t2.EmployeeNumber and
t1.AccountNumber = t2.AccountNumber)
where t2.ID is null;

However, when I change [SERVERB] to [SERVERC], I receive two errors:

"Could not find server 'ELEARN-FRM-BETA' in sysservers. Execute sp_addlinkedserver to add the server to sysservers."

And

OLE DB provider "SQLNCLI" for linked server "ELEARN-FRM-BETA" returned message "Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.".

When I run profiler on ServerC, I see traffic... mainly a whole bunch of exec "sp_cursorfetch" operations, so I know the connection is valid.

Any ideas?

Forch

Sorry... I forgot to mention that all three servers are SQL Server 2005 x64 edition with SP1.

Forch

sql

Multiple variables

Hi
Our database has a series of tables that are all linked by a field called
company_code (amongst others). Occasionally data needs to be deleted and
this involves deleting multiple rows from some of these tables and updating
data in another table. I tend to wait until there are a few to do and then
do them all at once. To make the task less onerous I have saved a simple
script that does all the deletions for each code at once, a cut down version
is below:
/*Delete all pay data and make contact only*/
DECLARE @.code int
SET @.code = 12345
DELETEcompany_size
WHEREcompany_code = @.code
--Other deletions go in here
UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code = @.code
What I would like to be able to do is change the 'where' statements so that
they use the IN operator rather than the = operator. Is there a way to do
this using variables so I can assign a list of company codes to a variable
that can be used with an IN operator?
Thanks
Andy
On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:

>Hi
>Our database has a series of tables that are all linked by a field called
>company_code (amongst others). Occasionally data needs to be deleted and
>this involves deleting multiple rows from some of these tables and updating
>data in another table. I tend to wait until there are a few to do and then
>do them all at once. To make the task less onerous I have saved a simple
>script that does all the deletions for each code at once, a cut down version
>is below:
>/*Delete all pay data and make contact only*/
>DECLARE @.code int
>SET @.code = 12345
>DELETEcompany_size
>WHEREcompany_code = @.code
>--Other deletions go in here
>UPDATEcompany_basic
>SETreport_status = null,
>report_entry_urn = null,
>next_rpt_archive = null,
>/* Other fields to be updated... */
>WHEREcompany_code = @.code
>What I would like to be able to do is change the 'where' statements so that
>they use the IN operator rather than the = operator. Is there a way to do
>this using variables so I can assign a list of company codes to a variable
>that can be used with an IN operator?
>Thanks
>Andy
>
Hi Andy,
To do that, you'll have to use dynamic SQL. Since this is in a script
that only you can execute, you don't have to worry about SQL Injection
in this case.
Rough outline:
/*Delete all pay data and make contact only*/
DECLARE @.codes nvarchar(40)
DECLARE @.SQL nvarchar(4000)
SET @.code = N'(12345,6789)'
SET @.sql = 'DELETEcompany_size
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
--Other deletions go in here
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
report_entry_urn = null,
next_rpt_archive = null,
/* Other fields to be updated... */
WHEREcompany_code IN ' + @.code
EXEC (@.sql)
In case you're tempted to use this technique in your production code as
well, read up on SQL injection and other dangers of this technique:
http://www.sommarskog.se/dynamic_sql.html
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Thanks very much for this - I've not tried it yet as I think there might be
a couple of typos that I want to confirm:
You declare a variable called @.codes, presumably all of my old instances of
@.code should now read @.codes instead.
Is the N in this line correct: SET @.code = N'(12345,6789)'?
I'm not brave enough to give it a go anyway!!!
Thanks again
"Hugo Kornelis" wrote:

> On Wed, 13 Apr 2005 08:48:06 -0700, Andy wrote:
>
> Hi Andy,
> To do that, you'll have to use dynamic SQL. Since this is in a script
> that only you can execute, you don't have to worry about SQL Injection
> in this case.
> Rough outline:
> /*Delete all pay data and make contact only*/
> DECLARE @.codes nvarchar(40)
> DECLARE @.SQL nvarchar(4000)
> SET @.code = N'(12345,6789)'
> SET @.sql = 'DELETEcompany_size
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
> --Other deletions go in here
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> report_entry_urn = null,
> next_rpt_archive = null,
> /* Other fields to be updated... */
> WHEREcompany_code IN ' + @.code
> EXEC (@.sql)
>
> In case you're tempted to use this technique in your production code as
> well, read up on SQL injection and other dangers of this technique:
> http://www.sommarskog.se/dynamic_sql.html
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:

>Hi Hugo
>Thanks very much for this - I've not tried it yet as I think there might be
>a couple of typos that I want to confirm:
>You declare a variable called @.codes, presumably all of my old instances of
>@.code should now read @.codes instead.
Hi Andy,
My bad - since I adapted the script to work for more than one code at
once, I thought it'd be best to change the variable name from @.code to
@.codes - but after changing the first, I promptly forgot to change the
rest. <blush>

>Is the N in this line correct: SET @.code = N'(12345,6789)'?
Yes. I've changed the datatype to nvarchar (note: not just varchar, but
nvarchar), as is required for dynamic SQL. The N in fron of the string
constant means that this is also regarded as nchar data instead of plain
char data. (Nothing bad will happen if you leave out the N, nor if you
use character type varchar - but it will cuase SQL Server to do an
implicit conversion under the hood. I prefer to use explicit conversion,
or no conversion at all).

>I'm not brave enough to give it a go anyway!!!
Always test code suggestions on a test database. Make sure you have a
recent backup or another means to retore the data. And if possible,
enclose the code to be tested in a transaction, so that you can rollback
the changes if something goes awry.
Following the above advise for code you write yourself is not exactly a
bad idea either :-)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hi Hugo
Thanks for this. I've been out of the office the last few days so have only
just picked this up. Thanks for the advice as well!
Andy
"Hugo Kornelis" wrote:

> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> Hi Andy,
> My bad - since I adapted the script to work for more than one code at
> once, I thought it'd be best to change the variable name from @.code to
> @.codes - but after changing the first, I promptly forgot to change the
> rest. <blush>
>
> Yes. I've changed the datatype to nvarchar (note: not just varchar, but
> nvarchar), as is required for dynamic SQL. The N in fron of the string
> constant means that this is also regarded as nchar data instead of plain
> char data. (Nothing bad will happen if you leave out the N, nor if you
> use character type varchar - but it will cuase SQL Server to do an
> implicit conversion under the hood. I prefer to use explicit conversion,
> or no conversion at all).
>
> Always test code suggestions on a test database. Make sure you have a
> recent backup or another means to retore the data. And if possible,
> enclose the code to be tested in a transaction, so that you can rollback
> the changes if something goes awry.
> Following the above advise for code you write yourself is not exactly a
> bad idea either :-)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||Hi Hugo
I'm getting a syntax error when running this script because oneof the fields
that gets updated is a char data type so:
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = 'C',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
As you can see it is because the quote before C is closing the string and so
on. Do I need to break the string at that point and concatenate it so that:
pay_or_contact = ' + 'C' + ', etc etc
or is there a way of getting SQL to ignore the string within the string?
Sorry I didn't include that bit in the initial question - I was trying to
cut down on the size of the post!
Thanks
Andy
"Hugo Kornelis" wrote:

> On Thu, 14 Apr 2005 04:52:00 -0700, Andy wrote:
>
> Hi Andy,
> My bad - since I adapted the script to work for more than one code at
> once, I thought it'd be best to change the variable name from @.code to
> @.codes - but after changing the first, I promptly forgot to change the
> rest. <blush>
>
> Yes. I've changed the datatype to nvarchar (note: not just varchar, but
> nvarchar), as is required for dynamic SQL. The N in fron of the string
> constant means that this is also regarded as nchar data instead of plain
> char data. (Nothing bad will happen if you leave out the N, nor if you
> use character type varchar - but it will cuase SQL Server to do an
> implicit conversion under the hood. I prefer to use explicit conversion,
> or no conversion at all).
>
> Always test code suggestions on a test database. Make sure you have a
> recent backup or another means to retore the data. And if possible,
> enclose the code to be tested in a transaction, so that you can rollback
> the changes if something goes awry.
> Following the above advise for code you write yourself is not exactly a
> bad idea either :-)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>
|||On Mon, 18 Apr 2005 09:09:02 -0700, Andy wrote:
(snip)
>As you can see it is because the quote before C is closing the string and so
>on. Do I need to break the string at that point and concatenate it so that:
>pay_or_contact = ' + 'C' + ', etc etc
>or is there a way of getting SQL to ignore the string within the string?
(snip)
Hi Andy,
You'll have to make sure that you embed a quote within the string. The
code passed to the EXEC (@.sql) needs quotes around the C to recognise it
as a literal. There are two ways to do that: either use the ASCII value
for the quote, or double the quote (one quote in a string is considered
a string delimiter, two consecutive quotes are considered one quote as
part of the string):
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = ''C'',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
or
--Update the various flags on the company basic table
SET @.sql = 'UPDATEcompany_basic
SETreport_status = null,
--Other fields being set to null
verify_svy_urn = null,
pay_or_contact = ' +CHAR(39)+ 'C' +CHAR(39)+ ',
year_end_date = null,
--Other fields being set to Null
WHEREcompany_code IN ' + @.codes
EXEC (@.sql)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Thanks Hugo
This has worked perfectly - its going to save me an awful lot of time!!!
Andy
"Hugo Kornelis" wrote:

> On Mon, 18 Apr 2005 09:09:02 -0700, Andy wrote:
> (snip)
> (snip)
> Hi Andy,
> You'll have to make sure that you embed a quote within the string. The
> code passed to the EXEC (@.sql) needs quotes around the C to recognise it
> as a literal. There are two ways to do that: either use the ASCII value
> for the quote, or double the quote (one quote in a string is considered
> a string delimiter, two consecutive quotes are considered one quote as
> part of the string):
> --Update the various flags on the company basic table
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ''C'',
> year_end_date = null,
> --Other fields being set to Null
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> or
> --Update the various flags on the company basic table
> SET @.sql = 'UPDATEcompany_basic
> SETreport_status = null,
> --Other fields being set to null
> verify_svy_urn = null,
> pay_or_contact = ' +CHAR(39)+ 'C' +CHAR(39)+ ',
> year_end_date = null,
> --Other fields being set to Null
> WHEREcompany_code IN ' + @.codes
> EXEC (@.sql)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>