Calling Stored Procedure from SSIS

Hi

I am trying to call a stored procedure which akes 1 input param from SSIS. I am using Execute SQL Task->Expressions->"exec s_Staging '"+ @.[User::tblName] +"'"

@.[User::tblName] is the variable with Data Type:String ,Value:My_table

SQLStatement->Stored Procedure Name

But It throws an error

[Execute SQL Task] Error: Executing the query "exec s_Staging 'My_Table' " failed with the following error: "Incorrect syntax near 'My_Table' ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What connection type are you using in your SQL task? Each connection type supports a different syntax.

Use the following syntax in your task:
OLEDB: MyStoredProc ?

ODBC: {call MyStoredProc (?)}
ADO, ADO.NET: MyStoredProc

Also, make sure that you have the paramter binding set appropriately (see http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx for more info)|||

Hi,

Try this out :

"exec s_Staging <ParameterName>='"+ @.[User::tblName] +"'"

Where <ParameterName> is parameter in SP whose value you are assigning. Also "IsQueryStoredProcedure" should be set to false because you are executing inline query.

Thanks

Mohit

|||

Thanks a lot now its working.

|||

Hi

I am converting a DTS into SSIS, this DTS package first truncates 5 tables and than uses 5 different transformation to insert data from ORACLE source to SQL Destination. I am using Execute SQL Task to truncate the tables and than 5 Data Flow Task to carry out the tranfer of data. My question is,is it avisable to put these Data Flow Task in one Sequence Container. Will it improve the performance of SSIS?

|||

Hi,

Using a sequence container only ensures that all the tasks in the container are executed simultaneously and only comes out of the after completing all the tasks in container. If you have any task to do only after completing all the 5 transfer task then use sequence container otherwise it's an overhead.

Thanks

Mohit

|||

We don't have any such requirement,as this DTS is used to transfer data to 5 different tables. I think it would better not to use it. I wanted to use it as the SSIS is looking very odd with 1 Execute SQl Task and on success 5 Data Flow Task.

Thanks.

|||

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

|||

Paarul wrote:

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

Have you examined the running queries using SQL Server Profiler?|||

Thanks. This works fine. I have another question related to SSIS and WSE 3.0. I have to use custom application within the script task but whenever the script tries to initialize my custom class which uses WSE policy, it fails. What i need to know is if there is any adaptor for WSE 3.0 for SSIS? if not then how can you load wse3policyCache.config file within SSIS so that the custom dll can refer to policy?

Any help will be appreciated.. My email id is gau2902@.yahoo.com

Thanks

Gaurav Gupta

|||

You can provide a config file to the SSIS package host, in which you can inlcude WSE settings. If you just use the MS tools to run packages then target the folowing with a config file including the WSE 3 stuff-

DTExec.exe

DtsDebugHost.exe

dtshost.exe|||

I have tried adding the following code in all 3 config files you have mentioned

<microsoft.web.services3>
<security>
<x509 allowTestRoot="true" revocationMode="Offline" />
</security>
<policy fileName="wse3policyCache.config" />
</microsoft.web.services3>

and have the wse3policyCache.config under the same location. When i run the task SSIS is not able to load the cache config file. Then i have even tried copying policies segment from config file and replacing the policay element mentioned above, but this again didn't work.

Note: The policy and config files work fine in .net wrapper application outside SSIS

sql

Calling Stored Procedure from SSIS

Hi

I am trying to call a stored procedure which akes 1 input param from SSIS. I am using Execute SQL Task->Expressions->"exec s_Staging '"+ @.[User::tblName] +"'"

@.[User::tblName] is the variable with Data Type:String ,Value:My_table

SQLStatement->Stored Procedure Name

But It throws an error

[Execute SQL Task] Error: Executing the query "exec s_Staging 'My_Table' " failed with the following error: "Incorrect syntax near 'My_Table' ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What connection type are you using in your SQL task? Each connection type supports a different syntax.

Use the following syntax in your task:
OLEDB: MyStoredProc ?

ODBC: {call MyStoredProc (?)}
ADO, ADO.NET: MyStoredProc

Also, make sure that you have the paramter binding set appropriately (see http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx for more info)|||

Hi,

Try this out :

"exec s_Staging <ParameterName>='"+ @.[User::tblName] +"'"

Where <ParameterName> is parameter in SP whose value you are assigning. Also "IsQueryStoredProcedure" should be set to false because you are executing inline query.

Thanks

Mohit

|||

Thanks a lot now its working.

|||

Hi

I am converting a DTS into SSIS, this DTS package first truncates 5 tables and than uses 5 different transformation to insert data from ORACLE source to SQL Destination. I am using Execute SQL Task to truncate the tables and than 5 Data Flow Task to carry out the tranfer of data. My question is,is it avisable to put these Data Flow Task in one Sequence Container. Will it improve the performance of SSIS?

|||

Hi,

Using a sequence container only ensures that all the tasks in the container are executed simultaneously and only comes out of the after completing all the tasks in container. If you have any task to do only after completing all the 5 transfer task then use sequence container otherwise it's an overhead.

Thanks

Mohit

|||

We don't have any such requirement,as this DTS is used to transfer data to 5 different tables. I think it would better not to use it. I wanted to use it as the SSIS is looking very odd with 1 Execute SQl Task and on success 5 Data Flow Task.

Thanks.

|||

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

|||

Paarul wrote:

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

Have you examined the running queries using SQL Server Profiler?|||

Thanks. This works fine. I have another question related to SSIS and WSE 3.0. I have to use custom application within the script task but whenever the script tries to initialize my custom class which uses WSE policy, it fails. What i need to know is if there is any adaptor for WSE 3.0 for SSIS? if not then how can you load wse3policyCache.config file within SSIS so that the custom dll can refer to policy?

Any help will be appreciated.. My email id is gau2902@.yahoo.com

Thanks

Gaurav Gupta

|||

You can provide a config file to the SSIS package host, in which you can inlcude WSE settings. If you just use the MS tools to run packages then target the folowing with a config file including the WSE 3 stuff-

DTExec.exe

DtsDebugHost.exe

dtshost.exe|||

I have tried adding the following code in all 3 config files you have mentioned

<microsoft.web.services3>
<security>
<x509 allowTestRoot="true" revocationMode="Offline" />
</security>
<policy fileName="wse3policyCache.config" />
</microsoft.web.services3>

and have the wse3policyCache.config under the same location. When i run the task SSIS is not able to load the cache config file. Then i have even tried copying policies segment from config file and replacing the policay element mentioned above, but this again didn't work.

Note: The policy and config files work fine in .net wrapper application outside SSIS

Calling Stored Procedure from SSIS

Hi

I am trying to call a stored procedure which akes 1 input param from SSIS. I am using Execute SQL Task->Expressions->"exec s_Staging '"+ @.[User::tblName] +"'"

@.[User::tblName] is the variable with Data Type:String ,Value:My_table

SQLStatement->Stored Procedure Name

But It throws an error

[Execute SQL Task] Error: Executing the query "exec s_Staging 'My_Table' " failed with the following error: "Incorrect syntax near 'My_Table' ". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

What connection type are you using in your SQL task? Each connection type supports a different syntax.

Use the following syntax in your task:
OLEDB: MyStoredProc ?

ODBC: {call MyStoredProc (?)}
ADO, ADO.NET: MyStoredProc

Also, make sure that you have the paramter binding set appropriately (see http://sqljunkies.com/WebLog/knight_reign/archive/2005/10/05/17016.aspx for more info)|||

Hi,

Try this out :

"exec s_Staging <ParameterName>='"+ @.[User::tblName] +"'"

Where <ParameterName> is parameter in SP whose value you are assigning. Also "IsQueryStoredProcedure" should be set to false because you are executing inline query.

Thanks

Mohit

|||

Thanks a lot now its working.

|||

Hi

I am converting a DTS into SSIS, this DTS package first truncates 5 tables and than uses 5 different transformation to insert data from ORACLE source to SQL Destination. I am using Execute SQL Task to truncate the tables and than 5 Data Flow Task to carry out the tranfer of data. My question is,is it avisable to put these Data Flow Task in one Sequence Container. Will it improve the performance of SSIS?

|||

Hi,

Using a sequence container only ensures that all the tasks in the container are executed simultaneously and only comes out of the after completing all the tasks in container. If you have any task to do only after completing all the 5 transfer task then use sequence container otherwise it's an overhead.

Thanks

Mohit

|||

We don't have any such requirement,as this DTS is used to transfer data to 5 different tables. I think it would better not to use it. I wanted to use it as the SSIS is looking very odd with 1 Execute SQl Task and on success 5 Data Flow Task.

Thanks.

|||

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

|||

Paarul wrote:

The only concern I have about this SSIS is, its running very slow. I just wanted to figure out something to improve the speed.

Have you examined the running queries using SQL Server Profiler?|||

Thanks. This works fine. I have another question related to SSIS and WSE 3.0. I have to use custom application within the script task but whenever the script tries to initialize my custom class which uses WSE policy, it fails. What i need to know is if there is any adaptor for WSE 3.0 for SSIS? if not then how can you load wse3policyCache.config file within SSIS so that the custom dll can refer to policy?

Any help will be appreciated.. My email id is gau2902@.yahoo.com

Thanks

Gaurav Gupta

|||

You can provide a config file to the SSIS package host, in which you can inlcude WSE settings. If you just use the MS tools to run packages then target the folowing with a config file including the WSE 3 stuff-

DTExec.exe

DtsDebugHost.exe

dtshost.exe|||

I have tried adding the following code in all 3 config files you have mentioned

<microsoft.web.services3>
<security>
<x509 allowTestRoot="true" revocationMode="Offline" />
</security>
<policy fileName="wse3policyCache.config" />
</microsoft.web.services3>

and have the wse3policyCache.config under the same location. When i run the task SSIS is not able to load the cache config file. Then i have even tried copying policies segment from config file and replacing the policay element mentioned above, but this again didn't work.

Note: The policy and config files work fine in .net wrapper application outside SSIS

Calling report from other machine (without domain)

Hi:
I have an stand alone win2k Advanced Server (without domain).
I created a folder (rpp) an I upload all my rdl reports.

Now I can see all reports in my server but I can′t see them in other machines
IE show me a message asking for a user, password an domain but I′ve not domain in my server.
Sorry, but It′s my first time using reporting service and IIS at the same time.

Thanks

ja

first you go to ReportManager and you assigment role to users.

And you calling other server s report on network

for example : \\192.168.1.1\ReportServer

|||

I did that but in other workstations on network IE promt me for user password and domain.

|||

but i did on workstation in Windows XP.

and not see promt.i think problem is in IIS.

i think checking IIS Web Site properties and checking Authentication Mode settings.( Anonymous acces )

|||

Before allowing anonymous access weight the security implications of this - all users will be treated as the anonymous user so you will not be able to set different security on items in the report server based on who is accessing the item. Anonymous is not recommended.

Rather your solution should be to create a local user on your Windows 2000 Server computer and log in with that user at the login prompt. As an administrator on the Windows 2000 Server computer, you will need to grant this windows users rights to view reports by assigning them one or more roles. These can be found on the security tab under properties for the item, either in Report Manager or in Management Studio if you're using SQL Server 2005.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Can you (or anyone) go into a little more detail on the line: Rather your solution should be to create a local user on your Windows 2000 Server computer and log in with that user at the login prompt.

By "log in" do you mean the report user enters username/pwd manually or can this be arranged automatically?

Thank you.

|||

Yes - this either happens automatically or you have to turn on Basic Authentication on your report manager/server virtual directory, but you'll get a standard prompt in IE and you can specify your credentials. You'll then be a windows user from the viewpoint of report server.

-Lukasz

Calling report from other machine (without domain)

Hi:
I have an stand alone win2k Advanced Server (without domain).
I created a folder (rpp) an I upload all my rdl reports.

Now I can see all reports in my server but I can′t see them in other machines
IE show me a message asking for a user, password an domain but I′ve not domain in my server.
Sorry, but It′s my first time using reporting service and IIS at the same time.

Thanks

ja

first you go to ReportManager and you assigment role to users.

And you calling other server s report on network

for example : \\192.168.1.1\ReportServer

|||

I did that but in other workstations on network IE promt me for user password and domain.

|||

but i did on workstation in Windows XP.

and not see promt.i think problem is in IIS.

i think checking IIS Web Site properties and checking Authentication Mode settings.( Anonymous acces )

|||

Before allowing anonymous access weight the security implications of this - all users will be treated as the anonymous user so you will not be able to set different security on items in the report server based on who is accessing the item. Anonymous is not recommended.

Rather your solution should be to create a local user on your Windows 2000 Server computer and log in with that user at the login prompt. As an administrator on the Windows 2000 Server computer, you will need to grant this windows users rights to view reports by assigning them one or more roles. These can be found on the security tab under properties for the item, either in Report Manager or in Management Studio if you're using SQL Server 2005.

Hope that helps,

-Lukasz

This posting is provided "AS IS" with no warranties, and confers no rights.

|||

Can you (or anyone) go into a little more detail on the line:Rather your solution should be to create a local user on your Windows 2000 Server computer and log in with that user at the login prompt.

By "log in" do you mean the report user enters username/pwd manually or can this be arranged automatically?

Thank you.

|||

Yes - this either happens automatically or you have to turn on Basic Authentication on your report manager/server virtual directory, but you'll get a standard prompt in IE and you can specify your credentials. You'll then be a windows user from the viewpoint of report server.

-Lukasz

Calling normal stored procedures from Extended Procedures

Hi

I want to call a database stored procedure from an extended procedure. Can some one provide snippet of code (either in C or C++) to achieve the same ?

Thanks & Regards
ImtiazYou have to make connection to server using ODBC or OLEDB to do this. Please take a look at the call escape sequence topic in Books Online. The url is mk:@.MSITStore:D:\Program%20Files\Microsoft%20SQL%20Server%20Books%20Online\1033\odbcsql.chm::/od_6_035_5dnp.htm. You may have to change the path depending on your installation. You can also find samples in the SQL Server CD.

Calling into a C# DLL from T-SQL

Hi:
I have an encryption DLL written in C#. Is there a way I can call into it
for encrypting and decrypting data during a T-SQL query?
Thanks,
CharlieSQL Server 2000? No; there is no supported way.
SQL Server 2005 -- yes. You can create a CLR UDF and wrap the DLL. But in
2005 it would make more sense to use the built-in encryption features that
SQL Server provides.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:%23MyZ0rt6FHA.2576@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I have an encryption DLL written in C#. Is there a way I can call into it
> for encrypting and decrypting data during a T-SQL query?
> Thanks,
> Charlie
>|||Hello Adam,

> SQL Server 2000? No; there is no supported way.
Not that I like to disagree with Adam [ :) ] but... could you generate a
COM callable wrapper then write an XP that use that to do this work?
But I do agree, with SQL Server 2005, use the built in stuff instead!
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||You -could-, but it is explicitly mentioned as not supported in BOL.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"Kent Tegels" <ktegels@.develop.com> wrote in message
news:b87ad74d0fd8c7b8c31d3bd030@.news.microsoft.com...
> Hello Adam,
>
> Not that I like to disagree with Adam [ :) ] but... could you generate a
> COM callable wrapper then write an XP that use that to do this work?
> But I do agree, with SQL Server 2005, use the built in stuff instead!
> Thank you,
> Kent Tegels
> DevelopMentor
> http://staff.develop.com/ktegels/
>|||"Charlie@.CBFC" <charle1@.comcast.net> wrote in message
news:%23MyZ0rt6FHA.2576@.TK2MSFTNGP09.phx.gbl...
> Hi:
> I have an encryption DLL written in C#. Is there a way I can call into it
> for encrypting and decrypting data during a T-SQL query?
> Thanks,
> Charlie
>
There is in SQL2005, not in SQL2000|||... and here is my canned response to calling managed code from SQL Server
(not including Yukon,
which is of course a different story):
It is not supported for extended stored procedures or sp_OA procedures to ca
ll .NET code in CLR;
hosted within SQL Server's address space..
See:
http://support.microsoft.com/defaul...kb;en-us;322884
Also, below is with permission from David Browne, explaining how you can hav
e SQL Server execute CLR
code executing in its own process:
"
Short answer: Don't do it.
Calling managed code inside a stored procedure is not supported.
http://support.microsoft.com/defaul...kb;en-us;322884
At least not directly. You need some sort of unmanaged proxy to communicate
with your component running in another process.
For instance, http, or, drum roll, a COM+ Server Application.
This will cause COM+ to load an unmanaged proxy object in the SqlServer
process and will load the CLR into a COM+ surrogate process (dllhost.exe).
Which somebody here mentioned last w, and I just got around to testing.
It's all perfectly transparent to you, but you have to set up the COM+
server application.
Remember this is something different from .net remoting. With .NET remoting
you have a _managed_ proxy object in the local process, and so you load the
CLR in the local process as well as the remote process.
Anyway here's what I did:
I created this VB class
comTest.vb listing:
Imports System.Runtime.InteropServices
<ClassInterface(ClassInterfaceType.AutoDual),
ProgId("comTest.comTestClass")> _
Public Class comTest
Public Function Hello() As String
Return "hello"
End Function
End Class
build comTest.dll and registered it with
regasm /codebase comTest.dll /tlb:comTest.tlb
(complains that I haven't strong-named my assembly, which you should do.)
created an empty COM+ server application, set to run under a local
administrator account, and dragged comTest.dll into its components folder.
created an unmanaged host (vbscript will do), and invoked the component
using IDispach just like SQLServer.
test.vbs listing
Set d = CreateObject("comTest.comTestClass")
MsgBox d.Hello
Then I used the .net command line debugger cordbg.exe's 'pro' command to
list the processes hosting the CLR. And procexp.exe from
www.sysinternals.com to verify that the CLR's dll's were not loaded in my
unmanaged process. My unmanaged host did not load the CLR, although it
loaded "comsvcs.dll", and the CLR was loaded by the dllhost.exe process.
Then in sql I ran
declare @.object int
declare @.msg varchar(50)
declare @.rc int
declare @.hr int
declare @.source varchar(1000)
declare @.description varchar(1000)
exec @.rc = sp_oacreate 'comTest.comTestClass', @.object output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'create failed ' + @.description
return
end
exec @.rc = sp_oamethod @.object, 'Hello', @.msg output
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'method failed ' + @.description
return
end
print 'return: ' + @.msg
exec @.rc = sp_oadestroy @.object
if @.rc <> 0
begin
EXEC @.hr = sp_OAGetErrorInfo @.object, @.source OUT, @.description OUT
print 'destroy failed ' + @.description
return
end
Ran fine, and still only one CLR loaded into dllhost.exe's process. So
think we can safely conclude that COM+ server applications do not violate
the prohibition against running managed code in SQLServer's process and
provide a convenient mechanism for interoperating with managed code from
TSQL.
David
"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:OsmkX9t6FHA.636@.TK2MSFTNGP10.phx.gbl...
> You -could-, but it is explicitly mentioned as not supported in BOL.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "Kent Tegels" <ktegels@.develop.com> wrote in message
> news:b87ad74d0fd8c7b8c31d3bd030@.news.microsoft.com...
>

Calling All T-SQL Gods & Goddesses

Hi

I'm having trouble with the script below that it just won't do a
correct ORDER BY for a date field.

When executing the two SELECT TOP statements on their own the records
are sorted correctly but when wrapped in the main statement, the ORDER
BY is just ignored. Tried to play around with the script but with no
luck. Script is for paging, in this case, select all records meeting
criteria, order and select Top 25 to display on first page. Any
questions, please don't hesitate contacting me.

Thanks very much to anyone who's having a look at this!

Martin

SELECT COALESCE((i2b_contact.Firstname + CHAR(32) +
i2b_contact.Lastname),i2b_company.CompanyName) AS CName,
i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,
(i2b_address.Address1) AS PropertyAddress,
A.ProgUserName AS ProgUserName,
CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) AS
TransactionDate,
CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
ReturnByDate
FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROM
i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN
(SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlog
LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
A.ProgUserID)
WHERE i2b_keytransactionlog.ProgClientID = 1 AND
i2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESC)
ORDER BY ReturnByDate DESCOn 29 May 2004 12:19:20 -0700, Martin wrote:

>Hi
>I'm having trouble with the script below that it just won't do a
>correct ORDER BY for a date field.
>When executing the two SELECT TOP statements on their own the records
>are sorted correctly but when wrapped in the main statement, the ORDER
>BY is just ignored. Tried to play around with the script but with no
>luck. Script is for paging, in this case, select all records meeting
>criteria, order and select Top 25 to display on first page. Any
>questions, please don't hesitate contacting me.
>Thanks very much to anyone who's having a look at this!
>Martin
>SELECT COALESCE((i2b_contact.Firstname + CHAR(32) +
>i2b_contact.Lastname),i2b_company.CompanyName) AS CName,
>i2b_keytransactionlog.KeyTransactionLogID, i2b_key.KeyCode,
>(i2b_address.Address1) AS PropertyAddress,
>A.ProgUserName AS ProgUserName,
>CONVERT (varchar(10), i2b_keytransactionlog.TransactionDate, 104 ) AS
>TransactionDate,
>CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
>ReturnByDate
> FROM i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE KeyTransactionLogID IN(SELECT TOP 25 KeyTransactionLogID FROM
>i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE i2b_keytransactionlog.ProgClientID = 1 AND
>i2b_keytransactionlog.ProgUserID = 3 AND KeyTransactionLogID NOT IN
> (SELECT TOP 0 KeyTransactionLogID FROM i2b_keytransactionlog
>LEFT JOIN i2b_contact ON (i2b_keytransactionlog.EntityID =
>i2b_contact.ContactID AND i2b_keytransactionlog.IsContact = 1)
>LEFT JOIN i2b_company ON (i2b_keytransactionlog.EntityID =
>i2b_company.CompanyID AND i2b_keytransactionlog.IsContact = 0)
>JOIN i2b_key ON (i2b_keytransactionlog.KeyID = i2b_key.KeyID)
>JOIN i2b_property on (i2b_key.PropertyID = i2b_property.PropertyID)
>JOIN i2b_address ON (i2b_property.AddressID = i2b_address.AddressID)
>JOIN i2b_proguser AS A ON (i2b_keytransactionlog.ProgUserID =
>A.ProgUserID)
>WHERE i2b_keytransactionlog.ProgClientID = 1 AND
>i2b_keytransactionlog.ProgUserID = 3 ORDER BY ReturnByDate DESC)
> ORDER BY ReturnByDate DESC)
> ORDER BY ReturnByDate DESC

Hi Martin,

I'm not entirely sure why you would use TOP 0 for the NOT IN subquery, but
that's not the cause of the problem you post about.

The ANSI standard for SQL allows only columns appearing in the SELECT list
in the ORDER BY clause. The rationale being that first, the query gets
executed and the result set is determined; the sorting is a seperate,
final stage. ANSI SQL allows things as ORDER BY 3 (3 being the third
column in the SELECT LIST) or SELECT MyColumn AS ColumnAlias ... ORDER BY
ColumnAlias.

MS SQL Server expands on this standard. With MS SQL Server, you can ORDER
BY on a column not in the SELECT list. If you use a column name that is
not an alias in the column list, SQL Server will try to locate a column
with that name in one of the tables used in the query and use that for
sorting.

Before I get back to your problem, run the following script. It will
clearly show that SQL Server will first attempt to resolve names in the
ORDER BY clause following ANSI standard before looking at the columns that
make up the data.

CREATE TABLE testtab (a int, b int)
go
INSERT testtab (a, b) SELECT 1, 20
INSERT testtab (a, b) SELECT 2, 10
go
SELECT b
FROM testtab
ORDER BY a
go
SELECT b AS a
FROM testtab
ORDER BY a
go
SELECT b AS a, a AS b
FROM testtab
ORDER BY a
go
DROP TABLE testtab
go

In the first query, no column "a" is in the SELECT list, so SQL Server
will sort on the column a in the table. In the second query, the SELECT
list has a column aliased as "a", so this is used for sorting. In the
third query, the column a itself is used in the select list; aliases are
disregarded and sorting is done on the contents of a.

Now back to your real problem. I'll show you the parts that cause the
unexpected ordering:

>SELECT (...)
> (...)
>CONVERT(varchar(10),i2b_keytransactionlog.ReturnBy Date,104) AS
>ReturnByDate
> (...)
> ORDER BY ReturnByDate DESC

The column ReturnByDate is not used in the SELECT list (the results of a
function are not cosidered equal to the column itself). There is however
an alias ReturnByDate, so there's no need to get back to the data in the
table either - the aliased column in the result set is used. This column
is the output of a CONVERT function call. It's varchar(10), therefor the
sorting will be done by alphabet, descending. "23-04-2003" will go before
"06-05-2004".

The fix? Either use something else as an alias ([Return By Date], for
example) or use ORDER BY CONVERT(datetime, ReturnByDate).

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

calling a stored procedure from another on sql server 2000, both using transactions

Hi!
I am having some trouble. I have two stored procedures:
CREATE PROCEDURE dbo.TestTran1
as
declare @.erri int
begin tran
insert into prosjekt values('this is a test', NULL, 1)
select @.erri = @.@.error
if @.erri <> 0
goto errHandler
exec TestTran2
select @.erri = @.@.error
if @.erri <> 0
begin
goto errHandler2
end
commit tran
return 0
errHandler:
if @.@.trancount > 0
rollback tran
Raiserror('Det oppstod en feil i TestTran1',16,1)
GO
Here is procedure number 2:
CREATE PROCEDURE dbo.TestTran2 AS
begin tran
print 'in proc 2'
insert into prosjekt values('Dette er et testprosjekt Dette er et
testprosjekt Dette er et testprosjekt ', null, 2)
if @.@.error <> 0
begin
goto errH
end
commit tran
return 0
errH:
rollback tran
print 'rolling back'
raiserror('Det oppstod en feil i TestTran2', 16, 1)
GO
The first procedure inserts values to a testtable with success. Then it
calls the second procedure that will generate an error because it tries
to insert too much text in a varchar(50) column. What I am trying to
achieve here is to be able to use both procedures by it self and also be
able to call one from the other if nessesairy.
If I run the first procedure (that calls the other), I get the error:
"Server: Msg 266, Level 16, State 2, Procedure TestTran2, Line 26
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK
TRANSACTION statement is missing. Previous count = 1, current count =
0."
If I run the second procedure or the first one without calling the
second, everything works fine. The same goes if I make the second
procedure not fail when called from the first.
I have so far learned that an inner commit will not actually commit
anything, rather just decrement the @.@.trancount by one. The outer commit
will make the actuall commit. A rollback on the other hand will allways
make a rollback and set @.@.trancount = 0.
1. What am I doing wrong. Can I not use begin tran in two different
procedures calling each other?
2. Is there any need for using the begin tran expression? or will sql
server still handle everything like a transaction what ever one does?
Hope I made my self clear on the matter. Thanks!
Henning :-)
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!Hi Henning.
What I've been doing, don't know if it's the best method or not, is
in each proc, test if it has been called within a transaction and if
not, use transactions. Also, to ensure your transaction isn't
automatically aborted upon a SQL error, use SET XACT_ABORT OFF
Example:
create procedure myProc
as
set xact_abort off
declare @.InTran tinyint
set @.InTran = @.@.trancount
if @.InTran = 0
begin
begin tran
end
-- do some stuff
if @.@.error <> 0
begin
if @.InTran = 0
begin
rollback tran
end
end
else
begin
if @.InTran = 0
begin
commit tran
end
end
Hope this helps. I look forward to anyone else's feedback.
Bryce

Calling a AS400 (IBM DB2) Stored Procedure from within SSIS Package

Hi:

I would like to find out how would I call an AS400 (IBM DB2) iSeries Stored Procedure from within my SSIS Package. What tasks should i be using? and do I need any additional adapters installed on my machine to access AS400(IBM DB2). Thanks.

MA

--Run this scrpit, AS400 is the name of your AS400 name
EXEC master.dbo.sp_addlinkedserver @.server = N'AS400', @.srvproduct=N'DB2 UDB for iSeries', @.provider=N'MSDASQL', @.datasrc=N'AS400', @.catalog=N'AS400DBNAME'
-- It will create a linked server name AS400, dtascrc parameter is the name of ODBC_DSN
--Open this linked server and add the UserID/Password--
Once this is done, then use the following to call the stored proc

Exec ('Call Library.StoredProc (?)') AT AS400

Task Name is Execute SQL Task, iSeries Navigator/drivers should be installed on the server.

This is very good article on this

http://www.mcpressonline.com/mc/.6b344718

Calculating last non empty of measures that are not included in a month

Hi!

I have two tables.

One is measures like this

Product Measure Date

Prod A 10 12, Jan 2006

Prod A 15 20, Jan 2006

Prod A 11 5, Mar 2006

The other is time dimension

When I create a cube, I would like to see by months last value of measure.

So in Feb I should get 15.

My question is - should I create a query and run it on server to

1. Find which months are not icluded in measuers

2. Then find last value for each product

3. Write into new table measures, where February will have last non empty value from January?

Or can I do it cube in some way?

The best way to solve this in AS2005 is to define measure aggregation function as LastNonEmpty - it will do exactly what you need.