Am I missing something here? Query works in SSMS, not inside function.

[gmmastros]

The parameter is varchar(20). Is it possible you are calling this with a userid longer than 20 characters?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheBugSlayer]

Hey G, the user ID is 8 characters long. Weird. InternalID is declared as VARCHAR(MAX) in the User table.

Strangely enough though, the program runs fine in Prod. But does not in Dev. So I suspect it is a data issue, even though I refreshed Dev from Prod not long ago...The SP that calls these UDFs fails with a "String or binary data would be truncated." error. I have narrowed it down to this one function but can't get to see what is wrong. Still looking.

Thanks.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[jbenson001]

You are probably getting an InternalID that is > 20 chars. That would cause the truncation issue when trying to insert into the table that has InternalID defined as VarChar(20)

 

[TheBugSlayer]

I am stepping through the code in Visual Studio 2010. What puzzles me is that the line

SELECT @intID = InternalID FROM [User] WHERE UserID='myuserID'

returns a value in SSMS, but does not inside the code. My user ID is 8 characters long and my internal ID is only 3 characters long. I guess I need lunch...maybe my muse will come back. Thanks and later.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[gmmastros]

Is it possible that there are multiple tables named [User], but with different schemas?

Select * From Information_Schema.Table Where Table_Name = 'User'

If the above query returns multiple rows, this may be your problem. Again... just spit ballin' ideas atcha.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom