Can I define global or static variables?

Hi, I need to define a global or static variable, but I don't know how I can do it.
I need to number each "Detail" line, but I can't use the "Record Number" of "Special Fields" because each database record can create more than one detail line. I try to solve this problem using a global or static variable, but if isn't possible, what do you suggest me?

Thanks and sorry for my bad English.I've just done this very thing in a report I've been working on. The method I used is dependent on using a group header along with the details section.

1) Create a formula field (I called mine CounterReset) and use the following formula:
WhilePrintingRecords;
Global numbervar GroupRecCount;
GroupRecCount := 0;

2) Add this field to your group header. You can supress it if you don't want it to show on your report.

3) Crate another formula field (I called mine CountOfRecordsInGroup) and paste the following formula:
WhilePrintingRecords;
Global numbervar GroupRecCount;
GroupRecCount := GroupRecCount + 1;

4) Use this field in your details section as the line number. It will reset to zero when your groupnumber advances and *sounds* like what you are looking for from the description of your problem.

Hope this helps!

Z|||Thank you very much zilonox!! I've been asking this for several days and nobody could help me.
Greetings,
Christian J.

Can I create Letter type reports within the Report Builder

Hi,

I need to create letter type reports using the report builder, is this possible?. I am trying to create a letter that embeds some variable fields, i.e,. name, address, contact number. I can't seem to find a way to create page headers, and embed report data fields within text. Is this not possible within the report builder? it seems that the only thing you can do within the report builder is add fields to their predesignated areas. I am being forced to use the report builder instead of the report designer in order to give the end user the ability to make modifications to the report/letter. This is very frustrating. not being able to allow the user to edit report designer files, and then having force them to use report builder which doesn't seem to support some simple features..

Any suggestions would be appreciated...

Sorry, the current version of Report Builder is very limited in its functionality and you can't do what you are trying to do.

There is an undocumented feature in Report Builder that may help. You can create a textbox and set its value to an RDL expression, by typing the RDL expression into the text area. This is very limited since you cannot create custom datasets to reference.

Can I ask how to use 'weight' variable in descision tree and how to use cross validati

Also when using cross validation, which descision tree should we choose?

Thanks very much

Thank you!

|||

What do you mean by 'Weight variable'?

Also, regarding cross validation -- it is not built in in SQL Server 2005. If you write your own cross validation mechanism (either a stored procedure or some client side code), then you should be able to use it with any algorithm.

can database name be assigned as a variable?

I need to have a query like below to run to select all the fields from different databases that each database have a table named 'table1'

select * from test...table1

select * from test1..table1

I have the following code sample:

declare @.dbname varchar(50)
set @.dbname='test'
select @.dbname
select * from @.dbname..table1

However I received the error messge when I ran this code. Can anyone help to resolve this?

Thank you!

Hi

One way of doing this is to build the query dynamically and use the Exec, as shown in the example below:

Declare @.xQuery varchar(1000)
Select @.xQuery = 'Select * from test..table1'
Exec(@.xQuery)

Select @.xQuery = 'Select * from test1..table1'
Exec(@.xQuery)

---

Best regards
Georg
www.l4ndash.com - Log4Net Dashboard / Log4Net Viewer

Can a sql 2005 function return more than a variable

Hi,

I have a sql 2005 function who return a distance from 2 zipcodes. This function is called from a Stored procedure like this :

SELECT *, dbo.fn_GetDistance (...) AS Distance

In this function, i have a Latitude and i want this Latitude to be also returned.

It is possible or a function can return only one variable?

If it is possible, what's the syntax of it?

Thanks in advance

You can return a table, something like this:

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

CREATEFUNCTION LongLatDistance

(

-- Add the parameters for the function here

@.ZipCode1varchar(10),@.ZipCode2varchar(10)

)

RETURNS @.ResultTableTABLE(LongitudeDecimal(18,6),Latitudedecimal(18,6),Distancedecimal(18,6))

AS

BEGIN

INSERTINTO @.ResultTable (Longitude,Latitude,Distance) SELECT FieldsFROM TableName

RETURN

END

GO

|||

And i call it how from the stored procedure?

|||

SELECT Longitude,Latitude,Distance FROM dbo.LongLatDistance('90210',92630')

I wasnt clear on what exactly else you wanted to return. The sample is only returning the coords of one zip code, you would modify it obviously for your situation.

Can a script prompt for a value to be inputed?

Hello all,
Is there any way to get a script to prompt for a value (input box or
something else) or to use a value from a variable using SQLCMD?
I have these SELECT scripts that are essentially Crosstab/Pivot queries
where each column is a month of data and is created by a CASE statement.
When updates are done, the CASE statements for the new months need to be
created.
We want to automate this process for a client so they can have one of their
non-DBA employees do the work.
Now I have come up with some Dynamic SQL that will create the same script
and run it based on just a start date and an end date. Start date will be
fixed, the end date will change from update to update. Using the Dynamic
SQL script is alot easier--all we need to do is change one single end date,
rather than adding several CASE statements to the non-Dynamic SQL script.
What I would like to do is create a Dynamic SQL script that will prompt for
an end date to be entered. Now I have figured out how to get SQLCMD to run
a script file and save results to a file, so if I could get the Dynamic SQL
script to accept a variable from SQLCMD to set the end date, that would work
too.
Thanks for any help anyone can provide,
Conan KellyEnd users, dynamic crosstab queries...have you heard of Rac? :)
www.rac4sql.net
We did the dirty work, you take the credit.
www.beyondsql.blogspot.com
Putting logic back in application development|||There's no prompting in the SQL Server tools. But SQLCMD accepts a variable (though the -v switch
and $(varname) literal inside the script file).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
news:a21Ei.500477$p47.262287@.bgtnsc04-news.ops.worldnet.att.net...
> Hello all,
> Is there any way to get a script to prompt for a value (input box or something else) or to use a
> value from a variable using SQLCMD?
> I have these SELECT scripts that are essentially Crosstab/Pivot queries where each column is a
> month of data and is created by a CASE statement. When updates are done, the CASE statements for
> the new months need to be created.
> We want to automate this process for a client so they can have one of their non-DBA employees do
> the work.
> Now I have come up with some Dynamic SQL that will create the same script and run it based on just
> a start date and an end date. Start date will be fixed, the end date will change from update to
> update. Using the Dynamic SQL script is alot easier--all we need to do is change one single end
> date, rather than adding several CASE statements to the non-Dynamic SQL script.
> What I would like to do is create a Dynamic SQL script that will prompt for an end date to be
> entered. Now I have figured out how to get SQLCMD to run a script file and save results to a
> file, so if I could get the Dynamic SQL script to accept a variable from SQLCMD to set the end
> date, that would work too.
> Thanks for any help anyone can provide,
> Conan Kelly
>|||Tibor,
Thank you for your help.
That works EXACTLY the way I want it to.
Thanks again,
Conan
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%23j$uliR8HHA.1168@.TK2MSFTNGP02.phx.gbl...
> There's no prompting in the SQL Server tools. But SQLCMD accepts a
> variable (though the -v switch and $(varname) literal inside the script
> file).
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Conan Kelly" <CTBarbarinNOSPAM@.msnNOSPAM.comNOSPAM> wrote in message
> news:a21Ei.500477$p47.262287@.bgtnsc04-news.ops.worldnet.att.net...
>> Hello all,
>> Is there any way to get a script to prompt for a value (input box or
>> something else) or to use a value from a variable using SQLCMD?
>> I have these SELECT scripts that are essentially Crosstab/Pivot queries
>> where each column is a month of data and is created by a CASE statement.
>> When updates are done, the CASE statements for the new months need to be
>> created.
>> We want to automate this process for a client so they can have one of
>> their non-DBA employees do the work.
>> Now I have come up with some Dynamic SQL that will create the same script
>> and run it based on just a start date and an end date. Start date will
>> be fixed, the end date will change from update to update. Using the
>> Dynamic SQL script is alot easier--all we need to do is change one single
>> end date, rather than adding several CASE statements to the non-Dynamic
>> SQL script.
>> What I would like to do is create a Dynamic SQL script that will prompt
>> for an end date to be entered. Now I have figured out how to get SQLCMD
>> to run a script file and save results to a file, so if I could get the
>> Dynamic SQL script to accept a variable from SQLCMD to set the end date,
>> that would work too.
>> Thanks for any help anyone can provide,
>> Conan Kelly
>

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure
A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name o
f the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it f
rom stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this
be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor =
@.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly some
thing with the
[sp_executesql] line. No amount of massaging will get this to work -- my gu
ess is that
the structure of [sp_executesql] won't permit a cursor variable to be handle
d. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in m
y case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the V
ARYING
keyword. From the stored procedure documentation (which I kind of assumed t
hat
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was nece
ssary when
using a cursor variable. But, it appears not to be the case, and that was t
he one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing dat
a
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the curso
r variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of th
e dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it compla
ins that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the o
riginal "By
Name" and the variable) -- and I need to essentially close both handles befo
re the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in
my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the
VARYING
> keyword. From the stored procedure documentation (which I kind of assumed
that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was ne
cessary when
> using a cursor variable. But, it appears not to be the case, and that was
the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor
output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could b
e found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cur
sor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dyna
mic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it comp
lains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the
original "By
> Name" and the variable) -- and I need to essentially close both handles before the

cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
I'm
>

Can a cursor variable be assigned to a dynamically named cursor?

(SQL Server 2000, SP3a)
(From a different thread.)
Hello, all!
I have an open global cursor that is created dynamically by stored procedure A. I'd like
to reference this cursor from stored procedure B. I know the dynamic name of the cursor,
but I know of no way to get a "handle" of this cursor so that I can use it from stored
procedure B in a cursor variable.
The [sp_describe_cursor] returns something called a cursor_handle. Can this be used
somehow to set a cursor variable?
I thought maybe I could do something like this:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor varying output', @.Cursor = @.Cursor
output
execute('deallocate ' + @.CursorName)
But I get this error:
Server: Msg 181, Level 15, State 1, Line 1
Cannot use the OUTPUT option in a DECLARE statement.
Server: Msg 137, Level 15, State 1, Line 1
Must declare the variable '@.Cursor'.
Which I don't fully understand. But, after some fiddling, it's clearly something with the
[sp_executesql] line. No amount of massaging will get this to work -- my guess is that
the structure of [sp_executesql] won't permit a cursor variable to be handled. :-(
Thanks for any help anyone can provide!
John PetersonCursors are usually best avoided because of their performance/resource
implications. Erland has an article on alternative methods for sharing data
between SPs:
http://www.sommarskog.se/share_data.html
For completeness, here's an amended version of your code:
...
SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
...
Now reference the cursor by variable (@.cursor).
--
David Portas
SQL Server MVP
--|||David,
You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
writing a management procedure that lends itself well to using cursors.
Thanks for the link on other techniques for sharing data. :-)
I think you solved my issue! From what I can tell, you merely removed the VARYING
keyword. From the stored procedure documentation (which I kind of assumed that
[sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
using a cursor variable. But, it appears not to be the case, and that was the one
combination I *didn't* try!
Thanks so much! :-)
John Peterson
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> Cursors are usually best avoided because of their performance/resource
> implications. Erland has an article on alternative methods for sharing data
> between SPs:
> http://www.sommarskog.se/share_data.html
> For completeness, here's an amended version of your code:
> ...
> SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> ...
> Now reference the cursor by variable (@.cursor).
> --
> David Portas
> SQL Server MVP
> --
>|||Oddly, it seems like there are some things that can't be done with the cursor variable.
For example, I tried:
open @.Cursor
But that doesn't appear to work. Only when the OPEN is in the context of the dynamic SQL
does it seem to open the cursor for the variable.
Additionally:
close @.Cursor
deallocate @.Cursor
Don't appear to work either. If I try and re-run my code snippet, it complains that the
cursor still exists.
Unless the issue is that there are *two* "handles" to the same cursor (the original "By
Name" and the variable) -- and I need to essentially close both handles before the cursor
will be destroyed?
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> David,
> You're right -- I appreciate that cursors aren't wholly performant, but in my case, I'm
> writing a management procedure that lends itself well to using cursors.
> Thanks for the link on other techniques for sharing data. :-)
> I think you solved my issue! From what I can tell, you merely removed the VARYING
> keyword. From the stored procedure documentation (which I kind of assumed that
> [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> using a cursor variable. But, it appears not to be the case, and that was the one
> combination I *didn't* try!
> Thanks so much! :-)
> John Peterson
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > Cursors are usually best avoided because of their performance/resource
> > implications. Erland has an article on alternative methods for sharing data
> > between SPs:
> >
> > http://www.sommarskog.se/share_data.html
> >
> > For completeness, here's an amended version of your code:
> > ...
> > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > ...
> >
> > Now reference the cursor by variable (@.cursor).
> >
> > --
> > David Portas
> > SQL Server MVP
> > --
> >
> >
>|||I finally settled on this test bed, which appears to work successfully:
declare @.CursorName nvarchar(4000) select @.CursorName = 'cur'
execute
(
'
declare ' + @.CursorName + ' cursor global forward_only read_only for
select name from sysobjects
'
)
declare @.Cursor cursor
declare @.Query nvarchar(4000)
select @.Query = 'set @.Cursor = ' + @.CursorName + ' open @.Cursor'
execute [dbo].[sp_executesql] @.Query, N'@.Cursor cursor output', @.Cursor = @.Cursor output
declare @.Name sysname
fetch next from @.Cursor into @.Name
print @.Name
close @.Cursor
deallocate @.Cursor
execute('deallocate ' + @.CursorName)
Thanks again for your help, David! I was dispairing that a solution could be found. :-)
John Peterson
"John Peterson" <j0hnp@.comcast.net> wrote in message
news:eKHgxlI%23DHA.2636@.TK2MSFTNGP09.phx.gbl...
> Oddly, it seems like there are some things that can't be done with the cursor variable.
> For example, I tried:
> open @.Cursor
> But that doesn't appear to work. Only when the OPEN is in the context of the dynamic
SQL
> does it seem to open the cursor for the variable.
> Additionally:
> close @.Cursor
> deallocate @.Cursor
> Don't appear to work either. If I try and re-run my code snippet, it complains that the
> cursor still exists.
> Unless the issue is that there are *two* "handles" to the same cursor (the original "By
> Name" and the variable) -- and I need to essentially close both handles before the
cursor
> will be destroyed?
>
> "John Peterson" <j0hnp@.comcast.net> wrote in message
> news:u4t38hI%23DHA.888@.tk2msftngp13.phx.gbl...
> > David,
> >
> > You're right -- I appreciate that cursors aren't wholly performant, but in my case,
I'm
> > writing a management procedure that lends itself well to using cursors.
> >
> > Thanks for the link on other techniques for sharing data. :-)
> >
> > I think you solved my issue! From what I can tell, you merely removed the VARYING
> > keyword. From the stored procedure documentation (which I kind of assumed that
> > [sp_executesql] was leveraging) it seemed as if the VARYING keyword was necessary when
> > using a cursor variable. But, it appears not to be the case, and that was the one
> > combination I *didn't* try!
> >
> > Thanks so much! :-)
> >
> > John Peterson
> >
> >
> > "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> > news:gLedncWQLe7NqKrdRVn-hQ@.giganews.com...
> > > Cursors are usually best avoided because of their performance/resource
> > > implications. Erland has an article on alternative methods for sharing data
> > > between SPs:
> > >
> > > http://www.sommarskog.se/share_data.html
> > >
> > > For completeness, here's an amended version of your code:
> > > ...
> > > SET @.query = 'SET @.cursor = ' + @.cursorname + ' OPEN @.cursor'
> > > EXEC sp_executesql @.query, N' @.CURSOR CURSOR OUTPUT', @.cursor OUTPUT
> > > ...
> > >
> > > Now reference the cursor by variable (@.cursor).
> > >
> > > --
> > > David Portas
> > > SQL Server MVP
> > > --
> > >
> > >
> >
> >
>

can a column be named as a variable?

i have declared a variable...lets say @.month
can i have a column named as value of this variable..ie any value this variable returns when executed?
let me know asap
regards
Nikhil
refer to this url:
http://www.algonet.se/~sommar/dynamic_sql.html
Vishal Parkar
vgparkar@.yahoo.co.in | vgparkar@.hotmail.com

Calling variable inside T-SQL statement

Can someone please take a quick look at this and tell me what I'm doing wrong I'm sure it's something simple. I'm a little new to stored procedures but I've been using SQL and T-SQL for quite some time, I've just always used inline queries with my ASP. This procedure needs to be run monthly by me or another person I grant access to and will update sales information that our sales staff will be paid commission on. I need to supply the start date and and end date for the query and it will pull this information from our business system which is hosted remotely by a third party and pull it into our local SQL server where we can run commission reports against it. (I hope this is enough information you can understand where I'm trying to go with this). I know my problem right now lies in how I'm trying to call the variable inside of my T-SQL. Any help is appreciated. This is an old Unix system and it stores the date as YYYYMMDD as numeric values incase someone wonders why I have dimed my dates as numeric instead of as datetime =)

I'm using a relativity client to create an ODBC connection to the UNIX server and then using a linked server to map a connection in SQL this is the reason for the OpenQuery(<CompanyName>

SETANSI_NULLSON

GO

SETQUOTED_IDENTIFIERON

GO

-- =============================================

-- Author: XXXXXXXXXXXXX

-- Create date: 10/4/2007

-- Description: This proc is designed to pull all CSA

-- part sales from XXXXXX business system and upload them

-- into the local XXXXXXXX Database for commission reporting

-- =============================================

CREATEproc usp_CSAPartsSalesUpdate@.date1int, @.date2int

As

INSERTINTO CSAPartsSales( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount)

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmtFromOpenQuery(<CompanyName>,'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE (((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>='''+ @.date1+''' And (PMINVHST.HInvDate)<='''+ @.date2+''') AND ((Trim([CPBASC_All].[SalesRoute]))<>'''' And (Trim([CPBASC_All].[SalesRoute]))<>''000''))')

In this example date1 will be equal to 20070901 and date2 will be equal to 20070930 so I can pull all CSA sales for the month of September.

This is the error message I get when I try to create the proc:

Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17

Incorrect syntax near '+'.

~~~ Thanks All~~~

You need to cast your variable as a varchar, ie:

>=''' + CAST(@.date1 as varchar(8)) + ''' AND ( ...etc

|||

Thanks for the reply Sswanner1 I'll try casting my variables tomorrow when I get into work and see if that corrects the problem.

|||

I tried casting my variables this morning as you suggested but it still returns the same error message

((PMINVHST.HInvDate)>='''+Cast(@.date1asvarchar(8))+''' And (PMINVHST.HInvDate)<='''+Cast(@.date2asvarchar(8))+''') AND

Msg 102, Level 15, State 1, Procedure usp_CSAPartsSalesUpdate, Line 17

Incorrect syntax near '+'.

|||

If PMINVHST.HInvDate is also an integer, you've got too many single quotes in there. Ultimately, you want it to look like this:

((PMINVHST.HInvDate)>=20070810, not ((PMINVHST.HInvDate)>='20070810'

|||

PMINVHST.HInvDate is stored as text in the unix system

If I take the exact same sql statement and declare the variables and then set the values and then print the results the query looks exactly like I need it to. I can take the results that print to the screen copy them into a query edititor and run the query and I get the results I'm looking for. It has something to do with the way I'm trying to call it...I can't put my finger on it =(

|||

If I run this SP and then copy and paste the results to query editor it returns exactly what I need.

Declare @.commandasnvarchar(4000)

Declare @.date1asvarchar(8)

Declare @.date2asvarchar(8)

Set @.date1= 20070901

Set @.date2= 20070930

Set @.command='INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(XXXXXXXX, ''Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE ((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>='''''+ @.date1+''''' and (PMINVHST.HInvDate)<='''''+ @.date2+''''') AND (Trim([CPBASC_All].[SalesRoute])<>'''''''' And Trim([CPBASC_All].[SalesRoute])<>''''000'''')'')'

Print @.command

Print Results:

INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(XXXXXXXXX, 'Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE ((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>=''20070901'' and (PMINVHST.HInvDate)<=''20070930'') AND (Trim([CPBASC_All].[SalesRoute])<>'''' And Trim([CPBASC_All].[SalesRoute])<>''000'')')

|||

I just copied and pasted your code and ran the query and got the same printed results. One thing I noticed is that it's putting 2 single quotes before and after each of your dates. Is this what you are wanting? Or are you wanting a regular quote? Or just a single, single quote?

'20070901'

''20070901''

"20070901"

|||

For everyone reading this if you ever run into a problem trying to use OpenQuery in an SP be aware that OpenQuery does not play nice. If you're problem is similar please try running your SP using this method. I battled this for a while and this was the only way I could make it work. OpenQuery does not like taking expressions and parameters directly.

setANSI_NULLSON

setQUOTED_IDENTIFIERON

GO

-- =============================================

-- Author:

-- Create date: 10/4/2007

-- Description: This proc is designed to pull all CSA

-- part sales from XXXXX business system and upload them

-- into the local XXXXXX Database for commission reporting

-- =============================================

ALTERproc [dbo].[usp_CSAPartsSalesUpdate]

(

@.date1varchar(8),

@.date2varchar(8)

)

As

Declare @.commandasnvarchar(4000)

Set @.command='INSERT INTO CSAPartsSales ( CSA, CustomerNumber, CustomerName, Location, InvoiceNumber, InvoiceDate, InvoiceAmount )

SELECT SalesRoute, HInvCust, CustOrCompName, HInvLoc, HInvNo, HInvDate, HInvAmt From OpenQuery(LinkedServerName, ''Select CPBASC_All.SalesRoute, PMINVHST.HInvCust, CPBASC_All.CustOrCompName, PMINVHST.HInvLoc, PMINVHST.HInvNo, PMINVHST.HInvDate, PMINVHST.HInvAmt

FROM PMINVHST INNER JOIN CPBASC_All ON PMINVHST.HInvCust = CPBASC_All.CustomerNo

WHERE ((PMINVHST.HInvAmt)<>0) AND ((PMINVHST.HInvDate)>='''''+ @.date1+''''' and (PMINVHST.HInvDate)<='''''+ @.date2+''''') AND (Trim([CPBASC_All].[SalesRoute])<>'''''''' And Trim([CPBASC_All].[SalesRoute])<>''''000'''')'')'

Exec(@.command)

|||

Thanks Sswanner1 for all your efforts!!