September 24, 2015 at 10:51 am
Hi,
I am getting the following message when trying to run the below sql
Msg 245, Level 16, State 1, Line 15
Conversion failed when converting the varchar value 'Select COUNT(*) From Adventures.dbo.tblLibrary' to data type int.
Declare @Datestart as Date
Declare @CntBefore as int
Declare @CntNow as int
Declare @Database as varchar(30)
Declare @TableName as varchar(60)
Declare @Dbo as varchar(5)
Declare @Path as varchar(95)
Set @Database= 'Adventures'
Set @Dbo= '.dbo.'
Set @TableName = 'tblLibrary'
Set @Path= @Database+@Dbo+@TableName
Set @Datestart= '24-SEP-2015'
Set @CntBefore= (Select row_count From Library.dbo.tbleAudit where tablename = @TableName and convert(varchar,Report_Date,112) = @Datestart)
Set @CntNow= ('Select COUNT(*) From ') +@Path
Select case when @CntBefore = @CntNow Then 'Row Count The Same' Else 'Row Count Different' End as MessagePrint
Thanks
September 24, 2015 at 11:43 am
This:
Set @CntNow= ('Select COUNT(*) From ') +@Path
You're attempting to set the @CntNow value, which is defined as an integer in your variable declaration, to a string value. If you're attempting to set the variable to the COUNT, then you change the code to execute the statement, but don't try to set the integer in that way. Best approach would be to use it as an output parameter and use sp_executesql. Then you can capture the value and use it in the rest of your code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 24, 2015 at 2:10 pm
Hi,
Thanks for the reply, I'm not sure what to change now on my script?
Thanks
September 24, 2015 at 2:19 pm
SQL_Kills (9/24/2015)
Hi,Thanks for the reply, I'm not sure what to change now on my script?
Thanks
Edit... whoops, this is wrong.
New attempt:
SELECT @CntNow = COUNT(*) FROM Adventures.dbo.tblLibrary
If the table that you are querying really needs to be dynamic, then do something like this:
CREATE TABLE #tempCounts (Counter BIGINT);
DECLARE @SQLCMD varchar(max);
SET @SQLCMD = 'SELECT COUNT(*) FROM ' + QUOTENAME(@Database) + '.' + QUOTENAME(@Dbo) + '.' + @Tablename;
INSERT INTO #tempCounts (Counter)
EXECUTE (@SQLCMD);
SELECT @CntNow = Counter FROM #tempCounts;
Oh... you have @path populated with:
Set @Path= @Database+@Dbo+@TableName
this won't work... you need the "." between the different parts of the fully qualified name.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 24, 2015 at 3:24 pm
So my current script i have cannot be amended to work pon what I have on there now?
September 24, 2015 at 8:50 pm
SQL_Kills (9/24/2015)
So my current script i have cannot be amended to work pon what I have on there now?
Pretty much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 25, 2015 at 12:53 am
WayneS (9/24/2015)
this won't work... you need the "." between the different parts of the fully qualified name.
O/P has
Set @Dbo= '.dbo.'
but I agree it would be better to have the schema as 'dbo' (or whatever else it might become in the future) and use QuoteName() and concatenate '.' as you have it. There might, heaven forbid!, be a "." in the table or schema name ...
September 25, 2015 at 3:55 pm
So with my current script that I have found what actually needs to be changed for it to work as not sure now?
Thanks
September 25, 2015 at 9:27 pm
SQL_Kills (9/25/2015)
So with my current script that I have found what actually needs to be changed for it to work as not sure now?Thanks
Did you see the part of my answer that starts with "If the table that you are querying really needs to be dynamic, then do something like this"?
That's what you need to do. Feel free to modify it to use @Path instead of what I did for the DB/schema/table name (but I prefer my method).
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 25, 2015 at 9:28 pm
Kristen-173977 (9/25/2015)
WayneS (9/24/2015)
this won't work... you need the "." between the different parts of the fully qualified name.O/P has
Set @Dbo= '.dbo.'
but I agree it would be better to have the schema as 'dbo' (or whatever else it might become in the future) and use QuoteName() and concatenate '.' as you have it. There might, heaven forbid!, be a "." in the table or schema name ...
Whoops, good eyes there Kristen! Yeah, then @Path would work, but I still think using the quotename around the parts would be best. And it doesn't take a period one of those names to mess this up... just a space would do it also.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
September 26, 2015 at 1:54 am
WayneS (9/25/2015)
And it doesn't take a period one of those names to mess this up... just a space would do it also.
Very entertaining video of a presentation by Rob Volk about using weird characters in object names - such as part of the name using right-to-left extended characters :hehe:
September 26, 2015 at 8:11 am
Kristen-173977 (9/26/2015)
WayneS (9/25/2015)
And it doesn't take a period one of those names to mess this up... just a space would do it also.Very entertaining video of a presentation by Rob Volk about using weird characters in object names - such as part of the name using right-to-left extended characters :hehe:
This does look entertaining. I looked through the presentation and love the Star Trek references. The code looks pretty diabolical. I'll have to watch the full presentation when I have the time. Thanks for posting it, Kristen. 😉
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply