Can I have Exec(...) statements within a transcation ?

Is the following T-SQL correct, in which I am trying to make sure that th effect of 3 stored procedures gets reversed in case of an error ? I know if in place of stored procedures I had action queries like 'ActionQry1', 'ActionQry2' and 'ActionQry3' then the transaction logic would work. But will it work even if exec(...) statements are there in the transaction ? Each stored procedure is made up of an action query.

begin tran
exec("storeprocedure1('2')")
if @.@.error=0
begin
exec("storeprocedure2")
if @.@.error=0
begin
exec("storeprocedure3(122)")
if @.error=0
commit tran
else
rollback tran
end
else
rollback tran
end
else
rollback transure, exec statements will work,
but your syntax is not correct, it should be like this:

 begin tran
 statement1
 if (@.@.error <> 0)
 begin
 rollback tran
 return
 end
 statement2
 if (@.@.error <> 0)
 begin
 rollback tran
 return
 end
 commit tran

can i get the output of 'sp_columns' system SP into a table through T-SQL

Hi,
I want to collect the output of system stored procedure 'sp_columns' into a temporary table. Please suggest how can i achieve this.

Ashu Sharma

Create a temp table with columns as described at sp_columns in BOL and then do
INSERT INTO your_temp_table EXEC sp_columns
--
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de|||Thanks Franksql

Can a managed udf contain a static dictionary<>? Is this a wise idea?

I currently have a udf written in T-SQL that's getting way too logically complicated!

It’s typically accessed like this:

SELECT PartNumber,dbo.PartPrice(Manufacturer, Model, AssemblageInfo, Version, CustomerDiscountLevel) FROM WorkOrders where OrderNumber=123456

The udf does some complicated manipulations on the parameters and eventually does a SELECT on a lookup table and returns the result.

If I make this a managed code udf, the logic gets much simpler to write (great!).

But, my question is:

Can I take the lookup table and embed it in the udf--so the udf doesn't have to go to the database to do the lookup?

Would I do that in a STATIC dictionary<>?

Is it wise to keep the info statically?

The lookup table consists of 3600(+/-) elements and changes exactly once a month.

The SELECT statement using the udf typically returns several thousand rows.

The SELECT is done often.

--Mark

If the table changes over the time, I would not implement this as a static dictionary as you would have to recreate the function everytime the tables changes and you would hae more trouble changing the data of the table than it would be in a normal static data table in SQL Server.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Hi Jens!

Thanks for the response!

I thought of the refresh function thing.

I 'm willing to make it a monthly chore to refresh the function in exchange for a faster static dictionary lookups--but only if it really will speed up the SELECTs.

I guess this brings the question of when the static dictionary goes out of scope on a function call.

-

For example, if the dictionary is reloaded for each row of a SELECT, then it's worse than worthless!

If the dictionary is retained for all rows in a SELECT statement, it **might** be useful.

If the dictionary is retained over many SELECT's then it's worthwhile.

If the dictionary is retained all month, then it's priceless (as the mastercard commercial says!).

When does a static dictionary within a function go out of scope?

Is a static dictionary retained in memory for all rows in a SELECT statement?

Is a static dictionary retained in memory between function calls?

Does the SQL engine cache the function and then release the dictionary from memory after a time-out period?

Thanks, Mark

|||

I finally got to a machine with SQL2005 to try the static dictionary<>

Turns out you can't even use static variables in a managed udf--at least not without declaring the code as unsafe.

--Mark

Calling webservice from a T-SQl stored Procedure

Hi,

I need to call a web service (consume a webservice)from a T-SQL stored procedure. Is there a way to do this. If not is there a way to make a simple http request, something like a utl_http in oracle.

At the moment iam using a MSSOAP30.SOAPCLIENT object created using sp_OACreate to make this call. However this means that the soap toolkit be installed on the pc on which SQL server is installed. I was hoping to find a completely independent way.

Also when i call sp_OACreate where does sqlserver 2005 look to find that object. Iam thinking of putting the MSSOAP30.dll on that machine, if all else fails.

Ahmad

Hi,

you can create an object of XMLHTTP using sp_oacreate as

exec @.hr = sp_OACreate 'MSXML2.ServerXMLHttp', @.obj OUT

exec @.hr = sp_OAMethod @.obj, 'Open', NULL, 'GET', @.sUrl, false

exec @.hr = sp_OAMethod @.obj, 'send'

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!!