Hi,
Alternate of Recordset.GetString() in Stored Procedure !If you explain what it does or what you want to accomplish, you make it a lo
t easier for us...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"DMP" <debdulal.mahapatra@.fi-tek.co.in> wrote in message
news:%23USo8WKRFHA.3076@.tk2msftngp13.phx.gbl...
> Hi,
> Alternate of Recordset.GetString() in Stored Procedure !
>|||Hi
Sounds like you may be wanting to use cursors, in which case you may be
looking at it the wrong way as set based solutions are usually the quickest.
Check out books online which has a wealth of information and examples. The
latest version is downloadable at
http://www.microsoft.com/sql/techin.../2000/books.asp
John
"DMP" wrote:
> Hi,
> Alternate of Recordset.GetString() in Stored Procedure !
>
>sql
Showing posts with label recordset. Show all posts
Showing posts with label recordset. Show all posts
Monday, March 26, 2012
Monday, March 19, 2012
Get XML On Its Merry Way
Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lq
Hello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lq
Hello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegro ups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
Get XML On Its Merry Way
Apologies ahead of time for XML newbie...
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lqHello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
I can use the FOR XML clause in SQL Server 2000 to create an XML
representation of a SELECT recordset, but what is the method to get
this XML outbound from SQL Server to the WebService on my server (using
SOAP)?
Anything that points me in the right direction is appreciated.
lqHello Lauren
No, in SQL Server 2000. You should make webservices yourself.
There is a /create endpoint/ statement which exposes your stored procedures
or UDFs as webservice. But it's only available in SQL 2005. With that you
can create proxy class on your web server.
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>|||Lauren,
If so, I've posted XML data resulting from a SQL query to CGI via HTTP Post
in an ActiveX script. You can probably do the same with a web service. I
forget the details, you will have to research the XMLHttpRequest object. If
your XML is more than a certain size (2K or 4K?) you will have to use an
HTTP Post. Sorry, I don't remember any more details.
-- Bill
"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:1169125941.462554.185310@.38g2000cwa.googlegroups.com...
> Apologies ahead of time for XML newbie...
> I can use the FOR XML clause in SQL Server 2000 to create an XML
> representation of a SELECT recordset, but what is the method to get
> this XML outbound from SQL Server to the WebService on my server (using
> SOAP)?
> Anything that points me in the right direction is appreciated.
> lq
>
Get Value from CURSOR
I'm writing a stored procedure that involves looping through a recordset and using the values from the recordset as parameters for a second stored procedure. Here's what I've got so far...
My question is, how do I get the value out of the Cursor? There's only one field.
Declare @.Day as int
Declare @.Plant as varchar(30)
SET NOCOUNT ON
CREATE Table #Temp (Facility varchar(30), ProductCategory nvarchar(3), Target int, Quantity int, Percentage decimal(10,2), Production_Date smalldatetime,As_Of_Time smalldatetime)
Declare Facility_Cursor CURSOR
For Select Distinct(Facility) From ProductionHistory
OPEN Facility_CURSOR
Declare @.Facility_Cursor as sysname
FETCH NEXT From Facility_CURSOR into @.Facility_Cursor
WHILE @.@.FETCHSTATUS = 1
--YESTERDAY
Set @.Day = -2
Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'
--TODAY
SET @.Day = -1
Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'
FETCH NEXT FROM Facility_CURSOR into @.Facility_Cursor
CLOSE Facility_Cursor
DEALLOCATE Facility_CURSOR
SET NOCOUNT OFF
Select * From #Temp ORDER BY Production_Date, Facility, ProductCategory DESCI see that you are trying to pass a variable "@.Facility" to your sproc without defining it, and the results of the cursor are being placed into @.Facility_Cursor. If you change the variable declaration to @.Facility and then FETCH NEXT From Facility_CURSOR into @.Facility it just might work!|||That did the trick. I knew it was something simple. Thanks!
My question is, how do I get the value out of the Cursor? There's only one field.
Declare @.Day as int
Declare @.Plant as varchar(30)
SET NOCOUNT ON
CREATE Table #Temp (Facility varchar(30), ProductCategory nvarchar(3), Target int, Quantity int, Percentage decimal(10,2), Production_Date smalldatetime,As_Of_Time smalldatetime)
Declare Facility_Cursor CURSOR
For Select Distinct(Facility) From ProductionHistory
OPEN Facility_CURSOR
Declare @.Facility_Cursor as sysname
FETCH NEXT From Facility_CURSOR into @.Facility_Cursor
WHILE @.@.FETCHSTATUS = 1
--YESTERDAY
Set @.Day = -2
Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'
--TODAY
SET @.Day = -1
Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'
FETCH NEXT FROM Facility_CURSOR into @.Facility_Cursor
CLOSE Facility_Cursor
DEALLOCATE Facility_CURSOR
SET NOCOUNT OFF
Select * From #Temp ORDER BY Production_Date, Facility, ProductCategory DESCI see that you are trying to pass a variable "@.Facility" to your sproc without defining it, and the results of the cursor are being placed into @.Facility_Cursor. If you change the variable declaration to @.Facility and then FETCH NEXT From Facility_CURSOR into @.Facility it just might work!|||That did the trick. I knew it was something simple. Thanks!
Wednesday, March 7, 2012
get table name
Hi,
how can i retreive a tble name from adodb.recordset object?I don't understand what you want... do you mean how to get all the table names from a database, or how to get the table name from an already opened recordset?
If your question is the second, i dont think it is possible, remember that a recordset can have records from more than one table.
how can i retreive a tble name from adodb.recordset object?I don't understand what you want... do you mean how to get all the table names from a database, or how to get the table name from an already opened recordset?
If your question is the second, i dont think it is possible, remember that a recordset can have records from more than one table.
Sunday, February 26, 2012
Get recordset result in variables
Hello folks.
I am running a series of queries in a stored procedure.
For example, my first query might return a recordset like this
Apples
Oranges
Pears
Turnips
I want to put those reults in variables
So I might have
Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
char(10
Select top 4 fruits from tblFruits
How do I get the recordset into the variable?Sorry to answer you with another question but could you explain just
*why* you would want to assign the results to variables? Your reason
may have some bearing on the answer.
Your requirement is a bit unusual. SQL Server doesn't have arrays. The
main data structure is a table and it is hard work to manipulate lists
of variables just because that's not really what the declarative SQL
language was designed to do.
David Portas
SQL Server MVP
--|||Try one by one.
declare @.Fruit1 char(10)
declare @.Fruit2 char(10)
declare @.Fruit3 char(10)
declare @.Fruit4 char(10)
select top 1 @.Fruit1 = fruits from tblFruits
select top 1 @.Fruit2 = fruits from tblFruits
where fruits != @.Fruit1
select top 1 @.Fruit3 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2
select top 1 @.Fruit4 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2 and fruits != @.Fruit3
go
AMB
"Bob" wrote:
> Hello folks.
> I am running a series of queries in a stored procedure.
> For example, my first query might return a recordset like this
> Apples
> Oranges
> Pears
> Turnips
> I want to put those reults in variables
> So I might have
> Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
> char(10
> Select top 4 fruits from tblFruits
> How do I get the recordset into the variable?
>
I am running a series of queries in a stored procedure.
For example, my first query might return a recordset like this
Apples
Oranges
Pears
Turnips
I want to put those reults in variables
So I might have
Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
char(10
Select top 4 fruits from tblFruits
How do I get the recordset into the variable?Sorry to answer you with another question but could you explain just
*why* you would want to assign the results to variables? Your reason
may have some bearing on the answer.
Your requirement is a bit unusual. SQL Server doesn't have arrays. The
main data structure is a table and it is hard work to manipulate lists
of variables just because that's not really what the declarative SQL
language was designed to do.
David Portas
SQL Server MVP
--|||Try one by one.
declare @.Fruit1 char(10)
declare @.Fruit2 char(10)
declare @.Fruit3 char(10)
declare @.Fruit4 char(10)
select top 1 @.Fruit1 = fruits from tblFruits
select top 1 @.Fruit2 = fruits from tblFruits
where fruits != @.Fruit1
select top 1 @.Fruit3 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2
select top 1 @.Fruit4 = fruits from tblFruits
where fruits != @.Fruit1 and fruits != @.Fruit2 and fruits != @.Fruit3
go
AMB
"Bob" wrote:
> Hello folks.
> I am running a series of queries in a stored procedure.
> For example, my first query might return a recordset like this
> Apples
> Oranges
> Pears
> Turnips
> I want to put those reults in variables
> So I might have
> Declare @.Fruit1 char(10),@.Fruit2 char(10),@.Fruit3 char(10),@.Fruit4
> char(10
> Select top 4 fruits from tblFruits
> How do I get the recordset into the variable?
>
Subscribe to:
Posts (Atom)