SQL Batch Query Execution Problem

Question

0.00/5 (No votes)

See more:

Daer Friends,

I am running a batch file in SQL from front end in C#.Net 2.0 where i am checking that whether the column exists in temporary table or not.

If not exists then create it. But the condition doesn't true in any case.

Don't know i am writing the check condition for column in temporary table wrong or i should try a different way around.

SQL

SET @DQuery = 'IF NOT EXISTS (SELECT col.name FROM tempdb.sys.objects obj INNER JOIN tempdb.sys.columns col ON obj.object_id = col.object_id WHERE col.name = ''child_FG'+ CAST(@INTCOUNT AS VARCHAR) +''' AND obj.name LIKE ''#tData%'')'
SET @DQuery = @DQuery + ' BEGIN ' 
SET @DQuery = @DQuery + ' ALTER TABLE #tData ADD child_FG'+ CAST(@INTCOUNT AS VARCHAR) +' varchar(26) ' 
SET @DQuery = @DQuery + ' ALTER TABLE #tData ADD child_CKT'+ CAST(@INTCOUNT AS VARCHAR) +' decimal(18,4) ' 
SET @DQuery = @DQuery + ' END' 
strSQL += " execute(@DQuery)";
SET @DQuery = ' UPDATE #tData SET #tData.child_FG'+ CAST(@INTCOUNT AS VARCHAR) +' = '''+ @CHILDFG +''' WHERE #tData.[fg Code] = '''+ @InnerBOMID +''''
execute(@DQuery)
SET @DQuery = ' UPDATE #tData SET child_CKT'+ CAST(@INTCOUNT AS VARCHAR) +' = '+ CAST(@CHILD_CKT AS VARCHAR) +' WHERE #tData.[fg Code] = '''+ @InnerBOMID +''''";
execute(@DQuery)

Here i get the error as:-

a). "Invalid Column Name child_FG1".
b). "Invalid Column Name child_FG2".

The above mentioned queries are running within a cursor.

If i am running this in SQL Server 2008 then i get the result but in SQL server 2005 the above mentioned query throws error (as mentioned).

Kindy help.

Regards

Posted 13-Mar-15 0:33am

Varun Sareen

Updated 13-Mar-15 0:43am

v3

Add a Solution

Comments

Saral S Stalin 13-Mar-15 6:52am

You are not executing the add column script I think. I do not see an execution statement for this in you program.
strSQL += " execute(@DQuery)";
line seems fishy. Please check once

CHill60 13-Mar-15 7:37am

As @Saral S Stalin has pointed out, you have a problem with the code posted here - it won't run at all.
It appears that you've tried to show us the queries but have included some of the C# code that you are using to create them.
I suggest running the queries one by one directly against the database so that you can at least work out which of the 3 is throwing the error

CHill60 13-Mar-15 7:43am

If you change the query to look at sysobjects instead of sys.objects does it work in 2005?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Varun Sareen · Accepted Answer · 2015-03-13T02:09:00

Solution i found:-

I was checking the table in temporary database with table name which was creating ambiguity as the table might be created in a different session so i changed the query to

SQL

SET @DQuery = 'IF NOT EXISTS (SELECT col.name FROM tempdb.sys.objects obj INNER JOIN tempdb.sys.columns col ON obj.object_id = col.object_id WHERE col.name = ''child_FG'+ CAST(@INTCOUNT AS VARCHAR) +''' 
AND OBJECT_ID(''tempdb..#tdata'') = obj.object_id)'
<pre lang="SQL">

and it solved my problem as the object id for any table (temporary or physical) will be unique.

Thanks for your time friends.