Create an XML file from a stored procedure

I'm trying to create a temp xml file to to convert it eventually into an excel file.

using C#

I am calling a stored procedure and from that data retrieved I want to write it in xml format.

I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)

I am new to using xml, so any help would be great..

Can I write an xml file by using a streamWriter or writing all bytes?

Thanks,

kt

You haven't mentioned if you are using SQL Server 2000 or 2005.
If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.

These will return for you the results in the XML format you want and you might not really need to format it manually using C#.

Hope that helps!
|||

I'm using SQL Server 2005...

You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?

sql

Create an XML file from a stored procedure

I'm trying to create a temp xml file to to convert it eventually into an excel file.

using C#

I am calling a stored procedure and from that data retrieved I want to write it in xml format.

I tried using the xmlTextWriter.. but I get an exception, it refers to a token..(?)

I am new to using xml, so any help would be great..

Can I write an xml file by using a streamWriter or writing all bytes?

Thanks,

kt

You haven't mentioned if you are using SQL Server 2000 or 2005.
If its 2000, then check the FOR XML {AUTO, RAW, EXPLICIT} syntax in books online.
If its 2005 you might want to use the FOR XML PATH.

These will return for you the results in the XML format you want and you might not really need to format it manually using C#.

Hope that helps!
|||

I'm using SQL Server 2005...

You wouldn't possibly know any good resources on formatting.. for using "FOR XML PATH"..?

create an Indexes/Keys Property with T-SQL

is there a function that i can use in a store procedure that allow me to create an Indexes/Keys Property
thanxYes. CREATE INDEX.

-PatP|||Yes. CREATE INDEX.

-PatP

excuse me while I climb back on my barstool...um office chair...

LNHockey...seriously though... alittle more background on what you're trying to do...

"create index"....why I outta.....|||Yes ok..excuse me.

i do this
<b>
ALTER TABLE TblSalle ADD [IdTypeTaxe] [int] NOT NULL default(0)<br>
CREATE INDEX PK_TblSalle ON TblSalle (IdTypeTaxe)</b>

and would like to assign is "Selected index value" to IX_IdTypeTaxe that i userly have in a combobox when i use the sql manager

but i want to do it in a store proc..

or how can i modify that value after i add the new column to my table|||Yup...I'm lost..

Huh?|||You know when you go to design mode of the table and go properties of the selected column and select the tab "Indexes/keys". under that you can switch the type "Primary key to Index" ?? !!!

just wondering if we can do the same thing in a Store proc using a function kind of thing

thanx !

create an incremental counter in the stored procedure

Hello, I have a following SP
I want to add an extra field "ranking" that just increments the row number.
Another feature would be: if several users have an equal totalvalue, they
should have an equal ranking number. the rankings following users would have
to be adjusted as well. thanks

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

CREATE PROCEDURE [dbo].[Rankings]
@.iErrorCode int OUTPUT
AS

SELECT top 30
###COUNTER##,
[user],
[totalvalue], [cash], [stocksvalue]

FROM [dbo].[users]
ORDER BY totalvalue DESC

SELECT @.iErrorCode=@.@.ERROR

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GOThere is more than one way to rank a set with tied values.

CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2) NOT
NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)

INSERT INTO Users VALUES (101,1010,100,99)
INSERT INTO Users VALUES (102,2020,100,99)
INSERT INTO Users VALUES (103,3030,100,99)
INSERT INTO Users VALUES (104,3030,100,99)
INSERT INTO Users VALUES (105,1002,100,99)
INSERT INTO Users VALUES (106,1002,100,99)
INSERT INTO Users VALUES (107,1002,100,99)
INSERT INTO Users VALUES (108,1002,100,99)
INSERT INTO Users VALUES (109,1000,100,99)

See if this gives the result you expect:

SELECT COUNT(U2.totalvalue)+1 AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue < U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
3 102 2020.00 100.00 99.00
4 101 1010.00 100.00 99.00
5 105 1002.00 100.00 99.00
5 106 1002.00 100.00 99.00
5 107 1002.00 100.00 99.00
5 108 1002.00 100.00 99.00
9 109 1000.00 100.00 99.00

(9 row(s) affected)

Or maybe this:

SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
FROM Users AS U1
LEFT JOIN Users AS U2
ON U1.totalvalue <= U2.totalvalue
GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
ORDER BY ranking, U1.userid

Result:

ranking userid totalvalue cash stocksvalue
---- ---- ---- ---- ----
1 103 3030.00 100.00 99.00
1 104 3030.00 100.00 99.00
2 102 2020.00 100.00 99.00
3 101 1010.00 100.00 99.00
4 105 1002.00 100.00 99.00
4 106 1002.00 100.00 99.00
4 107 1002.00 100.00 99.00
4 108 1002.00 100.00 99.00
5 109 1000.00 100.00 99.00

(9 row(s) affected)

--
David Portas
SQL Server MVP
--|||thank you! it works fine.

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> schrieb im
Newsbeitrag news:1pmdnW4hl-GFFt3dRVn-uA@.giganews.com...
> There is more than one way to rank a set with tied values.
> CREATE TABLE Users (userid INTEGER PRIMARY KEY, totalvalue NUMERIC(10,2)
NOT
> NULL, cash NUMERIC(10,2) NOT NULL, stocksvalue NUMERIC(10,2) NOT NULL)
> INSERT INTO Users VALUES (101,1010,100,99)
> INSERT INTO Users VALUES (102,2020,100,99)
> INSERT INTO Users VALUES (103,3030,100,99)
> INSERT INTO Users VALUES (104,3030,100,99)
> INSERT INTO Users VALUES (105,1002,100,99)
> INSERT INTO Users VALUES (106,1002,100,99)
> INSERT INTO Users VALUES (107,1002,100,99)
> INSERT INTO Users VALUES (108,1002,100,99)
> INSERT INTO Users VALUES (109,1000,100,99)
> See if this gives the result you expect:
> SELECT COUNT(U2.totalvalue)+1 AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue < U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 3 102 2020.00 100.00 99.00
> 4 101 1010.00 100.00 99.00
> 5 105 1002.00 100.00 99.00
> 5 106 1002.00 100.00 99.00
> 5 107 1002.00 100.00 99.00
> 5 108 1002.00 100.00 99.00
> 9 109 1000.00 100.00 99.00
> (9 row(s) affected)
> Or maybe this:
> SELECT COUNT(DISTINCT U2.totalvalue) AS ranking,
> U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> FROM Users AS U1
> LEFT JOIN Users AS U2
> ON U1.totalvalue <= U2.totalvalue
> GROUP BY U1.userid, U1.totalvalue, U1.cash, U1.stocksvalue
> ORDER BY ranking, U1.userid
> Result:
> ranking userid totalvalue cash stocksvalue
> ---- ---- ---- ---- ----
> 1 103 3030.00 100.00 99.00
> 1 104 3030.00 100.00 99.00
> 2 102 2020.00 100.00 99.00
> 3 101 1010.00 100.00 99.00
> 4 105 1002.00 100.00 99.00
> 4 106 1002.00 100.00 99.00
> 4 107 1002.00 100.00 99.00
> 4 108 1002.00 100.00 99.00
> 5 109 1000.00 100.00 99.00
> (9 row(s) affected)
> --
> David Portas
> SQL Server MVP
> --

Create a view help!

Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SR
Not sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>

Create a view help!

Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>

Create a view help!

Hi Guys,
I was wonder if it is possible to create a view using a stored procedure,
e.g
Create view [name] as
exec sp 'zxc'
or is there any other way to create a view using a query stored somewhere?
Thanks in advance
SRNot sure what you'd accomplish by doing this, but you could use a
table-valued function:
CREATE FUNCTION dbo.tables()
RETURNS @.tables TABLE
(
TABLE_NAME SYSNAME
)
AS
BEGIN
INSERT @.tables
SELECT TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
RETURN
END
GO
CREATE VIEW dbo.SillyTablesView
AS
SELECT TABLE_NAME FROM dbo.tables()
GO
SELECT * FROM dbo.SillyTablesView
SELECT * FROM dbo.tables()
GO
http://www.aspfaq.com/
(Reverse address to reply.)
"SR" <srego@.seclon.com> wrote in message
news:e8OkzGzbEHA.3144@.TK2MSFTNGP09.phx.gbl...
> Hi Guys,
> I was wonder if it is possible to create a view using a stored procedure,
> e.g
> Create view [name] as
> exec sp 'zxc'
> or is there any other way to create a view using a query stored somewhere?
> Thanks in advance
> SR
>

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itself), and a SELECT
statement read data from tables or views, it doesn't execute stored procedures. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...
> How can I create a view which based on a store procedure and then sp will return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: "00KobeBrian" <a@.b.com>
>>Subject: create a view
>>Date: Tue, 11 Apr 2006 15:54:51 +0800
>>Lines: 4
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2180
>>X-RFC2646: Format=Flowed; Original
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2180
>>Message-ID: <OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 210.177.248.66
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP04.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:427533
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>How can I create a view which based on a store procedure and then sp will
>>return a select statement only? Thanks.
>>|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

create a view

How can I create a view which based on a store procedure and then sp will
return a select statement only? Thanks.You can't (*). A view is one SELECT statement (which can be complex in itsel
f), and a SELECT
statement read data from tables or views, it doesn't execute stored procedur
es. Consider re-writing
the procedure to a table-valued function.
(*) Well, you can by using OPENROWSET or OPENQUERY, but that is a hack and I
wouldn't use it in
production.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"00KobeBrian" <a@.b.com> wrote in message news:OWbx40TXGHA.196@.TK2MSFTNGP04.phx.gbl...seagreen">
> How can I create a view which based on a store procedure and then sp will
return a select
> statement only? Thanks.
>|||Hi,
I'd like to know your concern clearly:
You want to create a store procedure which return a data set and you want
to create a view based on the data set.
I think you should use User Defined Function(UDF), it Returns a table Data
Type which can be used after As in Create View statement.
You can refer to following link for sample of UDF.
<http://msdn.microsoft.com/library/d...n-us/samples/sa
mples_1far.asp>
Hope it helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]|||or, maybe you'd be happier skipping the whole view thing entirely, and
using the stored procedures to return the table and or data you are
after.

Create a Trigger to run a Stored Procedure

I want to create a Trigger that will run once a day at a set time.

This trigger would actually run a Stored Procedure named say "spAppData".

This SP will in turn run and delete the entire tables data, and re-insert data into a table we'll call APPS.

Would anyone know how to help me get started on creating a Trigger with the info I've provided?

Thank you,

NBK

To schedule an action, you want a SQL Agent Job to execute your stored procedure at the determined time.

If you are using SQL Express (which does NOT include SQL Agent), you can use a combination of a Windows Scheduler task, SQLCmd.exe, and a Stored Procedure

|||

Thank you Arnie. That's just the direction I needed.

Wallace

|||can you please give more details on this.
i have same requrement.
thanks in advance

Create a table using a Stored Procedure

Hi,
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) wa
y
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:

> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--

Create a table using a Stored Procedure

Hi,
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks
"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END
|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:

> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>
|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457

Create a table using a Stored Procedure

Hi,
I am someone new to SQL Server in general. I am using the Trial Version of
SQL Server 2005. I want to create a stored procedure to create a table. The
table name would be the only parameter of the stored procedure. I was
thinking of something like this (I dont know if even the syntax is parcially
correct, sorry first time with Stored Procedures):
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
CREATE TABLE dbo.@.NyA
(
Table's Columns
)
END
But SQL Server says that there is an error "near" @.NyA in the line with the
CREATE TABLE.
Any thoughts?
Thanks"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> I am someone new to SQL Server in general. I am using the Trial Version of
> SQL Server 2005. I want to create a stored procedure to create a table.
> The
Why? There is probably a better way to do what you're trying to do.
Can you let us know what your requirements are?
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--|||In your exmaple you would create a table with the name @.Nya which is
not possible beacause @. is not allowed as part of an identifier. You
have to create a string which you can execute:
See below for an example
CREATE PROCEDURE CrearTabla
(
@.NyA nvarchar(100)
)
AS
BEGIN
DECLARE @.cmd as varchar (255)
SET @.cmd = 'CREATE TABLE dbo.'+ @.NyA + '
(
Table's Columns
) '
Exec @.cmd
END|||Well, I have an application that erases all the data in certain columns of a
table when the users decides. I have alredy done this. My problem is that I
wish to backup the table (the whole table, it is small, so size isnt a
problem) before the app deletes that data. I was thinking of using stored
procedures to do this, but if there is another (hopefully more efficient) way
to do this, I would be extremely happy to hear it...
Thanks a lot
"Adam Machanic" wrote:
> "Cargula" <Cargula@.discussions.microsoft.com> wrote in message
> news:99F1F504-8C88-47BD-A0AA-A593DBA2B370@.microsoft.com...
> >
> > I am someone new to SQL Server in general. I am using the Trial Version of
> > SQL Server 2005. I want to create a stored procedure to create a table.
> > The
> Why? There is probably a better way to do what you're trying to do.
> Can you let us know what your requirements are?
>
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
>|||"Cargula" <Cargula@.discussions.microsoft.com> wrote in message
news:2B8CE28B-3D8B-4214-B482-949CF7448CAC@.microsoft.com...
> Well, I have an application that erases all the data in certain columns of
> a
> table when the users decides. I have alredy done this. My problem is that
> I
> wish to backup the table (the whole table, it is small, so size isnt a
> problem) before the app deletes that data. I was thinking of using stored
> procedures to do this, but if there is another (hopefully more efficient)
> way
> to do this, I would be extremely happy to hear it...
The usual way this is handled is by creating an after trigger, for
delete, that inserts all of the rows from the 'deleted' table into a backup
table that's already been created -- this way, you'll keep a running log of
what's been deleted, in a single place. Much more manageable than creating
a new table every single time.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--sql

Create a string of records from a table in a stored procedure,

I have a table tblCustomers in a one-to-many relationship with table
tblProducts.
What I want to do is to create a stored procudure that returns a list
of each customer in tblCustomers but also creates a field showing a
string (separated by commas)of each matching record in tblProducts.

So the return would look like:
CustID Customer ProductList
1 Smith Apples, Oranges, Pears
2 Jones Pencils, Pens, Paper
etc...

Instead of:

CustID Customer Product
1 Smith Apples
1 Smith Oranges
1 Smith Pears
2 Jones Pencils
2 Jones Pens
2 Jones Paper

Which is what you get with this:

SELECT tblCusomers.CustID, tblCusomers.Customer,
tblProducts.Product
FROM
tblCusomers INNER JOIN
tblProducts ON
tblCustomers.CustID = tblProducts.CustID

I'd appreciate any help!
lq"Lauren Quantrell" <laurenquantrell@.hotmail.com> wrote in message
news:47e5bd72.0401190748.491c6219@.posting.google.c om...
> I have a table tblCustomers in a one-to-many relationship with table
> tblProducts.
> What I want to do is to create a stored procudure that returns a list
> of each customer in tblCustomers but also creates a field showing a
> string (separated by commas)of each matching record in tblProducts.
> So the return would look like:
> CustID Customer ProductList
> 1 Smith Apples, Oranges, Pears
> 2 Jones Pencils, Pens, Paper
> etc...
> Instead of:
> CustID Customer Product
> 1 Smith Apples
> 1 Smith Oranges
> 1 Smith Pears
> 2 Jones Pencils
> 2 Jones Pens
> 2 Jones Paper
> Which is what you get with this:
> SELECT tblCusomers.CustID, tblCusomers.Customer,
> tblProducts.Product
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.CustID = tblProducts.CustID
> I'd appreciate any help!
> lq

Generally the best way to do this would be in a front end application, where
it's easier to handle string manipulation. But this thread may be useful if
you have no other choice than to do it in MSSQL:

http://tinyurl.com/bib2

Simon|||laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> I have a table tblCustomers in a one-to-many relationship with table
> tblProducts.
> What I want to do is to create a stored procudure that returns a list
> of each customer in tblCustomers but also creates a field showing a
> string (separated by commas)of each matching record in tblProducts.
> So the return would look like:
> CustID Customer ProductList
> 1 Smith Apples, Oranges, Pears
> 2 Jones Pencils, Pens, Paper
> etc...
> Instead of:
> CustID Customer Product
> 1 Smith Apples
> 1 Smith Oranges
> 1 Smith Pears
> 2 Jones Pencils
> 2 Jones Pens
> 2 Jones Paper
> Which is what you get with this:
> SELECT tblCusomers.CustID, tblCusomers.Customer,
> tblProducts.Product
> FROM
> tblCusomers INNER JOIN
> tblProducts ON
> tblCustomers.CustID = tblProducts.CustID
> I'd appreciate any help!
> lq

You can try the following code in same sequence to get the string of
concatinated records
/*Temp table */
drop table tb_view
Create table dbo.tb_View
(
CustID int,
Customer varchar(20),
Product varchar(20)
)

INSERT INTO tb_View values (1,'Smith','Apples')
INSERT INTO tb_View values (1,'Smith','Oranges')
INSERT INTO tb_View values (1,'Smith','Pears')

INSERT INTO tb_View values (2,'Jones','Pencils')
INSERT INTO tb_View values (2,'Jones','Pens')
INSERT INTO tb_View values (2,'Jones','Paper')

/*Create a function to do the job*/
Create function dbo.fn_concatinate(@.CustId as int) returns
varchar(100)
as
begin
declare @.ret_value varchar(100)
SET @.ret_value=''
Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
CustID=@.CustId
RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
end

/*Use function in query */
select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
by CustID,Customer|||Amit Gupta (amiiit@.hotmail.com) writes:
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end

Not that this function relies on undefined behaviour. It may return
the expected result, or it may return something else. See
http://support.microsoft.com/default.aspx?scid=287515.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Amit,
Thank you for your examples. I realize there is still a lot to learn
for with the SQL. I have never used "Create function" and don't know
where it goes. Sorry for the ignorance...
lq

amiiit@.hotmail.com (Amit Gupta) wrote in message news:<6e4179ce.0401200003.67a1735e@.posting.google.com>...
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> > I have a table tblCustomers in a one-to-many relationship with table
> > tblProducts.
> > What I want to do is to create a stored procudure that returns a list
> > of each customer in tblCustomers but also creates a field showing a
> > string (separated by commas)of each matching record in tblProducts.
> > So the return would look like:
> > CustID Customer ProductList
> > 1 Smith Apples, Oranges, Pears
> > 2 Jones Pencils, Pens, Paper
> > etc...
> > Instead of:
> > CustID Customer Product
> > 1 Smith Apples
> > 1 Smith Oranges
> > 1 Smith Pears
> > 2 Jones Pencils
> > 2 Jones Pens
> > 2 Jones Paper
> > Which is what you get with this:
> > SELECT tblCusomers.CustID, tblCusomers.Customer,
> > tblProducts.Product
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.CustID = tblProducts.CustID
> > I'd appreciate any help!
> > lq
>
> You can try the following code in same sequence to get the string of
> concatinated records
> /*Temp table */
> drop table tb_view
> Create table dbo.tb_View
> (
> CustID int,
> Customer varchar(20),
> Product varchar(20)
> )
> INSERT INTO tb_View values (1,'Smith','Apples')
> INSERT INTO tb_View values (1,'Smith','Oranges')
> INSERT INTO tb_View values (1,'Smith','Pears')
> INSERT INTO tb_View values (2,'Jones','Pencils')
> INSERT INTO tb_View values (2,'Jones','Pens')
> INSERT INTO tb_View values (2,'Jones','Paper')
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end
> /*Use function in query */
> select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
> by CustID,Customer|||Specifically...
When I try to cretae a stored procedure containing this Create
Function, I get the error:
"You cannot chnage the object type in a script."
lq

amiiit@.hotmail.com (Amit Gupta) wrote in message news:<6e4179ce.0401200003.67a1735e@.posting.google.com>...
> laurenquantrell@.hotmail.com (Lauren Quantrell) wrote in message news:<47e5bd72.0401190748.491c6219@.posting.google.com>...
> > I have a table tblCustomers in a one-to-many relationship with table
> > tblProducts.
> > What I want to do is to create a stored procudure that returns a list
> > of each customer in tblCustomers but also creates a field showing a
> > string (separated by commas)of each matching record in tblProducts.
> > So the return would look like:
> > CustID Customer ProductList
> > 1 Smith Apples, Oranges, Pears
> > 2 Jones Pencils, Pens, Paper
> > etc...
> > Instead of:
> > CustID Customer Product
> > 1 Smith Apples
> > 1 Smith Oranges
> > 1 Smith Pears
> > 2 Jones Pencils
> > 2 Jones Pens
> > 2 Jones Paper
> > Which is what you get with this:
> > SELECT tblCusomers.CustID, tblCusomers.Customer,
> > tblProducts.Product
> > FROM
> > tblCusomers INNER JOIN
> > tblProducts ON
> > tblCustomers.CustID = tblProducts.CustID
> > I'd appreciate any help!
> > lq
>
> You can try the following code in same sequence to get the string of
> concatinated records
> /*Temp table */
> drop table tb_view
> Create table dbo.tb_View
> (
> CustID int,
> Customer varchar(20),
> Product varchar(20)
> )
> INSERT INTO tb_View values (1,'Smith','Apples')
> INSERT INTO tb_View values (1,'Smith','Oranges')
> INSERT INTO tb_View values (1,'Smith','Pears')
> INSERT INTO tb_View values (2,'Jones','Pencils')
> INSERT INTO tb_View values (2,'Jones','Pens')
> INSERT INTO tb_View values (2,'Jones','Paper')
> /*Create a function to do the job*/
> Create function dbo.fn_concatinate(@.CustId as int) returns
> varchar(100)
> as
> begin
> declare @.ret_value varchar(100)
> SET @.ret_value=''
> Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> CustID=@.CustId
> RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> end
> /*Use function in query */
> select CustID,Customer,dbo.fn_concatinate(CustID) from tb_View group
> by CustID,Customer|||In the DAH! Department...
I realize I have to do the Create Function in Enterprise Manager. I've
been using Microsoft Access MSDE as the front end development tool...
lq

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns947725BA51CYazorman@.127.0.0.1>...
> Amit Gupta (amiiit@.hotmail.com) writes:
> > /*Create a function to do the job*/
> > Create function dbo.fn_concatinate(@.CustId as int) returns
> > varchar(100)
> > as
> > begin
> > declare @.ret_value varchar(100)
> > SET @.ret_value=''
> > Select @.ret_value=@.ret_value + ',' + Product FROM dbo.tb_View where
> > CustID=@.CustId
> > RETURN RIGHT(@.ret_value,LEN(@.ret_value)-1)
> > end
> Not that this function relies on undefined behaviour. It may return
> the expected result, or it may return something else. See
> http://support.microsoft.com/default.aspx?scid=287515.

