January 22, 2013 at 10:49 am
SET @OrgName = N'SELECT ORG_ID ' + CONVERT(varchar(10), @orgid) +' '+ '[Description]' + ' ' +'('+ CONVERT(varchar(20),+ 'pol_id)'
+')'+ 'from'+ @dbname + '.dbo.Organization'+ 'where OrganizationID =' + @orgid;
Above is the dynamic query i wrote in my Sql mgmt studio and i ran it to get the following error. Actually this script is a part of my program that uses cursors concept
Error:
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near ';'.
--Pra:-):-)--------------------------------------------------------------------------------
January 22, 2013 at 11:03 am
prathibha_aviator (1/22/2013)
SET @OrgName = N'SELECT ORG_ID ' + CONVERT(varchar(10), @orgid) +' '+ '[Description]' + ' ' +'('+ CONVERT(varchar(20),+ 'pol_id)'+')'+ 'from'+ @dbname + '.dbo.Organization'+ 'where OrganizationID =' + @orgid;
Above is the dynamic i wrote in my Sql mgmt studio and i ran it to get the following error. Actually this script is a part of my program that uses cursors concept
Error:
Msg 102, Level 15, State 1, Line 53
Incorrect syntax near ';'.
I haven't actually tried this in SSMS so my answer may be off. The first thing I would try is to convert the @orgid at the end to a varchar as was done earlier. There may be a problem concatenating it at the end of the string without converting.
Good luck!
January 22, 2013 at 11:05 am
Talk about sparse details. It would be helpful if in the future you could provide more details. At the very least declare the variables.
Once I declared the variables so I could start looking it was pretty obvious.
declare @OrgName varchar(max), @OrgID int, @dbname varchar(50)
Then just start removing stuff from your code to isolate the issue.
The culprit is here.
CONVERT(varchar(20),+ 'pol_id)'
That will get rid of the syntax error but...
Actually this script is a part of my program that uses cursors concept
Cursors are horribly slow and should be used in certain situations. I can't tell from your snippet but I doubt that what you doing here is one of those situations. If you want to really improve your code, take a look at the first link in my signature about best practices, post ddl and sample data along with desired output and we can help you get rid of that nasty cursor.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 22, 2013 at 11:12 am
Sean, Have a look at my complete code
USE msdb
declare @dbname varchar(50);
declare @sql nvarchar(MAX)='';
declare @AsOfDate datetime = convert(datetime, '12/31/2012');
declare @orgid int = 0;
declare @assets money = 0.00;
declare @liabilities money = 0.00;
declare @netassets money = 0.00;
declare @income money = 0.00;
declare @expense money = 0.00;
declare @dedicated money = 0.00;
declare @Off money = 0.00;
declare @OrgName varchar(50);
DECLARE @dbloop CURSOR;
SET @dbloop= CURSOR
FOR
select name from sys.databases where name NOT IN ('master','model','msdb','tempdb')
AND State_desc = 'ONLINE'
DECLARE Parishes_CURSOR CURSOR
FAST_FORWARD
READ_ONLY
FOR
select OrganizationID from Organization where IsHostOrganization <> 1
OPEN @dbloop
fetch next from @dbloop into @dbname
while (@@FETCH_STATUS <>0)
BEGIN
IF OBJECT_ID(@DBName + N'.dbo.Account') IS NOT NULL
BEGIN
OPEN Parishes_CURSOR
FETCH NEXT FROM Parishes_CURSOR INTO @OrgID
WHILE (@@FETCH_STATUS <> -1)
BEGIN
IF (@@FETCH_STATUS <> -2)
BEGIN
IF ( @@FETCH_STATUS <> 0 )
BREAK
SET @OrgName = N'SELECT ORG_ID ' + CONVERT(varchar(10), @orgid) +' '+ '[Description]' + ' ' +'('+ CONVERT(varchar(20),+ 'pol_id)'
+')'+ 'from'+ @dbname + '.dbo.Organization'+ 'where'+ 'OrganizationID' +'='+ orgid;
drop table #AcctBal;
create table #AcctBal(AccountID int, Balance money);
insert #AcctBal(AccountID, Balance)
exec resultset_AccountEndingBalance @orgid,@AsOfDate;
SET @assets= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'
+' where'+' Account.AccountType'+ '=' +1+ 'and ' +'RecordStatus'+ '='+ '1';
SET @liabilities = N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account'+ 'ON' +'#AcctBal.AccountID'+' =' +' Account.AccountID'
+' where'+' Account.AccountType'+ '=' +2+ 'and ' +'RecordStatus'+ '='+ '1';
SET @netassets= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'
+' where'+' Account.AccountType'+ '=' +3+ 'and ' +'RecordStatus'+ '='+ '1';
SET @income= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'
+' where'+' Account.AccountType'+ '=' +4+ 'and ' +'RecordStatus'+ '='+ '1';
SET @expense= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'
+' where'+' Account.AccountType'+ '=' +5+ 'and ' +'RecordStatus'+ '='+ '1';
SET @dedicated= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'
+' where'+' Account.AccountType'+ '=' +6+ 'and ' +'RecordStatus'+ '='+ '1';
if (@dedicated is null)
set @dedicated = 0.00;
--print 'Assets ' + Convert(varchar(20), @assets);
--print 'Liabilities ' + Convert(varchar(20), @liabilities);
--print 'Net Assets ' + Convert(varchar(20), @netassets);
--print 'Income ' + Convert(varchar(20), @income);
--print 'Expense ' + Convert(varchar(20), @expense);
--print 'Current Period Change ' + Convert(varchar(20), (@income - @expense));
--print 'Dedicated ' + Convert(varchar(20), @dedicated);
set @Off = @assets - (@liabilities + @netassets + @dedicated + (@income - @expense));
if (@Off <> 0.00)
SET @sql= @sql + @OrgName + ' is out of balance by ' + convert(varchar(30), @Off) + CHAR(13) + CHAR(10) ;
print @sql;
END
FETCH NEXT FROM Parishes_CURSOR INTO @OrgID
END
CLOSE Parishes_CURSOR
DEALLOCATE Parishes_CURSOR
End
fetch next from @dbloop into @dbname
END
CLOSE @dbloop
deallocate @dbloop
--Pra:-):-)--------------------------------------------------------------------------------
January 22, 2013 at 11:15 am
The culprit is here.
CONVERT(varchar(20),+ 'pol_id)'
Exactly, it says the same.. But I couldn't find where it is.. Am i supposed to write the whole convert function dynamically???
--Pra:-):-)--------------------------------------------------------------------------------
January 22, 2013 at 12:09 pm
prathibha_aviator (1/22/2013)
The culprit is here.
CONVERT(varchar(20),+ 'pol_id)'
Exactly, it says the same.. But I couldn't find where it is.. Am i supposed to write the whole convert function dynamically???
Yes, you can't write part of a function. The syntax is all messed up on that.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 22, 2013 at 12:20 pm
prathibha_aviator (1/22/2013)
Sean, Have a look at my complete code
I am going to guess that you didn't read that article. It certainly is somewhat of a help to see all of the code but without any table definitions it is pretty hard to do much here.
You do probably have justification for using a cursor since you are looping through a series of databases. It is generally not a good idea to stick business logic in MSDB which you done here. I would recommend a database for this type of thing instead of tacking stuff onto the side of MSDB. Create a Common, or Utilities or something like that.
Also you can greatly streamline this by not using a separate query for each piece of aggregate data.
I will write up a simpler version of this in a bit and see what you think.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 22, 2013 at 12:29 pm
Is this what you wanted, I doubt you'd need ORG_ID value twice there..
SET @OrgName = 'SELECT ORG_ID+''' + CONVERT(varchar(10), @orgid) +''' '+ '+[Description]+' + ' ' +'''('''+ + '+CONVERT(varchar(20),pol_id)'
+'+'')'''+ ' from '+ @dbname + '.dbo.Organization'+ ' where OrganizationID =' + convert(varchar(10),@orgid)
January 22, 2013 at 12:57 pm
No offense but this code is a disaster. You have 2 cursors and the declarations are completely different, the check for fetch_status are overly complicated and not documented. Do you know what a fetch_status of -1 means? How about -2? Me neither. Does it really matter? Anything other than 0 means it failed and your code bails out.
What does resultset_AccountEndingBalance do?
You have tons of dynamic sql generated but you never execute any of it, as a result all of your variables will always be NULL. You are assigning your dynamic sql directly to a variable. Even if you got past all the current syntax errors you would have all sorts of other issues.
consider the following:
declare @assets money = 0.00;
SET @assets= N'select ' + 'SUM'+'('+'isnull'+'('+'Balance'+','+ 0+')'+')'+' from' + '#AcctBal' +'INNER '+ 'JOIN'+ @dbname+'.dbo.Account' +'ON' +'#AcctBal.AccountID '+'='+ 'Account.AccountID'
+' where'+' Account.AccountType'+ '=' +1+ 'and ' +'RecordStatus'+ '='+ '1';
Here you have declared a money datatype and are attempting to assign it a string value. Of course the string assignment won't work anyway because you have made it far more difficult on yourself than you need to and have tried to add a string and an integer and assign it to a money datatype.
What you have there is something like this:
declare @MyMoney money
set @MyMoney = 'asdf' + 10
To use dynamic sql you have to build your sql string and then execute it.
Something more like this.
SET @sql = N'select SUM(isnull(Balance, 0)) from #AcctBal INNER JOIN ' + @dbname + '.dbo.Account ON #AcctBal.AccountID = Account.AccountID where Account.AccountType = 1 and RecordStatus = 1';
select @sql
I put together a VERY simplified version of your code that should illustrate how to put this together. I removed most of the stuff in there because it is all way off target.
DECLARE @dbname VARCHAR(50);
DECLARE @sql NVARCHAR(MAX) = '';
declare @Assets money
DECLARE dbloop CURSOR FOR
SELECT NAME
FROM sys.databases
WHERE NAME NOT IN ('master','model','msdb','tempdb')
AND State_desc = 'ONLINE'
OPEN dbloop
FETCH NEXT FROM dbloop INTO @dbname
WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @sql = N'select @_Assets = SUM(isnull(Balance, 0)) from #AcctBal INNER JOIN ' + @dbname + '.dbo.Account ON #AcctBal.AccountID = Account.AccountID where Account.AccountType = 1 and RecordStatus = 1';
exec sp_executesql @sql, N'@_Assets money OUTPUT', @_Assets = @Assets OUTPUT
select @Assets as Assets, @dbname as DBName
FETCH NEXT FROM dbloop INTO @dbname
END
CLOSE dbloop
DEALLOCATE dbloop
Now as I said before you really need to not use MSDB to hold your table and all the other things you are using here. You need to step back and rethink what you are trying to do here. Read up on dynamic sql.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply