Declaring USER_NAME() as SQL Variable

Hi,

I have a User-defined function "Concatenate_NoteTexts" which I use in a
query (SQL Server 2000). On my local development machine it is called like
this:

SELECT
dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
FROM tblIntroducers

I want to run the same code on a shared remote server where I am user "JON"
instead of "dbo". I don't want to hard-code the User Name into the SQL, but
when I tried to put the user name into a variable as here:

DECLARE @.USER_NAME VarChar(30)
SET @.USER_NAME = USER_NAME()

SELECT
@.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
FROM tblIntroducers

I get the following error:

Server: Msg 170, Level 15, State 1, Line 4
Line 4: Incorrect syntax near '.'

Any advice?

TIA,

JON

PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
1"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bj4s3n$kh5$1@.online.de...
> Hi,
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
like
> this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
"JON"
> instead of "dbo". I don't want to hard-code the User Name into the SQL,
but
> when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> I get the following error:
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '.'
> Any advice?

Beg for your own database.
Development as a non-dbo is really a hastle.

You can have your own database without being a SystemAdministrator. Just
have a SystemAdministrator to run this code:

create database jon_dev
go
use jon_dev
go
sp_addalias 'jon', 'dbo'

David|||Hi David,

Thanks, nice idea, I'll have to see if the webhosts will do that.

But there must also be a way to code what I want *without* being a SysAd!

JON|||What are you trying to do with the variable? Concatenate? Or return it in
the select statement as a column? If the latter, change the period to a
comma.

DECLARE @.USER_NAME VarChar(30)
SET @.USER_NAME = USER_NAME()

SELECT
@.USER_NAME,Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
FROM tblIntroducers

"Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
news:bj4s3n$kh5$1@.online.de...
> Hi,
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
like
> this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
"JON"
> instead of "dbo". I don't want to hard-code the User Name into the SQL,
but
> when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> I get the following error:
> Server: Msg 170, Level 15, State 1, Line 4
> Line 4: Incorrect syntax near '.'
> Any advice?
> TIA,
> JON
>
> PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
> 1
>
>
>|||Sorry... misread your message - you need access to the function.

"Morgan" <mfears@.spamcop.net> wrote in message
news:OCNLY9icDHA.1280@.tk2msftngp13.phx.gbl...
> What are you trying to do with the variable? Concatenate? Or return it in
> the select statement as a column? If the latter, change the period to a
> comma.
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME,Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers
> "Jon Maz" <jonmaz@.NOSPAM.surfeu.de> wrote in message
> news:bj4s3n$kh5$1@.online.de...
> > Hi,
> > I have a User-defined function "Concatenate_NoteTexts" which I use in a
> > query (SQL Server 2000). On my local development machine it is called
> like
> > this:
> > SELECT
> > dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> > FROM tblIntroducers
> > I want to run the same code on a shared remote server where I am user
> "JON"
> > instead of "dbo". I don't want to hard-code the User Name into the SQL,
> but
> > when I tried to put the user name into a variable as here:
> > DECLARE @.USER_NAME VarChar(30)
> > SET @.USER_NAME = USER_NAME()
> > SELECT
> > @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as
NoteTexts
> > FROM tblIntroducers
> > I get the following error:
> > Server: Msg 170, Level 15, State 1, Line 4
> > Line 4: Incorrect syntax near '.'
> > Any advice?
> > TIA,
> > JON
> > PS First posted earlier today to AspMessageBoard - no answers yet.
http://www.aspmessageboard.com/foru...=626289&F=21&P=
> > 1|||Jon Maz (jonmaz@.NOSPAM.surfeu.de) writes:
> I have a User-defined function "Concatenate_NoteTexts" which I use in a
> query (SQL Server 2000). On my local development machine it is called
> like this:
> SELECT
> dbo.Concatenate_NoteTexts(Introducers.IntroducerID ) as NoteTexts
> FROM tblIntroducers
> I want to run the same code on a shared remote server where I am user
> "JON" instead of "dbo". I don't want to hard-code the User Name into
> the SQL, but when I tried to put the user name into a variable as here:
> DECLARE @.USER_NAME VarChar(30)
> SET @.USER_NAME = USER_NAME()
> SELECT
> @.USER_NAME.Concatenate_NoteTexts(Introducers.Intro ducerID) as NoteTexts
> FROM tblIntroducers

The question is slightly more interesting than it may look like.

Say that you instead had had a stored procedure, call it notetext_sp.
This would not have constituted any problem, because you could have
called it as:

EXEC notetext_sp

When you are logged in as JON on the remote server, SQL Server would
have found the notetext_sp owned by you. This works for any other
SQL Server object as well. Except scalar user-defined functions, because
you must refer to them with a two-part name. The reason for this is
syntactical, so that the parser can distinguish between UDF and built-in
functions.

However, there is an exception to the exception. This works:

ALTER FUNCTION nisse_fun (@.a int) returns varchar(90) as
BEGIN
RETURN (SELECT replicate('nisse', @.a))
END
go
declare @.g varchar(90)
exec @.g = nisse_fun 8
select @.g

That is you can invoke a scalar UDF with EXEC as well, and in this case
you don't need the two-part name. Whether this actually helps you, I
don't know.

However, as noted by David Browne, getting your database makes life a
lot easier.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

declaring ntext, text and image variable

I want to declare ntext, text and image variable to be used in sql script.
How can I do that?
Rohit
Hi,
You cant declare those datatypes in a declare statement.Instead you can pass
those as a parameter of a stored procedure.
create proc test_procedure @.k image,@.k1 text
as
begin
select @.k,@.k1
end
Thanks
Hari
MCDBA
"Rohit" <rohitk@.grapecity.com> wrote in message
news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
> I want to declare ntext, text and image variable to be used in sql script.
> How can I do that?
> Rohit
>
|||I am using cursor and fetching all the variables of tables in variables. One
of the variable has image datatype. If I want to fetch it into cursor, how
can I do that?
is there any way thru which we can declare variable that doesnt have local
scope?
Thanks
Rohit
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23$MT5ktGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You cant declare those datatypes in a declare statement.Instead you can
pass
> those as a parameter of a stored procedure.
> create proc test_procedure @.k image,@.k1 text
> as
> begin
> select @.k,@.k1
> end
>
> Thanks
> Hari
> MCDBA
>
>
> "Rohit" <rohitk@.grapecity.com> wrote in message
> news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
script.
>
|||FYI
Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.
JBandi
|||Thats why I have asked the question
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
> FYI
> Server: Msg 2739, Level 16, State 1, Line 1
> The text, ntext, and image data types are invalid for local variables.
> JBandi
|||It is by design. There is no way to declare a local variable for blob
datatype (i.e. text/ntext/image) in current version of sqlserver. You will
have to wait for the next version.
-oj
http://www.rac4sql.net
"Rohit" <rohitk@.grapecity.com> wrote in message
news:Oxz6$UvGEHA.3064@.tk2msftngp13.phx.gbl...
> Thats why I have asked the question
> "Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
> news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
>

declaring ntext, text and image variable

I want to declare ntext, text and image variable to be used in sql script.
How can I do that?
RohitHi,
You cant declare those datatypes in a declare statement.Instead you can pass
those as a parameter of a stored procedure.
create proc test_procedure @.k image,@.k1 text
as
begin
select @.k,@.k1
end
Thanks
Hari
MCDBA
"Rohit" <rohitk@.grapecity.com> wrote in message
news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
> I want to declare ntext, text and image variable to be used in sql script.
> How can I do that?
> Rohit
>|||I am using cursor and fetching all the variables of tables in variables. One
of the variable has image datatype. If I want to fetch it into cursor, how
can I do that?
is there any way thru which we can declare variable that doesnt have local
scope?
Thanks
Rohit
"Hari" <hari_prasad_k@.hotmail.com> wrote in message
news:%23$MT5ktGEHA.3032@.TK2MSFTNGP09.phx.gbl...
> Hi,
> You cant declare those datatypes in a declare statement.Instead you can
pass
> those as a parameter of a stored procedure.
> create proc test_procedure @.k image,@.k1 text
> as
> begin
> select @.k,@.k1
> end
>
> Thanks
> Hari
> MCDBA
>
>
> "Rohit" <rohitk@.grapecity.com> wrote in message
> news:uUa8CLtGEHA.2576@.TK2MSFTNGP11.phx.gbl...
script.
>|||FYI
Server: Msg 2739, Level 16, State 1, Line 1
The text, ntext, and image data types are invalid for local variables.
JBandi|||Thats why I have asked the question
"Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
> FYI
> Server: Msg 2739, Level 16, State 1, Line 1
> The text, ntext, and image data types are invalid for local variables.
> JBandi|||It is by design. There is no way to declare a local variable for blob
datatype (i.e. text/ntext/image) in current version of sqlserver. You will
have to wait for the next version.
-oj
http://www.rac4sql.net
"Rohit" <rohitk@.grapecity.com> wrote in message
news:Oxz6$UvGEHA.3064@.tk2msftngp13.phx.gbl...
> Thats why I have asked the question
> "Andras Jakus" <andras.jakus@.vodafone.com> wrote in message
> news:1DBB3032-0B77-4486-B413-7DADFA363367@.microsoft.com...
>

Declaring default value for a variable

I have a variable in some code.
DECLARE @.LastExportDateTime datetime
I then set the variable.
SET @.LastExportDateTime = (SELECT TOP 1 ExportDatetime FROM MyTable ORDER BY
ExportDatetime DESC)
The first time I run this in production @.LastExportDateTime will be null and
I'm concerned that my code will not like this. I rather set it to date in
the past, '20000101'
Can I set the default value in the DECLARE statement?
I see the default clause in BOL but I would like a syntax example.
Thanks> Can I set the default value in the DECLARE statement?
No.
But you can say
SELECT @.LastExportDateTime = MAX(ExportDateTime) FROM MyTable;
SET @.LastExportDateTime = COALESCE(@.LastExportDateTime, '20000101');|||"Terri" <terri@.cybernets.com> wrote in message
news:duhoc2$rf4$1@.reader2.nmix.net...
>I have a variable in some code.
> DECLARE @.LastExportDateTime datetime
> I then set the variable.
> SET @.LastExportDateTime = (SELECT TOP 1 ExportDatetime FROM MyTable ORDER
> BY
> ExportDatetime DESC)
> The first time I run this in production @.LastExportDateTime will be null
> and
> I'm concerned that my code will not like this. I rather set it to date in
> the past, '20000101'
> Can I set the default value in the DECLARE statement?
> I see the default clause in BOL but I would like a syntax example.
> Thanks
No.
Here are 2 ways you can do this:
SET @.LastExportDateTime = (SELECT TOP 1 ExportDatetime FROM MyTable ORDER BY
ExportDatetime DESC)
IF @.LastExportDateTime IS NULL
SET @.LastExportDateTime = '20000101'
SET @.LastExportDateTime = ISNULL((SELECT TOP 1 ExportDatetime FROM MyTable
ORDER BY ExportDatetime DESC), '20000101')|||> But you can say
> SELECT @.LastExportDateTime = MAX(ExportDateTime) FROM MyTable;
Is MAX preferable to TOP 1...ORDER BY DESC from a performance perspective?|||> Is MAX preferable to TOP 1...ORDER BY DESC from a performance perspective?
I don't think it will make a difference, but it is a lot shorter to type.

declaring cursors

I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3I don't see any error by this help u, you can try to probe ur parametirized select whit statement EXECUTE like this:

EXECUTE Select + @.LastNameField + ', ' + @.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' + @.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

so you'll can know if your statement is correct.

bye,
Maritzita

Originally posted by plineham
I need to declare and initialise a cursor using variable field names and tables. However, an error occurs when I use the fetch next into

Server: Msg 16924, Level 16, State 1, Line 98
Cursorfetch: The number of variables declared in the INTO list must match that of selected columns.

If I use the second (commented out) statement which hard codes the fields the whole script works fine.

Can anyone point out where I'm going wrong. The cursor appears to be built but I cannot use it.

Thanks
Paul

Declare Keys_cursor CURSOR FOR Select + @.LastNameField + ', ' +
@.GenderField + ', ' + @.PostCodeField + ', ' + @.FirstNameField + ', ' +
@.TitleField + ', ' + @.Add1Field + ', ' + @.Add2Field + ', ' + @.Add3Field + ' from ' + @.TableName + ' ' + @.WhereSQL

--Declare Keys_cursor CURSOR FOR Select surname,gender,post_code,forename,title,add1,add2, add3 from test_credit_data

OPEN Keys_cursor

FETCH NEXT FROM Keys_cursor
INTO @.LastName,@.Gender, @.PostCode, @.FirstName, @.Title, @.Add1, @.Add2, @.Add3|||To do this I need to change the sting to put quotes around the 'Select ' of the statement If I do this the SQL works fine

Thanks for your help|||Great...cursors are bad enough, noe we have dynamicx sql cursors...

what are you trying to do?

I hope it's an admin function...

(as opposed to an applcation function)

I mean you still have to code the fetch, and you still have to KNOW what you're working with, and you'll still have to reference the variables in the sproc...

so...

why bother?|||We have about 30 different databases which all have keys built on them in the same way. When component data is changed the keys need rebuilding.

If we use ADO and VB or C to move through a recordset to update these keys time can become a serious problem. Building the keys within SQL would be even more complicated. I decided to then write an ActiveX dll which was referenced from an SQL function. This was paramatized and would return the key I wanted to update into the database.

However, and I apologize for the life story but I have been unable to think of a quicker way to complete this process, building six keys and updating was obviuosly extremelly inefficient. Therefore I wanted to build all six from the ActiveX Dll, update the record for all keys in the same procedure. This again worked fine for one table but a generic script for all the tables was the obvious next step, hence the need for dynamic table and field names.

I have attached the script as is if anyone wants to have a look. Laugh at how badly I've done but still give me a better solution

Otherwise Brett Kaiser I'll see you behind the bike sheds after school if you've still got a problem with me|||You dude..no problem...just trying to help you..

What do you mean by building keys?

And this..

Building the keys within SQL would be even more complicated.

Doesn't make sense to me...

mostly because I don't understand what building keys means...

In any event...I hope it's working for you...

AND it's FRIDAY...yeeeeHaaaaaaa

Now if I can find my lost shaker of salt, I'd be in business..|||It was friday but it is now monday. Aaaaaaa not good at all.

The key I mean is essentially a string containing key components of an address

for instance on every record we have key which contains the first seven alpha characters of the surname and the postcode. This enables us to match records within databases based on this key.

To build these keys we have simple VB code - which is as you'd expect a series of mid, case and character matching to build the key. Not all the keys are this simple.

I cannot even imagine how I could do this in SQL. It is unfortunately not a simple case of using substring.

The solution at the moment is to open a recordset and move through the data updating the keys but this is obviously slow when updating 100000 records in a database containing 3 million.

Thanks for your help anyway|||What are the rules to build a "key" and how do you update it?

Sounds like a job for a user defined function.

If you can do it in VB, you can do it in T-SQL (Well except for Arrays, but youcan fake that out too)

Can you post some VB code?|||Find attached the classes we use to build some of the keys we use for address manipulation.

If you know a way to build all these keys into separate fields through SQL I will be very grateful

declaring a variable

I am learning T-SQL syntax and I am very familiar with it, however how would I do the following:

We have a table that actually has a column that contains SQL statements. I want to build a SQL statement in Reporting Services that is going to take that column to build a "dynamic" SQL statment and then I will use the exec sp_executesql statement.

Do I need to declare a parameter, or in SQL is there such thing as a variable?

So if I have:

DECLARE @.sql nvarchar(4000)

SELECT AdHocSQL from TheTable

SET @.sql=AdHocSQL

Would this work? Is this syntatically correct? Or should I be doing this some other way?

The report is sort of a summary report that has about 250 different items and each item has different data to get from different tables.

Thanks for the information.

You 'almost' have it down.

Code Snippet

DECLARE @.SQL nvarchar(4000)

SELECT @.SQL = AdHocSQL

FROM MyTable

WHERE {criteria}

EXECUTE sp_executesql @.SQL

|||Thanks for the help. I do appreciate it.

declaring a sql query to a variable...

Hello all! After I declar a variable how would I set the result of a sql query to the variable so i can utilize it further in my stored procedure?

-Thanks,
Rich

declare @.what varchar(2)

Code Snippet

select @.what = targetColumn

from targetTable

Where testKey = 'Whatever'

-- or the SET alternative:

Code Snippet

set @.what

= ( select taretColumn

from targetTable

where testKey = 'Whatever'

)

Give a look at SET and SELECT in books online.

|||

Kent Waldrop Se07 wrote:

declare @.what varchar(2)

Code Snippet

select @.what = targetColumn

from targetTable

Where testKey = 'Whatever'

-- or the SET alternative:

Code Snippet

set @.what

= ( select taretColumn

from targetTable

where testKey = 'Whatever'

)

Give a look at SET and SELECT in books online.

Thanks! When you mentioned to have a look at SET and SELECT in books online... are the books free or do i need to purchase them?

-Thanks,
Rich
|||

Free. As a download:

http://www.microsoft.com/downloads/results.aspx?pocId=&freetext=sql%20server%20books%20online&DisplayLang=en

As a webpage:

http://msdn2.microsoft.com/en-us/library/bb545450.aspx

Declaring a global variable

How does one go about declaring a global variable?

You don't. Not that it's impossible, but it's a thing mostly used in non object oriented architectures, and it's usually not suitable. The closest thing is to declare a static member on a class

public class SomeClass
{
public static string SomeValue="The Value";
}

which will then be accessible as SomeClass.SomeValue

|||

you can define global variables with Static keyword in C# within a class as..

Public Static String strCurruntUser

and in VB with Shared keyword as..

public Shared strCurrent as String

but better to prefer to use Session Variables instead of Global variables with Static / Shared...

hope it helps./.

|||

I'm trying to declare a global variable in Reporting Services

|||

Easiest is a parameter you default how you like.

declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
can someone tell me whats going on?XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>|||Compare the execution plans. You will most probably find that they aren't the same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on selectivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which is a good idea. I just
want t point out that none of your examples will actually expose parameter sniffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>

declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
can someone tell me whats going on?
XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/archive/2005/06/02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegr oups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
> can someone tell me whats going on?
>
|||Compare the execution plans. You will most probably find that they aren't the same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on selectivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which is a good idea. I just
want t point out that none of your examples will actually expose parameter sniffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/prodtechnol/sql/2005/recomp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegr oups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ~~~~~~
> can someone tell me whats going on?
>

declare variable slower then direct variable

Hi expert,
i have one doubt when i try 2 query give me big different
return time:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~
example 1:
this cost like 1 minutes
declare @.starttime datetime
declare @.endtime datetime
set @.starttime = '2007/06/14'
set @.endtime = '2007/06/15'
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= @.startdate
and startdatetime <= @.enddate
example 2:
this cost like 1 sec.
select top 1000 *
from table1 with ( nolock )
where count = 1 and startdatetime >= '2007/06/14'
and startdatetime <= '2007/06/15'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
can someone tell me whats going on?XJ
Yes it is expected behaviour especially you change the values of
variables.
1) Don't use TOP clause without ORDER BY clause (you may get wrong result)
2) Search on internet for 'parameter sniffing'
http://blogs.msdn.com/khen1234/arch.../02/424228.aspx
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>|||Compare the execution plans. You will most probably find that they aren't th
e same. For 1, the
optimizer doesn't know the values of the variables,m so it has to guess on s
electivity. For 2, the
values are hard-coded in the query, so thay are known to the optimizer.
You were suggested in another post to read up on "parameter sniffing", which
is a good idea. I just
want t point out that none of your examples will actually expose parameter s
niffing behaviour.
Here's some good reading: http://www.microsoft.com/technet/pr...r />
comp.mspx
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"XJ" <ianyian@.gmail.com> wrote in message
news:1183295526.995618.312370@.i38g2000prf.googlegroups.com...
> Hi expert,
> i have one doubt when i try 2 query give me big different
> return time:
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~
> example 1:
> this cost like 1 minutes
> declare @.starttime datetime
> declare @.endtime datetime
> set @.starttime = '2007/06/14'
> set @.endtime = '2007/06/15'
>
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= @.startdate
> and startdatetime <= @.enddate
>
> example 2:
> this cost like 1 sec.
> select top 1000 *
> from table1 with ( nolock )
> where count = 1 and startdatetime >= '2007/06/14'
> and startdatetime <= '2007/06/15'
>
> ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~
> can someone tell me whats going on?
>

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advanced
This would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>
|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>
|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be[vbcol=seagreen]
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
exercise
>
|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
David Gugick
Imceda Software
www.imceda.com

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advancedThis would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
exercise[vbcol=seagreen]
>|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
David Gugick
Imceda Software
www.imceda.com

Declare variable problem

DECLARE @.x varchar
Set @.x = (SELECT COUNT(*)FROM #t)
DECLARE '@.date' + @.x datetime
Lets say the count is 5
I want to DECLARE a datetime variable called @.date5
How do I do this?
Thanks in advancedThis would require dynamic sql, which would be either a pointless exercise
or a dynamic coding nightmare. What are you trying to accomplish? Why is
it important that the name also convey information/data?
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> DECLARE @.x varchar
> Set @.x = (SELECT COUNT(*)FROM #t)
> DECLARE '@.date' + @.x datetime
> Lets say the count is 5
> I want to DECLARE a datetime variable called @.date5
> How do I do this?
> Thanks in advanced
>|||I don't know how many dates (rows) are going to be returned and I need to be
able to dynamically decare a variable for each date returned.
"Scott Morris" <bogus@.bogus.com> wrote in message
news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> This would require dynamic sql, which would be either a pointless exercise
> or a dynamic coding nightmare. What are you trying to accomplish? Why
> is
> it important that the name also convey information/data?
> "Jim Campau" <jim_campau@.bausch.com> wrote in message
> news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
>> DECLARE @.x varchar
>> Set @.x = (SELECT COUNT(*)FROM #t)
>> DECLARE '@.date' + @.x datetime
>> Lets say the count is 5
>> I want to DECLARE a datetime variable called @.date5
>> How do I do this?
>> Thanks in advanced
>>
>|||This sounds like a problem with your approach to solving the problem. Since
you used the term "rows", you probably should be thinking in terms of a
table. You can search the newsgroups for the many posts regarding dynamic
sql - if you want to go down that path.
There might be a set-based approach; one that is more appropriate for a
relational dbms. If you post some details about what you are trying to
accomplish, someone might be able to provide a different approach that is
better suited to the environment.
"Jim Campau" <jim_campau@.bausch.com> wrote in message
news:uqnPiWAJFHA.2956@.TK2MSFTNGP12.phx.gbl...
> I don't know how many dates (rows) are going to be returned and I need to
be
> able to dynamically decare a variable for each date returned.
>
> "Scott Morris" <bogus@.bogus.com> wrote in message
> news:u0Xzn2$IFHA.580@.TK2MSFTNGP15.phx.gbl...
> > This would require dynamic sql, which would be either a pointless
exercise
> > or a dynamic coding nightmare. What are you trying to accomplish? Why
> > is
> > it important that the name also convey information/data?
> >
> > "Jim Campau" <jim_campau@.bausch.com> wrote in message
> > news:uL7Y$g$IFHA.3356@.TK2MSFTNGP12.phx.gbl...
> >> DECLARE @.x varchar
> >> Set @.x = (SELECT COUNT(*)FROM #t)
> >>
> >> DECLARE '@.date' + @.x datetime
> >>
> >> Lets say the count is 5
> >> I want to DECLARE a datetime variable called @.date5
> >> How do I do this?
> >>
> >> Thanks in advanced
> >>
> >>
> >
> >
>|||Jim Campau wrote:
> I don't know how many dates (rows) are going to be returned and I
> need to be able to dynamically decare a variable for each date
> returned.
Can you explain the problem, the tables involved, a sample query, and
what you expect to be returned. It sounds like what you're trying to do
can be done a lot more easily.
--
David Gugick
Imceda Software
www.imceda.com

Declare Variable For All In SP

In a previous life, for each variable that we passed into a query, we would set -1 to the default for all so that when we converted it to an SP, we could query a specific dataset or or all. The following is a sample bit of code, I can not for the life of me remember how to pull back all using -1.

The following is the code that I currently have, it's a simplified version of the total SP that I am trying to use, but enough to give you the idea of what I am trying to do.

The MemberId field is a varchar(20) in the table.

Create procedure sp_GetClaims_BY_MemberID
@.Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = @.memberid

EXEC sp_GetClaims_BY_MemberID '99999999999'

The above SP works fine, I just need to be able to modify it so that I can pull back all records for all member id's, any suggestions?

I am currently working in SQL 2000.

Here's one way I think should work.

Assume we have decided that '*' (star) is to mean 'all' (as in the T-SQL wildcard)
What below does, is just to turn '*' into null, and in the WHERE, if the var is null, use the column instead.
End result for parameter '*', is then WHERE Membid = Membid, which is what you want - all rows.

Create procedure sp_GetClaims_BY_MemberID
@.Memberid varchar (50)
as
Select top 100 * from [QICC-TEST].dbo.tblClaims_eligible
where Membid = COALESCE(NULLIF( @.memberid, '*'), Membid)

=;o)
/Kenneth

|||PERFECT!

Declare Variable Dynamically

I'm attempting to modify some Crosstab generating code, and I need some
advice/examples.

Currently, the code uses a single string variable to store the
dynamically generated query (www.johnmacintyre.ca). The problem is that
I am trying to pivot biological taxonomy information, and may end up
with a table containing over 200 columns. This takes the dynamic string
well over the 8000char limit for variables.

>From my understanding, the EXEC() command does not have the 8000char
limit if the execution string is broken into chunks, and concatenated
e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
to:

1) start a counter at the beginining of the dynamic generation
2) append the counter value to the end of a string variable name
3) DECLARE the new variable and attach that loop cycle of text to it,
or attach each chunk of characters < 8000
4) build the EXEC() string by concatenating each dynamic varible

Can this be done? Should it be done? Is there a better way to address
this type of problem?

Thanks for any ideas or insights

Tim Pascoe>> Can this be done? <<

Maybe, maybe not.

>> Should it be done? <<

No, this is a mis-use of SQL.

>> Is there a better way to address this type of problem? <<

Crosstabs (thank you for not calling them "pivot tables") are a report
and not a query. you ought to use a report tool and not SQL for this
kind of job.|||Hi
You may want to look at previous posts regarding crosstab queries, as Joe
says it is better to do this in the reporting tool such as RAC
http://www.rac4sql.net/ and others.

Here are a few links you may want to read:
http://www.windowsitpro.com/SQLServ...5608/15608.html
http://support.microsoft.com/defaul...b;EN-US;q175574

http://www.sqlteam.com/item.asp?ItemID=2955

John

<tim.pascoe@.cciw.ca> wrote in message
news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
> I'm attempting to modify some Crosstab generating code, and I need some
> advice/examples.
> Currently, the code uses a single string variable to store the
> dynamically generated query (www.johnmacintyre.ca). The problem is that
> I am trying to pivot biological taxonomy information, and may end up
> with a table containing over 200 columns. This takes the dynamic string
> well over the 8000char limit for variables.
>>From my understanding, the EXEC() command does not have the 8000char
> limit if the execution string is broken into chunks, and concatenated
> e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
> to:
> 1) start a counter at the beginining of the dynamic generation
> 2) append the counter value to the end of a string variable name
> 3) DECLARE the new variable and attach that loop cycle of text to it,
> or attach each chunk of characters < 8000
> 4) build the EXEC() string by concatenating each dynamic varible
> Can this be done? Should it be done? Is there a better way to address
> this type of problem?
> Thanks for any ideas or insights
> Tim Pascoe|||RAC was my next point of investigation. The problem is that I'm not
actually producing a report of the data (although I agree this is
generally what crosstabs are for). Instead, the data is required in
this format so it can be fed into statistical software, which requires
the crosstabulated structure. I'll look into RAC and the other sites
you listed.

Thanks.

John Bell wrote:
> Hi
> You may want to look at previous posts regarding crosstab queries, as
Joe
> says it is better to do this in the reporting tool such as RAC
> http://www.rac4sql.net/ and others.
> Here are a few links you may want to read:
http://www.windowsitpro.com/SQLServ...5608/15608.html
> http://support.microsoft.com/defaul...b;EN-US;q175574
> http://www.sqlteam.com/item.asp?ItemID=2955
> John
> <tim.pascoe@.cciw.ca> wrote in message
> news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
> > I'm attempting to modify some Crosstab generating code, and I need
some
> > advice/examples.
> > Currently, the code uses a single string variable to store the
> > dynamically generated query (www.johnmacintyre.ca). The problem is
that
> > I am trying to pivot biological taxonomy information, and may end
up
> > with a table containing over 200 columns. This takes the dynamic
string
> > well over the 8000char limit for variables.
> >>From my understanding, the EXEC() command does not have the
8000char
> > limit if the execution string is broken into chunks, and
concatenated
> > e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need
is
> > to:
> > 1) start a counter at the beginining of the dynamic generation
> > 2) append the counter value to the end of a string variable name
> > 3) DECLARE the new variable and attach that loop cycle of text to
it,
> > or attach each chunk of characters < 8000
> > 4) build the EXEC() string by concatenating each dynamic varible
> > Can this be done? Should it be done? Is there a better way to
address
> > this type of problem?
> > Thanks for any ideas or insights
> > Tim Pascoe|||Hi

You may want to consider Analysis services then?

John

<tim.pascoe@.cciw.ca> wrote in message
news:1103032212.575699.29580@.c13g2000cwb.googlegro ups.com...
> RAC was my next point of investigation. The problem is that I'm not
> actually producing a report of the data (although I agree this is
> generally what crosstabs are for). Instead, the data is required in
> this format so it can be fed into statistical software, which requires
> the crosstabulated structure. I'll look into RAC and the other sites
> you listed.
> Thanks.
>
> John Bell wrote:
>> Hi
>> You may want to look at previous posts regarding crosstab queries, as
> Joe
>> says it is better to do this in the reporting tool such as RAC
>> http://www.rac4sql.net/ and others.
>>
>> Here are a few links you may want to read:
>>
> http://www.windowsitpro.com/SQLServ...5608/15608.html
>> http://support.microsoft.com/defaul...b;EN-US;q175574
>>
>> http://www.sqlteam.com/item.asp?ItemID=2955
>>
>> John
>>
>> <tim.pascoe@.cciw.ca> wrote in message
>> news:1102971154.140806.89470@.f14g2000cwb.googlegro ups.com...
>> > I'm attempting to modify some Crosstab generating code, and I need
> some
>> > advice/examples.
>>> > Currently, the code uses a single string variable to store the
>> > dynamically generated query (www.johnmacintyre.ca). The problem is
> that
>> > I am trying to pivot biological taxonomy information, and may end
> up
>> > with a table containing over 200 columns. This takes the dynamic
> string
>> > well over the 8000char limit for variables.
>>> >>From my understanding, the EXEC() command does not have the
> 8000char
>> > limit if the execution string is broken into chunks, and
> concatenated
>> > e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need
> is
>> > to:
>>> > 1) start a counter at the beginining of the dynamic generation
>> > 2) append the counter value to the end of a string variable name
>> > 3) DECLARE the new variable and attach that loop cycle of text to
> it,
>> > or attach each chunk of characters < 8000
>> > 4) build the EXEC() string by concatenating each dynamic varible
>>> > Can this be done? Should it be done? Is there a better way to
> address
>> > this type of problem?
>>> > Thanks for any ideas or insights
>>> > Tim Pascoe
>|||(tim.pascoe@.cciw.ca) writes:
> I'm attempting to modify some Crosstab generating code, and I need some
> advice/examples.
> Currently, the code uses a single string variable to store the
> dynamically generated query (www.johnmacintyre.ca). The problem is that
> I am trying to pivot biological taxonomy information, and may end up
> with a table containing over 200 columns. This takes the dynamic string
> well over the 8000char limit for variables.
>>From my understanding, the EXEC() command does not have the 8000char
> limit if the execution string is broken into chunks, and concatenated
> e.g. EXEC(sql1 + sql2 + sql3 + ...). So the solution I think I need is
> to:
> 1) start a counter at the beginining of the dynamic generation
> 2) append the counter value to the end of a string variable name
> 3) DECLARE the new variable and attach that loop cycle of text to it,
> or attach each chunk of characters < 8000
> 4) build the EXEC() string by concatenating each dynamic varible
> Can this be done? Should it be done? Is there a better way to address
> this type of problem?

I think it can be done, but I would not like to do it. You would have
to generate dynamic SQL which in its turn generates the dynamic SQL
that executes the query.

It may be a better alternative to use a client language to generate the
SQL. If you use a language like Perl or Visual Basic which has unlimited
strings, you are saved the restriction of varchar(8000). Note that all
that you would bring to the client would be the meta data needed to
form the SQL statement.

In SQL 2005 there is a new data type varchar(MAX) which is akin to text,
but works more like varchar, and thus in SQL 2005 you would also be saved
from the varchar(8000) restriction. SQL 2005 is currently in beta, with
release planned next year.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

declare variable based on existing column

Is it possible to declare a variable based on an existing column?
Instead of:
DECLARE @.myvariable VARCHAR(20)
Use:
DECLARE @.myvariable mytable.mycolumn%type
WHERE
Table MYTABLE has column MYCOLUMN of data type VARCHAR(20)Not without doing the entire operation in dynamic SQL -- in other words, not
easily and probably not a great idea. Why would you want that
functionality?
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:0DF1A88B-C787-4BA1-BA24-FFB7F4630E57@.microsoft.com...
> Is it possible to declare a variable based on an existing column?
> Instead of:
> DECLARE @.myvariable VARCHAR(20)
> Use:
> DECLARE @.myvariable mytable.mycolumn%type
> WHERE
> Table MYTABLE has column MYCOLUMN of data type VARCHAR(20)
>|||ORABLE has that functionality. I admit it's a nice thing, especially when
putting together smaller systems where data types change.
What about using a SQLVariant? I've rarely used that ... does that
Internally store the type (likea VB/JScript type deal)? If so, I'd immagine
that'd work in most scenarios ... not sure how it rates on the Best PRactice
scale though ...
-- Alex
"Adam Machanic" wrote:

> Not without doing the entire operation in dynamic SQL -- in other words, n
ot
> easily and probably not a great idea. Why would you want that
> functionality?
> --
> Adam Machanic
> SQL Server MVP
> http://www.sqljunkies.com/weblog/amachanic
> --
>
> "Bevo" <Bevo@.discussions.microsoft.com> wrote in message
> news:0DF1A88B-C787-4BA1-BA24-FFB7F4630E57@.microsoft.com...
>
>|||"Alex Papadimoulis" <alexRemovePi@.pa3.14padimoulis.com> wrote in message
news:ACC91FFB-ADC5-4DD8-9966-05B78F22422C@.microsoft.com...
> What about using a SQLVariant? I've rarely used that ... does that
> Internally store the type (likea VB/JScript type deal)? If so, I'd
immagine
Yes, it's very similar to those languages' variant datatypes -- and just
like using them, it has lots of pitfalls... I wouldn't use it in production
code, personally, although I have found it useful in a few utility
operations -- but I had to work around a lot of problems, especially dealing
with datetime data.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||The larger the system, the more useful this is.
And its not even the data type changing, but more often the size of VARCHAR
fields for example. You design your system with LAST_NAME as VARCHAR(50),
then realize you need VARCHAR(1000). To make this change in SQL Server, it
seems that you would have to go through all your code to change any local
variables that are @.LAST_NAME. If you could base the declaration on the tabl
e
column, you would not have to do this.
In addition, it is good for global standardization of data types and sizes.
"Alex Papadimoulis" wrote:
> ORABLE has that functionality. I admit it's a nice thing, especially when
> putting together smaller systems where data types change.
> What about using a SQLVariant? I've rarely used that ... does that
> Internally store the type (likea VB/JScript type deal)? If so, I'd immagi
ne
> that'd work in most scenarios ... not sure how it rates on the Best PRacti
ce
> scale though ...
> -- Alex
> "Adam Machanic" wrote:
>|||sqlwish@.microsoft.com
Ask for DOMAINs, an ANSI SQL feature (Google for more info on it) -- it's
definitely at the top of my wishlist, too.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Bevo" <Bevo@.discussions.microsoft.com> wrote in message
news:332D49B5-1442-46BF-A821-77C6FF61D4BA@.microsoft.com...
> The larger the system, the more useful this is.
> And its not even the data type changing, but more often the size of
VARCHAR
> fields for example. You design your system with LAST_NAME as VARCHAR(50),
> then realize you need VARCHAR(1000). To make this change in SQL Server, it
> seems that you would have to go through all your code to change any local
> variables that are @.LAST_NAME. If you could base the declaration on the
table
> column, you would not have to do this.
> In addition, it is good for global standardization of data types and
sizes.
> "Alex Papadimoulis" wrote:
>
when
immagine
PRactice
words, not|||You could use sp_addtype to create your own types which are stored in
systypes, then you can define data table columns, sp and udf parameters, and
variables using the user defined types.
in addition, INFORMATION_SCHEMA.COLUMNS contains the columns DOMAIN_CATALOG,
DOMAIN_SCHEMA, and DOMAIN_NAME which can be used to tables that need to be
altered to accomodate the changed type definition. you can also do a search
on syscomments to find any additional objects that need to be recompiled in
addition to the table alterations.
note: sp_rename can be used to rename a user defined type.
"Bevo" wrote:
> The larger the system, the more useful this is.
> And its not even the data type changing, but more often the size of VARCHA
R
> fields for example. You design your system with LAST_NAME as VARCHAR(50),
> then realize you need VARCHAR(1000). To make this change in SQL Server, it
> seems that you would have to go through all your code to change any local
> variables that are @.LAST_NAME. If you could base the declaration on the ta
ble
> column, you would not have to do this.
> In addition, it is good for global standardization of data types and sizes
.
> "Alex Papadimoulis" wrote:
>|||"Brian Selzer" <BrianSelzer@.discussions.microsoft.com> wrote in message
news:23455E9D-7788-4229-80DB-DD46292346BD@.microsoft.com...
> You could use sp_addtype to create your own types which are stored in
> systypes, then you can define data table columns, sp and udf parameters,
and
> variables using the user defined types.
> in addition, INFORMATION_SCHEMA.COLUMNS contains the columns
DOMAIN_CATALOG,
> DOMAIN_SCHEMA, and DOMAIN_NAME which can be used to tables that need to be
> altered to accomodate the changed type definition. you can also do a
search
> on syscomments to find any additional objects that need to be recompiled
in
> addition to the table alterations.
Brian,
sp_addtype is deprecated in the next version of SQL Server. I recommend
that you do not use it. Unfortunately, those DOMAIN columns don't provide
full DOMAIN support -- the ANSI domains, as I understand them, operate
similarly to the types that can be added with sp_addtype, but with much
greater flexibility and bound constraints.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||>> the ANSI domains, as I understand them, operate similarly to the types
Other than offering a syntactic shorthand, what else can ANSI domains do?
Would it offer anything meaningful in terms of simplification, flexibility
and utility of existing ANSI standard built-in types? If T-SQL UDTs support
binding of constraints, wouldn't it obviate the need for ANSI domains, if
that is the lacking provision?
Anith|||"Anith Sen" <anith@.bizdatasolutions.com> wrote in message
news:OLLBtY1GFHA.3536@.TK2MSFTNGP14.phx.gbl...
> Other than offering a syntactic shorthand, what else can ANSI domains do?
> Would it offer anything meaningful in terms of simplification, flexibility
> and utility of existing ANSI standard built-in types? If T-SQL UDTs
support
> binding of constraints, wouldn't it obviate the need for ANSI domains, if
> that is the lacking provision?
Two things:
A) Correct me if I'm wrong but I know of no way to alter a T-SQL UDT.
The ANSI Standard does provide ALTER DOMAIN syntax. The OP in this
situation, it appears, wants his variables to be able to mimic the same
datatype used in the table -- even if that datatype should change (e.g. if
changing business requirements mean that the datatype needs to support 100
bytes instead of 50). Is that possible with a T-SQL UDT as they are
currently implemented?
B) My understanding is that the constraints bound to ANSI DOMAINs extend
to variables declared of a domain datatype... so if I define a domain
INTBETWEEN1AND10, which is an integer with a constraint that it must be
between 1 and 10, that will be enforced even for local variables of that
type. That's not the case with T-SQL UDTs, is it?
If I'm wrong on both of these counts, then I regret not making much
heavier use of T-SQL UDTs in my work to date -- I've pretty much ignored the
feature as I have been under the impression that it's inflexible and doesn't
add value.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--

Declare Variable As Table

hi all
how can I Declare Variable As Table Like Cursor
DECLARE parameter_cursor CURSOR FOR
SELECT top 1 c.name, t.name
FROM sysobjects o
JOIN syscolumns c on c.id = o.id
JOIN systypes t on c.xtype = t.xtype
WHERE o.name like 'Test'
order by o.name, c.colid
TanksYou cannot declare a table variable like a cursor, however you can declare a
table variable and then insert the data into the table variable. It will be
available for all the operations within the given scope.
Anith

declare syntax in a UDF

Hi, I'm trying to create a function that returns a table, however I want
to use a local variable in there and enterprise manager ain't liking it!

The error I get is number 156 'incorrect syntax near the keyword
'declare'.. hopefully this is just a simple thing where I've put it in
the wrong place.

The code follows:

CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
nvarchar(20))

RETURNS TABLE AS
RETURN

declare @.set nvarchar(150)

select "Total Pledged" as info, sum(total) as tot
FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)

Cheers for any help,
Chris"Not Me" <Noone.is.home@.here.com> wrote in message
news:ckoccr$olo$1@.ucsnew1.ncl.ac.uk...
> Hi, I'm trying to create a function that returns a table, however I want
> to use a local variable in there and enterprise manager ain't liking it!
> The error I get is number 156 'incorrect syntax near the keyword
> 'declare'.. hopefully this is just a simple thing where I've put it in the
> wrong place.
> The code follows:
> CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
> smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
> nvarchar(20))
> RETURNS TABLE AS
> RETURN
> declare @.set nvarchar(150)
> select "Total Pledged" as info, sum(total) as tot
> FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
>
> Cheers for any help,
> Chris

You seem to be mixing inline and multi-statement syntax. If you just say
RETURN TABLE, then the rest of the function can only be a single SELECT
statement; if you want to use multiple statements in the function, then you
must define the structure of the table you're returning. See the examples in
Books Online under CREATE FUNCTION.

In your function, you haven't defined the structure of the returned table,
so the only thing you can have in the body of the function is a single
SELECT.

Simon|||Simon Hayes wrote:
> "Not Me" <Noone.is.home@.here.com> wrote in message
> news:ckoccr$olo$1@.ucsnew1.ncl.ac.uk...
>>The error I get is number 156 'incorrect syntax near the keyword
>>'declare'.. hopefully this is just a simple thing where I've put it in the
>>wrong place.
>>
>>The code follows:
>>
>>CREATE FUNCTION AFGroupedTotals (@.campaign nvarchar(30),@.datefrom
>>smalldatetime, @.dateto smalldatetime, @.prospect nvarchar(30), @.type
>>nvarchar(20))
>>RETURNS TABLE AS
>>RETURN
>>declare @.set nvarchar(150)
>>select "Total Pledged" as info, sum(total) as tot
>>FROM AFresponseTotals (@.campaign, @.datefrom, @.dateto,@.prospect)
> You seem to be mixing inline and multi-statement syntax. If you just say
> RETURN TABLE, then the rest of the function can only be a single SELECT
> statement; if you want to use multiple statements in the function, then you
> must define the structure of the table you're returning. See the examples in
> Books Online under CREATE FUNCTION.

Aha! sounds about right, just needed a little shunt in the right
direction.. gonna have nightmares about BOL :p

cheers,
Chris

Declare Scalar Variable??

I've got a report that is pretty simple but for some reason I keep getting
the following error when I try to run it:
An error occured during local report processing.
An error has occured during report processing.
Query execution failed for data set "Dataset 1"
Must declare the scalar variable "@.TABLENAME".
This is the actual dataset I'm trying to run:
EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
This is set up as COMMAND TYPE of TEXT.
Here's the actual stored procedure being called:
ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
----
-- Description: Report all transactions from a given trace table by SQL_ID
-- Revision History:
----
@.TABLENAME varchar(128),
@.SQL_ID int,
@.DB_ID int,
@.Sort varchar(20) = 'CPU'
as
set nocount on
--DECLARE @.Table VARCHAR(128)
--Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
getdate()), 110)
exec ('
select StartTime, Reads, CPU, Duration, spid, [SQL] = convert( varchar(4000), substring( TextData, 1, 4000 ) )
from [' + @.TABLENAME + '] t
join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
where i.id = ' + @.SQL_ID + '
and i.databaseID = ' + @.DB_ID)-- + '
--order by ' + @.Sort + 'desc
--')
GO
I've got another report that is literally the exact same thing, except there
is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
also able to run the stored procedure by itself just fine.
If anyone has any idea as to what the issue may be, that would be fantastic!!
Thanks!On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
wrote:
> I've got a report that is pretty simple but for some reason I keep getting
> the following error when I try to run it:
> An error occured during local report processing.
> An error has occured during report processing.
> Query execution failed for data set "Dataset 1"
> Must declare the scalar variable "@.TABLENAME".
> This is the actual dataset I'm trying to run:
> EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> This is set up as COMMAND TYPE of TEXT.
> Here's the actual stored procedure being called:
> ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> ----
> -- Description: Report all transactions from a given trace table by SQL_ID
> -- Revision History:
> ----
> @.TABLENAME varchar(128),
> @.SQL_ID int,
> @.DB_ID int,
> @.Sort varchar(20) = 'CPU'
> as
> set nocount on
> --DECLARE @.Table VARCHAR(128)
> --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> getdate()), 110)
> exec ('
> select StartTime, Reads, CPU, Duration, spid, [SQL] => convert( varchar(4000), substring( TextData, 1, 4000 ) )
> from [' + @.TABLENAME + '] t
> join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> where i.id = ' + @.SQL_ID + '
> and i.databaseID = ' + @.DB_ID)-- + '
> --order by ' + @.Sort + 'desc
> --')
> GO
> I've got another report that is literally the exact same thing, except there
> is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> also able to run the stored procedure by itself just fine.
> If anyone has any idea as to what the issue may be, that would be fantastic!!
> Thanks!
I don't think you have the syntax correct on the Reporting Services
side.
This link outlines it:
http://msdn2.microsoft.com/en-us/library/aa337435.aspx
Let me know if this is what you're looking for. I have some scripts I
use to pass parameters into stored procedures at home. I can take a
look into it if the link isn't clear or if it doesn't work.|||I'm using the exact same syntax throughtout my project and all the reports
work fine. For example, this is the syntax in another report I'm using:
EXEC dbo.Report_TSQL_By_ID @.SQL_ID, @.DB_ID
This report works fine with no problems at all...
"Ayman" wrote:
> On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
> wrote:
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be fantastic!!
> >
> > Thanks!
> I don't think you have the syntax correct on the Reporting Services
> side.
> This link outlines it:
> http://msdn2.microsoft.com/en-us/library/aa337435.aspx
> Let me know if this is what you're looking for. I have some scripts I
> use to pass parameters into stored procedures at home. I can take a
> look into it if the link isn't clear or if it doesn't work.
>|||...and the link here is addressing the issue of binding input parameters to
user defined functions...unfirtunately that's not what I'm doing.
"Ayman" wrote:
> On May 4, 2:53 pm, A. Robinson <ARobin...@.discussions.microsoft.com>
> wrote:
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine. I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be fantastic!!
> >
> > Thanks!
> I don't think you have the syntax correct on the Reporting Services
> side.
> This link outlines it:
> http://msdn2.microsoft.com/en-us/library/aa337435.aspx
> Let me know if this is what you're looking for. I have some scripts I
> use to pass parameters into stored procedures at home. I can take a
> look into it if the link isn't clear or if it doesn't work.
>|||Is there a reason you are not using a command type of stored procedure? If
you do this then RS automatically determines the parameters and the
parameter data type and creates the report parameters for you. That would
solve your problem.
But, given what you have below the issue is that for whatever reason the
query parameter @.TABLENAME is not mapped to your report parameter. On the
dataset tab click on the ..., parameters tab and make sure the @.TABLENAME
parameter is mapped to the report parameter.
This error is what you get when this mapping has not occured.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
news:974C277C-B4F0-4940-A7E9-E8CDCD33D27F@.microsoft.com...
> I've got a report that is pretty simple but for some reason I keep getting
> the following error when I try to run it:
> An error occured during local report processing.
> An error has occured during report processing.
> Query execution failed for data set "Dataset 1"
> Must declare the scalar variable "@.TABLENAME".
> This is the actual dataset I'm trying to run:
> EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> This is set up as COMMAND TYPE of TEXT.
>
> Here's the actual stored procedure being called:
> ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> ----
> -- Description: Report all transactions from a given trace table by SQL_ID
> -- Revision History:
> ----
> @.TABLENAME varchar(128),
> @.SQL_ID int,
> @.DB_ID int,
> @.Sort varchar(20) = 'CPU'
> as
> set nocount on
> --DECLARE @.Table VARCHAR(128)
> --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> getdate()), 110)
> exec ('
> select StartTime, Reads, CPU, Duration, spid, [SQL] => convert( varchar(4000), substring( TextData, 1, 4000 ) )
> from [' + @.TABLENAME + '] t
> join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> where i.id = ' + @.SQL_ID + '
> and i.databaseID = ' + @.DB_ID)-- + '
> --order by ' + @.Sort + 'desc
> --')
> GO
> I've got another report that is literally the exact same thing, except
> there
> is no @.TABLENAME parameter in the stored procedure and it runs just fine.
> I'm
> also able to run the stored procedure by itself just fine.
> If anyone has any idea as to what the issue may be, that would be
> fantastic!!
> Thanks!
>
>
>|||Thanks!
I actually discovered the problem about five minutes after I posted my
question!
"Bruce L-C [MVP]" wrote:
> Is there a reason you are not using a command type of stored procedure? If
> you do this then RS automatically determines the parameters and the
> parameter data type and creates the report parameters for you. That would
> solve your problem.
> But, given what you have below the issue is that for whatever reason the
> query parameter @.TABLENAME is not mapped to your report parameter. On the
> dataset tab click on the ..., parameters tab and make sure the @.TABLENAME
> parameter is mapped to the report parameter.
> This error is what you get when this mapping has not occured.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "A. Robinson" <ARobinson@.discussions.microsoft.com> wrote in message
> news:974C277C-B4F0-4940-A7E9-E8CDCD33D27F@.microsoft.com...
> > I've got a report that is pretty simple but for some reason I keep getting
> > the following error when I try to run it:
> >
> > An error occured during local report processing.
> > An error has occured during report processing.
> > Query execution failed for data set "Dataset 1"
> > Must declare the scalar variable "@.TABLENAME".
> >
> > This is the actual dataset I'm trying to run:
> > EXEC dbo.Report_TSQL_By_ID_Archive @.TABLENAME, @.SQL_ID, @.DB_ID
> >
> > This is set up as COMMAND TYPE of TEXT.
> >
> >
> > Here's the actual stored procedure being called:
> >
> > ALTER proc [dbo].[Report_TSQL_by_ID_Archive]
> > ----
> > -- Description: Report all transactions from a given trace table by SQL_ID
> > -- Revision History:
> > ----
> > @.TABLENAME varchar(128),
> > @.SQL_ID int,
> > @.DB_ID int,
> > @.Sort varchar(20) = 'CPU'
> > as
> > set nocount on
> >
> > --DECLARE @.Table VARCHAR(128)
> >
> > --Set @.TABLENAME = N'MTGSMNEG034_' + CONVERT(VARCHAR(24), DATEADD(day, -1,
> > getdate()), 110)
> >
> > exec ('
> > select StartTime, Reads, CPU, Duration, spid, [SQL] => > convert( varchar(4000), substring( TextData, 1, 4000 ) )
> > from [' + @.TABLENAME + '] t
> > join [' + @.TABLENAME + '_id] i on t.RowNumber = i.RowNumber
> > where i.id = ' + @.SQL_ID + '
> > and i.databaseID = ' + @.DB_ID)-- + '
> > --order by ' + @.Sort + 'desc
> > --')
> > GO
> >
> > I've got another report that is literally the exact same thing, except
> > there
> > is no @.TABLENAME parameter in the stored procedure and it runs just fine.
> > I'm
> > also able to run the stored procedure by itself just fine.
> >
> > If anyone has any idea as to what the issue may be, that would be
> > fantastic!!
> >
> > Thanks!
> >
> >
> >
> >
> >
> >
>
>