Create a stored procedure in schema permision

I want to let my developers to create/alter stored procedure only under a
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)
The best way is to create a custum role with the permissions you need: no
built-in database role has such a permission design.
So, for example:
USE MyDatabase
CREATE ROLE MyNewRole
... add members to this new role:
EXEC sp_addrolemember 'MyNewRole', 'User1'
EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
then assign permissions:
GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
TO MyNewRole
...etc.
You'll find detailed explanations in BOL at the topics dedicated to the
different statements i've showed here
Gilberto Zampatti
"??" wrote:

> I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which role
> let the login make ddl changes only in on schema (not in the dbo schema)
|||Sorry. I don't know how to do that.
I need to let my user no ddl changes in dbo schema, only creaete(new)/update
permision in MySchema schema.
Can I?
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> The best way is to create a custum role with the permissions you need: no
> built-in database role has such a permission design.
> So, for example:
> USE MyDatabase
> CREATE ROLE MyNewRole
> ... add members to this new role:
> EXEC sp_addrolemember 'MyNewRole', 'User1'
> EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> then assign permissions:
> GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> TO MyNewRole
> ...etc.
> You'll find detailed explanations in BOL at the topics dedicated to the
> different statements i've showed here
> Gilberto Zampatti
> "??" wrote:
|||No users should executes the DDL changes: just a DBA (may be you?) should
create the Schema and assign the permissions.
To do that you can implement a script, or use Query analyzer in SQL 2000 OR
SQL Server management Studio in SQL 2005.
Gilberto Zampatti
"Gal" wrote:
[vbcol=seagreen]
> Sorry. I don't know how to do that.
>
> I need to let my user no ddl changes in dbo schema, only creaete(new)/update
> permision in MySchema schema.
> Can I?
> "Gilberto Zampatti" wrote:

Create a stored procedure in schema permision

I want to let my developers to create/alter stored procedure only under a
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)The best way is to create a custum role with the permissions you need: no
built-in database role has such a permission design.
So, for example:
USE MyDatabase
CREATE ROLE MyNewRole
... add members to this new role:
EXEC sp_addrolemember 'MyNewRole', 'User1'
EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
then assign permissions:
GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
TO MyNewRole
...etc.
You'll find detailed explanations in BOL at the topics dedicated to the
different statements i've showed here
Gilberto Zampatti
"×?×?" wrote:
> I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which role
> let the login make ddl changes only in on schema (not in the dbo schema)|||Sorry. I don't know how to do that.
I need to let my user no ddl changes in dbo schema, only creaete(new)/update
permision in MySchema schema.
Can I?
"Gilberto Zampatti" wrote:
> The best way is to create a custum role with the permissions you need: no
> built-in database role has such a permission design.
> So, for example:
> USE MyDatabase
> CREATE ROLE MyNewRole
> ... add members to this new role:
> EXEC sp_addrolemember 'MyNewRole', 'User1'
> EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> then assign permissions:
> GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> TO MyNewRole
> ...etc.
> You'll find detailed explanations in BOL at the topics dedicated to the
> different statements i've showed here
> Gilberto Zampatti
> "×?×?" wrote:
> > I want to let my developers to create/alter stored procedure only under a
> > specific schema. which role let the login create a procedure , or Which role
> > let the login make ddl changes only in on schema (not in the dbo schema)|||No users should executes the DDL changes: just a DBA (may be you?) should
create the Schema and assign the permissions.
To do that you can implement a script, or use Query analyzer in SQL 2000 OR
SQL Server management Studio in SQL 2005.
Gilberto Zampatti
"Gal" wrote:
> Sorry. I don't know how to do that.
>
> I need to let my user no ddl changes in dbo schema, only creaete(new)/update
> permision in MySchema schema.
> Can I?
> "Gilberto Zampatti" wrote:
> > The best way is to create a custum role with the permissions you need: no
> > built-in database role has such a permission design.
> > So, for example:
> >
> > USE MyDatabase
> > CREATE ROLE MyNewRole
> >
> > ... add members to this new role:
> >
> > EXEC sp_addrolemember 'MyNewRole', 'User1'
> > EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> >
> > then assign permissions:
> >
> > GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> > TO MyNewRole
> > ...etc.
> >
> > You'll find detailed explanations in BOL at the topics dedicated to the
> > different statements i've showed here
> > Gilberto Zampatti
> > "×?×?" wrote:
> >
> > > I want to let my developers to create/alter stored procedure only under a
> > > specific schema. which role let the login create a procedure , or Which role
> > > let the login make ddl changes only in on schema (not in the dbo schema)

Create a stored procedure in schema permision

I want to let my developers to create/alter stored procedure only under a
specific schema. which role let the login create a procedure , or Which role
let the login make ddl changes only in on schema (not in the dbo schema)The best way is to create a custum role with the permissions you need: no
built-in database role has such a permission design.
So, for example:
USE MyDatabase
CREATE ROLE MyNewRole
... add members to this new role:
EXEC sp_addrolemember 'MyNewRole', 'User1'
EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
then assign permissions:
GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
TO MyNewRole
...etc.
You'll find detailed explanations in BOL at the topics dedicated to the
different statements i've showed here
Gilberto Zampatti
"??" wrote:

> I want to let my developers to create/alter stored procedure only under a
> specific schema. which role let the login create a procedure , or Which ro
le
> let the login make ddl changes only in on schema (not in the dbo schema)|||Sorry. I don't know how to do that.
I need to let my user no ddl changes in dbo schema, only creaete(new)/update
permision in MySchema schema.
Can I?
"Gilberto Zampatti" wrote:
[vbcol=seagreen]
> The best way is to create a custum role with the permissions you need: no
> built-in database role has such a permission design.
> So, for example:
> USE MyDatabase
> CREATE ROLE MyNewRole
> ... add members to this new role:
> EXEC sp_addrolemember 'MyNewRole', 'User1'
> EXEC sp_addrolemember @.role = 'MyNewRole', @.membername = 'User2'
> then assign permissions:
> GRANT permiss1, permiss2, ..etc ON SCHEMA::MySchema
> TO MyNewRole
> ...etc.
> You'll find detailed explanations in BOL at the topics dedicated to the
> different statements i've showed here
> Gilberto Zampatti
> "??" wrote:
>|||No users should executes the DDL changes: just a DBA (may be you?) should
create the Schema and assign the permissions.
To do that you can implement a script, or use Query analyzer in SQL 2000 OR
SQL Server management Studio in SQL 2005.
Gilberto Zampatti
"Gal" wrote:
[vbcol=seagreen]
> Sorry. I don't know how to do that.
>
> I need to let my user no ddl changes in dbo schema, only creaete(new)/upda
te
> permision in MySchema schema.
> Can I?
> "Gilberto Zampatti" wrote:
>sql

Create a SP that will launch a DTS package

Hi all. How can I create a Stored Procedure that will launch a DTS package that I have already built dynamically ?

I have a string already built and I need to put it in a SP because my application is php (linux) and the database is SQL Server on a Windows machine.

I will use php to execute the stored proc, which is the only way to access it.

My string looks like this :
C:\Progra~1\Micros~3\80\Tools\Binn\ISQL.EXE -S [MyServer] -U [Username] -P [Password] -Q "ISQL_Batch 'D:\DDFIImporte\IMPICAFI.bat [user] [Schema] [Pwd] '" -n -d [database]

(Words in [] are only to show that I will put other values)

Thanks

CFGillesxp_cmdshell but it will have to execute with sysadmin permissions.|||Hi,

Can you be more specific ?? I'm a near-newby in SP building... Maybe some pseudo-code, some links or examples ?

Thanks

CFGillessql

create a procedure

Hello everyone, i need some help(((
i am creating a procedure as this :
CREATE procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
close od;
deallocate od;
everything is ok, the command completed successfuly.
1-but when i run the procedure by doing :
exec automated_mark
the procedure run, but there is no update.
2- when i open the microsoftsql management studio, and i do right-
click on the percedure,, execute Stored procedure : then the procedure
run, and tha table is updated. BUT when i alter my procedure, and i
change the "update products set Instock= '10000' " with the "update
products set Instock= '2222' ", and i do right-click on the
percedure,, execute Stored procedure THEN the procedure run, BUT NO
UPDATE.
why and how to solve the problem?
thank for your help.
@.@.fetch_status is a global value for each connection. So you cannot depend
on @.@.fetch_status being zero when the stored proc is executed. If you have
previously looped thru a cursor with this connection, doing a
while(@.@.fetch_status=0), then when it left that loop, it was non zero, so it
is still non zero and you loop will not be executed at all. Instead of
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
you want to code the cursor loop like:
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
update products set Instock= '10000' where prodid = @.nom1;
fetch next from od into @.nom1,@.nom2
end
Then you are not depending on @.@.fetch_status being zero before your first
fetch.
The other problem with this stored proc is that it uses a cursor. Cursor's
are almost always an ineffecient way to process data in SQL. It is almost
always much better and faster to use a set based solution. In your case, a
better stored proc would look like:
CREATE procedure [dbo].[automated_mark]
with recompile
as
update products
set Instock='10000'
where prodid in (select ProdID from Orders where Orders.Qty > 20);
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:13ae24ed-37ea-4dbb-91f5-d278bb4f6b77@.d62g2000hsf.googlegroups.com...
> Hello everyone, i need some help(((
> i am creating a procedure as this :
> --
> CREATE procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> while (@.@.fetch_status=0)
> begin
> fetch next from od into @.nom1,@.nom2
> update products set Instock= '10000' where prodid = @.nom1;
> end
> close od;
> deallocate od;
> --
> everything is ok, the command completed successfuly.
> 1-but when i run the procedure by doing :
> --
> exec automated_mark
> --
> the procedure run, but there is no update.
> 2- when i open the microsoftsql management studio, and i do right-
> click on the percedure,, execute Stored procedure : then the procedure
> run, and tha table is updated. BUT when i alter my procedure, and i
> change the "update products set Instock= '10000' " with the "update
> products set Instock= '2222' ", and i do right-click on the
> percedure,, execute Stored procedure THEN the procedure run, BUT NO
> UPDATE.
> why and how to solve the problem?
> thank for your help.
|||thank you very much Tom.
its now working.thanks.
Now, about the set based solution, i agree with you when i have an
update to do.
But, what if instead an update i have an insert to do, like this :
ALTER procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
fetch next from od into @.nom1,@.nom2
end
close od;
deallocate od;
is there a better way to do that?
thanks again.
|||On Feb 29, 4:14Xam, noumian <n.nou...@.gmail.com> wrote:
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> X X X X for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> X X X X fetch next from od into @.nom1,@.nom2
> X X X X while (@.@.fetch_status=0)
> X X X X X X X X begin
> X X X X X X X X X X X X insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> X X X X X X X X X X X X fetch next from od into @.nom1,@.nom2
> X X X X X X X X end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
What about:
INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
dbo.Orders WHERE Orders.Quantity > 20
|||insert into sales (prodid,qty,action)
select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> fetch next from od into @.nom1,@.nom2
> while (@.@.fetch_status=0)
> begin
> insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> fetch next from od into @.nom1,@.nom2
> end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
|||On Feb 29, 4:39 pm, Sean <ColdFusion...@.gmail.com> wrote:
> On Feb 29, 4:14 am, noumian <n.nou...@.gmail.com> wrote:
>
>
>
> What about:
> INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
> dbo.Orders WHERE Orders.Quantity > 20
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?
|||On Feb 29, 5:56 pm, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.net> wrote:[vbcol=seagreen]
> insert into sales (prodid,qty,action)
> select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
> Tom
> "noumian" <n.nou...@.gmail.com> wrote in message
> news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
>
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?
|||On Feb 29, 10:22Xam, noumian <n.nou...@.gmail.com> wrote:
> On Feb 29, 5:56 pm, "Tom Cooper"
>
>
> <tomcoo...@.comcast.no.spam.please.net> wrote:
>
>
>
> CHECK MY insert: X insert into sales (prodid,qty,action) values
> (@.nom1,@.nom2,'sold');
> what about the values of my column action... Hide quoted text -
> - Show quoted text -
Argh, sorry it's friday... a very long... friday.
INSERT INTO Sales(prodid, qty, action) SELECT prodid, qty, 'Sold' as
Action FROM Orders WHERE qty > 20
That should do the trick.
|||ok, thanks very much.

create a procedure

Hello everyone, i need some help(((
i am creating a procedure as this :
--
CREATE procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
close od;
deallocate od;
--
everything is ok, the command completed successfuly.
1-but when i run the procedure by doing :
--
exec automated_mark
--
the procedure run, but there is no update.
2- when i open the microsoftsql management studio, and i do right-
click on the percedure,, execute Stored procedure : then the procedure
run, and tha table is updated. BUT when i alter my procedure, and i
change the "update products set Instock= '10000' " with the "update
products set Instock= '2222' ", and i do right-click on the
percedure,, execute Stored procedure THEN the procedure run, BUT NO
UPDATE.
why and how to solve the problem?
thank for your help.@.@.fetch_status is a global value for each connection. So you cannot depend
on @.@.fetch_status being zero when the stored proc is executed. If you have
previously looped thru a cursor with this connection, doing a
while(@.@.fetch_status=0), then when it left that loop, it was non zero, so it
is still non zero and you loop will not be executed at all. Instead of
open od
while (@.@.fetch_status=0)
begin
fetch next from od into @.nom1,@.nom2
update products set Instock= '10000' where prodid = @.nom1;
end
you want to code the cursor loop like:
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
update products set Instock= '10000' where prodid = @.nom1;
fetch next from od into @.nom1,@.nom2
end
Then you are not depending on @.@.fetch_status being zero before your first
fetch.
The other problem with this stored proc is that it uses a cursor. Cursor's
are almost always an ineffecient way to process data in SQL. It is almost
always much better and faster to use a set based solution. In your case, a
better stored proc would look like:
CREATE procedure [dbo].[automated_mark]
with recompile
as
update products
set Instock='10000'
where prodid in (select ProdID from Orders where Orders.Qty > 20);
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:13ae24ed-37ea-4dbb-91f5-d278bb4f6b77@.d62g2000hsf.googlegroups.com...
> Hello everyone, i need some help(((
> i am creating a procedure as this :
> --
> CREATE procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> while (@.@.fetch_status=0)
> begin
> fetch next from od into @.nom1,@.nom2
> update products set Instock= '10000' where prodid = @.nom1;
> end
> close od;
> deallocate od;
> --
> everything is ok, the command completed successfuly.
> 1-but when i run the procedure by doing :
> --
> exec automated_mark
> --
> the procedure run, but there is no update.
> 2- when i open the microsoftsql management studio, and i do right-
> click on the percedure,, execute Stored procedure : then the procedure
> run, and tha table is updated. BUT when i alter my procedure, and i
> change the "update products set Instock= '10000' " with the "update
> products set Instock= '2222' ", and i do right-click on the
> percedure,, execute Stored procedure THEN the procedure run, BUT NO
> UPDATE.
> why and how to solve the problem?
> thank for your help.|||thank you very much Tom.
its now working.thanks.
Now, about the set based solution, i agree with you when i have an
update to do.
But, what if instead an update i have an insert to do, like this :
--
ALTER procedure [dbo].[automated_mark]
with recompile
as
declare @.nom1 nvarchar(50)
declare @.nom2 nvarchar(50)
declare od cursor
for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
open od
fetch next from od into @.nom1,@.nom2
while (@.@.fetch_status=0)
begin
insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
fetch next from od into @.nom1,@.nom2
end
close od;
deallocate od;
--
is there a better way to do that?
thanks again.|||On Feb 29, 4:14=A0am, noumian <n.nou...@.gmail.com> wrote:
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> =A0 =A0 =A0 =A0 for select Orders.ProdID,Orders.Qty from Orders where Orde=rs.Qty > 20
> open od
> =A0 =A0 =A0 =A0 fetch next from od into @.nom1,@.nom2
> =A0 =A0 =A0 =A0 while (@.@.fetch_status=3D0)
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 begin
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 insert into sales (prodid,=qty,action) values (@.nom1,@.nom2,'sold');
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 fetch next from od into @.n=om1,@.nom2
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.
What about:
INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
dbo.Orders WHERE Orders.Quantity > 20|||insert into sales (prodid,qty,action)
select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
Tom
"noumian" <n.noumia@.gmail.com> wrote in message
news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> thank you very much Tom.
> its now working.thanks.
> Now, about the set based solution, i agree with you when i have an
> update to do.
> But, what if instead an update i have an insert to do, like this :
> --
> ALTER procedure [dbo].[automated_mark]
> with recompile
> as
> declare @.nom1 nvarchar(50)
> declare @.nom2 nvarchar(50)
> declare od cursor
> for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> open od
> fetch next from od into @.nom1,@.nom2
> while (@.@.fetch_status=0)
> begin
> insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> fetch next from od into @.nom1,@.nom2
> end
> close od;
> deallocate od;
> --
> is there a better way to do that?
> thanks again.|||On Feb 29, 4:39 pm, Sean <ColdFusion...@.gmail.com> wrote:
> On Feb 29, 4:14 am, noumian <n.nou...@.gmail.com> wrote:
>
> > thank you very much Tom.
> > its now working.thanks.
> > Now, about the set based solution, i agree with you when i have an
> > update to do.
> > But, what if instead an update i have an insert to do, like this :
> > --
> > ALTER procedure [dbo].[automated_mark]
> > with recompile
> > as
> > declare @.nom1 nvarchar(50)
> > declare @.nom2 nvarchar(50)
> > declare od cursor
> > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > open od
> > fetch next from od into @.nom1,@.nom2
> > while (@.@.fetch_status=0)
> > begin
> > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > fetch next from od into @.nom1,@.nom2
> > end
> > close od;
> > deallocate od;
> > --
> > is there a better way to do that?
> > thanks again.
> What about:
> INSERT dbo.Sales(ProductID, Quantity) SELECT ProductID, Quantity FROM
> dbo.Orders WHERE Orders.Quantity > 20
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?|||On Feb 29, 5:56 pm, "Tom Cooper"
<tomcoo...@.comcast.no.spam.please.net> wrote:
> insert into sales (prodid,qty,action)
> select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 20;
> Tom
> "noumian" <n.nou...@.gmail.com> wrote in message
> news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...
> > thank you very much Tom.
> > its now working.thanks.
> > Now, about the set based solution, i agree with you when i have an
> > update to do.
> > But, what if instead an update i have an insert to do, like this :
> > --
> > ALTER procedure [dbo].[automated_mark]
> > with recompile
> > as
> > declare @.nom1 nvarchar(50)
> > declare @.nom2 nvarchar(50)
> > declare od cursor
> > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > open od
> > fetch next from od into @.nom1,@.nom2
> > while (@.@.fetch_status=0)
> > begin
> > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > fetch next from od into @.nom1,@.nom2
> > end
> > close od;
> > deallocate od;
> > --
> > is there a better way to do that?
> > thanks again.
CHECK MY insert: insert into sales (prodid,qty,action) values
(@.nom1,@.nom2,'sold');
what about the values of my column action?|||On Feb 29, 10:22=A0am, noumian <n.nou...@.gmail.com> wrote:
> On Feb 29, 5:56 pm, "Tom Cooper"
>
>
> <tomcoo...@.comcast.no.spam.please.net> wrote:
> > insert into sales (prodid,qty,action)
> > select Orders.ProdID,Orders.Qty, 'sold' from Orders where Orders.Qty > 2=0;
> > Tom
> > "noumian" <n.nou...@.gmail.com> wrote in message
> >news:7c48ae96-95b4-4497-818d-2450591dd2d6@.n77g2000hse.googlegroups.com...=
> > > thank you very much Tom.
> > > its now working.thanks.
> > > Now, about the set based solution, i agree with you when i have an
> > > update to do.
> > > But, what if instead an update i have an insert to do, like this :
> > > --
> > > ALTER procedure [dbo].[automated_mark]
> > > with recompile
> > > as
> > > declare @.nom1 nvarchar(50)
> > > declare @.nom2 nvarchar(50)
> > > declare od cursor
> > > for select Orders.ProdID,Orders.Qty from Orders where Orders.Qty > 20
> > > open od
> > > fetch next from od into @.nom1,@.nom2
> > > while (@.@.fetch_status=3D0)
> > > begin
> > > insert into sales (prodid,qty,action) values (@.nom1,@.nom2,'sold');
> > > fetch next from od into @.nom1,@.nom2
> > > end
> > > close od;
> > > deallocate od;
> > > --
> > > is there a better way to do that?
> > > thanks again.
> CHECK MY insert: =A0 insert into sales (prodid,qty,action) values
> (@.nom1,@.nom2,'sold');
> what about the values of my column action... Hide quoted text -
> - Show quoted text -
Argh, sorry it's friday... a very long... friday.
INSERT INTO Sales(prodid, qty, action) SELECT prodid, qty, 'Sold' as
Action FROM Orders WHERE qty > 20
That should do the trick.|||ok, thanks very much.