August 13, 2006 at 1:45 pm
I keep getting the message
Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'declare'.
Msg 156, Level 15, State 1, Line 4
Incorrect syntax near the keyword 'declare'.
What am I doing wrong?
declare @dbname varchar(8000),
declare
@countyname varchar (200) ,
declare
@sql varchar(8000)
declare
county_name cursor for
select distinct county from Zipcodes
open
county_name
fetch
next from county_name
into
@countyname
declare
dbname_name cursor for
select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'
open
dbname_name
fetch
next from dbname_name
into
@dbname
WHILE
@@FETCH_STATUS = 0
BEGIN
set @sql =
'
select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,@dbname ,(select @@servername) as servername, county'
+@countyname+'
from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'
exec (@sql)
end
set
@sql = ''
fetch
next from dbname_name into @dbname
fetch
next from county_name into @countyname
CLOSE
county_name
DEALLOCATE
county_name
CLOSE
dbname_name
DEALLOCATE
dbname_name
August 13, 2006 at 2:39 pm
Remove the commas....
declare @dbname varchar(8000)
declare @countyname varchar (200)
declare @sql varchar(8000)
--Jeff Moden
Change is inevitable... Change for the better is not.
August 13, 2006 at 2:45 pm
I tried that and receive these errors
Msg 137, Level 15, State 2, Line 3
Must declare the scalar variable "@dbname".
Msg 156, Level 15, State 1, Line 3
August 13, 2006 at 3:28 pm
You have @dbname inside of your dynamic script.
I don't see where you have it declared in this script.
_____________
Code for TallyGenerator
August 13, 2006 at 3:37 pm
And I better kep silence about the rest of your code.
I can only guarantee that you could not pass even entry level test on any programming course presenting this kind of code.
Programming language does not matter.
_____________
Code for TallyGenerator
August 13, 2006 at 4:01 pm
Your right.. I'm learning..
Any tips?? Or to many to mention?
I modified the code, but still get the same error.
August 13, 2006 at 5:34 pm
Yes, too many to mention.
Start from the flow. Where do you enter the loop, what will get you out of there?
Then why do you need 2 cursors?
Status of which one do you check with @@FETCH_STATUS ?
More to follow.
_____________
Code for TallyGenerator
August 13, 2006 at 10:32 pm
SG,
Normally, a couple of us would answer the question of "What am I doing wrong?" with "You're using a cursor".
Rather than trying to salvage this code, I think it would be beneficial to you if you posted "what" you are trying to do without indicating "how". And, you need to post a little info about your environment... a table schema here and there... a sprinkling of data from each... and information like did they really put the screws to you by putting this information in more than one database?
--Jeff Moden
Change is inevitable... Change for the better is not.
August 14, 2006 at 8:42 am
Hello SG,
To correct your specific error: Must declare the scalar variable "@dbname",
change your code as shown below.
------------------------------------------------
set @sql =
'select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,'''+@dbname+
''' ,(select @@servername) as servername
, ''' + @countyname + ''' AS county
from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'
exec (@sql)
------------------------------------------------
However, as the other responders have said, this looks like the long way around whatever it is you're trying really trying to do! I took the liberty of formatting your SQL to make it easier for me to read:
================================================
declare @dbname varchar(8000)
declare @countyname varchar (200)
declare @sql varchar(8000)
declare county_name cursor for
select distinct county from Zipcodes
open county_name
fetch next from county_name
into @countyname
declare dbname_name cursor for
select name from sys.databases where name like 'Property%' and name <> 'PropertyCenter'
open dbname_name
fetch next from dbname_name
into @dbname
WHILE @@FETCH_STATUS = 0
BEGIN
set @sql =
'select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,'+@dbname+
' ,(select @@servername) as servername, county'+@countyname+
'from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip'
exec (@sql)
end
set @sql = ''
fetch next from dbname_name into @dbname
fetch next from county_name into @countyname
CLOSE county_name
DEALLOCATE county_name
CLOSE dbname_name
DEALLOCATE dbname_name
================================================
The two nested cursors look like they run through for every combination of
- distinct county from the zipcode table,
- and dbname from sys.databases where name like 'Property%' and name <> 'PropertyCenter'
You can get that in one "cartesian join" like this:
Bob's Sub-query #1:
===================
SELECT zz.county, dd.name
FROM (SELECT distinct county from zipcodes) AS zz
, sys.databases AS dd
WHERE dd.name like 'Property%' and dd.name <> 'PropertyCenter'
Now, the dynamic SQL looks like you want to run it for each row in the result set from sub-query #1. You might be able to do it like this:
SELECT p.sa_property_id
, z.zipcode as sa_site_zip
, z.state as sa_site_state
, z.city as sa_site_city
, z.county as sa_site_county
, subq.name
,(select @@servername) as servername
, z.county
from (zipcodes z
join (
SELECT zz.county, dd.name
FROM (SELECT distinct county from zipcodes) AS zz
, sys.databases AS dd
WHERE dd.name like 'Property%' and dd.name <> 'PropertyCenter'
) as subq ON z.county = subq.county)
join tbl_reply_assr_final p
on z.zipcode = p.sa_site_zip
Anyway, that's a shot at it. Good luck...
Regards,
Bob Monahon
August 14, 2006 at 8:45 am
Try to do this as follows
Exec(' select p.sa_property_id, z.zipcode as sa_site_zip, z.state as sa_site_state, z.city as sa_site_city, z.county as sa_site_county,'''+@dbname +''','''+@@servername +''' as servername, county'''+@countyname+''' from zipcodes z join tbl_reply_assr_final p on z.zipcode = p.sa_site_zip' )
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply