Help with Dynamic SQL error

Question

Post reply

Help with Dynamic SQL error

Jeffery Williams

SSCertifiable

Points: 6441
More actions
January 15, 2008 at 7:19 am

#378019

Ok. I have a piece of Dynamic SQL that WAS working until I added one more variable.
Here is the SQL that is throwing the error:
Error converting data type varchar to bigint.
****************************************************
SET@ObjectString =
'INSERT INTO SYS_Object
(MSGID, SQLID, ObjectName, ObjectTypeID, ModuleID, isActive, CreateDate, DBID)
SELECT ' + @MSGID + ', [ID], [Name], 1, ' + @ModuleID + ', 1, CrDate, ' + @SQLID + '
FROM '+ @DBName + '..SYSObjects
WHERE [ID] NOT IN (SELECT SQLID FROM SYS_Object)
AND xType = ''P'' AND Category = ''0'''
*********************************************************
If I remove the last variable (the one added that breaks everything) the code works.
The last variable being (DBID in the Insert list) and @SQLID in the values list.
This Variable @SQLID is a big int in the DB and declared as such.
Anyone have any ideas?
<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 1

It is because of datatype precedence. Try casting the variables to varchars like:

SET @ObjectString = 'INSERT INTO SYS_Object

(MSGID, SQLID, ObjectName, ObjectTypeID, ModuleID, isActive, CreateDate, DBID)

SELECT ' + cast(@MSGID as varchar(100)) + ', [ID], [Name], 1, '

+ cast(@ModuleID as varchar(100)) + ', 1, CrDate, '

+ cast(@SQLID as varchar) + '

FROM ' + cast(@DBName as varchar(100)) + '..SYSObjects

WHERE [ID] NOT IN (SELECT SQLID FROM SYS_Object)

AND xType = ''P'' AND Category = ''0'''

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 2

STUPID mistake! 🙂 Thank you.

Actually I did not need to cast, all of the variables ARE varchar. I thought that ModuleID was a int however it is a varchar.

I simple changed the @SQLID from a declared int to a varchar and all is well.

Thanks a bunch.... Maybe it is time to push away from the desk for 15 and come back?

Thanks again.

Jeff

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 3

I call that "changing the bag on the coffee infusion"....:w00t:

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 4

🙂

Hey one more question for you if you don't mind.

I am writing an automated route that I do not want to run (will be scheduled as a job) unless the server is not under a significant load.

In other words one of the first things I would like to do at execution is check server load. If it is too high I want to exit and try again later.

Any ideas on what to check and what a good threshold value might be?

Thanks

Jeff

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 5

Jeffery Williams (1/15/2008)
🙂
Hey one more question for you if you don't mind.
I am writing an automated route that I do not want to run (will be scheduled as a job) unless the server is not under a significant load.
In other words one of the first things I would like to do at execution is check server load. If it is too high I want to exit and try again later.
Any ideas on what to check and what a good threshold value might be?
Thanks
Jeff

You seem to be hijacking your own thread 🙂 It may be worth to post this question as a new topic. Anyway, on SQL Server 2005 there are some system views and functions that are referred to as dynamic management objects. You can get a lot of system related information. Depends on what you mean by high load, you may want to look at sys.dm_exec_... views or the sys.dm_os_performance_counters

Regards,

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Jeffery Williams SSCertifiable Points: 6441 More actions · Answer 6

LOL Yeah I guess I did hijack

I figured while I had your ear I would avoid a whole new thread. Then again someone else may find the information useful as well..

Thanks, have a great day.

<hr noshade size=1 width=250 color=#BBC8E5> Regards,Jeffery Williams http://www.linkedin.com/in/jwilliamsoh

Andras Belokosztolszki SSC-Insane Points: 22031 More actions · Answer 7

Jeffery Williams (1/15/2008)
LOL Yeah I guess I did hijack
I figured while I had your ear I would avoid a whole new thread. Then again someone else may find the information useful as well..
Thanks, have a great day.

🙂

The forum has an excellent feature that allows people to see the most recently posted messages. No matter which thread they were posted in, so questions rarely remain unanswered. Hope you find the info useful.

Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software