Hi,
Anyone know why the view/ store procedure does not replicate from ServerA to
ServerB?
Thanks,
Kenny
They will be in place if you deploy your subscribers from a backup. If you
create the subscription manually you will need to create them on each node
as well.
Note that if you create a new view or stored procedure they will not be
replicated to each node.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Kenny" <Kenny@.discussions.microsoft.com> wrote in message
news:5CA78A4E-78B4-488C-BF83-2D4B88A1FCA9@.microsoft.com...
> Hi,
> Anyone know why the view/ store procedure does not replicate from ServerA
> to
> ServerB?
> Thanks,
> Kenny
Showing posts with label procedure. Show all posts
Showing posts with label procedure. Show all posts
Friday, March 30, 2012
Pease help - Feild value is truncated
I use a stored procedure to populate a table and it truncates the value to
one character for the field eMail.
you have an idea what is the problem?
i have to say also that I have tried this and it works:
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
So it looks like there is a problem at the @.eMailPlus variable level.
TABLE IS:
Id int
eMail varchar(MAX)
STORED PROCEDURE IS:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
DECLARE @.IdMaxPlus AS INT
DECLARE @.eMailPlus AS VARCHAR
BEGIN TRANSACTION
SET @.eMailPlus = 'Ring' VARCHAR
SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
(UPDLOCK)
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
COMMIT TRANSACTIONok fixed it
DECLARE @.eMailPlus AS VARCHAR(MAX)
"Progman" <adfawefqw@.hotmail.com> wrote in message
news:1fmKf.2089$Jb7.681075@.weber.videotron.net...
>I use a stored procedure to populate a table and it truncates the value to
>one character for the field eMail.
> you have an idea what is the problem?
> i have to say also that I have tried this and it works:
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
> So it looks like there is a problem at the @.eMailPlus variable level.
> TABLE IS:
> Id int
> eMail varchar(MAX)
> STORED PROCEDURE IS:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
> DECLARE @.IdMaxPlus AS INT
> DECLARE @.eMailPlus AS VARCHAR
> BEGIN TRANSACTION
> SET @.eMailPlus = 'Ring' VARCHAR
> SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
> (UPDLOCK)
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
> COMMIT TRANSACTION
>
one character for the field eMail.
you have an idea what is the problem?
i have to say also that I have tried this and it works:
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
So it looks like there is a problem at the @.eMailPlus variable level.
TABLE IS:
Id int
eMail varchar(MAX)
STORED PROCEDURE IS:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
DECLARE @.IdMaxPlus AS INT
DECLARE @.eMailPlus AS VARCHAR
BEGIN TRANSACTION
SET @.eMailPlus = 'Ring' VARCHAR
SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
(UPDLOCK)
INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
COMMIT TRANSACTIONok fixed it
DECLARE @.eMailPlus AS VARCHAR(MAX)
"Progman" <adfawefqw@.hotmail.com> wrote in message
news:1fmKf.2089$Jb7.681075@.weber.videotron.net...
>I use a stored procedure to populate a table and it truncates the value to
>one character for the field eMail.
> you have an idea what is the problem?
> i have to say also that I have tried this and it works:
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, `string`)
> So it looks like there is a problem at the @.eMailPlus variable level.
> TABLE IS:
> Id int
> eMail varchar(MAX)
> STORED PROCEDURE IS:
> set ANSI_NULLS ON
> set QUOTED_IDENTIFIER ON
> GO
> CREATE PROCEDURE [dbo].[sp_myInsertStoredProcedure] @.eMail varchar AS
> DECLARE @.IdMaxPlus AS INT
> DECLARE @.eMailPlus AS VARCHAR
> BEGIN TRANSACTION
> SET @.eMailPlus = 'Ring' VARCHAR
> SELECT @.IdMaxPlus = coalesce(MAX(Id), 0) + 1 FROM VH_Data_NewsEmail
> (UPDLOCK)
> INSERT VH_Data_NewsEmail (Id, eMail) VALUES (@.IdMaxPlus, @.eMailPlus)
> COMMIT TRANSACTION
>
Wednesday, March 28, 2012
PDF Report not reflecting changes after update
I've working on a report at a client and I'm having an issue with a report
not reflecting multiple updated. The basic procedure that is followed is:
1. The client adds an event to the timeline.
2. The client runs the timeline report and the new event shows up.
3. The client adds a new event to the timeline.
4. The second, and any subsequent events do not show up.
I've checked the stored procedure that the report calls and it returns the
correct data every time (reflects the events as added). I've also checked
the execution properties in the report to be sure that the report isn't set
to be cached, and it's not. I'm using SQL Server 2000 & Reporting Services,
and I'm using Visual Studio 2003 SP1.
Any help would be really appreciated.It took me awhile to find the answer when I had the same problem.
Use;
rs:ClearSession=true in the url assuming you are using the url method.
directs the report server to remove a report from session. All report
instances associated with an authenticated user are removed from
session. (A report instance is defined as the same report run multiple
times with different report parameter values.)
Neil
John Swaringen wrote:
> I've working on a report at a client and I'm having an issue with a report
> not reflecting multiple updated. The basic procedure that is followed is:
> 1. The client adds an event to the timeline.
> 2. The client runs the timeline report and the new event shows up.
> 3. The client adds a new event to the timeline.
> 4. The second, and any subsequent events do not show up.
> I've checked the stored procedure that the report calls and it returns the
> correct data every time (reflects the events as added). I've also checked
> the execution properties in the report to be sure that the report isn't set
> to be cached, and it's not. I'm using SQL Server 2000 & Reporting Services,
> and I'm using Visual Studio 2003 SP1.
> Any help would be really appreciated.|||Neil,
That worked great. It took me a while to figure out that I had to add it
via the ReportViewer.SetParameter() function. But once I did that everything
is cooking!
Thanks for the help!
"neilgould@.gmail.com" wrote:
> It took me awhile to find the answer when I had the same problem.
> Use;
> rs:ClearSession=true in the url assuming you are using the url method.
> directs the report server to remove a report from session. All report
> instances associated with an authenticated user are removed from
> session. (A report instance is defined as the same report run multiple
> times with different report parameter values.)
> Neil
>
> John Swaringen wrote:
> > I've working on a report at a client and I'm having an issue with a report
> > not reflecting multiple updated. The basic procedure that is followed is:
> >
> > 1. The client adds an event to the timeline.
> > 2. The client runs the timeline report and the new event shows up.
> > 3. The client adds a new event to the timeline.
> > 4. The second, and any subsequent events do not show up.
> >
> > I've checked the stored procedure that the report calls and it returns the
> > correct data every time (reflects the events as added). I've also checked
> > the execution properties in the report to be sure that the report isn't set
> > to be cached, and it's not. I'm using SQL Server 2000 & Reporting Services,
> > and I'm using Visual Studio 2003 SP1.
> >
> > Any help would be really appreciated.
>
not reflecting multiple updated. The basic procedure that is followed is:
1. The client adds an event to the timeline.
2. The client runs the timeline report and the new event shows up.
3. The client adds a new event to the timeline.
4. The second, and any subsequent events do not show up.
I've checked the stored procedure that the report calls and it returns the
correct data every time (reflects the events as added). I've also checked
the execution properties in the report to be sure that the report isn't set
to be cached, and it's not. I'm using SQL Server 2000 & Reporting Services,
and I'm using Visual Studio 2003 SP1.
Any help would be really appreciated.It took me awhile to find the answer when I had the same problem.
Use;
rs:ClearSession=true in the url assuming you are using the url method.
directs the report server to remove a report from session. All report
instances associated with an authenticated user are removed from
session. (A report instance is defined as the same report run multiple
times with different report parameter values.)
Neil
John Swaringen wrote:
> I've working on a report at a client and I'm having an issue with a report
> not reflecting multiple updated. The basic procedure that is followed is:
> 1. The client adds an event to the timeline.
> 2. The client runs the timeline report and the new event shows up.
> 3. The client adds a new event to the timeline.
> 4. The second, and any subsequent events do not show up.
> I've checked the stored procedure that the report calls and it returns the
> correct data every time (reflects the events as added). I've also checked
> the execution properties in the report to be sure that the report isn't set
> to be cached, and it's not. I'm using SQL Server 2000 & Reporting Services,
> and I'm using Visual Studio 2003 SP1.
> Any help would be really appreciated.|||Neil,
That worked great. It took me a while to figure out that I had to add it
via the ReportViewer.SetParameter() function. But once I did that everything
is cooking!
Thanks for the help!
"neilgould@.gmail.com" wrote:
> It took me awhile to find the answer when I had the same problem.
> Use;
> rs:ClearSession=true in the url assuming you are using the url method.
> directs the report server to remove a report from session. All report
> instances associated with an authenticated user are removed from
> session. (A report instance is defined as the same report run multiple
> times with different report parameter values.)
> Neil
>
> John Swaringen wrote:
> > I've working on a report at a client and I'm having an issue with a report
> > not reflecting multiple updated. The basic procedure that is followed is:
> >
> > 1. The client adds an event to the timeline.
> > 2. The client runs the timeline report and the new event shows up.
> > 3. The client adds a new event to the timeline.
> > 4. The second, and any subsequent events do not show up.
> >
> > I've checked the stored procedure that the report calls and it returns the
> > correct data every time (reflects the events as added). I've also checked
> > the execution properties in the report to be sure that the report isn't set
> > to be cached, and it's not. I'm using SQL Server 2000 & Reporting Services,
> > and I'm using Visual Studio 2003 SP1.
> >
> > Any help would be really appreciated.
>
Wednesday, March 21, 2012
pausing stored procedures
Is there any way to execute a stored procedure, get it's thread id and pause it in someway...particularly in another stored procedure?
If not, I guess I could manually create a mechanism. This would most likely be used for stored procedures that run for long amounts of time in a while loop where i could just check a flag in a record representing the instance of the stored procedure at the top of the loop and do a WAITFOR DELAY within a nexted loop and have the condition set to end when the flag is marked as 'run' as opposed to 'halt.'You can look at the SP. Find a query that it hasn't executed yet and lock a table that is involved in that query.
You could also dedicate a resource table to the SP, code a select or update of that table before every statement then locking that table will pause the SP.|||Does the SQL Profiler read information from a table in the master database or something? I am wondering how it knows what stored procedures are running and if I can use that information in this experiment.
If not, I guess I could manually create a mechanism. This would most likely be used for stored procedures that run for long amounts of time in a while loop where i could just check a flag in a record representing the instance of the stored procedure at the top of the loop and do a WAITFOR DELAY within a nexted loop and have the condition set to end when the flag is marked as 'run' as opposed to 'halt.'You can look at the SP. Find a query that it hasn't executed yet and lock a table that is involved in that query.
You could also dedicate a resource table to the SP, code a select or update of that table before every statement then locking that table will pause the SP.|||Does the SQL Profiler read information from a table in the master database or something? I am wondering how it knows what stored procedures are running and if I can use that information in this experiment.
Pause between pages when printing RS report
I am using Report Services on SQL Server 2005. I have a report that gets its data from a single SQL Server stored procedure. One other small table is used, but only to provide a list of values for selecting a parameter value to send to the stored procedure.
On the report I use a list control to display multiple records per page.
When printing the report (which currently produces over 500 pages - future printings of this report will not be nearly so large), the printer pauses between each page as if they are seperate print jobs. This is an HP LaserJet 5Si that normally cranks out reports very fast.
The only thing I can think of that might contribute to this sort of behavior is the fact that I set my margin sizes to 0in to get as much design room on the report as possible. Could that cause this sort of behavior?
Just bumping this topic. I'm very much in need of an answer on this. I've tried different settings for the margins as well, but anything other than 0in. causes extra pages to be printed.
How can I eliminate the pause between pages?|||How are you printing the report? Are you using the printing via the web or using delivery? I have not heard of this sort of behavior. The only thing I can think of is that the server is busy rendering each page and is causing the printer to wait for additional pages.
-Daniel|||Thanks for the reply.
I am printing the reports via the web. This behavior is exhibited when printing them from the Visual Studio design environment as well.
|||Most likely, your printer is able to print faster than the ability of the server to generate the pages. This could happen if the pages are complex and/or have lots of images. The overall time should be similar to exporting the report to an image format.|||I guess I can see where the report generation might be the bottleneck, however it is just a standard report with a basic list control displaying some 20-25 data fields per record. There are no images being used at all. The most graphically intensive element is a light gray filled box around the report title.
Honestly, I can't imagine a much simpler report. That is why I have trouble accepting this as being as good as it gets.
|||The fact that you see this in the designer as well as the server seems to mean that the issue may lie elsewhere. Do any other reports behave this way? Are you able to try any other printers?
When you print from the web you can preview the report first. Can you first preview the entire report before printing? By doing this you will have downloaded all the pages to the client, when you actually print you will just be sending the emf file to the printer. If the problem still occurs then it is most likely an issue downstream from RS if it doesn't occur then RS would have some issue.
Thanks.
-Daniel
Pause between pages when printing RS report
I am using Report Services on SQL Server 2005. I have a report that gets its data from a single SQL Server stored procedure. One other small table is used, but only to provide a list of values for selecting a parameter value to send to the stored procedure.
On the report I use a list control to display multiple records per page.
When printing the report (which currently produces over 500 pages - future printings of this report will not be nearly so large), the printer pauses between each page as if they are seperate print jobs. This is an HP LaserJet 5Si that normally cranks out reports very fast.
The only thing I can think of that might contribute to this sort of behavior is the fact that I set my margin sizes to 0in to get as much design room on the report as possible. Could that cause this sort of behavior?Just bumping this topic. I'm very much in need of an answer on this. I've tried different settings for the margins as well, but anything other than 0in. causes extra pages to be printed.
How can I eliminate the pause between pages?|||How are you printing the report? Are you using the printing via the web or using delivery? I have not heard of this sort of behavior. The only thing I can think of is that the server is busy rendering each page and is causing the printer to wait for additional pages.
-Daniel|||Thanks for the reply.
I am printing the reports via the web. This behavior is exhibited when printing them from the Visual Studio design environment as well.|||Most likely, your printer is able to print faster than the ability of the server to generate the pages. This could happen if the pages are complex and/or have lots of images. The overall time should be similar to exporting the report to an image format.|||I guess I can see where the report generation might be the bottleneck, however it is just a standard report with a basic list control displaying some 20-25 data fields per record. There are no images being used at all. The most graphically intensive element is a light gray filled box around the report title.
Honestly, I can't imagine a much simpler report. That is why I have trouble accepting this as being as good as it gets. 
When you print from the web you can preview the report first. Can you first preview the entire report before printing? By doing this you will have downloaded all the pages to the client, when you actually print you will just be sending the emf file to the printer. If the problem still occurs then it is most likely an issue downstream from RS if it doesn't occur then RS would have some issue.
Thanks.
-Danielsql
Tuesday, March 20, 2012
Patching for W2k3 SP1 server w/SQL Server 2005
Is the correct procedure for SUS patches to a server running SQL Server 2005, to set the client to download only and then manually shutdown SQL Server, apply patches, reboot, and restart SQL Server?
Do SQL Server 2005 patches download through SUS or is it manual SP only, except for hotfixes?
Thanks, Tom
I don't have much experience with SUS, but the hotfix patches will take care of the services for you. SP2, which has publicly released quite yet, shows a list of processes in use to allow you to shut them down before patching. This will eliminate the reboot need.
Thanks,
Sam Lester (MSFT)
Monday, March 12, 2012
patch application procedure
Hi,
I am having an mssql server with sp3. It is in sql cluster
(NT) as well as it participates in replication (merge). My
comany is very paranoid about doing things and all the
more there is no testing environment. Could any one please
explain how to apply SP4 for this server? We need to
consider the problematic conditions and disaster recovery
as well. My recovery mode is set to full.
james bond...-- james bond wrote: --
> Hi,
>> I am having an mssql server with sp3. It is in sql cluster
> (NT) as well as it participates in replication (merge). My
> company is very paranoid about doing things and all the
> more there is no testing environment. Could any one please
> explain how to apply SP4 for this server? We need to
> consider the problematic conditions and disaster recovery
> as well. My recovery mode is set to full.
--
Hi James Bond,
The best source of information for a service pack install is the README.htm of the service pack itself.
Hope this helps,
-Eric Cárdenas
SQL Server support
I am having an mssql server with sp3. It is in sql cluster
(NT) as well as it participates in replication (merge). My
comany is very paranoid about doing things and all the
more there is no testing environment. Could any one please
explain how to apply SP4 for this server? We need to
consider the problematic conditions and disaster recovery
as well. My recovery mode is set to full.
james bond...-- james bond wrote: --
> Hi,
>> I am having an mssql server with sp3. It is in sql cluster
> (NT) as well as it participates in replication (merge). My
> company is very paranoid about doing things and all the
> more there is no testing environment. Could any one please
> explain how to apply SP4 for this server? We need to
> consider the problematic conditions and disaster recovery
> as well. My recovery mode is set to full.
--
Hi James Bond,
The best source of information for a service pack install is the README.htm of the service pack itself.
Hope this helps,
-Eric Cárdenas
SQL Server support
Friday, March 9, 2012
Password protected stored procedure question
I have a password protected stored procedure that I have created.
If the user issues just the name of the stored procedure he gets a
message telling him that he needs to input the password.
If the user inputs the wrong password he gets a "syntax error" message
back in the query browser.
Now, here is the question:
How can I set it up so that the password shows up as asterisk in the
query browser?
Example - Sp_test password
Shows up as Sp_test ******** in the query browser."Varied_Interest" <Varied_Interest@.mail.com> wrote in message
news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>I have a password protected stored procedure that I have created.
> If the user issues just the name of the stored procedure he gets a
> message telling him that he needs to input the password.
> If the user inputs the wrong password he gets a "syntax error" message
> back in the query browser.
> Now, here is the question:
> How can I set it up so that the password shows up as asterisk in the
> query browser?
> Example - Sp_test password
> Shows up as Sp_test ******** in the query browser.
>
Make the password ********
Seriously, you can't do this in query analyzer.
And not really sure the point, there's better ways to enforce security than
this.
(and if the user has the ability to run sp_helptext sp_test you have zero
security anyway.)
Also, you generally do NOT want to name a stored proc sp_ unless you intend
to put it in the master database and make it available to all databases.
sp_ stands for system procedure, NOT stored procedure and the sp_ forces the
optmizer to handle things a bit differently.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Regarding "name a stored proc sp_ unless you intend to put it in the master
database and make it available to all databases"
This is a "non-supported" activity, and it does not work in SQL 2005! I
made the mistake, and we have a lot of work to do to move our stuff from
2000 to 2005.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:O7fZCVaYHHA.208@.TK2MSFTNGP05.phx.gbl...
> "Varied_Interest" <Varied_Interest@.mail.com> wrote in message
> news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>>I have a password protected stored procedure that I have created.
>> If the user issues just the name of the stored procedure he gets a
>> message telling him that he needs to input the password.
>> If the user inputs the wrong password he gets a "syntax error" message
>> back in the query browser.
>> Now, here is the question:
>> How can I set it up so that the password shows up as asterisk in the
>> query browser?
>> Example - Sp_test password
>> Shows up as Sp_test ******** in the query browser.
>
> Make the password ********
> Seriously, you can't do this in query analyzer.
> And not really sure the point, there's better ways to enforce security
> than this.
> (and if the user has the ability to run sp_helptext sp_test you have zero
> security anyway.)
> Also, you generally do NOT want to name a stored proc sp_ unless you
> intend to put it in the master database and make it available to all
> databases. sp_ stands for system procedure, NOT stored procedure and the
> sp_ forces the optmizer to handle things a bit differently.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:Ogum6JdYHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Regarding "name a stored proc sp_ unless you intend to put it in the
> master database and make it available to all databases"
> This is a "non-supported" activity, and it does not work in SQL 2005! I
> made the mistake, and we have a lot of work to do to move our stuff from
> 2000 to 2005.
Hmm, wasn't sure if 2005 still allowed that or not.
Probably better that they don't.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
If the user issues just the name of the stored procedure he gets a
message telling him that he needs to input the password.
If the user inputs the wrong password he gets a "syntax error" message
back in the query browser.
Now, here is the question:
How can I set it up so that the password shows up as asterisk in the
query browser?
Example - Sp_test password
Shows up as Sp_test ******** in the query browser."Varied_Interest" <Varied_Interest@.mail.com> wrote in message
news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>I have a password protected stored procedure that I have created.
> If the user issues just the name of the stored procedure he gets a
> message telling him that he needs to input the password.
> If the user inputs the wrong password he gets a "syntax error" message
> back in the query browser.
> Now, here is the question:
> How can I set it up so that the password shows up as asterisk in the
> query browser?
> Example - Sp_test password
> Shows up as Sp_test ******** in the query browser.
>
Make the password ********
Seriously, you can't do this in query analyzer.
And not really sure the point, there's better ways to enforce security than
this.
(and if the user has the ability to run sp_helptext sp_test you have zero
security anyway.)
Also, you generally do NOT want to name a stored proc sp_ unless you intend
to put it in the master database and make it available to all databases.
sp_ stands for system procedure, NOT stored procedure and the sp_ forces the
optmizer to handle things a bit differently.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Regarding "name a stored proc sp_ unless you intend to put it in the master
database and make it available to all databases"
This is a "non-supported" activity, and it does not work in SQL 2005! I
made the mistake, and we have a lot of work to do to move our stuff from
2000 to 2005.
--
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:O7fZCVaYHHA.208@.TK2MSFTNGP05.phx.gbl...
> "Varied_Interest" <Varied_Interest@.mail.com> wrote in message
> news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>>I have a password protected stored procedure that I have created.
>> If the user issues just the name of the stored procedure he gets a
>> message telling him that he needs to input the password.
>> If the user inputs the wrong password he gets a "syntax error" message
>> back in the query browser.
>> Now, here is the question:
>> How can I set it up so that the password shows up as asterisk in the
>> query browser?
>> Example - Sp_test password
>> Shows up as Sp_test ******** in the query browser.
>
> Make the password ********
> Seriously, you can't do this in query analyzer.
> And not really sure the point, there's better ways to enforce security
> than this.
> (and if the user has the ability to run sp_helptext sp_test you have zero
> security anyway.)
> Also, you generally do NOT want to name a stored proc sp_ unless you
> intend to put it in the master database and make it available to all
> databases. sp_ stands for system procedure, NOT stored procedure and the
> sp_ forces the optmizer to handle things a bit differently.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:Ogum6JdYHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Regarding "name a stored proc sp_ unless you intend to put it in the
> master database and make it available to all databases"
> This is a "non-supported" activity, and it does not work in SQL 2005! I
> made the mistake, and we have a lot of work to do to move our stuff from
> 2000 to 2005.
Hmm, wasn't sure if 2005 still allowed that or not.
Probably better that they don't.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Password protected stored procedure question
I have a password protected stored procedure that I have created.
If the user issues just the name of the stored procedure he gets a
message telling him that he needs to input the password.
If the user inputs the wrong password he gets a "syntax error" message
back in the query browser.
Now, here is the question:
How can I set it up so that the password shows up as asterisk in the
query browser?
Example - Sp_test password
Shows up as Sp_test ******** in the query browser.
"Varied_Interest" <Varied_Interest@.mail.com> wrote in message
news:1173373412.914934.102930@.p10g2000cwp.googlegr oups.com...
>I have a password protected stored procedure that I have created.
> If the user issues just the name of the stored procedure he gets a
> message telling him that he needs to input the password.
> If the user inputs the wrong password he gets a "syntax error" message
> back in the query browser.
> Now, here is the question:
> How can I set it up so that the password shows up as asterisk in the
> query browser?
> Example - Sp_test password
> Shows up as Sp_test ******** in the query browser.
>
Make the password ********
Seriously, you can't do this in query analyzer.
And not really sure the point, there's better ways to enforce security than
this.
(and if the user has the ability to run sp_helptext sp_test you have zero
security anyway.)
Also, you generally do NOT want to name a stored proc sp_ unless you intend
to put it in the master database and make it available to all databases.
sp_ stands for system procedure, NOT stored procedure and the sp_ forces the
optmizer to handle things a bit differently.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Regarding "name a stored proc sp_ unless you intend to put it in the master
database and make it available to all databases"
This is a "non-supported" activity, and it does not work in SQL 2005! I
made the mistake, and we have a lot of work to do to move our stuff from
2000 to 2005.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:O7fZCVaYHHA.208@.TK2MSFTNGP05.phx.gbl...
> "Varied_Interest" <Varied_Interest@.mail.com> wrote in message
> news:1173373412.914934.102930@.p10g2000cwp.googlegr oups.com...
>
> Make the password ********
> Seriously, you can't do this in query analyzer.
> And not really sure the point, there's better ways to enforce security
> than this.
> (and if the user has the ability to run sp_helptext sp_test you have zero
> security anyway.)
> Also, you generally do NOT want to name a stored proc sp_ unless you
> intend to put it in the master database and make it available to all
> databases. sp_ stands for system procedure, NOT stored procedure and the
> sp_ forces the optmizer to handle things a bit differently.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
|||"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:Ogum6JdYHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Regarding "name a stored proc sp_ unless you intend to put it in the
> master database and make it available to all databases"
> This is a "non-supported" activity, and it does not work in SQL 2005! I
> made the mistake, and we have a lot of work to do to move our stuff from
> 2000 to 2005.
Hmm, wasn't sure if 2005 still allowed that or not.
Probably better that they don't.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
If the user issues just the name of the stored procedure he gets a
message telling him that he needs to input the password.
If the user inputs the wrong password he gets a "syntax error" message
back in the query browser.
Now, here is the question:
How can I set it up so that the password shows up as asterisk in the
query browser?
Example - Sp_test password
Shows up as Sp_test ******** in the query browser.
"Varied_Interest" <Varied_Interest@.mail.com> wrote in message
news:1173373412.914934.102930@.p10g2000cwp.googlegr oups.com...
>I have a password protected stored procedure that I have created.
> If the user issues just the name of the stored procedure he gets a
> message telling him that he needs to input the password.
> If the user inputs the wrong password he gets a "syntax error" message
> back in the query browser.
> Now, here is the question:
> How can I set it up so that the password shows up as asterisk in the
> query browser?
> Example - Sp_test password
> Shows up as Sp_test ******** in the query browser.
>
Make the password ********
Seriously, you can't do this in query analyzer.
And not really sure the point, there's better ways to enforce security than
this.
(and if the user has the ability to run sp_helptext sp_test you have zero
security anyway.)
Also, you generally do NOT want to name a stored proc sp_ unless you intend
to put it in the master database and make it available to all databases.
sp_ stands for system procedure, NOT stored procedure and the sp_ forces the
optmizer to handle things a bit differently.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
|||Hi,
Regarding "name a stored proc sp_ unless you intend to put it in the master
database and make it available to all databases"
This is a "non-supported" activity, and it does not work in SQL 2005! I
made the mistake, and we have a lot of work to do to move our stuff from
2000 to 2005.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:O7fZCVaYHHA.208@.TK2MSFTNGP05.phx.gbl...
> "Varied_Interest" <Varied_Interest@.mail.com> wrote in message
> news:1173373412.914934.102930@.p10g2000cwp.googlegr oups.com...
>
> Make the password ********
> Seriously, you can't do this in query analyzer.
> And not really sure the point, there's better ways to enforce security
> than this.
> (and if the user has the ability to run sp_helptext sp_test you have zero
> security anyway.)
> Also, you generally do NOT want to name a stored proc sp_ unless you
> intend to put it in the master database and make it available to all
> databases. sp_ stands for system procedure, NOT stored procedure and the
> sp_ forces the optmizer to handle things a bit differently.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>
|||"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:Ogum6JdYHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Regarding "name a stored proc sp_ unless you intend to put it in the
> master database and make it available to all databases"
> This is a "non-supported" activity, and it does not work in SQL 2005! I
> made the mistake, and we have a lot of work to do to move our stuff from
> 2000 to 2005.
Hmm, wasn't sure if 2005 still allowed that or not.
Probably better that they don't.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Password protected stored procedure question
I have a password protected stored procedure that I have created.
If the user issues just the name of the stored procedure he gets a
message telling him that he needs to input the password.
If the user inputs the wrong password he gets a "syntax error" message
back in the query browser.
Now, here is the question:
How can I set it up so that the password shows up as asterisk in the
query browser?
Example - Sp_test password
Shows up as Sp_test ******** in the query browser."Varied_Interest" <Varied_Interest@.mail.com> wrote in message
news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>I have a password protected stored procedure that I have created.
> If the user issues just the name of the stored procedure he gets a
> message telling him that he needs to input the password.
> If the user inputs the wrong password he gets a "syntax error" message
> back in the query browser.
> Now, here is the question:
> How can I set it up so that the password shows up as asterisk in the
> query browser?
> Example - Sp_test password
> Shows up as Sp_test ******** in the query browser.
>
Make the password ********
Seriously, you can't do this in query analyzer.
And not really sure the point, there's better ways to enforce security than
this.
(and if the user has the ability to run sp_helptext sp_test you have zero
security anyway.)
Also, you generally do NOT want to name a stored proc sp_ unless you intend
to put it in the master database and make it available to all databases.
sp_ stands for system procedure, NOT stored procedure and the sp_ forces the
optmizer to handle things a bit differently.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Regarding "name a stored proc sp_ unless you intend to put it in the master
database and make it available to all databases"
This is a "non-supported" activity, and it does not work in SQL 2005! I
made the mistake, and we have a lot of work to do to move our stuff from
2000 to 2005.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:O7fZCVaYHHA.208@.TK2MSFTNGP05.phx.gbl...
> "Varied_Interest" <Varied_Interest@.mail.com> wrote in message
> news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>
> Make the password ********
> Seriously, you can't do this in query analyzer.
> And not really sure the point, there's better ways to enforce security
> than this.
> (and if the user has the ability to run sp_helptext sp_test you have zero
> security anyway.)
> Also, you generally do NOT want to name a stored proc sp_ unless you
> intend to put it in the master database and make it available to all
> databases. sp_ stands for system procedure, NOT stored procedure and the
> sp_ forces the optmizer to handle things a bit differently.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:Ogum6JdYHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Regarding "name a stored proc sp_ unless you intend to put it in the
> master database and make it available to all databases"
> This is a "non-supported" activity, and it does not work in SQL 2005! I
> made the mistake, and we have a lot of work to do to move our stuff from
> 2000 to 2005.
Hmm, wasn't sure if 2005 still allowed that or not.
Probably better that they don't.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
If the user issues just the name of the stored procedure he gets a
message telling him that he needs to input the password.
If the user inputs the wrong password he gets a "syntax error" message
back in the query browser.
Now, here is the question:
How can I set it up so that the password shows up as asterisk in the
query browser?
Example - Sp_test password
Shows up as Sp_test ******** in the query browser."Varied_Interest" <Varied_Interest@.mail.com> wrote in message
news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>I have a password protected stored procedure that I have created.
> If the user issues just the name of the stored procedure he gets a
> message telling him that he needs to input the password.
> If the user inputs the wrong password he gets a "syntax error" message
> back in the query browser.
> Now, here is the question:
> How can I set it up so that the password shows up as asterisk in the
> query browser?
> Example - Sp_test password
> Shows up as Sp_test ******** in the query browser.
>
Make the password ********
Seriously, you can't do this in query analyzer.
And not really sure the point, there's better ways to enforce security than
this.
(and if the user has the ability to run sp_helptext sp_test you have zero
security anyway.)
Also, you generally do NOT want to name a stored proc sp_ unless you intend
to put it in the master database and make it available to all databases.
sp_ stands for system procedure, NOT stored procedure and the sp_ forces the
optmizer to handle things a bit differently.
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com|||Hi,
Regarding "name a stored proc sp_ unless you intend to put it in the master
database and make it available to all databases"
This is a "non-supported" activity, and it does not work in SQL 2005! I
made the mistake, and we have a lot of work to do to move our stuff from
2000 to 2005.
Thank you,
Daniel Jameson
SQL Server DBA
Children's Oncology Group
www.childrensoncologygroup.org
"Greg D. Moore (Strider)" <mooregr_deleteth1s@.greenms.com> wrote in message
news:O7fZCVaYHHA.208@.TK2MSFTNGP05.phx.gbl...
> "Varied_Interest" <Varied_Interest@.mail.com> wrote in message
> news:1173373412.914934.102930@.p10g2000cwp.googlegroups.com...
>
> Make the password ********
> Seriously, you can't do this in query analyzer.
> And not really sure the point, there's better ways to enforce security
> than this.
> (and if the user has the ability to run sp_helptext sp_test you have zero
> security anyway.)
> Also, you generally do NOT want to name a stored proc sp_ unless you
> intend to put it in the master database and make it available to all
> databases. sp_ stands for system procedure, NOT stored procedure and the
> sp_ forces the optmizer to handle things a bit differently.
>
> --
> Greg Moore
> SQL Server DBA Consulting
> sql (at) greenms.com http://www.greenms.com
>|||"Daniel Jameson" <djameson@.childrensoncologygroup.org> wrote in message
news:Ogum6JdYHHA.4772@.TK2MSFTNGP05.phx.gbl...
> Hi,
> Regarding "name a stored proc sp_ unless you intend to put it in the
> master database and make it available to all databases"
> This is a "non-supported" activity, and it does not work in SQL 2005! I
> made the mistake, and we have a lot of work to do to move our stuff from
> 2000 to 2005.
Hmm, wasn't sure if 2005 still allowed that or not.
Probably better that they don't.
> --
> Thank you,
> Daniel Jameson
> SQL Server DBA
> Children's Oncology Group
> www.childrensoncologygroup.org
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com
Wednesday, March 7, 2012
password hash
It seems like there is no built in procedure for making a password hash in
SQL2000. Am I wrong.
Do I have to make it from scratch myself or is there samples out there?
SQL2000. Am I wrong.
Do I have to make it from scratch myself or is there samples out there?
/Jens Ulrik"Jens U. K." <1jk2@.3bsopatent4.dk> wrote in message
news:i3t8d.3709$UU4.720@.news.get2net.dk...
> It seems like there is no built in procedure for making a password hash in
> SQL2000. Am I wrong.
> Do I have to make it from scratch myself or is there samples out there?
> /Jens Ulrik
That's correct - the usual solution is to encrypt/decrypt in the client
application using the Win32 CrpytoAPI, or some other suitable API, then
store only the encrypted password in the database:
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=22
Simon|||"Simon Hayes" <sql@.hayes.ch> wrote in message
news:41626a41$1_1@.news.bluewin.ch...
> "Jens U. K." <1jk2@.3bsopatent4.dk> wrote in message
> news:i3t8d.3709$UU4.720@.news.get2net.dk...
>> It seems like there is no built in procedure for making a password hash
>> in SQL2000. Am I wrong.
>> Do I have to make it from scratch myself or is there samples out there?
>>
>> /Jens Ulrik
> That's correct - the usual solution is to encrypt/decrypt in the client
> application using the Win32 CrpytoAPI, or some other suitable API, then
> store only the encrypted password in the database:
Hmm, it would have so easy with a stored procedure. Only problem is that
neither the SQL-server nor the HTTP-server are in my possesion. So
unfortunately I do not have access to "whatever" API I want :-( But as I
recall the provider have ASP.NET and I think there is some encryption
functions in there...
/Jens Ulrik|||The asp.net System.Web.Security.FormsAuthentication class contains a static
method for hashing passwords that you may find helpful:
string password = "god";
string passwordFormat = "sha1";
string encryptedPassword =
FormsAuthentication.HashPasswordForStoringInConfig File(password,passwordFormat);
"Jens U. K." <1jk2@.3bsopatent4.dk> wrote in message
news:3bu8d.4068$xP1.2605@.news.get2net.dk...
> "Simon Hayes" <sql@.hayes.ch> wrote in message
> news:41626a41$1_1@.news.bluewin.ch...
>>
>> "Jens U. K." <1jk2@.3bsopatent4.dk> wrote in message
>> news:i3t8d.3709$UU4.720@.news.get2net.dk...
>>> It seems like there is no built in procedure for making a password hash
>>> in SQL2000. Am I wrong.
>>> Do I have to make it from scratch myself or is there samples out there?
>>>
>>> /Jens Ulrik
>>
>> That's correct - the usual solution is to encrypt/decrypt in the client
>> application using the Win32 CrpytoAPI, or some other suitable API, then
>> store only the encrypted password in the database:
> Hmm, it would have so easy with a stored procedure. Only problem is that
> neither the SQL-server nor the HTTP-server are in my possesion. So
> unfortunately I do not have access to "whatever" API I want :-( But as I
> recall the provider have ASP.NET and I think there is some encryption
> functions in there...
> /Jens Ulrik
Saturday, February 25, 2012
Passing text parameters
Hello Guys,
create procedure spsCheckMsg(
@.sTyp as varchar(100),
@.tMsg as text) as
select @.tMsg = md_body from tbl_msgdef where msgname = @.sTyp
Parameters of the type text can be used in stored procedures. So I wanted to get the text data (md_body) by a select statement and pass it to the parameter @.tMsg. This code doesn't work. I always get the message "The assignment operation can't have a text datatype as argument" which refers to the select statement.
Does anyone have a solution for this?Howdy
If I have understood correctly, try :
-------------------
Create Procedure spsCheckMsg
(
@.sTyp as varchar(1000),
@.tMsg as varchar(1000)
)
AS
set @.tMsg =
( select convert(varchar(1000),md_body) from tbl_msgdef where msgname = @.sTyp )
-------------------
Cheers,
SG
create procedure spsCheckMsg(
@.sTyp as varchar(100),
@.tMsg as text) as
select @.tMsg = md_body from tbl_msgdef where msgname = @.sTyp
Parameters of the type text can be used in stored procedures. So I wanted to get the text data (md_body) by a select statement and pass it to the parameter @.tMsg. This code doesn't work. I always get the message "The assignment operation can't have a text datatype as argument" which refers to the select statement.
Does anyone have a solution for this?Howdy
If I have understood correctly, try :
-------------------
Create Procedure spsCheckMsg
(
@.sTyp as varchar(1000),
@.tMsg as varchar(1000)
)
AS
set @.tMsg =
( select convert(varchar(1000),md_body) from tbl_msgdef where msgname = @.sTyp )
-------------------
Cheers,
SG
Passing table-type variables to SP?
Sorry about posting twice, but I had some trouble with setting the date on
this machine...
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
this machine...
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
Passing table-type variables to SP?
Sorry about posting twice, but I had some trouble with setting the date on
this machine... :)
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine... :)
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine... :)
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
this machine... :)
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine... :)
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine... :)
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
Passing table-type variables to SP?
Sorry about posting twice, but I had some trouble with setting the date on
this machine...
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
this machine...
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
Passing table-type variables to SP?
Sorry about posting twice, but I had some trouble with setting the date on
this machine...
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
this machine...
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Hi
You can not pass a table data type to a stored procedure. You may want to
use temporary tables instead.
From Books online "Create procedure" topic
CREATE PROC [ EDURE ] [ owner. ] procedure_name [ ; number ]
[ { @.parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ]
] [ ,...n ]
[ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ]
[ FOR REPLICATION ]
AS sql_statement [ ...n ]
data_type
Is the parameter data type. All data types, except the table data type, can
be used as a parameter for a stored procedure. However, the cursor data type
can be used only on OUTPUT parameters. When you specify a data type of
cursor, the VARYING and OUTPUT keywords must also be specified. For more
information about SQL Server - supplied data types and their syntax, see
Data Types.
John
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>|||You cannot pass a table variable from one sp to another.
This article discuss the options to share data between procs.
http://www.sommarskog.se/share_data.html
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Agoston Bejo" <gusz1@.freemail.hu> wrote in message
news:OppBdMHtEHA.4040@.TK2MSFTNGP09.phx.gbl...
> Sorry about posting twice, but I had some trouble with setting the date on
> this machine...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
> find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
> enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
passing table/column name as parameter
I know passing table/column name as parameter to a stored procedure is
not good practice, but sometimes I need to do that occasionally. I
know there's a way can do that but forget how. Can someone refresh my
memory?
Thanks.
Saiyouwangc@.alexian.net (Saiyou Anh) wrote in message news:<a51d3ca7.0407271253.91fae7@.posting.google.com>...
> I know passing table/column name as parameter to a stored procedure is
> not good practice, but sometimes I need to do that occasionally. I
> know there's a way can do that but forget how. Can someone refresh my
> memory?
> Thanks.
> Saiyou
not good practice, but sometimes I need to do that occasionally. I
know there's a way can do that but forget how. Can someone refresh my
memory?
Thanks.
Saiyouwangc@.alexian.net (Saiyou Anh) wrote in message news:<a51d3ca7.0407271253.91fae7@.posting.google.com>...
> I know passing table/column name as parameter to a stored procedure is
> not good practice, but sometimes I need to do that occasionally. I
> know there's a way can do that but forget how. Can someone refresh my
> memory?
> Thanks.
> Saiyou
See here, which will also refresh your memory about why you shouldn't do this... :-)
http://www.sommarskog.se/dynamic_sql.html
Simon|||Saiyou Anh (wangc@.alexian.net) writes:
> I know passing table/column name as parameter to a stored procedure is
> not good practice, but sometimes I need to do that occasionally. I
> know there's a way can do that but forget how. Can someone refresh my
> memory?
All about it, including why shouldn't do it on
http://www.sommarskog.se/dynamic_sql.html
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp
Passing table type variables to SP
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az albbiakat rta a kvetkez
hrzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.
Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az albbiakat rta a kvetkez
hrzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
Passing table type variables to SP
I would like to write a stored procedure that takes a table (i.e. table
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az alábbiakat írta a következõ
hírüzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
variable) as its parameter.
E.g.:
DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
SP_MYPROC @.mytable
What should SP_MYPROC's signature look like? I can't seem to be able to find
anything about this in the documenation.
I guess it would look something like this:
ALTER PROCEDURE SP_MYPROC
(@.t TABLE)
or
ALTER PROCEDURE SP_MYPROC
(@.t TABLE(ID INT, NAME VARCHAR(50)))
Actually, how big is the freedom when taking such a parameter? Is it enough
to use the first version and treat the table whatever way I would like to?
E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
any tables the second column of which is of type VARCHAR of any size.Sorry, sorry, sorry!
Actually I've posted this message with the wrong date. I also posted it now
with the real date, so no need to answer this one!
Agoston
"Agoston Bejo" <gusz1@.freemail.hu> az alábbiakat írta a következõ
hírüzenetben: uRzr0JHtEHA.2808@.TK2MSFTNGP14.phx.gbl...
> I would like to write a stored procedure that takes a table (i.e. table
> variable) as its parameter.
> E.g.:
> DECLARE @.mytable = TABLE(ID INT, NAME VARCHAR(50))
> SP_MYPROC @.mytable
> What should SP_MYPROC's signature look like? I can't seem to be able to
find
> anything about this in the documenation.
> I guess it would look something like this:
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE)
> or
> ALTER PROCEDURE SP_MYPROC
> (@.t TABLE(ID INT, NAME VARCHAR(50)))
> Actually, how big is the freedom when taking such a parameter? Is it
enough
> to use the first version and treat the table whatever way I would like to?
> E.g. not assuming that the 'NAME' column is a VARCHAR(50), but taking
> any tables the second column of which is of type VARCHAR of any size.
>
>
Subscribe to:
Posts (Atom)