InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 23 May 2013 11:59:23 GMT20http://www.sqlservercentral.com/Forums/Topic1410163-391-1.aspxNo 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:[code]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';[/code]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:[code]declare @MyMoney moneyset @MyMoney = 'asdf' + 10[/code]To use dynamic sql you have to build your sql string and then execute it.Something more like this.[code]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[/code]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.[code]DECLARE @dbname VARCHAR(50);DECLARE @SQL NVARCHAR(MAX) = '';declare @Assets moneyDECLARE dbloop CURSOR FOR SELECT NAME FROM sys.databases WHERE NAME NOT IN ('master','model','msdb','tempdb') AND State_desc = 'ONLINE'OPEN dbloopFETCH NEXT FROM dbloop INTO @dbnameWHILE (@@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 ENDCLOSE dbloopDEALLOCATE dbloop[/code]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. Tue, 22 Jan 2013 12:57:55 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1410163-391-1.aspxIs 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)Tue, 22 Jan 2013 12:29:05 GMTsqlbi.vvamsihttp://www.sqlservercentral.com/Forums/Topic1410163-391-1.aspx[quote][b]prathibha_aviator (1/22/2013)[/b][hr]Sean, Have a look at my complete code[/quote]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.Tue, 22 Jan 2013 12:20:35 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1410163-391-1.aspx[quote][b]prathibha_aviator (1/22/2013)[/b][hr][quote][b]The culprit is here.CONVERT(varchar(20),+ 'pol_id)'[/quote]Exactly, it says the same.. But I couldn't find where it is.. Am i supposed to write the whole convert function dynamically???[/quote]Yes, you can't write part of a function. The syntax is all messed up on that.Tue, 22 Jan 2013 12:09:17 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1410163-391-1.aspx[quote][b]The culprit is here.CONVERT(varchar(20),+ 'pol_id)'[/quote]Exactly, it says the same.. But I couldn't find where it is.. Am i supposed to write the whole convert function dynamically???Tue, 22 Jan 2013 11:15:23 GMTprathibha_aviatorhttp://www.sqlservercentral.com/Forums/Topic1410163-391-1.aspxSean, Have a look at my complete codeUSE 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 Endfetch next from @dbloop into @dbnameENDCLOSE @dbloopdeallocate @dbloopTue, 22 Jan 2013 11:12:10 GMTprathibha_aviatorhttp://www.sqlservercentral.com/Forums/Topic1410163-391-1.aspxTalk 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.[code]declare @OrgName varchar(max), @OrgID int, @dbname varchar(50)[/code]Then just start removing stuff from your code to isolate the issue.The culprit is here.[code]CONVERT(varchar(20),+ 'pol_id)'[/code]That will get rid of the syntax error but...[code]Actually this script is a part of my program that uses cursors concept[/code]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.Tue, 22 Jan 2013 11:05:43 GMTSean Langehttp://www.sqlservercentral.com/Forums/Topic1410163-391-1.aspx[quote][b]prathibha_aviator (1/22/2013)[/b][hr]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 conceptError:Msg 102, Level 15, State 1, Line 53Incorrect syntax near ';'.[/quote]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!Tue, 22 Jan 2013 11:03:10 GMTDave62http://www.sqlservercentral.com/Forums/Topic1410163-391-1.aspxSET @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 conceptError:Msg 102, Level 15, State 1, Line 53Incorrect syntax near ';'.Tue, 22 Jan 2013 10:49:00 GMTprathibha_aviator