Must declare the scalar variable... ???

[Bernhard Wurm]

Hello togehter!

If I try to run the following Statement I get the following error:
INSERT INTO [Topic].[Confirmation]([Guid], [GuidOffer], [GuidTopicEngineer],
[Allowance], [GuidAccount], [Canceled], [LeadinText], [LeadoutText],
[Number], [CreationDate], [Date], [GuidCustomer], [GuidTopicAgent],
[GuidDealerAgent], [GuidDealerEngineer], [GuidDeliveryAddress],
[GuidInvoiceAddress], [GuidUser]) VALUES (@guid, @GuidOffer,
@GuidTopicEngineer, @Allowance, @GuidAccount, @Canceled, @LeadinText,
@LeadoutText, @Number, @CreationDate, @Date, @GuidCustomer, @GuidTopicAgent,
@GuidDealerAgent, @GuidDealerEngineer, @GuidDeliveryAddress,
@GuidInvoiceAddress, @GuidUser) (@GuidOffer:
8675b71f-3eac-4af3-a5ef-7411bb220de7, @GuidTopicEngineer: NULL, @Allowance:
0, @GuidAccount: afa3d8c3-8a21-4c0f-86ac-3702a42590e2, @Canceled: False,
@LeadinText: Hier steht der Einleitungstext, @LeadoutText: <p>Hier der
Abschlusstext</p>, @Number: 2006AB0001, @CreationDate: 14.02.2006 09:24:38,
@Date: 14.02.2006 09:24:38, @GuidCustomer:
00e81a5d-10fe-43cb-9f1c-262f5fba74d7, @GuidTopicAgent:
25b4bf8f-01eb-4db0-a2ac-b91dd2d0f3df, @GuidDealerAgent:
0c928105-6aec-4621-a4ef-549098f94846, @GuidDealerEngineer:
bb802700-a657-47a7-9cdf-347564863025, @GuidDeliveryAddress:
5cb48263-4b69-47f1-a279-84418f251fd7, @GuidInvoiceAddress:
6a685229-4673-4fb1-a38a-2a8574ba9e7e, @GuidUser:
0c928105-6aec-4621-a4ef-549098f94846, @guid:
a9d8c5e3-13e8-47f2-bb9e-c8c8f6c86a17)

Error:
Must declare the scalar variable "@guid".



What the hell is wrong here?



Thank you

Bernhard

[Erland Sommarskog]

Post by Bernhard Wurm
Hello togehter!
INSERT INTO [Topic].[Confirmation]([Guid], [GuidOffer],
[GuidTopicEngineer],
[Allowance], [GuidAccount], [Canceled], [LeadinText], [LeadoutText],
[Number], [CreationDate], [Date], [GuidCustomer], [GuidTopicAgent],
[GuidDealerAgent], [GuidDealerEngineer], [GuidDeliveryAddress],
@GuidTopicEngineer, @Allowance, @GuidAccount, @Canceled, @LeadinText,
@LeadoutText, @Number, @CreationDate, @Date, @GuidCustomer,
@GuidTopicAgent,
@GuidDealerAgent, @GuidDealerEngineer, @GuidDeliveryAddress,
@LeadinText: Hier steht der Einleitungstext, @LeadoutText: <p>Hier der
a9d8c5e3-13e8-47f2-bb9e-c8c8f6c86a17)
What the hell is wrong here?

Did you declare the variable? And what is all that stuff that follows
the the VALUES clause? The part starting with "(@GuidOffer: ..."). It
certainly is not legal T-SQL.

[Bernhard Wurm]

So it isn't a legal statement, because it doesn't start with @Guid?
ok....
I moved the @guid parameter from the last position to the first, but it also
didn't work.
So the order of the parameters is equal. It doesn't work...

INSERT INTO [Topic].[Confirmation]([Guid], [GuidOffer], [GuidTopicEngineer],
[Allowance], [GuidAccount], [Canceled], [LeadinText], [LeadoutText],
[Number], [CreationDate], [Date], [GuidCustomer], [GuidTopicAgent],
[GuidDealerAgent], [GuidDealerEngineer], [GuidDeliveryAddress],
[GuidInvoiceAddress], [GuidUser])

VALUES (@guid, @GuidOffer, @GuidTopicEngineer, @Allowance, @GuidAccount,

@Canceled, @LeadinText, @LeadoutText, @Number, @CreationDate, @Date,

@GuidCustomer, @GuidTopicAgent, @GuidDealerAgent, @GuidDealerEngineer,

@GuidDeliveryAddress, @GuidInvoiceAddress, @GuidUser)

(@guid: a9d8c5e3-13e8-47f2-bb9e-c8c8f6c86a17,

@GuidOffer: 8675b71f-3eac-4af3-a5ef-7411bb220de7,

@GuidTopicEngineer: NULL,

@Allowance: 0,

@GuidAccount: afa3d8c3-8a21-4c0f-86ac-3702a42590e2,

@Canceled: False,

@LeadinText: Hier steht der Einleitungstext,

@LeadoutText: <p>Hier der Abschlusstext</p>,

@Number: 2006AB0001,

@CreationDate: 14.02.2006 09:24:38,

@Date: 14.02.2006 09:24:38,

@GuidCustomer: 00e81a5d-10fe-43cb-9f1c-262f5fba74d7,

@GuidTopicAgent: 25b4bf8f-01eb-4db0-a2ac-b91dd2d0f3df,

@GuidDealerAgent: 0c928105-6aec-4621-a4ef-549098f94846,

@GuidDealerEngineer: bb802700-a657-47a7-9cdf-347564863025,

@GuidDeliveryAddress: 5cb48263-4b69-47f1-a279-84418f251fd7,

@GuidInvoiceAddress: 6a685229-4673-4fb1-a38a-2a8574ba9e7e,

@GuidUser: 0c928105-6aec-4621-a4ef-549098f94846)

Post by Erland Sommarskog
Did you declare the variable? And what is all that stuff that follows
certainly is not legal T-SQL.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[ML]

Did you read my previous post?


ML

---
http://milambda.blogspot.com/

[ML]

If you're trying to execute this in MS SQL then you need to declare variables
*before* you use them, and assign values to them before you use them in an
INSERT statement.

E.g.:
declare @<variable name> <data type>

set @<variable name> = <value>

insert <table>
(
<column>
,...
)
values (
@<variable name>
,...
)


ML

---
http://milambda.blogspot.com/

[Bernhard Wurm]

Sorry, your response occured after I submitted. The refresh of the
newsreader was a little bit bad.

I allready have many statements like this:
SELECT [Guid] FROM [Topic].[Invoice] WHERE GuidCustomer = @guid AND
[GuidAccount] = @guidAccount (@guid: 00f80a5d-12de-43cb-9f1c-262f5fba74d7,
@guidAccount: bfb3d8c3-8a12-4c0f-86ac-3702a42590e2)

all work fine. only this one doesn't. So I don't really understand, why I
have to redeclare this varliable, because its declared in the parameterlist.

thx
Bernhard

Post by ML
If you're trying to execute this in MS SQL then you need to declare variables
*before* you use them, and assign values to them before you use them in an
INSERT statement.
insert <table>
(
<column>
,...
)
values (
@<variable name>
,...
)
ML
---
http://milambda.blogspot.com/

[ML]

What SQL product are you using?


ML

---
http://milambda.blogspot.com/

[Bernhard Wurm]

SQL Server 2005 Express

Post by ML
What SQL product are you using?
ML
---
http://milambda.blogspot.com/

[ML]

And this statement works in SQL 2005 Express?

SELECT [Guid] FROM [Topic].[Invoice] WHERE GuidCustomer = @guid AND
[GuidAccount] = @guidAccount (@guid: 00f80a5d-12de-43cb-9f1c-262f5fba74d7,
@guidAccount: bfb3d8c3-8a12-4c0f-86ac-3702a42590e2)

This is not T-SQL - in T-SQL all variables must be declared with the DECLARE
statement before they are used in queries.


ML

---
http://milambda.blogspot.com/

[Bernhard Wurm]

sure :-)
Either other statements like the posted one works fine! (Ok they arn't as
complex at some times :-) but they work fine. only this one which I posted
doesn't)

regards
Bernhard

Post by ML
And this statement works in SQL 2005 Express?
@guidAccount: bfb3d8c3-8a12-4c0f-86ac-3702a42590e2)
This is not T-SQL - in T-SQL all variables must be declared with the DECLARE
statement before they are used in queries.
ML
---
http://milambda.blogspot.com/

[ML]

You'll have to post more code. It's impossible to guess what else is there.

Also read this:
http://www.aspfaq.com/etiquette.asp?id=5006


ML

---
http://milambda.blogspot.com/

[Bernhard Wurm]

there is no more code :-)
this is the statement I want to execute.

It is generated and executed by an asp.net application.

Post by ML
You'll have to post more code. It's impossible to guess what else is there.
http://www.aspfaq.com/etiquette.asp?id=5006
ML
---
http://milambda.blogspot.com/

[ML]

Why haven't you mentioned that already? What application? Obviously this
application pre-processes the query text before sending it to the server.

Run SQL Profiler to trace the actual query being passed to the server, and
try debugging that. Or find support for this application you're using.
Basically, your post has very little to do with T-SQL.


ML

---
http://milambda.blogspot.com/

[Bernhard Wurm]

Stop.
I allready debugged the application and this was the sql statement, which
was created by the application an which created the exception.
It is the sql statement i postet, which creates an sql error. The other
statements (also generated by the application, which I posted all work fine)

Bernhard

Post by ML
Why haven't you mentioned that already? What application? Obviously this
application pre-processes the query text before sending it to the server.
Run SQL Profiler to trace the actual query being passed to the server, and
try debugging that. Or find support for this application you're using.
Basically, your post has very little to do with T-SQL.
ML
---
http://milambda.blogspot.com/

[Erland Sommarskog]

Post by Bernhard Wurm
Stop.
I allready debugged the application and this was the sql statement,
which was created by the application an which created the exception. It
is the sql statement i postet, which creates an sql error. The other
statements (also generated by the application, which I posted all work fine)

Then obviously there is something wrong with the application that generates
the statement.

The point is that if you just post some code created by your application
and exclaims "What the hell is wrong", but do not give information of
what you are doing, you are not going to get any good answers.

All we can tell you with the information you posted is that you need
to declare that variable. But how you actually do that, you probably
have more chances to get a good answer for in forum devoted to ASP .Net,
if that is the tool you use.

[Kalen Delaney]

Bernhard has already stated that the values are parameters, and supposedly
declared in the parameter list.

This mass of characters is almost impossible to analyze. It looks like you
have way more than what is needed for an INSERT.

An insert looks like this:

INSERT into <tablename> (list of columns)
VALUES (list of values)

So Bernhard, even assuming your variables were correctly defined as in the
parameter list, your statement should end must sooner:

INSERT INTO [Topic].[Confirmation]
([Guid], [GuidOffer], [GuidTopicEngineer],
[Allowance], [GuidAccount], [Canceled], [LeadinText], [LeadoutText],
[Number], [CreationDate], [Date], [GuidCustomer], [GuidTopicAgent],
[GuidDealerAgent], [GuidDealerEngineer], [GuidDeliveryAddress],
[GuidInvoiceAddress], [GuidUser])
VALUES (@guid, @GuidOffer,
@GuidTopicEngineer, @Allowance, @GuidAccount, @Canceled, @LeadinText,
@LeadoutText, @Number, @CreationDate, @Date, @GuidCustomer, @GuidTopicAgent,
@GuidDealerAgent, @GuidDealerEngineer, @GuidDeliveryAddress,
@GuidInvoiceAddress, @GuidUser)

The above is a TSQL statement. I have not bothered to count the values to
make sure they match in number with the columns, and without the procedure
header or table DDL I certainly can't verify datatypes.

But Bernhard, your code then goes on and has all this:
(@GuidOffer:
8675b71f-3eac-4af3-a5ef-7411bb220de7, @GuidTopicEngineer: NULL, @Allowance:
0, @GuidAccount: afa3d8c3-8a21-4c0f-86ac-3702a42590e2, @Canceled: False,
@LeadinText: Hier steht der Einleitungstext, @LeadoutText: <p>Hier der
Abschlusstext</p>, @Number: 2006AB0001, @CreationDate: 14.02.2006 09:24:38,
@Date: 14.02.2006 09:24:38, @GuidCustomer:
00e81a5d-10fe-43cb-9f1c-262f5fba74d7, @GuidTopicAgent:
25b4bf8f-01eb-4db0-a2ac-b91dd2d0f3df, @GuidDealerAgent:
0c928105-6aec-4621-a4ef-549098f94846, @GuidDealerEngineer:
bb802700-a657-47a7-9cdf-347564863025, @GuidDeliveryAddress:
5cb48263-4b69-47f1-a279-84418f251fd7, @GuidInvoiceAddress:
6a685229-4673-4fb1-a38a-2a8574ba9e7e, @GuidUser:
0c928105-6aec-4621-a4ef-549098f94846, @guid:
a9d8c5e3-13e8-47f2-bb9e-c8c8f6c86a17)

This is NOT TSQL code; it may be something the app is doing, but SQL Server
will choke on it.
Please use Profiler, as someone else suggested, to see exactly what the app
is sending to SQL Server.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com

Post by Erland Sommarskog

Post by Bernhard Wurm
Stop.
I allready debugged the application and this was the sql statement,
which was created by the application an which created the exception. It
is the sql statement i postet, which creates an sql error. The other
statements (also generated by the application, which I posted all work fine)

Then obviously there is something wrong with the application that generates
the statement.
The point is that if you just post some code created by your application
and exclaims "What the hell is wrong", but do not give information of
what you are doing, you are not going to get any good answers.
All we can tell you with the information you posted is that you need
to declare that variable. But how you actually do that, you probably
have more chances to get a good answer for in forum devoted to ASP .Net,
if that is the tool you use.
--
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx