Null values: February 2012

Saturday, February 25, 2012

Returning Stored Proceduers via ADO Schema Rowsets

Hello,
I've been trying to get Stored Procedures and parameter information
via the ADO Schema Rowsets.
However, it seems that the MS SQL driver does not support this.
Can someone confirm this?
--
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com> However, it seems that the MS SQL driver does not support this.
The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
Set conn = CreateObject("ADODB.Connection")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open connectionString
MsgBox ListProcParameters("MyDatabase", "dbo", "MyProcedure")
conn.Close
Function ListProcParameters(Database, Schema, ProcName)
Dim result, filter(3)
filter(0) = Database
filter(1) = Schema
filter(2) = ProcName
Set rs = conn.OpenSchema(26, filter)
Do While rs.EOF = False
result = result & _
"PROCEDURE_SCHEMA" & "=" & _
rs.Fields("PROCEDURE_SCHEMA") & vbCrLf & _
"PROCEDURE_NAME" & "=" & _
rs.Fields("PROCEDURE_NAME") & vbCrLf & _
"PARAMETER_NAME" & "=" & _
rs.Fields("PARAMETER_NAME") & vbCrLf & _
vbCrLf
rs.MoveNext
Loop
rs.Close
ListProcParameters = result
End Function
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:%23B6mxWZRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been trying to get Stored Procedures and parameter information
> via the ADO Schema Rowsets.
> However, it seems that the MS SQL driver does not support this.
> Can someone confirm this?
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||Hello Dan,
> > However, it seems that the MS SQL driver does not support this.
> The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
Can you list procedures as well?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com
> Set conn = CreateObject("ADODB.Connection")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open connectionString
> MsgBox ListProcParameters("MyDatabase", "dbo", "MyProcedure")
> conn.Close
> Function ListProcParameters(Database, Schema, ProcName)
> Dim result, filter(3)
> filter(0) = Database
> filter(1) = Schema
> filter(2) = ProcName
> Set rs = conn.OpenSchema(26, filter)
> Do While rs.EOF = False
> result = result & _
> "PROCEDURE_SCHEMA" & "=" & _
> rs.Fields("PROCEDURE_SCHEMA") & vbCrLf & _
> "PROCEDURE_NAME" & "=" & _
> rs.Fields("PROCEDURE_NAME") & vbCrLf & _
> "PARAMETER_NAME" & "=" & _
> rs.Fields("PARAMETER_NAME") & vbCrLf & _
> vbCrLf
> rs.MoveNext
> Loop
> rs.Close
> ListProcParameters = result
> End Function
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:%23B6mxWZRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> > Hello,
> >
> > I've been trying to get Stored Procedures and parameter information
> > via the ADO Schema Rowsets.
> >
> > However, it seems that the MS SQL driver does not support this.
> >
> > Can someone confirm this?
> >
> > --
> > With regards,
> >
> > Martijn Tonies
> > Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> > SQL
> > Server
> > Upscene Productions
> > http://www.upscene.com
> >
> >
>|||Hello Dan,
> > > However, it seems that the MS SQL driver does not support this.
> >
> > The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
> Can you list procedures as well?
Gave it another try -- it seems that the first filter parameter "catalog"
needs
to be set, else it will return an error.
Works for other drivers though :-)
Thanks for the reality check.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com|||> > > > However, it seems that the MS SQL driver does not support this.
> > >
> > > The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
> >
> > Can you list procedures as well?
> Gave it another try -- it seems that the first filter parameter "catalog"
> needs
> to be set, else it will return an error.
> Works for other drivers though :-)
> Thanks for the reality check.
Funny, that wasn't it ... I did a filter (4th parameter) on DB_PT_PROCEDURE
which seems to fail...
This way, I should get procedures only and no functions...
Hmmm...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com|||> This way, I should get procedures only and no functions...
It seems both procedures and functions return DB_PT_PROCEDURE. To
distinguish between procs and functions, you can instead use the ANSI
standard INFORMATION_SCHEMA.ROUTINES view in a SQL query.
In my experience, schema rowset behavior can be inconsistent among various
providers and provider versions. If you need to retrieve meta-data from a
variety of DBMS vendors, you might consider developing your own interface so
that you can use the best technique depending on the underlying DBMS and
version.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:eBLVI7jRFHA.4028@.tk2msftngp13.phx.gbl...
>> > > > However, it seems that the MS SQL driver does not support this.
>> > >
>> > > The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
>> >
>> > Can you list procedures as well?
>> Gave it another try -- it seems that the first filter parameter "catalog"
>> needs
>> to be set, else it will return an error.
>> Works for other drivers though :-)
>> Thanks for the reality check.
> Funny, that wasn't it ... I did a filter (4th parameter) on
> DB_PT_PROCEDURE
> which seems to fail...
> This way, I should get procedures only and no functions...
> Hmmm...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, MySQL & MS
> SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> > This way, I should get procedures only and no functions...
> It seems both procedures and functions return DB_PT_PROCEDURE. To
Apparently so.
> distinguish between procs and functions, you can instead use the ANSI
> standard INFORMATION_SCHEMA.ROUTINES view in a SQL query.
Hmm... given that this isn't as supported as it should ... :-/
> In my experience, schema rowset behavior can be inconsistent among various
> providers and provider versions. If you need to retrieve meta-data from a
> variety of DBMS vendors, you might consider developing your own interface
so
> that you can use the best technique depending on the underlying DBMS and
> version.
Yes I know ... but for this particular tool, this is a no-go area...
I'll settle for somewhat weirdish results.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server
Upscene Productions
http://www.upscene.com

Returning Stored Proceduers via ADO Schema Rowsets

Hello,
I've been trying to get Stored Procedures and parameter information
via the ADO Schema Rowsets.
However, it seems that the MS SQL driver does not support this.
Can someone confirm this?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com> However, it seems that the MS SQL driver does not support this.
The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
Set conn = CreateObject("ADODB.Connection")
ConnectionString = "Provider=SQLOLEDB" & _
";Data Source=MyServer" & _
";Initial Catalog=MyDatabase" & _
";Integrated Security=SSPI"
conn.Open connectionString
MsgBox ListProcParameters("MyDatabase", "dbo", "MyProcedure")
conn.Close
Function ListProcParameters(Database, Schema, ProcName)
Dim result, filter(3)
filter(0) = Database
filter(1) = Schema
filter(2) = ProcName
Set rs = conn.OpenSchema(26, filter)
Do While rs.EOF = False
result = result & _
"PROCEDURE_SCHEMA" & "=" & _
rs.Fields("PROCEDURE_SCHEMA") & vbCrLf & _
"PROCEDURE_NAME" & "=" & _
rs.Fields("PROCEDURE_NAME") & vbCrLf & _
"PARAMETER_NAME" & "=" & _
rs.Fields("PARAMETER_NAME") & vbCrLf & _
vbCrLf
rs.MoveNext
Loop
rs.Close
ListProcParameters = result
End Function
Hope this helps.
Dan Guzman
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:%23B6mxWZRFHA.2744@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been trying to get Stored Procedures and parameter information
> via the ADO Schema Rowsets.
> However, it seems that the MS SQL driver does not support this.
> Can someone confirm this?
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, mysql & MS
> SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||Hello Dan,

> The following VBScript works fine for me using ADO 2.8 and SQLOLEDB.
Can you list procedures as well?
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com

> Set conn = CreateObject("ADODB.Connection")
> ConnectionString = "Provider=SQLOLEDB" & _
> ";Data Source=MyServer" & _
> ";Initial Catalog=MyDatabase" & _
> ";Integrated Security=SSPI"
> conn.Open connectionString
> MsgBox ListProcParameters("MyDatabase", "dbo", "MyProcedure")
> conn.Close
> Function ListProcParameters(Database, Schema, ProcName)
> Dim result, filter(3)
> filter(0) = Database
> filter(1) = Schema
> filter(2) = ProcName
> Set rs = conn.OpenSchema(26, filter)
> Do While rs.EOF = False
> result = result & _
> "PROCEDURE_SCHEMA" & "=" & _
> rs.Fields("PROCEDURE_SCHEMA") & vbCrLf & _
> "PROCEDURE_NAME" & "=" & _
> rs.Fields("PROCEDURE_NAME") & vbCrLf & _
> "PARAMETER_NAME" & "=" & _
> rs.Fields("PARAMETER_NAME") & vbCrLf & _
> vbCrLf
> rs.MoveNext
> Loop
> rs.Close
> ListProcParameters = result
> End Function
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
> news:%23B6mxWZRFHA.2744@.TK2MSFTNGP10.phx.gbl...
>|||Hello Dan,

> Can you list procedures as well?
Gave it another try -- it seems that the first filter parameter "catalog"
needs
to be set, else it will return an error.
Works for other drivers though :-)
Thanks for the reality check.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com|||> > > > However, it seems that the MS SQL driver does not support this.
> Gave it another try -- it seems that the first filter parameter "catalog"
> needs
> to be set, else it will return an error.
> Works for other drivers though :-)
> Thanks for the reality check.
Funny, that wasn't it ... I did a filter (4th parameter) on DB_PT_PROCEDURE
which seems to fail...
This way, I should get procedures only and no functions...
Hmmm...
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com|||> This way, I should get procedures only and no functions...
It seems both procedures and functions return DB_PT_PROCEDURE. To
distinguish between procs and functions, you can instead use the ANSI
standard INFORMATION_SCHEMA.ROUTINES view in a SQL query.
In my experience, schema rowset behavior can be inconsistent among various
providers and provider versions. If you need to retrieve meta-data from a
variety of DBMS vendors, you might consider developing your own interface so
that you can use the best technique depending on the underlying DBMS and
version.
Hope this helps.
Dan Guzman
SQL Server MVP
"Martijn Tonies" <m.tonies@.upscene-removethis.nospam.com> wrote in message
news:eBLVI7jRFHA.4028@.tk2msftngp13.phx.gbl...
> Funny, that wasn't it ... I did a filter (4th parameter) on
> DB_PT_PROCEDURE
> which seems to fail...
> This way, I should get procedures only and no functions...
> Hmmm...
>
> --
> With regards,
> Martijn Tonies
> Database Workbench - developer tool for InterBase, Firebird, mysql & MS
> SQL
> Server
> Upscene Productions
> http://www.upscene.com
>|||> > This way, I should get procedures only and no functions...
> It seems both procedures and functions return DB_PT_PROCEDURE. To
Apparently so.

> distinguish between procs and functions, you can instead use the ANSI
> standard INFORMATION_SCHEMA.ROUTINES view in a SQL query.
Hmm... given that this isn't as supported as it should ... :-/

> In my experience, schema rowset behavior can be inconsistent among various
> providers and provider versions. If you need to retrieve meta-data from a
> variety of DBMS vendors, you might consider developing your own interface
so
> that you can use the best technique depending on the underlying DBMS and
> version.
Yes I know ... but for this particular tool, this is a no-go area...
I'll settle for somewhat weirdish results.
With regards,
Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, mysql & MS SQL
Server
Upscene Productions
http://www.upscene.com

returning status periodically from stored procedure

I'm fairly new at anything ambitious in SQL. I can set up tables and create
basic stored procedures, triggers and such. I know my way around ok, but
there is a lot to learn. I'm just curious about something. Nothing to show
you in particular becuase it's not been started but I want to know if 2
things are possible with stored procedures.
1. Can a stored procedure pass a result to the front end more than once
during execution? I want to write a procedure that does some processing that
might take more than just a few seconds. I want the front end to display a
status bar. If the procedure has say 5 steps in it, can I send out a result
at the end of each step so I can increment my front end status bar. If not,
other than doing the processing on the front end, what is the standard way
to accomplish this?
2. Use the same situation above. But now I'd like to have a cancel button on
a form in the front end so that if a backend process takes too long the use
can cancel it before it completes. Is there a way to cancel a stored
procedure from the front end while that procedure is executing?
Thanks,
Keith1.SP are executed as a whole or not, so a status bar is not possible, you
can fire your single queries to your db (each in a seperate SP) and use the
statusbar for that.
2. Like in the above situation, executing a procedure will force some
applications to wait for the execution until the result gets back. Sure
there is a possibilities to use different threads, but I dont think that
you mean that. There will be a chang in VS.NET 2005 to fire up queries
asynchronly but I dont know about anything in other Data Providers about
it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Keith G Hicks" <krh@.comcast.net> schrieb im Newsbeitrag
news:uAPJ7AlWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I'm fairly new at anything ambitious in SQL. I can set up tables and
> create
> basic stored procedures, triggers and such. I know my way around ok, but
> there is a lot to learn. I'm just curious about something. Nothing to show
> you in particular becuase it's not been started but I want to know if 2
> things are possible with stored procedures.
> 1. Can a stored procedure pass a result to the front end more than once
> during execution? I want to write a procedure that does some processing
> that
> might take more than just a few seconds. I want the front end to display a
> status bar. If the procedure has say 5 steps in it, can I send out a
> result
> at the end of each step so I can increment my front end status bar. If
> not,
> other than doing the processing on the front end, what is the standard way
> to accomplish this?
> 2. Use the same situation above. But now I'd like to have a cancel button
> on
> a form in the front end so that if a backend process takes too long the
> use
> can cancel it before it completes. Is there a way to cancel a stored
> procedure from the front end while that procedure is executing?
> Thanks,
> Keith
>|||Just as I suspected. Thanks for the quick response. -keith :)
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OEWtnKlWFHA.3140@.TK2MSFTNGP14.phx.gbl...
1.SP are executed as a whole or not, so a status bar is not possible, you
can fire your single queries to your db (each in a seperate SP) and use the
statusbar for that.
2. Like in the above situation, executing a procedure will force some
applications to wait for the execution until the result gets back. Sure
there is a possibilities to use different threads, but I dont think that
you mean that. There will be a chang in VS.NET 2005 to fire up queries
asynchronly but I dont know about anything in other Data Providers about
it.
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Keith G Hicks" <krh@.comcast.net> schrieb im Newsbeitrag
news:uAPJ7AlWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I'm fairly new at anything ambitious in SQL. I can set up tables and
> create
> basic stored procedures, triggers and such. I know my way around ok, but
> there is a lot to learn. I'm just curious about something. Nothing to show
> you in particular becuase it's not been started but I want to know if 2
> things are possible with stored procedures.
> 1. Can a stored procedure pass a result to the front end more than once
> during execution? I want to write a procedure that does some processing
> that
> might take more than just a few seconds. I want the front end to display a
> status bar. If the procedure has say 5 steps in it, can I send out a
> result
> at the end of each step so I can increment my front end status bar. If
> not,
> other than doing the processing on the front end, what is the standard way
> to accomplish this?
> 2. Use the same situation above. But now I'd like to have a cancel button
> on
> a form in the front end so that if a backend process takes too long the
> use
> can cancel it before it completes. Is there a way to cancel a stored
> procedure from the front end while that procedure is executing?
> Thanks,
> Keith
>|||I don't think that this is true, not in the strictest of sense. True what
he is suggesting is not a good idea, but certainly result sets or error
messages can be returned, and parts of procs can be completed or ignored
based on what happens in the procedure.
As an example, consider error messages. They have to be answered before the
procedure continues on. Same with count messages. If you return 2 result
sets, you can stop retrieving data and never get to the second call (and
subsequently not ever run a statement that is more important, like a commit
transaction. This bit me once in a pooled connection app. Had to roll back
several hours of work!) In fact, if you don't retrieve all of the rows from
the first result set (if there are > 1) then it will never get to the
second.
Even in 2005, a single query batch will be synchronous, but you can run two
different batches asynch on the same connection (or a reasonable facimile
thereof.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Jens Smeyer" <Jens@.Remove_this_For_Contacting.sqlserver2005.de> wrote in
message news:OEWtnKlWFHA.3140@.TK2MSFTNGP14.phx.gbl...
> 1.SP are executed as a whole or not, so a status bar is not possible, you
> can fire your single queries to your db (each in a seperate SP) and use
> the statusbar for that.
> 2. Like in the above situation, executing a procedure will force some
> applications to wait for the execution until the result gets back. Sure
> there is a possibilities to use different threads, but I dont think that
> you mean that. There will be a chang in VS.NET 2005 to fire up queries
> asynchronly but I dont know about anything in other Data Providers about
> it.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
> "Keith G Hicks" <krh@.comcast.net> schrieb im Newsbeitrag
> news:uAPJ7AlWFHA.2520@.TK2MSFTNGP09.phx.gbl...
>|||Basically, you would return multiple resultsets, and the ADO command would
need to be called asynchronously in order to update a status and allow
cancellation. Never done this before, so I'm not sure about the details.
When you get it working, then post back with your final solution.
"Keith G Hicks" <krh@.comcast.net> wrote in message
news:uAPJ7AlWFHA.2520@.TK2MSFTNGP09.phx.gbl...
> I'm fairly new at anything ambitious in SQL. I can set up tables and
create
> basic stored procedures, triggers and such. I know my way around ok, but
> there is a lot to learn. I'm just curious about something. Nothing to show
> you in particular becuase it's not been started but I want to know if 2
> things are possible with stored procedures.
> 1. Can a stored procedure pass a result to the front end more than once
> during execution? I want to write a procedure that does some processing
that
> might take more than just a few seconds. I want the front end to display a
> status bar. If the procedure has say 5 steps in it, can I send out a
result
> at the end of each step so I can increment my front end status bar. If
not,
> other than doing the processing on the front end, what is the standard way
> to accomplish this?
> 2. Use the same situation above. But now I'd like to have a cancel button
on
> a form in the front end so that if a backend process takes too long the
use
> can cancel it before it completes. Is there a way to cancel a stored
> procedure from the front end while that procedure is executing?
> Thanks,
> Keith
>

Returning SSIS stored procedure name

I am currently trying to write a number of processe's to keep track of what information is held in my SSIS package. The package I have created is rather large and it would prove a long labourious process to look through every task to see what stored procedure has been used.

What I wanted to do was write a stored procedure in SQL Server 2005 that pick's up each package name and checks for any stored procedures used and returns the names of these stored procedures and any other relevant information i.e required variables.

So far I have managed to create a stored procedure that picks up the name(s) of the packages but I am stuck after this.

Declare @.Filename varchar(1000)
Declare @.cmd varchar(1000)

Create table #dir (Filename varchar(1000))
Insert #dir
Exec master..xp_cmdshell 'dir /B C:\Development\SumColumn\SumColumn\*.dtsx'
delete #dir where Filename is null or Filename like '%not found%'
Select @.Filename = ''
While @.Filename < (select max(Filename) from #dir)

drop table #dir

Any help would be appreciated.

Thanks

Hi,

you can use a "SQL profiler" trace to trace whats happening in your server when the package is run.
anyway here's something that might help you. dtsx file of SSIS is stored in XML format
you can open the file with notepad and figure out from there. you can use the notepad "find" feature
if you have some XML background
or data processing skill you might be able to extract the information you need.
you may also use the Sql server 2005 XML datatype to do that.

regards

,joey

|||

Using the SSIS API, it's generally possible to load a package, find all the instances of some specific tasks, and retrieve the values of some of their properties. At the time that you wrote the code, you'd need to determine which types of tasks you wanted to scan. For each of these types, you'd need to write code to intelligently parse the values stored in the properties of the task for the name of the stored procedure. You can read up on the SSIS API, here: http://msdn2.microsoft.com/en-us/library/ms136025.aspx

In particular, you'll probably want to take a look at:

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

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

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.runtime.package.executables.aspx

http://msdn2.microsoft.com/en-us/library/microsoft.sqlserver.dts.tasks.executesqltask.executesqltask.aspx

While it might be possible to scan through the xml, that approach would be unsupported.

Returning SqlDecimal with the correct scale

I have a UDF
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Diagnostics;
using System.Text;
using Microsoft.SqlServer.Server;

public partial class UserDefinedFunctions
{
[SqlFunction(DataAccess = DataAccessKind.Read)]
public static SqlDecimal GetCustomPrice(SqlGuid Item_Key, SqlGuid Company_Key)
{
SqlDecimal retvalue = new SqlDecimal();
try
{
StringBuilder sqlstr = new StringBuilder();
sqlstr.Append("SELECT A.Price ");
sqlstr.Append("FROM Price A ");
sqlstr.Append("JOIN Link_Table B ");
sqlstr.Append("ON A.Price_Key = B.FKey ");
sqlstr.Append("JOIN Lookup C ");
sqlstr.Append("ON B.Lookup_Key = C.Lookup_Key ");
sqlstr.Append("AND C.Group_Name = 'Price_Type' ");
sqlstr.Append("AND C.Value = 'Custom' ");
sqlstr.Append("JOIN Item D ");
sqlstr.Append("ON B.PKey = D.Item_Key ");
sqlstr.Append("JOIN Link_Table E ");
sqlstr.Append("ON A.Price_Key = E.FKey ");
sqlstr.Append("JOIN Lookup F ");
sqlstr.Append("ON E.Lookup_Key = F.Lookup_Key ");
sqlstr.Append("AND F.Group_Name = 'Price_Type' ");
sqlstr.Append("AND F.Value = 'Custom' ");
sqlstr.Append("JOIN Company G ");
sqlstr.Append("ON E.PKey = G.Company_Key ");
sqlstr.Append("WHERE D.Item_Key = @.Item_Key ");
sqlstr.Append("AND G.Company_Key = @.Company_Key");
using (SqlConnection conn = new SqlConnection("context connection=true;"))
{
conn.Open();
SqlCommand cmd = new SqlCommand(sqlstr.ToString(), conn);
SqlDataAdapter adap = new SqlDataAdapter(cmd);
SqlParameter para = new SqlParameter();
para.ParameterName = "@.Item_Key";
para.SqlDbType = SqlDbType.UniqueIdentifier;
para.SqlValue = Item_Key;
cmd.Parameters.Add(para);
para = new SqlParameter();
para.ParameterName = "@.Company_Key";
para.SqlDbType = SqlDbType.UniqueIdentifier;
para.SqlValue = Company_Key;
cmd.Parameters.Add(para);
DataSet ds = new DataSet();
adap.Fill(ds);
if (ds.Tables.Count > 0)
{
if (ds.Tables[0].Rows.Count > 0)
{

retvalue = SqlDecimal.ConvertToPrecScale(new SqlDecimal((decimal)ds.Tables[0].Rows[0]["Price"]),12,3);
}
}
}
}
catch (Exception e)
{
throw e;
}
return SqlDecimal.ConvertToPrecScale(retvalue, 12, 3);
}
};

The value in the record is a decimal 12,3 which is 0.950 and when it is returned from this UDF it is 1. Using the debuger the value is 0.950 until it gets to the calling program were it
is 1. I have looked for an answer all over the place with not much luck Can anyone help I will be in their debt.

Thanks
Larry

If you are deploying from Visual Studio, you can use SqlFacet to specify the precision and scale of your return value by adding another attribute to your method definition like so:

[return: SqlFacet(Precision=12, Scale=3)]

Otherwise, you specify the Precision and Scale as part of CREATE FUNCTION:

CREATE FUNCTION GetCustomPrice(...) RETURNS decimal(12,3) ...

Steven

Returning SQL servers on a network

Hi,
I would like some help with what the best way is to return a list of the
Server names on a network using VB .NET.
I found a reference to the dbserverenum function, is this the best thing to
use, I was unable to find any examples? I would be most grateful if someone
could provide some robust example code of the best way to do this.
Many thanks.Hi
I'm sure you can use SQLDMO object to list all servers
Dim i As Integer
Set oSQLServerDMOApp = New SQLDMO.Application
gShowServerEvents = False
Dim namX As NameList
Set namX = oSQLServerDMOApp.ListAvailableSQLServers
For i = 1 To namX.Count
txtServer.AddItem namX.Item(i)
Next
--
or call the following SP from vb.net
--
CREATE PROCEDURE dbo.ListLocalServers
AS
BEGIN
SET NOCOUNT ON
CREATE TABLE #servers(sname VARCHAR(255))
INSERT #servers EXEC master..XP_CMDShell 'OSQL -L'
DELETE #servers WHERE sname='Servers:'
SELECT LTRIM(sname) FROM #servers WHERE sname != 'NULL'
DROP TABLE #servers
END
"11Oppidan" <11Oppidan@.community.nospam> wrote in message
news:efgTgepEFHA.628@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I would like some help with what the best way is to return a list of the
> Server names on a network using VB .NET.
> I found a reference to the dbserverenum function, is this the best thing
to
> use, I was unable to find any examples? I would be most grateful if
someone
> could provide some robust example code of the best way to do this.
> Many thanks.
>|||If you want the instances of sql server, then you can use SQL-DMO API,
specifically the ListAvailableSQLServers Method of the Application object.
http://www.microsoft.com/resources/... />
c3561.mspx
AMB
"11Oppidan" wrote:

> Hi,
> I would like some help with what the best way is to return a list of the
> Server names on a network using VB .NET.
> I found a reference to the dbserverenum function, is this the best thing t
o
> use, I was unable to find any examples? I would be most grateful if someo
ne
> could provide some robust example code of the best way to do this.
> Many thanks.
>
>

Returning small managable sets from a SELECT statement

I have a Database that contains 10,000 records. I execute a SELECT statement that returns 8000. I need those 8000 and cannot refine my statement to return a smaller subset.

My problem is I don't want all the results back in one go. I would like to return to 1000 rows on my first call. On my second call I would like another 1000 rows starting from the end of the last call and so on...

Is this possible? Also the table has no unique key either, and that ain't gonna change.

Any suggestions. I would like to implement this in SQL.

Thanks in advance to anyone who comes up with a good solutionHello,

you can use PL/SQL to open a cursor an fetch the records block wise ..

Use

OPEN, FETCH and CLOSE statements to create a package and use this in your application.

Hope that hint helps ?

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

Returning single value from multiple recordsets

Hi- I am sending several queries simultaneously to SS; I declare a variable,
and do several conditional selects to set the value of the variable. At the
end, I select the value of the variable as the return value of the complete
transaction. But, the query ends up returning multiple recordsets, because I
am doing multiple selects (although these selects only set the value of my
declared variable). After I get the recordsets back, I have to iterate
through all just to find my return value. Is there an easier way to isolate
the return value, or return just the return value in one recordset?
ThanksTry SET NOCOUNT ON in the beginning of your proc code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"boney" <boney@.discussions.microsoft.com> wrote in message
news:7148B087-86E9-4838-A985-774418731886@.microsoft.com...
> Hi- I am sending several queries simultaneously to SS; I declare a variabl
e,
> and do several conditional selects to set the value of the variable. At th
e
> end, I select the value of the variable as the return value of the complet
e
> transaction. But, the query ends up returning multiple recordsets, because
I
> am doing multiple selects (although these selects only set the value of my
> declared variable). After I get the recordsets back, I have to iterate
> through all just to find my return value. Is there an easier way to isolat
e
> the return value, or return just the return value in one recordset?
> Thanks|||boney
Can you call a SP with an OUTPUT parameter?
For more details pls refer to the BOL.
"boney" <boney@.discussions.microsoft.com> wrote in message
news:7148B087-86E9-4838-A985-774418731886@.microsoft.com...
> Hi- I am sending several queries simultaneously to SS; I declare a
> variable,
> and do several conditional selects to set the value of the variable. At
> the
> end, I select the value of the variable as the return value of the
> complete
> transaction. But, the query ends up returning multiple recordsets, because
> I
> am doing multiple selects (although these selects only set the value of my
> declared variable). After I get the recordsets back, I have to iterate
> through all just to find my return value. Is there an easier way to
> isolate
> the return value, or return just the return value in one recordset?
> Thanks

Returning Scope_Identity() for GUID

I'm stumped... I'm trying to get the GUID value returned from an insert (I
really need to use a GUID because this is a web app and I want the records t
o
be difficult to find i.e. identity key within range is too easy to browse)
Declare @.Identity nvarchar(30)
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
This returns null while
Drop Table #T
Declare @.Identity uniqueidentifier
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (LName) Values('Rosner')
Set @.Identity = Scope_IDENTITY()
SELECT @.IDENTITY
returns error - "Operand type clash: numeric is incompativle with
uniqueidentifier.
Suggestions would be greatly appreciated. Thanks in advance.
- Abe1. scope_identity() only returns the last (scoped) identity value - this
value datatype has to be numeric.
2. the first batch returns NULL because there is no identity column in your
table. Thus, the last insert generates a NULL identity (INT) value. The
implicit conversion from INT to nvarchar is allowed. Hence, no error is
raised.
3. the second batch fails because of the implicit conversion. You can't
convert a numeric to uniqueidentifier.
Here is a quick proof of the conversion error:
select convert(uniqueidentifier,1)
-oj
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
> to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||"Returns the last IDENTITY value inserted into an IDENTITY column in the
same scope. A scope is a module -- a stored procedure, trigger, function, or
batch" from BOL .Identity columns need to be of type bigint, int or smallint
and are not compatible with type uniqueidentifier .Therefore,to ask SQL
Server to give you last identity inserted and then assigning that to a
variable previously declared as a uniqueidentifier, equals compatibility
error
To return it do something like:
Declare @.Identity uniqueidentifier
Select @.Identity = newID()
Create Table #T (I uniqueidentifier, LName nvarchar(10))
Insert INTO #T (I,LName) Values(@.identity,'Rosner')
SELECT @.IDENTITY
Jack Vamvas
________________________________________
__________________________
Receive free SQL tips - register at www.ciquery.com/sqlserver.htm
SQL Server Performance Audit - check www.ciquery.com/sqlserver_audit.htm
New article by Jack Vamvas - SQL and Markov Chains -
www.ciquery.com/articles/art_04.asp
"AbeR" <AbeR@.discussions.microsoft.com> wrote in message
news:5BD89A94-9006-48F3-B73C-426C845E4729@.microsoft.com...
> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe|||Hi,
How about capturing the GUID into a local variable and using it for insert
and then as return value as well.
Declare @.PersistGUID uniqueidentifier
Select @.PersistGUID = newid()
Now you could use @.PersistGUID as per your need.
Best Regards
Vadivel
http://vadivel.blogspot.com
"AbeR" wrote:

> I'm stumped... I'm trying to get the GUID value returned from an insert (I
> really need to use a GUID because this is a web app and I want the records
to
> be difficult to find i.e. identity key within range is too easy to browse)
> Declare @.Identity nvarchar(30)
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> This returns null while
> Drop Table #T
> Declare @.Identity uniqueidentifier
> Create Table #T (I uniqueidentifier, LName nvarchar(10))
> Insert INTO #T (LName) Values('Rosner')
> Set @.Identity = Scope_IDENTITY()
> SELECT @.IDENTITY
> returns error - "Operand type clash: numeric is incompativle with
> uniqueidentifier.
> Suggestions would be greatly appreciated. Thanks in advance.
> - Abe

Returning Scope_Identity from Stored Procedures

Hi:

I've seen a lot of posts about returning the identity after an insert but for every variation, the return value or output parameter is null. Below is the sp, SQLDataSource and vb code for the return value attempt.

Here is the stored procedure:

ALTER PROCEDUREdbo.sp_InsertPlayer

(

@.UserNamevarchar(64),

@.Emailvarchar(128)

)

INSERT INTOtblPlayer ([UserName], Email [Email] )

VALUES(@.UserName, @.Email)

RETURN SCOPE_IDENTITY()

If the sp is executed in the SQL Express that is included in VS 2005, the return value is the last autogenerated PlayerId.

SqlDataSource:

<asp:SqlDataSourceID="SqlDataPlayer"runat="server"

ConnectionString="<%$ ConnectionStrings:ConnectionString %>"

SelectCommand ...

InsertCommand="sp_InsertPlayer"

InsertCommandType="StoredProcedure"

...

<asp:ParameterName="RETURN_VALUE"Direction="ReturnValue"Type="Int32"/>

<asp:ParameterName="UserName"Type="String"/>

<asp:ParameterName="Email"Type="String"/>

</InsertParameters>

</asp:SqlDataSource>

code behind vb:

SqlDataPlayer.InsertParameters("UserName").DefaultValue = CreateUserWizard1.UserName

SqlDataPlayer.InsertParameters("Email").DefaultValue = CreateUserWizard1.Email

SqlDataPlayer.Insert()

Profile.PlayerId = SqlDataPlayer.InsertParameters("RETURN_VALUE").DefaultValue

The DefaultValue is Nothing.

Thanks for your help again.

arora

What I end up doing is something like this:

...
SELECT ScopeIdentity = SCOPE_IDENTITY()

That was back in the .NET 1.1 days, so I don't know if that would be of use with the SqlDataSource. Does that work?

|||

That doeswork (in fact that is my workaround) but just the integer identity value needs to be returned.

Using the SELECT, a result set with one column ScopeIdentity is returned.

I have to use code similar to below to access the identity value:

SqlDataPlayer.SelectParameters("UserName").DefaultValue = CreateUserWizard1.UserName

Dim argsAs DataSourceSelectArguments =New DataSourceSelectArguments()

Dim dvAs Data.DataView

dv = SqlDataPlayer.Select(args)

Session("PlayerId") = dv.Table.Rows(0).Item("PlayerId")

and the sp is:

ALTER PROCEDUREdbo.sp_PlayerIDFromUserName

(

@.UserNamevarchar(64)

)

SELECTPlayerIDFrom[tblPlayer]

WHERE[UserName] = @.UserName

It just seems there has to be an easier way than returning a table for one value.

|||The return value is only available in the SqlDataSource_Inserted event.|||

Thanks Motley.

Here's a link with an example.

http://msdn2.microsoft.com/en-us/library/system.web.ui.webcontrols.sqldatasource.inserted.aspx

Returning rows with NULL using WHERE statements

Hi All

I have only just started using SQL Server as part of a new job, and have come across something while using Select statements. When you do a query using the Where clause eg. Where city = 'London' it returns results for just London, if you then do the same but using Where city <> 'London' again the results are obvious, but records where the city field is NULL do not appear in either? I would have assumed them to appear as part of the second query...

As I said, this is probably a daft question but if anyone can shed some light on it, it'd be much appreciated.

take a look here http://sqlservercode.blogspot.com/2006/01/null-trouble-in-sql-server-land.html

you need to do Where city <> 'London' or city is null

because null compared to anything is unknown

even null compared to null is unknown take a look at this

declare @.i int,@.i2 int
select @.i ,@.i2
if @.i = @.i2
select 'yes'
else
select 'no'

assuming ansi nulls is on (which is the default and you should leave it like that)

if it is set to off you will get no as a result

SET ANSI_NULLS OFF
go
declare @.i int,@.i2 int
select @.i ,@.i2
if @.i = @.i2
select 'yes'
else
select 'no'
go

SET ANSI_NULLS ON

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||That's great - thanks very much!

Returning rows as columns

Hi,
I have two tables ItemMaster and ItemCondition. One item can have multiple
conditions in the form of rows in ItemCondition. But while returning these,
I
would like to return it in the form of columns. Is that possible?
Below is the DDL of my table structure
CREATE TABLE [dbo].[ItemCondition] (
[ItemConditionId] [int] NOT NULL ,
[ItemId] [int] NOT NULL ,
[ItemCondition] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ItemConditionDesc] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[ItemMaster] (
[ItemId] [int] NOT NULL ,
[ItemName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemCondition] WITH NOCHECK ADD
CONSTRAINT [PK_ItemCondition] PRIMARY KEY CLUSTERED
(
[ItemConditionId]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[ItemMaster] WITH NOCHECK ADD
CONSTRAINT [PK_ItemMaster] PRIMARY KEY CLUSTERED
(
[ItemId]
) ON [PRIMARY]
GO
Below are some sample inserts
INSERT INTO itemmaster (ItemId, ItemName)
VALUES (1,"CD ROM")
GO
INSERT INTO itemmaster (ItemId, ItemName)
VALUES (2,"Pen drive")
GO
INSERT INTO itemmaster (ItemId, ItemName)
VALUES (3,"DVD")
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (1,1,"Write once read many","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (2,1,"Silver coated","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (3,1,"700 MB","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (4,3,"Power DVD","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (5,3,"8.2 GB","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (6,2,"1 GB","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (7,2,"Write lock","")
GO
INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
ItemConditionDesc)
VALUES (8,2,"password protection","")
GO
Sincerely appreciate any help on this.
Thank you.
Regards,
KarthikHave a look at
http://www.aspfaq.com/show.asp?id=2462
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Karthik" <Karthik@.discussions.microsoft.com> wrote in message
news:F279A4E0-72C3-466D-A122-66F885679485@.microsoft.com...
> Hi,
> I have two tables ItemMaster and ItemCondition. One item can have multiple
> conditions in the form of rows in ItemCondition. But while returning
> these, I
> would like to return it in the form of columns. Is that possible?
> Below is the DDL of my table structure
> CREATE TABLE [dbo].[ItemCondition] (
> [ItemConditionId] [int] NOT NULL ,
> [ItemId] [int] NOT NULL ,
> [ItemCondition] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
> ,
> [ItemConditionDesc] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemMaster] (
> [ItemId] [int] NOT NULL ,
> [ItemName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemCondition] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemCondition] PRIMARY KEY CLUSTERED
> (
> [ItemConditionId]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemMaster] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemMaster] PRIMARY KEY CLUSTERED
> (
> [ItemId]
> ) ON [PRIMARY]
> GO
>
> Below are some sample inserts
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (1,"CD ROM")
> GO
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (2,"Pen drive")
> GO
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (3,"DVD")
>
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (1,1,"Write once read many","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (2,1,"Silver coated","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (3,1,"700 MB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (4,3,"Power DVD","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (5,3,"8.2 GB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (6,2,"1 GB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (7,2,"Write lock","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (8,2,"password protection","")
> GO
> Sincerely appreciate any help on this.
> Thank you.
> Regards,
> Karthik|||Hi
Usually you are better doing this on the client.
If there are a maximum number of possibilities then you can use self-joins
such as :
SELECT M.ItemId, M.ItemName, C1.ItemCondition, C2.ItemCondition,
C3.ItemCondition, ....
FROM ItemMaster I
LEFT JOIN ItemCondition C1 ON I.ItemId = C1.ItemId AND C1.ItemConditionId =
1
LEFT JOIN ItemCondition C2 ON I.ItemId = C2.ItemId AND C2.ItemConditionId =
2
LEFT JOIN ItemCondition C3 ON I.ItemId = C3.ItemId AND C3.ItemConditionId =
3
LEFT JOIN ItemCondition C4 ON I.ItemId = C4.ItemId AND C4.ItemConditionId =
4
LEFT JOIN ItemCondition C5 ON I.ItemId = C5.ItemId AND C5.ItemConditionId =
5
You may also want to look at:
http://www.winnetmag.com/SQLServer/...5608/15608.html
and other postings on CROSSTABs.
John
"Karthik" wrote:

> Hi,
> I have two tables ItemMaster and ItemCondition. One item can have multiple
> conditions in the form of rows in ItemCondition. But while returning these
, I
> would like to return it in the form of columns. Is that possible?
> Below is the DDL of my table structure
> CREATE TABLE [dbo].[ItemCondition] (
> [ItemConditionId] [int] NOT NULL ,
> [ItemId] [int] NOT NULL ,
> [ItemCondition] [varchar] (200) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
> [ItemConditionDesc] [varchar] (2000) COLLATE SQL_Latin1_General_CP1_CI_AS
> NULL
> ) ON [PRIMARY]
> GO
> CREATE TABLE [dbo].[ItemMaster] (
> [ItemId] [int] NOT NULL ,
> [ItemName] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemCondition] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemCondition] PRIMARY KEY CLUSTERED
> (
> [ItemConditionId]
> ) ON [PRIMARY]
> GO
> ALTER TABLE [dbo].[ItemMaster] WITH NOCHECK ADD
> CONSTRAINT [PK_ItemMaster] PRIMARY KEY CLUSTERED
> (
> [ItemId]
> ) ON [PRIMARY]
> GO
>
> Below are some sample inserts
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (1,"CD ROM")
> GO
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (2,"Pen drive")
> GO
> INSERT INTO itemmaster (ItemId, ItemName)
> VALUES (3,"DVD")
>
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (1,1,"Write once read many","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (2,1,"Silver coated","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (3,1,"700 MB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (4,3,"Power DVD","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (5,3,"8.2 GB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (6,2,"1 GB","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (7,2,"Write lock","")
> GO
> INSERT INTO ItemCondition (ItemConditionId, ItemId, ItemCondition,
> ItemConditionDesc)
> VALUES (8,2,"password protection","")
> GO
> Sincerely appreciate any help on this.
> Thank you.
> Regards,
> Karthik

Returning row value before deletion

I want to return a value from a row before i delete it. Is this
possible to do in one operation something like...
SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
RETURN t.columnValCJ wrote:

> I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
Not in one query but why don't you make a stored procedure that returns
the value(s) and deletes the rows?
Kind regards,
Stijn Verrept.|||Thanks Stijn-
This is running inside a stored proc. I have an output param that
needs to return a value from the record being deleted. I didn't want
to run both SELECT and DELETE against the table if there was a way of
capturing the record from the DELETE.
Regards,
-CJ|||in 2005 check out the OUTPUT clause.
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>|||Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on the
SELECT statement that precedes the DELETE.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
> Thanks Stijn-
> This is running inside a stored proc. I have an output param that
> needs to return a value from the record being deleted. I didn't want
> to run both SELECT and DELETE against the table if there was a way of
> capturing the record from the DELETE.
> Regards,
> -CJ
>|||Many thanks!
So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
in a transaction and the DELETE cascades over sevreal tables?|||Brian
>Be sure to use WITH(UPDLOCK)
I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
I remember our last discussion sometime ago where you explained why we need
to use HOLDLOCK hint in addition to UPDLOCK.
"Brian Selzer" <brian@.selzer-software.com> wrote in message
news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
> Nope. Not in SQL 2000. Be sure to use WITH(UPDLOCK) or WITH(XLOCK) on
> the SELECT statement that precedes the DELETE.
> "CJ" <Charles.Deisler@.gmail.com> wrote in message
> news:1133143713.634416.313700@.o13g2000cwo.googlegroups.com...
>|||Not prior to a DELETE. UPDLOCK is sufficient. UPDLOCK, HOLDLOCK is
necessary if there is a possibility of an INSERT or UPDATE on another
connection that could cause a PRIMARY KEY or UNIQUE constraint violation by
changing the value to one that is about to be INSERTed or UPDATEed.
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eTAKC698FHA.2192@.TK2MSFTNGP14.phx.gbl...
> Brian
> I think we should add WITH (UPDLOCK,HOLDLOCK) hint , shouldn't we?
> I remember our last discussion sometime ago where you explained why we
> need to use HOLDLOCK hint in addition to UPDLOCK.
>
> "Brian Selzer" <brian@.selzer-software.com> wrote in message
> news:uTj93P88FHA.2364@.TK2MSFTNGP12.phx.gbl...
>|||If you have cascading referential actions, then you have to obtain update
locks all affected rows in all affected tables. I don't like cascading
referential actions. I think they are a tool for the lazy or the
incompetent and cause more problems then they're worth. They can cause
locking order confusion and can increase the probability of deadlocks. It
is not possible to determine with certainty which objects will be locked in
which order if cascading referential actions are active. It's better to
write the code manually in the stored procedure that performs the DELETE
(this is the preferred method) or to use a trigger (preferrably an INSTEAD
OF trigger) because then you have complete control over the order in which
locks are obtained, thus eliminating the main cause of deadlocks that can be
avoided. There still exists the possibility of deadlocks, due to threading
or a poorly generated execution plan, but deadlocks caused by accessing
objects in the wrong order can be completely eliminated.
You are correct that the SELECT(s) and the DELETE must be wrapped in a
transaction. In addition, if you don't use WITH(UPDLOCK), two separate
transactions can obtain and hold shared locks on the same row such that
neither can obtain an exclusive lock in order to perform the DELETE, thus
causing a deadlock.
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133153077.282668.76100@.g14g2000cwa.googlegroups.com...
> Many thanks!
> So UPDLOCK on the SELECT will work if the SELECT and DELETE are wrapped
> in a transaction and the DELETE cascades over sevreal tables?
>|||Would the Ouput clause not fit here?USE AdventureWorks;
GO
DELETE TOP(1) dbo.DatabaseLog WITH (READPAST)
OUTPUT deleted.*
WHERE DatabaseLogID = 7;
GO
William Stacey [MVP]
"CJ" <Charles.Deisler@.gmail.com> wrote in message
news:1133142606.786674.84690@.f14g2000cwb.googlegroups.com...
>I want to return a value from a row before i delete it. Is this
> possible to do in one operation something like...
> SELECT t.columnVal FROM (DELETE FROM table t WHERE t.c1 = @.p1)
> RETURN t.columnVal
>

returning row from function

Is it possible to do something like this:
First create function(function returns only one row):
CREATE FUNCTION dbo.s_function(@.fID int)
RETURNS @.tbl TABLE (pName varchar(20),quantity decimal(15,5)) AS
BEGIN
INSERT INTO @.tbl
SELECT TOP 1 * from...
WHERE tID=@.fID
RETURN
END
Then use function like this:
select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
If I would need only one value, than there won't be a problem:
I would create a function which returns only one value and use it:
select *,retValue=dbo.s_function(t.tID)
FROM testTable t
Regards,Ssimon,

> Is it possible to do something like this:
This can not be done in SQL Server 2000. In version 2005, you can use "CROSS
APPLY" operator.
Using CROSS APPLY in SQL Server 2005
http://www.sqlteam.com/item.asp?ItemID=21502
AMB
"simon" wrote:

> Is it possible to do something like this:
> First create function(function returns only one row):
> CREATE FUNCTION dbo.s_function(@.fID int)
> RETURNS @.tbl TABLE (pName varchar(20),quantity decimal(15,5)) AS
> BEGIN
> INSERT INTO @.tbl
> SELECT TOP 1 * from...
> WHERE tID=@.fID
> RETURN
> END
>
> Then use function like this:
> select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
> If I would need only one value, than there won't be a problem:
> I would create a function which returns only one value and use it:
> select *,retValue=dbo.s_function(t.tID)
> FROM testTable t
> Regards,S
>
>|||
>select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
Don't believe you can do this in SQL Server 2000. For SQL Server 2005
you can use CROSS APPLY|||simon wrote:
> Is it possible to do something like this:
> First create function(function returns only one row):
> CREATE FUNCTION dbo.s_function(@.fID int)
> RETURNS @.tbl TABLE (pName varchar(20),quantity decimal(15,5)) AS
> BEGIN
> INSERT INTO @.tbl
> SELECT TOP 1 * from...
> WHERE tID=@.fID
> RETURN
> END
>
> Then use function like this:
> select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
> If I would need only one value, than there won't be a problem:
> I would create a function which returns only one value and use it:
> select *,retValue=dbo.s_function(t.tID)
> FROM testTable t
> Regards,S
In SQL Server 2005 you could use the OUTER APPLY operator. In your case
however that seems to be unnecessary. For instance you could perhaps
use a derived table instead:
SELECT T.*, G.*
FROM testTable AS T
JOIN
(SELECT MIN(key_col) AS key_col
FROM /* unspecified */
GROUP BY tID) AS F
ON F.tID = T.tID
JOIN /* unspecified */ AS G
ON F.key_col = G.key_col ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Hi David,
I had very simple question, so I didn't post all code with samples,
otherwise, I agree with you.
I can use derived tables - thank you, but it doesn't work fast enough.
The function is couple of times faster than join with derived table, when
you have a lot of data.
So I usualy use functions, which filters all rows only to those I need (on
the other hand derived table first insert all data into some internal temp
table and than filters it in join - I think)
The problem which I have is, that I can't include in resulting row more than
one data returned from function.
So I use function for each data, when I would like to get more than one
column from function:
SELECT t.*, columnF1=dbo.function1(t.ID),columnF2=dbo.function2(t.ID) from
table t ...
I wonder if it,s possible to include in result set more than one column
returned from function?
my function would return for example 3 data, so 3 columns - only one row if
we speak in table world.
Something like this would work if I had table(with one row) instead of
function:
select t.*,f.* FROM testTable t INNER JOIN testTable1 f ON 1=1
But if I have function,which returns table with one row, won't work.
select t.*,f.* FROM testTable t INNER JOIN dbo.s_function(t.tID)f ON 1=1
So, I guess that it's not possible.
Regards,Simon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145282430.488437.92570@.g10g2000cwb.googlegroups.com...
> simon wrote:
> In SQL Server 2005 you could use the OUTER APPLY operator. In your case
> however that seems to be unnecessary. For instance you could perhaps
> use a derived table instead:
> SELECT T.*, G.*
> FROM testTable AS T
> JOIN
> (SELECT MIN(key_col) AS key_col
> FROM /* unspecified */
> GROUP BY tID) AS F
> ON F.tID = T.tID
> JOIN /* unspecified */ AS G
> ON F.key_col = G.key_col ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||simonZ wrote:
> I can use derived tables - thank you, but it doesn't work fast enough.
> The function is couple of times faster than join with derived table, when
> you have a lot of data.
How did you test that? Do you have a working solution using a function?
I'm actually surprised if a multi-statement table-valued function could
improve on a dervied table query for what you posted.

> SELECT t.*, columnF1=dbo.function1(t.ID),columnF2=dbo.function2(t.ID) from
> table t ...
> I wonder if it,s possible to include in result set more than one column
> returned from function?
Already answered. Not possible in 2000. CROSS APPLY / OUTER APPLY in
2005.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||I have table with a lot of data. When I need one data, for example, a volume
on some location in warehouse, I have 2 possibilities:
first option with derived table:
SELECT t.*,T1.volume, FROM table t1
INNER JOIN (SELECT wID,volume=sum(w.volume) from .......) as T1 ON
t.wID=T1.wID
WHERE ....
second option with function:
SELECT t.*,volume=dbo.functionV(wID) FROM table t1
and function:
set @.returnValue=(SELECT sum(volume) from ...
WHERE wID=@.wID)
Here, the function filters all rows to the single wID and calculate volume
only for this wID.
In derived table, I get all volumes for all wIDs, and than it's filtered to
my wID with join condition: ON t.wID=T1.wID
That is the reason, that function is much faster than derived table - I
guess.
My examples are more complicated, but it's the same idea. I have many cases,
where I create a function instead of derived table just because of
performance.
Even when I need more than one data (volume, mass,...), which can all be
calculated with single query in derived table, it's faster to create a
function for each column withs it's own query.
Am I doing something wrong?
Another question?
A while ago I posted one example but nobody answer me.
Can you look at it?
Just run the code and you will see, what is the point, otherwise I can
explain to you in more details.
The example works correct but it uses 2 cursors, which has bad performance.
Can you do the query which will return the same results as this example but
without 2 cursors?
http://groups.google.com/group/micr...2252c36ea4152d5
This example is not real one, it's simplified, but it serves as problem
review.
Thanks,
Simon
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145358771.297709.215400@.t31g2000cwb.googlegroups.com...
> simonZ wrote:
> How did you test that? Do you have a working solution using a function?
> I'm actually surprised if a multi-statement table-valued function could
> improve on a dervied table query for what you posted.
>
> Already answered. Not possible in 2000. CROSS APPLY / OUTER APPLY in
> 2005.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>

Returning role memberships FROM sql Server

A problem: I created some user defined roles in SQL SERVER 2000 and assigned users to different roles. Can you help me with a way (stored procedure etc.) to return the correct roles of my logged in users to my ASP.NET application.

Well if you have a table of logged in users you just have to do an Inner Join

SELECT *
FROM  LoggedInUsers liu INNER JOIN Roles r ON liu.role_id = r.id

Returning results while the query is executing

Hi,

I wanted to know if this is possible and if so, how do I
do it. Say, I have a query "SELECT * FROM Table WHERE
Column="some_value". This executes on a very large data
set and I would like to return the results as they query
executes rather than wait for the whole query to execute.
Basically, I want to get the results as they are prepared
by the database. Any way to do this?

Regards,
San"san" <sans11@.hotmail.com> wrote in message
news:8e29a54a.0309242021.1b3d4cd4@.posting.google.c om...
> Hi,
> I wanted to know if this is possible and if so, how do I
> do it. Say, I have a query "SELECT * FROM Table WHERE
> Column="some_value". This executes on a very large data
> set and I would like to return the results as they query
> executes rather than wait for the whole query to execute.
> Basically, I want to get the results as they are prepared
> by the database. Any way to do this?
> Regards,
> San

Use a CURSOR methodology which reads each
or a group of the input rows one at a time, and if
some value is detected, writes the results to a table.

Periodically query the table for updates.

But it is slower.
Much slower.

Is this a one-off task, or is it to be queued
as a repetitive cyclic task?

--
Farmer Brown
Falls Creek
Australia
www.mountainman.com.au/software|||san (sans11@.hotmail.com) writes:
> I wanted to know if this is possible and if so, how do I
> do it. Say, I have a query "SELECT * FROM Table WHERE
> Column="some_value". This executes on a very large data
> set and I would like to return the results as they query
> executes rather than wait for the whole query to execute.
> Basically, I want to get the results as they are prepared
> by the database. Any way to do this?

Since you cross-posted this to comp.databases.theory, I'm uncertain whether
you actually use MS SQL Server. In any case, this is engine dependent.

If you use MS SQL Server, you can achieve this without any special thrills.
It depends on your context, though. If you run the query from Query
Analyzer, you should have set output to text to see the rows coming in.
Results to grid and you will have to wait until all is done. If you
connect from ADO, you need to use a server-side forward-only cursor.

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

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

You can only get rows of the final result set. It is not possible to
peek at the intermediate results (results after each physical operator).

By default, individual rows of the result set will be sent to the client
whenever they are ready. You might be able to speed up the creation of
the first x rows by adding the hint OPTION (FAST x) where x is the
number of rows you want to have returned as fast as possible. Also,
dropping any ORDER BY clause may help.

Hope this helps,
Gert-Jan

san wrote:
> Hi,
> I wanted to know if this is possible and if so, how do I
> do it. Say, I have a query "SELECT * FROM Table WHERE
> Column="some_value". This executes on a very large data
> set and I would like to return the results as they query
> executes rather than wait for the whole query to execute.
> Basically, I want to get the results as they are prepared
> by the database. Any way to do this?
> Regards,
> San|||sans11@.hotmail.com (san) wrote in message news:<8e29a54a.0309242021.1b3d4cd4@.posting.google.com>...
> Hi,
> I wanted to know if this is possible and if so, how do I
> do it. Say, I have a query "SELECT * FROM Table WHERE
> Column="some_value". This executes on a very large data
> set and I would like to return the results as they query
> executes rather than wait for the whole query to execute.
> Basically, I want to get the results as they are prepared
> by the database. Any way to do this?
> Regards,
> San

i cant speak for all implementations, but I know how to do this with
oracle. Its not 'exactly' peaking. but it has a similiar result. You
optimize to get the first few rows, then you 'page'. Its what
google.com uses when you do a search and you get that bit estimate of
hits? Downside is the query will run slower overall, so its useful
when you have alot of data and your users will look at what comes up
first, then page to the next.

I dont remember the code exactly, its on asktom.oracle.com

select /*+ FIRST_ROWS */ b.*
from ( select a.*, a.rownum
from (your query here) a
where rownum < <<pick max number of rows you want in a batch >>)
where rownum > <<will start with 1 and be max + 1 for each 'page' >
Rownum is a pseudo-column that can be used as a counter. Its not a
real value. so you cant go:

select blah
from tab
where rownum = 200;

it counts the return value of the result set.

Id assume that many databases can do this. I know google uses oracle
to do this, Id assume other web vendors use other databases to do the
same thing.|||"mountain man" <hobbit@.southern_seaweed.com.op> wrote in message news:<iHvcb.122136$bo1.33845@.news-server.bigpond.net.au>...
> "san" <sans11@.hotmail.com> wrote in message
> news:8e29a54a.0309242021.1b3d4cd4@.posting.google.c om...
> > Hi,
> > I wanted to know if this is possible and if so, how do I
> > do it. Say, I have a query "SELECT * FROM Table WHERE
> > Column="some_value". This executes on a very large data
> > set and I would like to return the results as they query
> > executes rather than wait for the whole query to execute.
> > Basically, I want to get the results as they are prepared
> > by the database. Any way to do this?
> > Regards,
> > San
>
> Use a CURSOR methodology which reads each
> or a group of the input rows one at a time, and if
> some value is detected, writes the results to a table.
> Periodically query the table for updates.
>
> But it is slower.
> Much slower.
> Is this a one-off task, or is it to be queued
> as a repetitive cyclic task?

Hi,

My question is: Are the results of the query returned as they are
processed? That is, as the database engine constructs the result it
returns them (without waiting for the rest of the results to be
generated)?

Regards,
San|||Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns9401605629DDDYazorman@.127.0.0.1>...
> san (sans11@.hotmail.com) writes:
> > I wanted to know if this is possible and if so, how do I
> > do it. Say, I have a query "SELECT * FROM Table WHERE
> > Column="some_value". This executes on a very large data
> > set and I would like to return the results as they query
> > executes rather than wait for the whole query to execute.
> > Basically, I want to get the results as they are prepared
> > by the database. Any way to do this?
> Since you cross-posted this to comp.databases.theory, I'm uncertain whether
> you actually use MS SQL Server. In any case, this is engine dependent.
> If you use MS SQL Server, you can achieve this without any special thrills.
> It depends on your context, though. If you run the query from Query
> Analyzer, you should have set output to text to see the rows coming in.
> Results to grid and you will have to wait until all is done. If you
> connect from ADO, you need to use a server-side forward-only cursor.

Hi,

Regards,
San|||san (sans11@.hotmail.com) writes:
> My question is: Are the results of the query returned as they are
> processed? That is, as the database engine constructs the result it
> returns them (without waiting for the rest of the results to be
> generated)?

I believe so, although I have to admit that I have not conducted any
tests to verify that this is actually the case.

Notice also that it is likely to depend on the query.

If you say "SELECT * FROM big_tbl" you will probably get rows more or
less immediately. But if you say "SELECT * FROM big_tbl ORDER BY col",
you cannot get any rows before SQL Server has sorted the data.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||"san" <sans11@.hotmail.com> wrote in message
news:8e29a54a.0309282046.609f48f9@.posting.google.c om...
> "mountain man" <hobbit@.southern_seaweed.com.op> wrote in message
news:<iHvcb.122136$bo1.33845@.news-server.bigpond.net.au>...
> > "san" <sans11@.hotmail.com> wrote in message
> > news:8e29a54a.0309242021.1b3d4cd4@.posting.google.c om...
> > > Hi,
> > > > I wanted to know if this is possible and if so, how do I
> > > do it. Say, I have a query "SELECT * FROM Table WHERE
> > > Column="some_value". This executes on a very large data
> > > set and I would like to return the results as they query
> > > executes rather than wait for the whole query to execute.
> > > Basically, I want to get the results as they are prepared
> > > by the database. Any way to do this?
> > > > Regards,
> > > San
> > Use a CURSOR methodology which reads each
> > or a group of the input rows one at a time, and if
> > some value is detected, writes the results to a table.
> > Periodically query the table for updates.
> > But it is slower.
> > Much slower.
> > Is this a one-off task, or is it to be queued
> > as a repetitive cyclic task?
>
> Hi,
> My question is: Are the results of the query returned as they are
> processed? That is, as the database engine constructs the result it
> returns them (without waiting for the rest of the results to be
> generated)?

If you use a CURSOR, and select records in batches,
and write records in batches, how can it not? Have you
looked up 'cursor' in the query language documentation?

The results are returned in batches which correspond to your
use of the cursor. If you wish to see these results incrementally
accumulating then query the table being written.

Returning results to MSMQ from SSIS Package

I want my package to return the results to the MSMQ queue. from where it will be picked by another application. How do we do that.

Do u suggest using service broker for that. in that case how to connect that application to ssis service broker?Any Idea Experts?|||Does the MSMQ task not do what you want?
It's hard to answer a vague question like this.
Results from what? What form are the results in?
Where do you expect to use the results in the future?
Usually, the quality of the answer you get here is directly correlated to the quality of the question.|||Well this is confusing me also. Suppose I have a simple flat file, I want to process the file and send each row as a separate message to an MSMQ queue for consumption elsewhere. How do I do that ? Feels like it should be a MSMQ destination in the dataflow, not an MSMQ task in the control flow; something here I am not understanding.
Thanks
Tim|||MSMQ task should be able to take care of this.

As far as I'm aware the new T-SQL extensions are the method of interfacing with SQL Service Broker. In which case they can be issued using the Execute SQL Task.

-Jamie|||

Tim Daniels wrote:
Well this is confusing me also. Suppose I have a simple flat file, I want to process the file and send each row as a separate message to an MSMQ queue for consumption elsewhere. How do I do that ? Feels like it should be a MSMQ destination in the dataflow, not an MSMQ task in the control flow; something here I am not understanding.
Thanks
Tim

Tim,
Read the data into a variable using the Recordset destination. Loop over that variable using a Foreach loop container, each time passing the resultant row values into some locally scoped variables. Inside the Foreach loop container have an MSMQ task to put the row onto the queue.

It sounds like an MSMQ destination may be a viable request. You could write one yourself, perhaps using the script component.

-Jamie

Returning results Left Outer Join with Conditions

I have a SELECT Statement that I am using that is pulling from two tables. There won't always be results in the second table so I made a LEFT OUTER JOIN. The problem I am having is that I need to have three conditions in there:

WHERE (employee.emp_id = @.emp_id)AND (request.requested_time_taken ='FALSE')AND (request.request_end_date >=GETDATE()))

The two conditions from the request table are causing the entire query to return NULL as the value. I need help trying get a value whether or not there are any results in the request table.

Here is the full select statement:

SELECT (SELECTSUM(ISNULL(request.request_duration,'0'))AS Expr1FROM employeeLEFTOUTER JOIN requestAS requestON employee.emp_id = request.emp_idWHERE (employee.emp_id = @.emp_id)AND (request.requested_time_taken ='FALSE')AND (request.request_end_date >=GETDATE()))AS dayspendingFROM employeeAS employee_1LEFTOUTER JOIN requestAS request_1ON employee_1.emp_id = request_1.emp_idWHERE (employee_1.emp_id = @.emp_id)GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial, employee_1.emp_lname

Null values are ignored in some T-SQL aggregate function such as SUM/AVG, so you can move the ISNULL function to outer:

SELECT (SELECT ISNULL(SUM(request.request_duration),0))AS Expr1...

|||

Sounds like this is what you want:

SELECT ISNULL((SELECT SUM(ISNULL(request.request_duration,'0'))AS Expr1
FROM employeeLEFTOUTER JOIN
requestAS requestON employee.emp_id = request.emp_id
WHERE (employee.emp_id = @.emp_id)AND (request.requested_time_taken ='FALSE')AND (request.request_end_date >=GETDATE())) ,0)
AS dayspending
FROM employeeAS employee_1LEFTOUTER JOIN
requestAS request_1ON employee_1.emp_id = request_1.emp_id

WHERE (employee_1.emp_id = @.emp_id)
GROUP BY employee_1.emp_id, employee_1.emp_begin_accrual, employee_1.emp_accrual_rate, employee_1.emp_fname, employee_1.emp_minitial,
employee_1.emp_lname

Returning resulsets between procedures !!

Hello, I need to do the following, But I receive an error:

CREATE PROCEDURE dbo.EJEMPLO_A AS
BEGIN
SELECT TOP 10 NAME, ID FROM SYSCOLUMNS
END
-- and Later use the one above... like this...

CREATE PROCEDURE dbo.EJEMPLO_B AS
BEGIN

DECLARE @.temp_lista TABLE (NOMBRE VARCHAR(500),
IDENT NUMERIC(5))

INSERT INTO @.temp_lista execute dbo.EJEMPLO_JULIAN

END

Pd. I just have to capture the dbo.EJEMPLO_A resulset... and I can't modify It

Jortiz:

You have to use a temp table instead of a table variable to receive your data. create a temp table instead of declaring a table variable; you are not allowed to use a table variable for what you are attempting.

Dave

|||Hello, could you please provide me an Example ? or tell me how to do it ?|||I got to Do It using a temporary table... But... is there any possibility to use a table variable or a Cursor ?
|||

Jortiz:

I think in this case it would really be more appropriate to convert your stored procedure to an inline function -- something like:

create function dbo.EJEMPLO_A ()
returns table
as
return (SELECT TOP 10 NAME, ID FROM SYSCOLUMNS)

Also, you cannot return it into a table variable and your are usually going to be better off avoiding a cursor-based solution; moreover, you cannot use the INSERT INTO ... EXEC PROC construct on more than one nesting level. You are really better off with the inline function solution.

Dave

|||Hi Dave, thank you so much for your answer... The problem here Is that I can't modify the dbo.EJEMPLO_A procedure, I just can change the second one...
|||

jortiz wrote:
Hi Dave, thank you so much for your answer... The problem here Is that I can't modify the dbo.EJEMPLO_A procedure, I just can change the second one...

I mean ... I have to do in the second procedure something like SELECT dbo.EJEMPLO_A

Thanks
|||

Jortiz:

OK. Then I think best option is probably to use a temp table to receive the data. The code for that would be something like:

CREATE TABLE #receiver
( column1
...
columnN
)
INSERT INTO #receiver
EXEC EJEMPLO_A

After this INSERT ... EXEC the #receiver table will be populated with the values returned from the stored procedure. If you could create an alternate function version of EJEMPLOY_A -- say a function named EJEMPLOY_Af the select to return the values from that function would simply be something like:

SELECT * FROM EJEMPLOY_Af ()

in addition, you would be able to join to that function similar to the way that you would join to a table.

|||I think I'll have to do that !!!

Thanks !!

Returning Resulset

Hello, I need to do the following, But I receive an error:

CREATE PROCEDURE dbo.EJEMPLO_A AS
BEGIN
SELECT TOP 10 NAME, ID FROM SYSCOLUMNS
END
-- and Later use the one above... like this...

CREATE PROCEDURE dbo.EJEMPLO_B AS
BEGIN

DECLARE @.temp_lista TABLE (NOMBRE VARCHAR(500),
IDENT NUMERIC(5))

INSERT INTO @.temp_lista execute dbo.EJEMPLO_JULIAN

END

Pd. I just have to capture the dbo.EJEMPLO_A resulset... and I can't modify ItTable variables cannot be used in a INSERT EXEC or SELECT INTO statement.

http://support.microsoft.com/default.aspx/kb/305977

in your second sproc you can use a temp table and it should be ok. EDIT: however this sproc will do nothing because it's not returning any data, or modifying any data in any permanent tables. so why bother?

Returning recordset that includes a DateDiff field in hh:mm:ss format?

Hi, not too swift with anything other than simple SQL statements, so
I'm looking for some help.

Using SQL Server 2000 with this stored proc:

(@.varCust varchar(50))

AS
SET NOCOUNT ON

SELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station,
d.WIdoc,
d.Start, d.StartUser, d.Finish, d.FinishUser
FROM tblWorkOrder w, tblDocs d
WHERE w.WorkOrder IS NULL AND w.WorkOrder = d.WorkOrder AND
d.Customer = @.varCust
GO

I'm trying to get a complete dataset so I can simply apply it as the
datasource to a datagrid in asp.net. I need to include a 'TimeSpan'
column that is the difference between d.Start and d.Finish. I also
need it to present in hh:mm:ss format in the datagrid column. (A) is
it possible to do this within the stored proc, and (B) how would "I"
do that?

Thanks!
Kathy[posted and mailed, please reply in news]

KathyB (KathyBurke40@.attbi.com) writes:
> SELECT d.WorkOrder, d.Customer, d.SerialNo, d.Assy, d.Station,
> d.WIdoc,
> d.Start, d.StartUser, d.Finish, d.FinishUser
> FROM tblWorkOrder w, tblDocs d
> WHERE w.WorkOrder IS NULL AND w.WorkOrder = d.WorkOrder AND
> d.Customer = @.varCust
> GO
> I'm trying to get a complete dataset so I can simply apply it as the
> datasource to a datagrid in asp.net. I need to include a 'TimeSpan'
> column that is the difference between d.Start and d.Finish. I also
> need it to present in hh:mm:ss format in the datagrid column. (A) is
> it possible to do this within the stored proc, and (B) how would "I"
> do that?

See this example:

declare @.a datetime, @.b datetime
select @.a = '2003-12-23 10:55:12',
@.b = '2003-12-23 21:45:09'
select convert(char(10),
dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

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

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

Would you please tell me what this line does:

dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

Also, not to be totally gready, but how do I fit this into my return
dataset as a column?

thanks again for answering.

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Erland, this works GREAT!!!! Thanks so much and happy holidays!

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Kathy Burke (kathyburke40@.attbi.com) writes:
> Would you please tell me what this line does:
> dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

The complete expression was:

select convert(char(10),
dateadd(ss, datediff(ss, @.a, @.b), '19000101'), 108)

I encourage you look up the convert, dateadd and datediff cuntions
in Books Online. You find Convert under the top Cast and Convert.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Erland, I did look it up but still didn't understand the 19000101 (other
than it is the date 01/01/1900). I found that the 108 is the format
code, etc.

Thanks again...it saves me a lot of time doing this is the query
results.

Kathy

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!|||Kathy Burke (kathyburke40@.attbi.com) writes:
> Erland, I did look it up but still didn't understand the 19000101 (other
> than it is the date 01/01/1900). I found that the 108 is the format
> code, etc.

You can replace 1900-01-01 with any date. I could also have left an
empty string - which would be the same as 1900-01-01 thanks to the
default rules for datetime literals. The important for the example is
that we use a datetime value of which the time portion is midnight.

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

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

Returning recordset as xml via DomDocument in ASP results in error

Hi all,
I'm trying to return a recordset obtained from a sql server 2000 stored
procedure, as a text/XML content type via old asp.
The problem is the sql data contains foreign characters and when returning
the xml page to the IE6, the default xsl shows the error "invalid character"
for the foreign characters.
The code for the sp is the following:
Create Procedure webTest( @.nDummy Int )
As
Begin
Select Id = 1, Name = 'PARAGRAPH SYMBOL '
End
The code for the asp is:
<%@. Language=JavaScript %>
<%
Response.ContentType = "text/xml";
var cn=Server.CreateObject("ADODB.Connection");
var rs=Server.CreateObject("ADODB.Recordset");
var cmd=Server.CreateObject("ADODB.Command");
cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
Catalog=MATDESA;Data Source=sqlrespaldo01");
rs.CursorLocation = 3;
cmd.ActiveConnection = cn;
cmd.CommandType = adCmdStoredProc;
cmd.CommandText = "webTest";
var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
cmd.Parameters.Append(prm);
var rs = cmd.Execute();
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
rs.Close();
cn.Close();
%>
Looking at the return on IE6 it returns "invalid character" on the ""
symbol
But if I modify the code to save the recordset directly to the Response
stream, then it works. I mean if substituting the lines
var doc = Server.CreateObject("MSXML2.DOMDocument");
rs.Save(doc, adPersistXML);
Response.Write(doc.xml);
by
rs.Save(Response, adPersistXML);
the browser shows ok the foreign symbols.
Where is the error on the first case? It looks like the created doc assumes
ascii character encoding or something alike...
Any hint is welcomed
Thanks in advance
Sammy
"SammyBar" <sammybar@.gmail.com> wrote in message
news:e%2393NI9QHHA.3592@.TK2MSFTNGP06.phx.gbl...
> Hi all,
> I'm trying to return a recordset obtained from a sql server 2000 stored
> procedure, as a text/XML content type via old asp.
> The problem is the sql data contains foreign characters and when returning
> the xml page to the IE6, the default xsl shows the error "invalid
character"
> for the foreign characters.
> The code for the sp is the following:
> Create Procedure webTest( @.nDummy Int )
> As
> Begin
> Select Id = 1, Name = 'PARAGRAPH SYMBOL '
> End
> The code for the asp is:
> <%@. Language=JavaScript %>
> <%
> Response.ContentType = "text/xml";
> var cn=Server.CreateObject("ADODB.Connection");
> var rs=Server.CreateObject("ADODB.Recordset");
> var cmd=Server.CreateObject("ADODB.Command");
> cn.Open("Provider=SQLOLEDB.1;User ID=xxxx;Password=yyyy;Initial
> Catalog=MATDESA;Data Source=sqlrespaldo01");
> rs.CursorLocation = 3;
> cmd.ActiveConnection = cn;
> cmd.CommandType = adCmdStoredProc;
> cmd.CommandText = "webTest";
> var prm = cmd.CreateParameter("@.nDummy", adInteger, adParamInput, 0, 123);
> cmd.Parameters.Append(prm);
> var rs = cmd.Execute();
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> rs.Close();
> cn.Close();
> %>
> Looking at the return on IE6 it returns "invalid character" on the ""
> symbol
> But if I modify the code to save the recordset directly to the Response
> stream, then it works. I mean if substituting the lines
> var doc = Server.CreateObject("MSXML2.DOMDocument");
> rs.Save(doc, adPersistXML);
> Response.Write(doc.xml);
> by
> rs.Save(Response, adPersistXML);
> the browser shows ok the foreign symbols.
> Where is the error on the first case? It looks like the created doc
assumes
> ascii character encoding or something alike...
>
All strings in script are unicode.
Hence doc.xml returns a unicode encoding of the XML document content.
Response.Write takes such a string and sends it to the client encoding it
using the the current Response.CodePage setting (Session.Codepage on
IIS5[.1])
The codepage defaults to the system codepage, typically in the West 1252.
Hence the character is encoded as a standard ANSI 1252 single byte
character.
However the receiving client XML DOM is expecting UTF-8 which is the default
encoding used by XML. Hence the character causes an error.
rs.Save(Response, adPersistXML)
OTH, will send the XML generated by the recordset in it's 'native' UTF-8
format directly to the client without being converted to another codepage.
Hence all is well.
As Martin has said this is the recommended approach.
BTW, personnally I prefer to tell the client explicitly it is receiving
UTF-8 using:-
Response.CharSet = "UTF-8"
Immediately after setting the content type.
Anthony.

Saturday, February 25, 2012

Returning Stored Proceduers via ADO Schema Rowsets

Returning Stored Proceduers via ADO Schema Rowsets

returning status periodically from stored procedure

Returning SSIS stored procedure name

Returning SqlDecimal with the correct scale

Returning SQL servers on a network

Returning small managable sets from a SELECT statement

Returning single value from multiple recordsets

Returning Scope_Identity() for GUID

Returning Scope_Identity from Stored Procedures

Returning rows with NULL using WHERE statements

Returning rows as columns

Returning row value before deletion

returning row from function

Returning role memberships FROM sql Server

Returning results while the query is executing

Returning results to MSMQ from SSIS Package

Returning results Left Outer Join with Conditions

Returning resulsets between procedures !!

Returning Resulset

Returning recordset that includes a DateDiff field in hh:mm:ss format?

Returning recordset as xml via DomDocument in ASP results in error

Null values

Blog Archive

About Me