August 11, 2015 at 3:07 pm
WHILE @@FETCH_STATUS = 0
BEGIN
FETCH NEXT FROM C_tables INTO
@ColName
,@ColOrdPosit
,@TabSchema
,@TabName
,@ColDataType
,@ColDefaultVal
;
PRINT @ColName + ' ' +STR(@ColOrdPosit) + ' ' +@TabSchema + ' ' +@TabName + ' ' + @ColDataType + ' ' + @ColDefaultVal
END
Nothing shows up in Messages.
If I print just @ColName, the column name appears.
WTF ?
August 11, 2015 at 3:11 pm
mar.ko (8/11/2015)
WHILE @@FETCH_STATUS = 0BEGIN
FETCH NEXT FROM C_tables INTO
@ColName
,@ColOrdPosit
,@TabSchema
,@TabName
,@ColDataType
,@ColDefaultVal
;
PRINT @ColName + ' ' +STR(@ColOrdPosit) + ' ' +@TabSchema + ' ' +@TabName + ' ' + @ColDataType + ' ' + @ColDefaultVal
END
Nothing shows up in Messages.
If I print just @ColName, the column name appears.
WTF ?
What about all the other variables? If just one of them has a null value the entire concatenated string will be null.
August 11, 2015 at 3:20 pm
mar.ko (8/11/2015)
WHILE @@FETCH_STATUS = 0BEGIN
FETCH NEXT FROM C_tables INTO
@ColName
,@ColOrdPosit
,@TabSchema
,@TabName
,@ColDataType
,@ColDefaultVal
;
PRINT @ColName + ' ' +STR(@ColOrdPosit) + ' ' +@TabSchema + ' ' +@TabName + ' ' + @ColDataType + ' ' + @ColDefaultVal
END
Nothing shows up in Messages.
If I print just @ColName, the column name appears.
WTF ?
Well WTF is my response too, how on earth can on unravel this mess without any information on what declares and populates those variables?
😎
August 11, 2015 at 3:22 pm
Eirikur Eiriksson (8/11/2015)
mar.ko (8/11/2015)
WHILE @@FETCH_STATUS = 0BEGIN
FETCH NEXT FROM C_tables INTO
@ColName
,@ColOrdPosit
,@TabSchema
,@TabName
,@ColDataType
,@ColDefaultVal
;
PRINT @ColName + ' ' +STR(@ColOrdPosit) + ' ' +@TabSchema + ' ' +@TabName + ' ' + @ColDataType + ' ' + @ColDefaultVal
END
Nothing shows up in Messages.
If I print just @ColName, the column name appears.
WTF ?
Well WTF is my response too, how on earth can on unravel this mess without any information on what declares and populates those variables?
😎
Not that hard. I agree with Lynn. There's likely a NULL that's messing up the concatenation.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2015 at 3:32 pm
Alvin Ramard (8/11/2015)
Eirikur Eiriksson (8/11/2015)
mar.ko (8/11/2015)
WHILE @@FETCH_STATUS = 0BEGIN
FETCH NEXT FROM C_tables INTO
@ColName
,@ColOrdPosit
,@TabSchema
,@TabName
,@ColDataType
,@ColDefaultVal
;
PRINT @ColName + ' ' +STR(@ColOrdPosit) + ' ' +@TabSchema + ' ' +@TabName + ' ' + @ColDataType + ' ' + @ColDefaultVal
END
Nothing shows up in Messages.
If I print just @ColName, the column name appears.
WTF ?
Well WTF is my response too, how on earth can on unravel this mess without any information on what declares and populates those variables?
😎
Not that hard. I agree with Lynn. There's likely a NULL that's messing up the concatenation.
Agreed with Lynn and Alvin here. And, looking at just the names of those variables and making one humongous assumption, I'll bet that the last variable is the one with the issue.
And I also agree with Eirikur. You don't need to post something with that kind of WTF attitude, especially if you don't show us a bit more about your code. Remember that we're all volunteers here, and your attitude is just a quick way to get everyone to ignore you.
As posted, your code can't run to reproduce your issue. Please read the first link in my signature on how to post question, and follow that.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
August 11, 2015 at 3:35 pm
Absolutely agree. There is most likely a NULL value popping into one of the variables that is jacking it all up.
try to select the variable values (each and every one) and see what you get that way.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2015 at 3:36 pm
I thought he was just asking: "Wed, Thurs, Fri?" 😀
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2015 at 3:40 pm
Alvin Ramard (8/11/2015)
I thought he was just asking: "Wed, Thurs, Fri?" 😀
No. It means "Where's The Fire?"
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 11, 2015 at 3:44 pm
SQLRNNR (8/11/2015)
Alvin Ramard (8/11/2015)
I thought he was just asking: "Wed, Thurs, Fri?" 😀No. It means "Where's The Fire?"
Well, That's Fine.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 11, 2015 at 4:22 pm
I'm gonna take a leap and presume you are looping through all of the tables to get some basic information - like columns and defaults.
Not really sure what you would be doing with that info, but here is a query that would populate that data.
/* based on this infor from OP
@ColName
,@ColOrdPosit
,@TabSchema
,@TabName
,@ColDataType
,@ColDefaultVal
*/
DECLARE @tablename sysname = NULL
/* NULL for all tables and table name for a specific table */
, @isMSShipped TINYINT = NULL
/* null for all, 0 for user objects, 1 for ms objects */
;
SELECT SCHEMA_NAME(t.schema_id) AS TabSchema
,t.name AS TableName
, c.name AS ColumnName
, c.column_id AS ColOrdPosit
, dt.name AS DataType
, c.max_length AS ColLength
, c.precision AS Precision
, c.scale AS Scale
, dc.type_desc
,dc.definition
, c.user_type_id , dt.user_type_id , dt.system_type_id, c.system_type_id
FROM sys.tables t
INNER JOIN sys.columns c
ON c.object_id = t.object_id
INNER JOIN sys.types dt
ON c.user_type_id = dt.user_type_id
--For system data types, user_type_id = system_type_id
LEFT OUTER JOIN sys.default_constraints dc
ON c.object_id = dc.parent_object_id
AND c.column_id = dc.parent_column_id
WHERE t.is_ms_shipped = ISNULL(@isMSShipped,t.is_ms_shipped)
AND t.name = ISNULL(@tablename,t.name)
ORDER BY TableName;
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2015 at 8:58 am
Well guys, not one of you provided a solution.
There are two:
1) ISNULL(@ColDefaultVal,'-')
That variable was the one preventing the PRINT from executing.
2) RAISERROR ('ColDflt=%s',0,1,@ColDefaultVal);
This is even better as it indicates (null) for the variable.
It works just like PRINT with more flexibilty.
August 12, 2015 at 9:05 am
mar.ko (8/12/2015)
Well guys, not one of you provided a solution.
Two people explained to you why you were getting null in your print. Your question read as 'why is this happening', which was answered.
Feel free to give us more information and details to work with next time, and please remember that everyone posting here is a volunteer, is posting in their spare time and isn't obliged to give exactly the response you may be after.
p.s. Raiserror doesn't work exactly like print. There are some differences about when the message gets returned to the console. Print is allowed to be delayed but a Raiserror must return at the point it is specified.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 12, 2015 at 9:33 am
mar.ko (8/12/2015)
Well guys, not one of you provided a solution.There are two:
1) ISNULL(@ColDefaultVal,'-')
That variable was the one preventing the PRINT from executing.
2) RAISERROR ('ColDflt=%s',0,1,@ColDefaultVal);
This is even better as it indicates (null) for the variable.
It works just like PRINT with more flexibilty.
Correct, not one provided an answer - more than one did.
As for print and the flexibility, Print can be more flexible too. You just have to code for it.
PRINT 'ColDefaultVal = ' + convert(varchar,ISNULL(@ColDefaultVal,'NULL'))
But like I said previously, you can get similar by just selecting the variable values like this:
SELECT @ColName
,@ColOrdPosit
,@TabSchema
,@TabName
,@ColDataType
,@ColDefaultVal
If any is NULL, then it will return a NULL value.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
August 12, 2015 at 10:12 am
mar.ko (8/12/2015)
Well guys, not one of you provided a solution.There are two:
1) ISNULL(@ColDefaultVal,'-')
That variable was the one preventing the PRINT from executing.
2) RAISERROR ('ColDflt=%s',0,1,@ColDefaultVal);
This is even better as it indicates (null) for the variable.
It works just like PRINT with more flexibilty.
I'm sorry, but with the total lack of information from you I provided the best response I could. How about a little thought on your part, it would go a long way.
I guess we should add you to the Silver Spoon Society.
August 12, 2015 at 10:21 am
Let's keep this friendly.
Let's not antagonize.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply