Problem with Link server from sql 2005 to sql 2005 - Openquery doesnt works

I have created a linked server on which following query works fine.

EXECUTE ('SELECT TOP 10 * FROM dummyOBJECTS') AT [REMOTE]

but the same statement executed with openquery

select * from openquery([remote],'select top 10 * from dummyObjects') returns following error.

Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "remote" supplied inconsistent metadata for a column. The column "dummyObjectID" (compile-time ordinal 1) of object "select top 10 * from dummyobjects" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.

Hi ck!

Could you remove all columns except dummyObjectID and run this again?

If this reproes, could you reply with a sequence of "CREATE TABLE" and "INSERT" statements that will allow me to repro this on my machine?

Problem with INSERT Statement.

I have an insert statement thats causing me some issues.
Dim strname As String
Dim myname As String
myname = My.User.Name
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES(" & myname & ")"
How do I do the syntax correctly for it to be inserted into my SQL Server correctly?

Thanks,
Tom

I'm not going to answer your question directly because what you are doing is a great security risk. You risk your database coming under a SQL Injection attack so I am not going to give you the quick fix. I am going to tell you how to fix your code AND plug the gaping security hole that you have.
Your code injects the string myname directly into the SQL statement. This should be replaced with a parameter so that the attack surface of the application is reduced.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES(@.myname)"
Dim cmd as SqlCommand
cmd = New SqlCommand(strname, connection)
cmd.Parameters.Add("@.myname", myname)
cmd.ExecuteNonQuery()
I have replaced your injection with a parameter name (@.myname). Then in the command object I add a parameter with the same name and give it the value it needs.
Finally, here is an article aboutSQL Injection attacks and how to prevent them.

|||Om Sri Sai Ram
Forgot the single '. Use following statement.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES('" & myname & "')"
Thanks,
Ram|||

potturi_rp wrote:

Om Sri Sai Ram
Forgot the single '. Use following statement.
strname = "INSERT INTO [Item Conversion Header Table]([InitiatorName])VALUES('" & myname & "')"
Thanks,
Ram

Thats the same thing I had.

But I follow on not using the pure injection method.

Thanks,

Tom

Problem with Insert statement - Arithmetic overflow

Hi
I'm trying to do a script that can combine data from different databases and
then present them in a list to for some payment purposes. I have little
problem though with a small bit of the script.
The following small part of the script works fine :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR , belb
This is part works fine - I get 1731 records inserted into my temp table.
I'd like to get the records grouped a little bit further though, so I've
tried with the modification below :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
Here I just remove the "belb" column in the GROUP BY clause and now I get
the following error :
"Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."
I don't quite understand why I get this error message since it's the same
values I'm trying to insert in both cases.
If I just do a -
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
- I get 129 records which looks fine and looks like what I want.
The spe_temp..LonSum table is created as -
CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
I hope that some of you can shed some light on this?
Regards
Steen
The greatest amount you can store in a decimal(9,2) column is 9,999,999.99.
Is the sum for one of the CVR's you group by greater than that?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
> Hi
> I'm trying to do a script that can combine data from different databases
> and
> then present them in a list to for some payment purposes. I have little
> problem though with a small bit of the script.
> The following small part of the script works fine :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR , belb
> This is part works fine - I get 1731 records inserted into my temp table.
> I'd like to get the records grouped a little bit further though, so I've
> tried with the modification below :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> Here I just remove the "belb" column in the GROUP BY clause and now I get
> the following error :
> "Arithmetic overflow error converting float to data type numeric.
> The statement has been terminated."
> I don't quite understand why I get this error message since it's the same
> values I'm trying to insert in both cases.
> If I just do a -
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> - I get 129 records which looks fine and looks like what I want.
> The spe_temp..LonSum table is created as -
> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
> CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
> I hope that some of you can shed some light on this?
> Regards
> Steen
>
|||Hi Jacco
You're right that there could be an issue here. The biggest value seems to
be 33.213.057,179999199 but even though I change the [Amount] and [Belb]
definition for my temp table to e.g. (Decimal 25,12) it still gives me the
error.
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> The greatest amount you can store in a decimal(9,2) column is
> 9,999,999.99. Is the sum for one of the CVR's you group by greater
> than that?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
|||Can you post the result of:
SELECT CVR, SUM([Amount]), SUM([belb])
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi Jacco
> You're right that there could be an issue here. The biggest value seems to
> be 33.213.057,179999199 but even though I change the [Amount] and [Belb]
> definition for my temp table to e.g. (Decimal 25,12) it still gives me the
> error.
> Regards
> Steen
>
> Jacco Schalkwijk wrote:
>
|||Hi Jacco
I got the problem solved - and it was the definition of the Decimal column
that wasn't big enough. First time I changed the definition I just changed
the syntax for CREATE TABLE.... - but I actually missed to re-create the
table.....doohhhh.....;-).
Thanks for you help.....it was spot on....
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> Can you post the result of:
> SELECT CVR, SUM([Amount]), SUM([belb])
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
> AND Belb <>0
> GROUP BY CVR
> ?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
sql

Problem with Insert statement - Arithmetic overflow

Hi
I'm trying to do a script that can combine data from different databases and
then present them in a list to for some payment purposes. I have little
problem though with a small bit of the script.
The following small part of the script works fine :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR , belb
This is part works fine - I get 1731 records inserted into my temp table.
I'd like to get the records grouped a little bit further though, so I've
tried with the modification below :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
Here I just remove the "belb" column in the GROUP BY clause and now I get
the following error :
"Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."
I don't quite understand why I get this error message since it's the same
values I'm trying to insert in both cases.
If I just do a -
SELECT SUM([Amount]), SUM([belb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
- I get 129 records which looks fine and looks like what I want.
The spe_temp..LonSum table is created as -
CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
I hope that some of you can shed some light on this?
Regards
SteenThe greatest amount you can store in a decimal(9,2) column is 9,999,999.99.
Is the sum for one of the CVR's you group by greater than that?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
> Hi
> I'm trying to do a script that can combine data from different databases
> and
> then present them in a list to for some payment purposes. I have little
> problem though with a small bit of the script.
> The following small part of the script works fine :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR , belb
> This is part works fine - I get 1731 records inserted into my temp table.
> I'd like to get the records grouped a little bit further though, so I've
> tried with the modification below :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> Here I just remove the "belb" column in the GROUP BY clause and now I get
> the following error :
> "Arithmetic overflow error converting float to data type numeric.
> The statement has been terminated."
> I don't quite understand why I get this error message since it's the same
> values I'm trying to insert in both cases.
> If I just do a -
> SELECT SUM([Amount]), SUM([belb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Belb <>0
> GROUP BY CVR
> - I get 129 records which looks fine and looks like what I want.
> The spe_temp..LonSum table is created as -
> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
> CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
> I hope that some of you can shed some light on this?
> Regards
> Steen
>|||Hi Jacco
You're right that there could be an issue here. The biggest value seems to
be 33.213.057,179999199 but even though I change the [Amount] and [B
elb]
definition for my temp table to e.g. (Decimal 25,12) it still gives me the
error.
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> The greatest amount you can store in a decimal(9,2) column is
> 9,999,999.99. Is the sum for one of the CVR's you group by greater
> than that?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...|||Can you post the result of:
SELECT CVR, SUM([Amount]), SUM([belb])
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Belb <>0
GROUP BY CVR
?
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi Jacco
> You're right that there could be an issue here. The biggest value seems to
> be 33.213.057,179999199 but even though I change the [Amount] and [
;Belb]
> definition for my temp table to e.g. (Decimal 25,12) it still gives me the
> error.
> Regards
> Steen
>
> Jacco Schalkwijk wrote:
>|||Hi Jacco
I got the problem solved - and it was the definition of the Decimal column
that wasn't big enough. First time I changed the definition I just changed
the syntax for CREATE TABLE.... - but I actually missed to re-create the
table.....doohhhh.....;-).
Thanks for you help.....it was spot on....
Regards
Steen
Jacco Schalkwijk wrote:[vbcol=seagreen]
> Can you post the result of:
> SELECT CVR, SUM([Amount]), SUM([belb])
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
> AND Belb <>0
> GROUP BY CVR
> ?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...

Problem with Insert statement - Arithmetic overflow

Hi
I'm trying to do a script that can combine data from different databases and
then present them in a list to for some payment purposes. I have little
problem though with a small bit of the script.
The following small part of the script works fine :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([beløb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Beløb <>0
GROUP BY CVR , beløb
This is part works fine - I get 1731 records inserted into my temp table.
I'd like to get the records grouped a little bit further though, so I've
tried with the modification below :
Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
SELECT SUM([Amount]), SUM([beløb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Beløb <>0
GROUP BY CVR
Here I just remove the "beløb" column in the GROUP BY clause and now I get
the following error :
"Arithmetic overflow error converting float to data type numeric.
The statement has been terminated."
I don't quite understand why I get this error message since it's the same
values I'm trying to insert in both cases.
If I just do a -
SELECT SUM([Amount]), SUM([beløb]), CVR
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Beløb <>0
GROUP BY CVR
- I get 129 records which looks fine and looks like what I want.
The spe_temp..LonSum table is created as -
CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
I hope that some of you can shed some light on this?
Regards
SteenThe greatest amount you can store in a decimal(9,2) column is 9,999,999.99.
Is the sum for one of the CVR's you group by greater than that?
--
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
> Hi
> I'm trying to do a script that can combine data from different databases
> and
> then present them in a list to for some payment purposes. I have little
> problem though with a small bit of the script.
> The following small part of the script works fine :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([beløb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Beløb <>0
> GROUP BY CVR , beløb
> This is part works fine - I get 1731 records inserted into my temp table.
> I'd like to get the records grouped a little bit further though, so I've
> tried with the modification below :
> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
> SELECT SUM([Amount]), SUM([beløb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Beløb <>0
> GROUP BY CVR
> Here I just remove the "beløb" column in the GROUP BY clause and now I get
> the following error :
> "Arithmetic overflow error converting float to data type numeric.
> The statement has been terminated."
> I don't quite understand why I get this error message since it's the same
> values I'm trying to insert in both cases.
> If I just do a -
> SELECT SUM([Amount]), SUM([beløb]), CVR
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
> Beløb <>0
> GROUP BY CVR
> - I get 129 records which looks fine and looks like what I want.
> The spe_temp..LonSum table is created as -
> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb Decimal(9,2),
> CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
> I hope that some of you can shed some light on this?
> Regards
> Steen
>|||Hi Jacco
You're right that there could be an issue here. The biggest value seems to
be 33.213.057,179999199 but even though I change the [Amount] and [Beløb]
definition for my temp table to e.g. (Decimal 25,12) it still gives me the
error.
Regards
Steen
Jacco Schalkwijk wrote:
> The greatest amount you can store in a decimal(9,2) column is
> 9,999,999.99. Is the sum for one of the CVR's you group by greater
> than that?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
>> Hi
>> I'm trying to do a script that can combine data from different
>> databases and
>> then present them in a list to for some payment purposes. I have
>> little problem though with a small bit of the script.
>> The following small part of the script works fine :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR , beløb
>> This is part works fine - I get 1731 records inserted into my temp
>> table. I'd like to get the records grouped a little bit further
>> though, so I've tried with the modification below :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> Here I just remove the "beløb" column in the GROUP BY clause and now
>> I get the following error :
>> "Arithmetic overflow error converting float to data type numeric.
>> The statement has been terminated."
>> I don't quite understand why I get this error message since it's the
>> same values I'm trying to insert in both cases.
>> If I just do a -
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> - I get 129 records which looks fine and looks like what I want.
>> The spe_temp..LonSum table is created as -
>> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb
>> Decimal(9,2), CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
>> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
>> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
>> I hope that some of you can shed some light on this?
>> Regards
>> Steen|||Can you post the result of:
SELECT CVR, SUM([Amount]), SUM([beløb])
FROM Excel...Grundlag$
WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0 AND
Beløb <>0
GROUP BY CVR
?
--
Jacco Schalkwijk
SQL Server MVP
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi Jacco
> You're right that there could be an issue here. The biggest value seems to
> be 33.213.057,179999199 but even though I change the [Amount] and [Beløb]
> definition for my temp table to e.g. (Decimal 25,12) it still gives me the
> error.
> Regards
> Steen
>
> Jacco Schalkwijk wrote:
>> The greatest amount you can store in a decimal(9,2) column is
>> 9,999,999.99. Is the sum for one of the CVR's you group by greater
>> than that?
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
>> Hi
>> I'm trying to do a script that can combine data from different
>> databases and
>> then present them in a list to for some payment purposes. I have
>> little problem though with a small bit of the script.
>> The following small part of the script works fine :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR , beløb
>> This is part works fine - I get 1731 records inserted into my temp
>> table. I'd like to get the records grouped a little bit further
>> though, so I've tried with the modification below :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> Here I just remove the "beløb" column in the GROUP BY clause and now
>> I get the following error :
>> "Arithmetic overflow error converting float to data type numeric.
>> The statement has been terminated."
>> I don't quite understand why I get this error message since it's the
>> same values I'm trying to insert in both cases.
>> If I just do a -
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> - I get 129 records which looks fine and looks like what I want.
>> The spe_temp..LonSum table is created as -
>> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb
>> Decimal(9,2), CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
>> Periode Int, Sats int, Areal_total numeric(9,2), Areal_andelsbolig
>> numeric(9,2), Procent numeric(9,2), LoenSumBeloeb decimal(9,2) )
>> I hope that some of you can shed some light on this?
>> Regards
>> Steen
>|||Hi Jacco
I got the problem solved - and it was the definition of the Decimal column
that wasn't big enough. First time I changed the definition I just changed
the syntax for CREATE TABLE.... - but I actually missed to re-create the
table.....doohhhh.....;-).
Thanks for you help.....it was spot on....
Regards
Steen
Jacco Schalkwijk wrote:
> Can you post the result of:
> SELECT CVR, SUM([Amount]), SUM([beløb])
> FROM Excel...Grundlag$
> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
> AND Beløb <>0
> GROUP BY CVR
> ?
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:Oixs1L2PFHA.1176@.TK2MSFTNGP12.phx.gbl...
>> Hi Jacco
>> You're right that there could be an issue here. The biggest value
>> seems to be 33.213.057,179999199 but even though I change the
>> [Amount] and [Beløb] definition for my temp table to e.g. (Decimal
>> 25,12) it still gives me the error.
>> Regards
>> Steen
>>
>> Jacco Schalkwijk wrote:
>> The greatest amount you can store in a decimal(9,2) column is
>> 9,999,999.99. Is the sum for one of the CVR's you group by greater
>> than that?
>>
>> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
>> news:ejWLt61PFHA.3788@.tk2msftngp13.phx.gbl...
>> Hi
>> I'm trying to do a script that can combine data from different
>> databases and
>> then present them in a list to for some payment purposes. I have
>> little problem though with a small bit of the script.
>> The following small part of the script works fine :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR , beløb
>> This is part works fine - I get 1731 records inserted into my temp
>> table. I'd like to get the records grouped a little bit further
>> though, so I've tried with the modification below :
>> Insert into spe_temp..LonSum (Amount_, Beloeb,CVR)
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> Here I just remove the "beløb" column in the GROUP BY clause and
>> now I get the following error :
>> "Arithmetic overflow error converting float to data type numeric.
>> The statement has been terminated."
>> I don't quite understand why I get this error message since it's
>> the same values I'm trying to insert in both cases.
>> If I just do a -
>> SELECT SUM([Amount]), SUM([beløb]), CVR
>> FROM Excel...Grundlag$
>> WHERE PERIODE IN (@.Per_date3,@.Per_date2, @.Per_date1) AND amount <>0
>> AND Beløb <>0
>> GROUP BY CVR
>> - I get 129 records which looks fine and looks like what I want.
>> The spe_temp..LonSum table is created as -
>> CREATE TABLE spe_temp..Lonsum (Amount_ decimal(9,2), Beloeb
>> Decimal(9,2), CVR varchar(10)COLLATE SQL_Danish_Pref_CP1_CI_AS ,
>> Periode Int, Sats int, Areal_total numeric(9,2),
>> Areal_andelsbolig numeric(9,2), Procent numeric(9,2),
>> LoenSumBeloeb decimal(9,2) )
>> I hope that some of you can shed some light on this?
>> Regards
>> Steen

Problem with INSERT INTO statement

When I try to run INSERT INTO statement, I receive
following message:
[Microsoft][ODBC Microsoft Access Driver] Characters
found after end of SQL statement.
SQL state: 37000
Error code: -3517
Thanx in advance
Simon
The whole code for the program is:
public boolean RUNSQL(Zapis zapis, int id, int nacin)
throws java.rmi.RemoteException{
try {
Connection connection = DriverManager.getConnection
("jdbc:odbc:Zapis", "", "");
connection.setAutoCommit(false);
try{
Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
if(nacin == 1)
{
Statement stmt = connection.createStatement();
String query = "INSERT INTO zapisi ("+
"agent, indeks, vrsta, velikost,
lokacija, lastnik"+
") VALUES ('"+
zapis.getAgent() + "','"+
zapis.getId() + "','"+
zapis.getVrsta() + "','"+
zapis.getVelikost() + "','"+
zapis.getLokacija() + "','"+
zapis.getLastnik() + "');'";
int result = stmt.executeUpdate(query);
stmt.close();
}
}
catch(ClassNotFoundException cnfe)
{
System.out.println("Class " + cnfe);
return false;
}
catch(SQLException i)
{
try
{
connection.rollback();
}
catch(SQLException s)
{
System.out.println("Rollback failed");
return false;
}
System.out.println("SQL exception " + i);
return false;
}
finally{
try
{
connection.commit();
connection.close();
}
catch(SQLException sqle)
{
System.out.println("Commit failed");
return false;
}
}
connection.close();
return true;
}
catch(Exception e){
System.out.println("Error connecting to database");
}
return true;
}
You have an extra semicolon after the closing parenthesis in your INSERT
statement. The line that reads
zapis.getLastnik() + ");";
should read
zapis.getLastnik() + ")";
"Simon" <simon.sauperl@.uni-mb.si> wrote in message
news:3bab01c42a56$aaa32870$a601280a@.phx.gbl...
> When I try to run INSERT INTO statement, I receive
> following message:
> [Microsoft][ODBC Microsoft Access Driver] Characters
> found after end of SQL statement.
> SQL state: 37000
> Error code: -3517
> Thanx in advance
> Simon
> The whole code for the program is:
> public boolean RUNSQL(Zapis zapis, int id, int nacin)
> throws java.rmi.RemoteException{
> try {
> Connection connection = DriverManager.getConnection
> ("jdbc:odbc:Zapis", "", "");
> connection.setAutoCommit(false);
> try{
> Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
> if(nacin == 1)
> {
> Statement stmt = connection.createStatement();
> String query = "INSERT INTO zapisi ("+
> "agent, indeks, vrsta, velikost,
> lokacija, lastnik"+
> ") VALUES ('"+
> zapis.getAgent() + "','"+
> zapis.getId() + "','"+
> zapis.getVrsta() + "','"+
> zapis.getVelikost() + "','"+
> zapis.getLokacija() + "','"+
> zapis.getLastnik() + "');'";
> int result = stmt.executeUpdate(query);
> stmt.close();
> }
> }
> catch(ClassNotFoundException cnfe)
> {
> System.out.println("Class " + cnfe);
> return false;
> }
> catch(SQLException i)
> {
> try
> {
> connection.rollback();
> }
> catch(SQLException s)
> {
> System.out.println("Rollback failed");
> return false;
> }
> System.out.println("SQL exception " + i);
> return false;
> }
> finally{
> try
> {
> connection.commit();
> connection.close();
> }
> catch(SQLException sqle)
> {
> System.out.println("Commit failed");
> return false;
> }
> }
> connection.close();
> return true;
> }
> catch(Exception e){
> System.out.println("Error connecting to database");
> }
> return true;
> }
sql

problem with IIF statement

I've got this iif statement
=iif(Fields!Month.Value =13,"YTD",MonthName(Fields!Month.Value))
in a matrix header field that has the month numbers in it. it will
display ytd if i drop the month name function but with the monthname in
it throws an error. Also theres a warning that says
[rsRuntimeErrorInExpression] The Value expression for the textbox
'textbox47' contains an error: Argument 'Month' is not a valid
value.
any ideas on how to get it to display the month name and the YTD text?
Thanks for the help
MathiasI saw some other post as well, IIF evaluates both the truw and false
expression and then goes for comparison. so MonthName(13) will give error
since there is no 13. So reframe your conditions.
Amarnath.
"Mathias" wrote:
> I've got this iif statement
> =iif(Fields!Month.Value =13,"YTD",MonthName(Fields!Month.Value))
> in a matrix header field that has the month numbers in it. it will
> display ytd if i drop the month name function but with the monthname in
> it throws an error. Also theres a warning that says
> [rsRuntimeErrorInExpression] The Value expression for the textbox
> 'textbox47' contains an error: Argument 'Month' is not a valid
> value.
> any ideas on how to get it to display the month name and the YTD text?
> Thanks for the help
> Mathias
>|||so umm care to point out those posts or tell me something i don't
already know?
any hint as to how to reframe my condition's would be of great help.|||Mathias,
As far as posts go, just search for "IIF error" or "IIF doesn't work"
and you'll come up with tons of 'em.
My experience with this issue comes from trying to do divide by zero
error checking. For example, =IIF(exp2 = 0,0,exp1/exp2); SSRS
evaluates both T and F and blows up when exp2 = 0.
The only way I've found to work around is to create a custom code
function then use that function in your expression. For your situation
the function would be something like:
Public Function MonthValue (Exp1)
If Exp1 = 13 Then
MonthValue = "YTD"
Else MonthValue = MonthName(Exp1)
End If
End Function
Your expression would then be:
=code.MonthValue(Fields!Month.Value)
Good luck
toolman|||Thanks for the help. don't know why i never thought to look for iif
error. I'll give that custom code a shot and see what I come up with.
Thanks
Mathias
toolman wrote:
> Mathias,
> As far as posts go, just search for "IIF error" or "IIF doesn't work"
> and you'll come up with tons of 'em.
> My experience with this issue comes from trying to do divide by zero
> error checking. For example, =IIF(exp2 = 0,0,exp1/exp2); SSRS
> evaluates both T and F and blows up when exp2 = 0.
> The only way I've found to work around is to create a custom code
> function then use that function in your expression. For your situation
> the function would be something like:
> Public Function MonthValue (Exp1)
> If Exp1 = 13 Then
> MonthValue = "YTD"
> Else MonthValue = MonthName(Exp1)
> End If
> End Function
> Your expression would then be:
> =code.MonthValue(Fields!Month.Value)
> Good luck
> toolmansql

Problem with Hyphen in Full Text Catalog Search

Hi All,

we have a problem with the Full Text Catalog Search.
We use the following SQL Statement for matching companies from a table:

select company, lastname, firstname, pkcustomers, fkcustomers,
location, title, fkFunktionen, TypeOfPosition
from customers
where (fkcustomers = 0 or fkcustomers is null)
and active = 1
and pkCustomers in (select [KEY] from CONTAINSTABLE(Customers, Company,
'"*SEARCHTERMS*"'))
order by company asc

The search so far is working perfect.
Now the problem: There are two companies in the table called "i-fabrik"
and "b-wise". Theres no way to find these two companies. I find out
that the search is successful if there are more than three letters in
front of the hyphen (for exampe iii-fabrik or bbb-wise). How can that
be? Why exactly 3 letters?

I hope somebody can help me.

Best regards
Markus Weberhttp://support.microsoft.com/defaul...b;en-us;Q200043

Simon

problem with group by

hello all,

i am using odbc connection and it works fine, but i'm having trouble with select statement using group by. i want to display selected fields from 4 different table and it will display by grouping events.t_id.

this my scripts

$query = " select events.t_id, events.e_status, events.e_assignedto, events.e_id, ";
$query .= " events.e_timestamp, tmpeid.e_id, category.c_name, ";
$query .= " ticket.t_summary, ticket.t_category, ";
$query .= " ticket.t_user, ticket.t_priority, ticket.t_timestamp_opened, ";
$query .= " ticket.t_id2, ticket.t_id, COUNT (*)";
$query .= " FROM events, tmpeid, category, ticket ";
$query .= " WHERE ticket.t_id = events.t_id ";
$query .= " AND events.e_id=tmpeid.e_id";
$query .= " GROUP BY events.t_id";
$query .= " HAVING COUNT(events.t_id) >= 1 ";

this is error msg that i've found

Warning: SQL error: [Oracle][ODBC][Ora]ORA-00979: not a GROUP BY expression , SQL state S1000 in SQLExecDirect in c:\apache\htdocs\scripts

can anybody solve for me???Hello,

the problem is, that you use GROUP BY AND COUNT and do not specifiy what Oracle has to do with all the other fields in your SELECT statement.
f.e

SELECT grade FROM scott.salgrade GROUP BY grade (works fine)

SELECT grade, COUNT(losal) FROM scott.salgrade GROUP BY grade
(also works fine, cause grade will be grouped and in every grouped record you will get a count of losal)

SELECT grade, COUNR(losal), hisal FROM scott.salgrade GROUP BY grade

(will raise an exception - cause Oracle does not know what to do with hisal in the grouped record)

a

SELECT grade, COUNR(losal), SUM(hisal) FROM scott.salgrade GROUP BY grade

(works also fine)

So ... what you have to do is to kick out all the fields that has no group or agregate command and run the statement again.

or ...

group every field in the list

Hope this helps

Manfred Peter
(Alligator Company)
http://www.alligatorsql.comsql

Problem with GETDATE()

Hello All,

I have a problem as follows

if i execute SELECT GETDATE() statement multiple times in a single run it returns me the same datetime without any difference in even milliseconds.

I am unable to figure out what is wrong. I am assuming that whenever executed in a transaction it will give the same result.

could anybody let me know what is correct. Thanks for your help in advance.

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

SELECT GETDATE()

even then i get the same date.

What are you trying to achieve? The amount of time it takes to run multiple Select GetDate() is very minor. We would be able to help you better if we knew what your goal was.

|||

Hi, mate

I just executed:

SELECTGETDATE()SELECT *FROM Table1SELECTGETDATE()

and the the two dates was different. (Table1 has 120 000 rows)

This means that the query is executing too fast (in less than a millisecond) and that is why you receive the same results.

|||

yeah... if u execute query select getdate() several times one after another u cant understand the difference of milliseconds. don't worry...

|||

Hi Diamsorn,

Thanks for the reply. but all i am trying to do was i have a history table and i have included modified date as a part of primary key and when i am trying to update my main table i am inserting a record into history table. eventhough i am doing it in different time system says it is a violation of primary key.

For eg. Table1 is having below columns

Column1 Column2 Column3 and Suppose Primary key is composite key of column1 and column2

I have HistoryTable having columns

Column1 Column2 modifieddate and Suppose Primary key is composite key of Column1,Column2 and Modifieddate. but when i am trying to update the table1, and though trigger i am capturing getdate() to fill modifieddate, then as it is not different it is giving error.

how to overcome this problem?

Gneralproblem

|||

Which table is giving the primary key violation error? Table1 or HistoryTable.

What is your purpose of having a composite primary key in your history table of column1, column2, and modified date?

I would move away from using a trigger to insert into your history table, and do your update/insert inside of a transaction in a stored procedure. Triggers are a maintenance nightmare and I avoid them personally at all costs.

|||

Hi Diamsorn,

History table is giving me error. As i have to update the same record in Table1 and track the changes in HistoryTable. As my operation is so fast and as it is caputring same date it is giving primary key violation.

I would appreciate if any way to handle this problem using Triggers.

Thanks,

GeneralProblem

Problem with FOR XML clause

I am trying to persist data from SQL Server 2005 table into an XML file using FOR XML clause in the SELECT statement. I have a column named “Photo” of type Image. Issue is; the FOR XML clause is returning the picture as some reference instead of binary format.

<Photo>dbobject/employees[@.EmployeeID='1']/@.Photo</Photo>

Writing XML file using DataSet.WriteXml() method persists the same column as binary format

<Photo>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQABBQAAAgAAAAcAAABQQnJ1c2

I have trimmed the above binary string for brevity. The XML file is used for backing and restoring data in the database.

Thanks

Try using the "SELECT * FROM <table> FOR XML RAW, BINARY BASE64". This option helps to write binary columns. See ms-help topic:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/02c1bc0b-760c-4589-9ab1-6927c6d9c734.htm

For more details.

Jeff Derstadt - MSFT

Problem with FOR XML clause

I am trying to persist data from SQL Server 2005 table into an XML file using FOR XML clause in the SELECT statement. I have a column named “Photo” of type Image. Issue is; the FOR XML clause is returning the picture as some reference instead of binary format.

<Photo>dbobject/employees[@.EmployeeID='1']/@.Photo</Photo>

Writing XML file using DataSet.WriteXml() method persists the same column as binary format

<Photo>FRwvAAIAAAANAA4AFAAhAP////9CaXRtYXAgSW1hZ2UAUGFpbnQuUGljdHVyZQABBQAAAgAAAAcAAABQQnJ1c2

I have trimmed the above binary string for brevity. The XML file is used for backing and restoring data in the database.

Thanks

Try using the "SELECT * FROM <table> FOR XML RAW, BINARY BASE64". This option helps to write binary columns. See ms-help topic:

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/02c1bc0b-760c-4589-9ab1-6927c6d9c734.htm

For more details.

Jeff Derstadt - MSFT

Problem with EXEC xp_cmdshell in UDF's

Hi All

Some what of a noob to UDF's and the EXEC statement...
I am confused on the following code....

CREATE FUNCTION DBO.DoesFileExist(@.FileLocation NVARCHAR(500))
RETURNS INT AS
BEGIN
DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd
RETURN @.return
END

GO

DECLARE @.FileLocation AS NVARCHAR(500)
SET @.FileLocation = 'C:\to\log.txt'

DECLARE @.return AS INT
DECLARE @.strCmd AS NVARCHAR(1050)
SET @.strCmd = 'IF EXIST "' + @.FileLocation + '" ECHO 0
ELSE ECHO 1'
EXEC @.return = XP_CMDSHELL @.strCmd

SELECT @.return AS Exp1

SELECT DBO.DoesFileExist(@.FileLocation) AS Exp2


RETURNS:
Exp1 = 0
Exp2 = 1

WHY IS THAT? Shouldn't Exp2 also be equal to 0? It's running the same function...

Any help would be much appreciated
Thanks
Mike
IF EXIST c:\MyFile.txt (ECHO 0) ELSE (ECHO 1)

Problem with dynamic SQL syntax

I' m having a problem with the syntax when I'm trying to run a dynamic SQL
statement.
The code -
set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' Where
Date_ >='+'2001-01-01'+'
And CompanyNo Is Not NULL And Type <>'
exec sp_executesql @.sql
- gives me the error "Incorrect syntax near '>'." The purpose of the last <>
is to find the records where this field is empty (that's my understanding of
it...). The basic structure of the query is from a DTS Transform task, but
I'm trying to "convert" this whole task to TSql.
I've tried all sorts of different combinations of <> and ' but it still
won't do it. If I just prins the @.sql var. it looks fine.
The CompanyNo field is int(4) and the Type field is varchar(30).
Is there any other ways to check for an empty varchar field of can some of
you guide me to what it is I'm missing in my "set @.sql...." statement?
Best Regards
SteenWhat exactly do you mean by an empty field? Generally if
your field allows null and you don't set a value into
that field for a row, the field is set to NULL and you
can test for this by using "type IS NULL" in your where
clause.
"<>" is the operator for not equals and it is not a
singleton operator, it is a comparison operator. You
need something on both sides of the "<>" to compare to
each other.
For instance, if you are looking for rows where the type
field is not equal to a space, you could try "type <> ' '"
I hope that this helps.
Matthew Bando
bandoM@.CSCTechnologies-dot-com
>--Original Message--
>I' m having a problem with the syntax when I'm trying to
run a dynamic SQL
>statement.
>The code -
>set @.sql = 'SELECT *
INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
>
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
le_name+' Where
>Date_ >='+'2001-01-01'+'
> And CompanyNo Is Not NULL And Type <>'
>exec sp_executesql @.sql
>- gives me the error "Incorrect syntax near '>'." The
purpose of the last <>
>is to find the records where this field is empty (that's
my understanding of
>it...). The basic structure of the query is from a DTS
Transform task, but
>I'm trying to "convert" this whole task to TSql.
>I've tried all sorts of different combinations of <>
and ' but it still
>won't do it. If I just prins the @.sql var. it looks fine.
>The CompanyNo field is int(4) and the Type field is
varchar(30).
>Is there any other ways to check for an empty varchar
field of can some of
>you guide me to what it is I'm missing in my "set
@.sql...." statement?
>Best Regards
>Steen
>
>.
>|||Hi
Sorry if I wasn't very clear. I assume that the purpose is to check for an
empty field. The "original" code that's being used in the DTS Transform task
is "...AND CompanyNo is not NULL and Type <>'' ". It's not me that have
written the transform task, but I assume that this last piece checks if
there're any empty fields. It might be my understanding of it that's wrong,
but then I'd be happy to hear about it.
This SQL statement runs fine in the DST task and also when I run it in Query
analyser using fixed values, but when I do it with variables/dynamic SQL it
seems to fail and not accept this last bit.
Regards
Steen
"Matthew Bando" <anonymous@.discussions.microsoft.com> skrev i en meddelelse
news:071c01c46e4c$74405700$a501280a@.phx.gbl...
> What exactly do you mean by an empty field? Generally if
> your field allows null and you don't set a value into
> that field for a row, the field is set to NULL and you
> can test for this by using "type IS NULL" in your where
> clause.
> "<>" is the operator for not equals and it is not a
> singleton operator, it is a comparison operator. You
> need something on both sides of the "<>" to compare to
> each other.
> For instance, if you are looking for rows where the type
> field is not equal to a space, you could try "type <> ' '"
> I hope that this helps.
> Matthew Bando
> bandoM@.CSCTechnologies-dot-com
> >--Original Message--
> >I' m having a problem with the syntax when I'm trying to
> run a dynamic SQL
> >statement.
> >
> >The code -
> >
> >set @.sql = 'SELECT *
> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> >
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
> le_name+' Where
> >Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> >exec sp_executesql @.sql
> >
> >- gives me the error "Incorrect syntax near '>'." The
> purpose of the last <>
> >is to find the records where this field is empty (that's
> my understanding of
> >it...). The basic structure of the query is from a DTS
> Transform task, but
> >I'm trying to "convert" this whole task to TSql.
> >
> >I've tried all sorts of different combinations of <>
> and ' but it still
> >won't do it. If I just prins the @.sql var. it looks fine.
> >The CompanyNo field is int(4) and the Type field is
> varchar(30).
> >Is there any other ways to check for an empty varchar
> field of can some of
> >you guide me to what it is I'm missing in my "set
> @.sql...." statement?
> >
> >Best Regards
> >Steen
> >
> >
> >.
> >|||I think you're having a comprehension problem between an empty/blank string
(which is a valid value) and a NULL (which is an unknown/missing value).
Anyway, your syntax is wrong. It ends at ' which is the closing of the
string, so of course the EXEC call will break.
DECLARE @.sql NVARCHAR(2000)
SET @.sql = N'SELECT <column_list> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
'') != ''
You might try getting it working as a normal statement first, then putting
it into dynamic SQL. Remember that anytime you have a literal ' you must
escape it so it isn't interpreted as a string terminator.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> I' m having a problem with the syntax when I'm trying to run a dynamic SQL
> statement.
> The code -
> set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
Where
> Date_ >='+'2001-01-01'+'
> And CompanyNo Is Not NULL And Type <>'
> exec sp_executesql @.sql
> - gives me the error "Incorrect syntax near '>'." The purpose of the last
<>
> is to find the records where this field is empty (that's my understanding
of
> it...). The basic structure of the query is from a DTS Transform task, but
> I'm trying to "convert" this whole task to TSql.
> I've tried all sorts of different combinations of <> and ' but it still
> won't do it. If I just prins the @.sql var. it looks fine.
> The CompanyNo field is int(4) and the Type field is varchar(30).
> Is there any other ways to check for an empty varchar field of can some of
> you guide me to what it is I'm missing in my "set @.sql...." statement?
> Best Regards
> Steen
>|||Hi Araron
I think the ' was a remicense for my playing around with the statement, so
that's not the cause of the problem- sorry for the confusion.
I do know the difference between an empty field and NULL (or at least I hope
I know...:-)...) so I'm sorry if I've made some confusions about this. The
statement actually works when I'm running it without the variables and
without the dynamic SQL. It's not until I add the variable and dyn. SQL
part, I have problems getting it to accept the ' ' in the end. I'm not a all
experienced in using dynamic SQL so I thought that it was just something
simple I was missing in the syntax.
I'll take a closer look at your suggestion to see if that will do the trick.
Thanks
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
> DECLARE @.sql NVARCHAR(2000)
> SET @.sql = N'SELECT <column_list> INTO
'+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != ''
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @.sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @.sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @.sql...." statement?
> >
> > Best Regards
> > Steen
> >
> >
>|||Hi Aaron
While reading your post once more, I stumbled over your comment "..that
anytime you have a literal ' you must escape it...". What do you mean about
escape it? Do you just mean that I need a "start" and a "stop" '?
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
> DECLARE @.sql NVARCHAR(2000)
> SET @.sql = N'SELECT <column_list> INTO
'+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != ''
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @.sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @.sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @.sql...." statement?
> >
> > Best Regards
> > Steen
> >
> >
>|||Anytime a string contains ' you need to 'escape' it by doubling it. This
tells the engine that your ' is part of the string, and should not be
interpreted as an end-of-string marker.
Imagine your string looks like this:
Bob's Bait Shack
When you put it into a string,
SET @.string = 'Bob's Bait Shack'
Well, where does the string end? Between the b and the s, so the rest of
the string is lost. Except you will get an unclosed character string error
because other stuff follows the termination of the string.
Try these in Query Analyzer to see what I mean:
SELECT 'Bob's bait shack'
GO
SELECT 'Bob''s bait shack'
GO
SELECT 'Bob''s bait shack = ''''?'
GO
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:ucbpAImbEHA.252@.TK2MSFTNGP10.phx.gbl...
> Hi Aaron
> While reading your post once more, I stumbled over your comment "..that
> anytime you have a literal ' you must escape it...". What do you mean
about
> escape it? Do you just mean that I need a "start" and a "stop" '?
> Steen
>
> "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > I think you're having a comprehension problem between an empty/blank
> string
> > (which is a valid value) and a NULL (which is an unknown/missing value).
> >
> > Anyway, your syntax is wrong. It ends at ' which is the closing of the
> > string, so of course the EXEC call will break.
> >
> > DECLARE @.sql NVARCHAR(2000)
> > SET @.sql = N'SELECT <column_list> INTO
> '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
WHERE
> > Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND
COALESCE(RTRIM(Type),
> > '') != ''
> >
> > You might try getting it working as a normal statement first, then
putting
> > it into dynamic SQL. Remember that anytime you have a literal ' you
must
> > escape it so it isn't interpreted as a string terminator.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > > I' m having a problem with the syntax when I'm trying to run a dynamic
> SQL
> > > statement.
> > >
> > > The code -
> > >
> > > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> > Where
> > > Date_ >='+'2001-01-01'+'
> > > And CompanyNo Is Not NULL And Type <>'
> > > exec sp_executesql @.sql
> > >
> > > - gives me the error "Incorrect syntax near '>'." The purpose of the
> last
> > <>
> > > is to find the records where this field is empty (that's my
> understanding
> > of
> > > it...). The basic structure of the query is from a DTS Transform task,
> but
> > > I'm trying to "convert" this whole task to TSql.
> > >
> > > I've tried all sorts of different combinations of <> and ' but it
still
> > > won't do it. If I just prins the @.sql var. it looks fine.
> > > The CompanyNo field is int(4) and the Type field is varchar(30).
> > > Is there any other ways to check for an empty varchar field of can
some
> of
> > > you guide me to what it is I'm missing in my "set @.sql...."
statement?
> > >
> > > Best Regards
> > > Steen
> > >
> > >
> >
> >
>|||Sorry. It was the missing second single quote I was
referring to as missing.
Aaron is correct. You need to repeat the single quotes
since they are inside of a quoted expression.
>--Original Message--
>Hi
>Sorry if I wasn't very clear. I assume that the purpose
is to check for an
>empty field. The "original" code that's being used in
the DTS Transform task
>is "...AND CompanyNo is not NULL and Type <>'' ". It's
not me that have
>written the transform task, but I assume that this last
piece checks if
>there're any empty fields. It might be my understanding
of it that's wrong,
>but then I'd be happy to hear about it.
>This SQL statement runs fine in the DST task and also
when I run it in Query
>analyser using fixed values, but when I do it with
variables/dynamic SQL it
>seems to fail and not accept this last bit.
>Regards
>Steen
>"Matthew Bando" <anonymous@.discussions.microsoft.com>
skrev i en meddelelse
>news:071c01c46e4c$74405700$a501280a@.phx.gbl...
>> What exactly do you mean by an empty field? Generally
if
>> your field allows null and you don't set a value into
>> that field for a row, the field is set to NULL and you
>> can test for this by using "type IS NULL" in your where
>> clause.
>> "<>" is the operator for not equals and it is not a
>> singleton operator, it is a comparison operator. You
>> need something on both sides of the "<>" to compare to
>> each other.
>> For instance, if you are looking for rows where the
type
>> field is not equal to a space, you could try "type
<> ' '"
>> I hope that this helps.
>> Matthew Bando
>> bandoM@.CSCTechnologies-dot-com
>> >--Original Message--
>> >I' m having a problem with the syntax when I'm trying
to
>> run a dynamic SQL
>> >statement.
>> >
>> >The code -
>> >
>> >set @.sql = 'SELECT *
>> INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
>> >
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.tab
>> le_name+' Where
>> >Date_ >='+'2001-01-01'+'
>> > And CompanyNo Is Not NULL And Type <>'
>> >exec sp_executesql @.sql
>> >
>> >- gives me the error "Incorrect syntax near '>'." The
>> purpose of the last <>
>> >is to find the records where this field is empty
(that's
>> my understanding of
>> >it...). The basic structure of the query is from a DTS
>> Transform task, but
>> >I'm trying to "convert" this whole task to TSql.
>> >
>> >I've tried all sorts of different combinations of <>
>> and ' but it still
>> >won't do it. If I just prins the @.sql var. it looks
fine.
>> >The CompanyNo field is int(4) and the Type field is
>> varchar(30).
>> >Is there any other ways to check for an empty varchar
>> field of can some of
>> >you guide me to what it is I'm missing in my "set
>> @.sql...." statement?
>> >
>> >Best Regards
>> >Steen
>> >
>> >
>> >.
>> >
>
>.
>|||Thanks Aaron - that was also the understanding I had about using ' . I just
wanted to make sure that there wasn't anything basic about it that I had
misunderstood.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:eWVwRMmbEHA.3864@.TK2MSFTNGP10.phx.gbl...
> Anytime a string contains ' you need to 'escape' it by doubling it. This
> tells the engine that your ' is part of the string, and should not be
> interpreted as an end-of-string marker.
> Imagine your string looks like this:
> Bob's Bait Shack
> When you put it into a string,
> SET @.string = 'Bob's Bait Shack'
> Well, where does the string end? Between the b and the s, so the rest of
> the string is lost. Except you will get an unclosed character string
error
> because other stuff follows the termination of the string.
> Try these in Query Analyzer to see what I mean:
> SELECT 'Bob's bait shack'
> GO
> SELECT 'Bob''s bait shack'
> GO
> SELECT 'Bob''s bait shack = ''''?'
> GO
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:ucbpAImbEHA.252@.TK2MSFTNGP10.phx.gbl...
> > Hi Aaron
> >
> > While reading your post once more, I stumbled over your comment "..that
> > anytime you have a literal ' you must escape it...". What do you mean
> about
> > escape it? Do you just mean that I need a "start" and a "stop" '?
> >
> > Steen
> >
> >
> > "Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
> > news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> > > I think you're having a comprehension problem between an empty/blank
> > string
> > > (which is a valid value) and a NULL (which is an unknown/missing
value).
> > >
> > > Anyway, your syntax is wrong. It ends at ' which is the closing of
the
> > > string, so of course the EXEC call will break.
> > >
> > > DECLARE @.sql NVARCHAR(2000)
> > > SET @.sql = N'SELECT <column_list> INTO
> > '+@.db_name_dest+'.dbo.'+@.table_name+'
> > > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> WHERE
> > > Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND
> COALESCE(RTRIM(Type),
> > > '') != ''
> > >
> > > You might try getting it working as a normal statement first, then
> putting
> > > it into dynamic SQL. Remember that anytime you have a literal ' you
> must
> > > escape it so it isn't interpreted as a string terminator.
> > >
> > > --
> > > http://www.aspfaq.com/
> > > (Reverse address to reply.)
> > >
> > >
> > >
> > >
> > > "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> > > news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > > > I' m having a problem with the syntax when I'm trying to run a
dynamic
> > SQL
> > > > statement.
> > > >
> > > > The code -
> > > >
> > > > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > > > FROM
'+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> > > Where
> > > > Date_ >='+'2001-01-01'+'
> > > > And CompanyNo Is Not NULL And Type <>'
> > > > exec sp_executesql @.sql
> > > >
> > > > - gives me the error "Incorrect syntax near '>'." The purpose of the
> > last
> > > <>
> > > > is to find the records where this field is empty (that's my
> > understanding
> > > of
> > > > it...). The basic structure of the query is from a DTS Transform
task,
> > but
> > > > I'm trying to "convert" this whole task to TSql.
> > > >
> > > > I've tried all sorts of different combinations of <> and ' but it
> still
> > > > won't do it. If I just prins the @.sql var. it looks fine.
> > > > The CompanyNo field is int(4) and the Type field is varchar(30).
> > > > Is there any other ways to check for an empty varchar field of can
> some
> > of
> > > > you guide me to what it is I'm missing in my "set @.sql...."
> statement?
> > > >
> > > > Best Regards
> > > > Steen
> > > >
> > > >
> > >
> > >
> >
> >
>|||I've now tried to play around with both my own code and the suggestion from
Aaron - but it still wont work.
When I run the code as a regular SQL statemen with fixed values, it works
fine - both my own as well as Aaron's suggestion.
When I then try to use the variables and run it as dynamic SQL, it fails
with the syntax error around the '' in the end.
I've tried as good as I can to debug the code, and it looks like when
running it as dynamic SQL it doesn't like to do the "<> '' " comparison in
the end of the statement. If I e.g. add a value so it looks like "...And Art
<> 1 " then it works. My conclusion is that when running it as dynamic SQL,
then it doesnt like to have a '' as an "indicator" of an empty field in the
end (if you get my point...).
My question is of ocurse now if any of you can suggest another way of doing
the last bit with the check of the "art" field or if there's a way to "wrap"
the Dynamic SQL statement so it understand the last '' as wanted?
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:Of83LqlbEHA.2660@.TK2MSFTNGP12.phx.gbl...
> I think you're having a comprehension problem between an empty/blank
string
> (which is a valid value) and a NULL (which is an unknown/missing value).
> Anyway, your syntax is wrong. It ends at ' which is the closing of the
> string, so of course the EXEC call will break.
> DECLARE @.sql NVARCHAR(2000)
> SET @.sql = N'SELECT <column_list> INTO
'+@.db_name_dest+'.dbo.'+@.table_name+'
> FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' WHERE
> Date_ >= ''20010101'' AND CompanyNo IS NOT NULL AND COALESCE(RTRIM(Type),
> '') != ''
> You might try getting it working as a normal statement first, then putting
> it into dynamic SQL. Remember that anytime you have a literal ' you must
> escape it so it isn't interpreted as a string terminator.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OJ1CDpkbEHA.3016@.tk2msftngp13.phx.gbl...
> > I' m having a problem with the syntax when I'm trying to run a dynamic
SQL
> > statement.
> >
> > The code -
> >
> > set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
> > FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+'
> Where
> > Date_ >='+'2001-01-01'+'
> > And CompanyNo Is Not NULL And Type <>'
> > exec sp_executesql @.sql
> >
> > - gives me the error "Incorrect syntax near '>'." The purpose of the
last
> <>
> > is to find the records where this field is empty (that's my
understanding
> of
> > it...). The basic structure of the query is from a DTS Transform task,
but
> > I'm trying to "convert" this whole task to TSql.
> >
> > I've tried all sorts of different combinations of <> and ' but it still
> > won't do it. If I just prins the @.sql var. it looks fine.
> > The CompanyNo field is int(4) and the Type field is varchar(30).
> > Is there any other ways to check for an empty varchar field of can some
of
> > you guide me to what it is I'm missing in my "set @.sql...." statement?
> >
> > Best Regards
> > Steen
> >
> >
>|||> When I then try to use the variables and run it as dynamic SQL, it fails
> with the syntax error around the '' in the end.
SHOW US!
> it doesn't like to do the "<> '' " comparison
Stop making assumptions about T-SQL's preferences, likes and dislikes,
dating habits, etc. Show us your code and we'll show you how to fix it. We
can't fix what we can't see!
--
http://www.aspfaq.com/
(Reverse address to reply.)|||Hi
Here's the code - which I actually now have got working ...:-).
set @.sql = 'SELECT * INTO '+@.db_name_dest+'.dbo.'+@.table_name+'
FROM '+@.servername_source+'.'+@.db_name_source+'.dbo.'+@.table_name+' Where
Date_ >='+'2001-01-01'+'
And CompanyNo Is Not NULL And Type <>''
exec sp_executesql @.sql
What I was missing, was the last two ' ( which I think was what Aaron was
indicating...). I was rest assured that I had tried that earlier without
getting it working, but I must be wrong.
Thanks for all your efforts in helping me....At least all this lead me to
the article written by Erland Sommerskog about Dynamic SQL plus what Aaron
has written about it on aspfaq.com. That helps understanding and knowing
Dynamic SQL a bit better.
Regards
Steen
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> skrev i en meddelelse
news:uMoRcGybEHA.252@.TK2MSFTNGP10.phx.gbl...
> > When I then try to use the variables and run it as dynamic SQL, it fails
> > with the syntax error around the '' in the end.
> SHOW US!
> > it doesn't like to do the "<> '' " comparison
> Stop making assumptions about T-SQL's preferences, likes and dislikes,
> dating habits, etc. Show us your code and we'll show you how to fix it.
We
> can't fix what we can't see!
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>

Problem with dynamic sql statement

Can someone tell me why this is not executing properly. No error
messages, just no rows returned. I need to have an output parameter
and a return value.
Thanks in advance
Julie Barnet
CREATE PROCEDURE dbo.sel_LookupChar
(
@.Lookup_Value NVarChar(30),
@.Lookup_Field NVarChar(30),
@.Lookup_Table NVarChar(30),
@.MyOutput nVarChar(100) OUTPUT
)
AS
Declare @.SqlStr VarChar(1000)
Select @.SqlStr = "Select " + @.MyOutput + " = " + @.Lookup_Field + "
From " + @.Lookup_Table + " Where " + @.Lookup_Field
Select @.SqlStr = @.SqlStr + " = '" + @.Lookup_Value + "'"
Exec(@.SqlStr)
return @.@.rowcount
GOUse ' not " for string delimiters.
Also, try PRINT @.SqlStr instead of EXEC, and show us the result.
"Julie Barnet" <barnetj@.pr.fraserpapers.com> wrote in message
news:438e1811.0308270858.4563cc29@.posting.google.com...
> Can someone tell me why this is not executing properly. No error
> messages, just no rows returned. I need to have an output parameter
> and a return value.
> Thanks in advance
> Julie Barnet
> CREATE PROCEDURE dbo.sel_LookupChar
> (
> @.Lookup_Value NVarChar(30),
> @.Lookup_Field NVarChar(30),
> @.Lookup_Table NVarChar(30),
> @.MyOutput nVarChar(100) OUTPUT
> )
> AS
> Declare @.SqlStr VarChar(1000)
> Select @.SqlStr = "Select " + @.MyOutput + " = " + @.Lookup_Field + "
> From " + @.Lookup_Table + " Where " + @.Lookup_Field
> Select @.SqlStr = @.SqlStr + " = '" + @.Lookup_Value + "'"
>
> Exec(@.SqlStr)
> return @.@.rowcount
> GO

Problem with dynamic query and statement IN

Hi, try to execute this:

"where a12.year_id = " & Parameters!years.Value &
" and a12.month_of_year in ( " & Parameters!months.Value &" ) " &

but when I run the report raise an error
someone kwon if it's possible to use the IN statement within a dynamic query?

I've setting The parameter month as multivalue

thanksI don′t know where you enter that query, but if you use the query in the command text of the report designer you can simply use the WHERE SomeColumn IN(@.ParameterName)

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||I enter thsi query in the Edit Expression because I need to use the IIF function!
Within the Edit Expression the @.someVariable is an identifier Unknow, you must use Parametres!someVariable.value

If I use the variable without multivalue option , it works right
"where table.fied =" & Parametres!someVariable.value

If I use the variable with multivalue option , it don't work right

"where table.fied IN ( " & Parametres!someVariable.value & ")"

tanks|||You can use dynamic queries as expressions and use a "IN" statement. The problem, or "bug", is that with dynamic queries the dataset may not get refreshed when something is changed. You can use the field value (Parameters!Some_param.Value) or the query syntax (@.Some_Param).

The way I've worked around this is to actually drop in a straight query with my parameters, refresh the dataset, then run the report for good measure. Once I know it runs I then put in my dynamic query expression and all seems to work.

Try this... and I hope it helps.|||thanks
Now it works
abc_abc

Problem with dtsrun

I am trying to execute a DTS Package from within a stored procedure, however the Execute statement hangs while running the debugger.

The sql is EXECUTE @.SP_Return = master.dbo.xp_cmdshell 'DTSRun /E /SBIGO /NPFW Data Load', NO_OUTPUT

The debugger hangs, SQL Query Analyser does not respond after exiting the debugger and MSSQLSERVER needs to be stopped & restarted.

The DTS Packages runs fine from the cmd prompt as can be seen below

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Greg>DTSRun /E /SBIGO /NPFW Data Load
DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Monthly_Load_Data] Step
DTSRun OnProgress: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Monthly_Load_Data]
Step; 500 Rows have been transformed or copied.; PercentComple te = 0; ProgressCount = 500
DTSRun OnFinish: Copy Data from PFWDataLoad to [Bistro Group].[dbo].[tblPFW_Mon
thly_Load_Data] Step
DTSRun: Package execution complete.

C:\Documents and Settings\Greg>

Any help would be great.

Are debugging procedure in QA?

If so comment the dts package during debug there may be disconnect from DTSRun utility and debugger might be not able to handle situation properly...

Problem with DTC on Win2K3 server and cluster

I can't get DTC to work on any of my Wondows 2k3 servers. when I run a
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?
I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Problem with DTC on Win2K3 server and cluster

I can't get DTC to work on any of my Wondows 2k3 servers. when I run a
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Problem with DTC on Win2K3 server and cluster

I can't get DTC to work on any of my Wondows 2k3 servers. when I run a
simple select statement with a begin transaction block I get the
following error:
Server: Msg 7391, Level 16, State 1, Line 2
The operation could not be performed because the OLE DB provider
'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in
the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB'
ITransactionJoin::JoinTransaction returned 0x8004d00a].
Any ideas?I have the same problem, pls let me know if you ever get a resolution
for this.
Thanks
KP
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!