Create and Administer SQL Server Agent Jobs via Sprocs?

Hi,

Does anyone know if it is possible to set up stored procedures which will create, modify, and disable SQL Server Agent Jobs?

I have a web based application which I need to enable an administrator to change the frequency which a job runs and encapsulating the modifications in a stored procedure would be the easiest way for me to integrate this, if it's possbible.

Regards, MattHi,
You may use sp_update_jobschedule, sp_update_job etc or if you create your own sp and use tables such as sysjobschedules, sysjobs etc.
Tables and procs are located in MSDB.
Ex for updating scheduled time:
UPDATE msdb.dbo.sysjobschedules
SET active_start_time = 164000
WHERE (job_id = '8A0F1080-D22A-4F82-AE13-68F789989D1D')
AND (name = 'Once')

Try this and let us know how it work

Regards|||Thanks Tommy,

Going to give this a shot this afternoon. I'll let you know how it turns out.

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 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 txt file and rename the file from a SP

I can populate a txt file using DTS.
How do i Rename the file using data from the table using
DTS or a stored procedure?You could rename the file using DTS :-
http://www.sqldts.com/default.aspx?292
You could also use the xp_cmdshell stored proc
--
HTH
Ryan Waight, MCDBA, MCSE
"Brian" <anonymous@.discussions.microsoft.com> wrote in message
news:092701c3a51d$bc93bfe0$a401280a@.phx.gbl...
> I can populate a txt file using DTS.
> How do i Rename the file using data from the table using
> DTS or a stored procedure?

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 variable from an existing table's schema

Hello.
What's the best way to copy a table's schema and create a "table" data type
inside of a stored procedure?
Thanks in advance,
MikeI think your only choice will be dynamic sql, and you will not have the
chance to access this variable from the original procedure. What are you
trying to do?. Can you use a temporary table created with SELECT ... INTO ..
.
FROM?
select col1, ..., coln
into #t
from table1
where 0 = 1
...
AMB
"MikeL" wrote:

> Hello.
> What's the best way to copy a table's schema and create a "table" data typ
e
> inside of a stored procedure?
> Thanks in advance,
> Mike
>
>|||> What's the best way to copy a table's schema and create a "table" data
type
> inside of a stored procedure?
Well, if you have to rely on dynamic determination of the data model, then
in my opinion: use a temp table. You can't use SELECT INTO for @.table
variables. Some other differences are listed here:
http://www.aspfaq.com/2475
There is no substitute for understanding your data model and not having to
generate this kind of thing on the fly. Why do you need to copy the
structure at all?
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.

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 function

hi, I try to do insert a stored procedure's result into a temp table,
then I try to put it inside the function, but it doesn't work.
The reason I want to do this is because we have 200 stored procedures which
need to reuse the same code,
so by putting these logics into a function, the code would be cleaner..
How can I get around this?
CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
@.LId INT)
RETURNS TABLE
AS
CREATE TABLE #tempList
(
UId INT,
CompanyId NVARCHAR(10),
LId INT,
CompanyName NVARCHAR(50)
.....
)
INSERT INTO #tempList
Exec GetListInfo @.UId, @.CompanyId ,@.LId
RETURN (SELECT * FROM #tempList)You can't execute a stored proc within a function -- you'll have to do this
within another stored proc.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uxpTfkAIFHA.2936@.TK2MSFTNGP15.phx.gbl...
> hi, I try to do insert a stored procedure's result into a temp table,
> then I try to put it inside the function, but it doesn't work.
> The reason I want to do this is because we have 200 stored procedures
which
> need to reuse the same code,
> so by putting these logics into a function, the code would be cleaner..
> How can I get around this?
>
> CREATE FUNCTION dbo.GetPortfolioList (@.UId INT, @.CompanyId NVARCHAR(10),
> @.LId INT)
> RETURNS TABLE
> AS
> CREATE TABLE #tempList
> (
> UId INT,
> CompanyId NVARCHAR(10),
> LId INT,
> CompanyName NVARCHAR(50)
> .....
> )
> INSERT INTO #tempList
> Exec GetListInfo @.UId, @.CompanyId ,@.LId
>
> RETURN (SELECT * FROM #tempList)
>|||You can't call an stored procedure from within a function. Put the SP
logic into the body of the function instead if you can.
David Portas
SQL Server MVP
--|||you can't put temp table in the function also.
correct me if I'm wrong.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:%23bBHPtAIFHA.1948@.TK2MSFTNGP14.phx.gbl...
> you can't put temp table in the function also.
> correct me if I'm wrong.
You can use a table variable in a function -- logically the same thing.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||That's correct. However you can use table variables in a function.
Table variables provide a lot of the same functionality as temp tables
but you can't insert the result of an SP into a table variable even if
it was possible to execute the SP.
David Portas
SQL Server MVP
--|||Hi David,
Well, I know that will work, but if I make a change in the logics in
the future,
then I will have to change all 200 procedures. I try to avoid this problem.
If function works, then all I need to do is to change the logic in the
function. Too bad there is no way to get around it.
=(
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
> You can't call an stored procedure from within a function. Put the SP
> logic into the body of the function instead if you can.
> --
> David Portas
> SQL Server MVP
> --
>|||Oh never mind.. ignore the previous question,
I misunderstood it. you're right, put the sp logic into function will work.
"Britney" <britneychen_2001@.yahoo.com> wrote in message
news:uOGLGtBIFHA.572@.tk2msftngp13.phx.gbl...
> Hi David,
> Well, I know that will work, but if I make a change in the logics
in
> the future,
> then I will have to change all 200 procedures. I try to avoid this
problem.
> If function works, then all I need to do is to change the logic in the
> function. Too bad there is no way to get around it.
> =(
>
> "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
> news:1109865843.035934.164930@.g14g2000cwa.googlegroups.com...
>

CREATE / EXECUTE Stored Procedure (SQL Server 7.0)

Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>