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
>
Showing posts with label stored. Show all posts
Showing posts with label stored. Show all posts
Friday, March 30, 2012
pdf-files in full-text-search
Hallo, I tryed to add pdf-files that are stored in a image-column to
full-text-search. As mime-type in the "data type column" I used
"application/pdf". So, I can see at the "unique key count" that the
files are read from the index but I can't find anything in the files by
searching with contains(). Whats wrong? Is the data type not the right.
Is there a generally problem with pdf? Thanks in advance, Silvio
Hi Hilary, thanks for your help. I still found the right way to do this
here: http://weblogs.asp.net/wallym/archiv...28/382060.aspx
Now it works fine.
full-text-search. As mime-type in the "data type column" I used
"application/pdf". So, I can see at the "unique key count" that the
files are read from the index but I can't find anything in the files by
searching with contains(). Whats wrong? Is the data type not the right.
Is there a generally problem with pdf? Thanks in advance, Silvio
Hi Hilary, thanks for your help. I still found the right way to do this
here: http://weblogs.asp.net/wallym/archiv...28/382060.aspx
Now it works fine.
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
Patterns & Practices
I have looked through the Patterns & Practices information and cannot find
anything on naming conventions for SQL Server stored procedures, tables, and
user-defined functions. Where might this information be found?
Thanks,
MikeHere's one:
http://vyaskn.tripod.com/object_naming.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"memead" <memead@.discussions.microsoft.com> wrote in message
news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
>I have looked through the Patterns & Practices information and cannot find
> anything on naming conventions for SQL Server stored procedures, tables, and
> user-defined functions. Where might this information be found?
> Thanks,
> Mike|||Thank you so much for your timely response.
Mike
"Tibor Karaszi" wrote:
> Here's one:
> http://vyaskn.tripod.com/object_naming.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "memead" <memead@.discussions.microsoft.com> wrote in message
> news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
> >I have looked through the Patterns & Practices information and cannot find
> > anything on naming conventions for SQL Server stored procedures, tables, and
> > user-defined functions. Where might this information be found?
> >
> > Thanks,
> >
> > Mike
>|||Vyas has some GREAT scripts here too: http://vyaskn.tripod.com/code.htm
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF3CF558-1162-4057-ABD8-841A9CF541EB@.microsoft.com...
> Here's one:
> http://vyaskn.tripod.com/object_naming.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "memead" <memead@.discussions.microsoft.com> wrote in message
> news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
>>I have looked through the Patterns & Practices information and cannot find
>> anything on naming conventions for SQL Server stored procedures, tables,
>> and
>> user-defined functions. Where might this information be found?
>> Thanks,
>> Mike
>|||Thank you for the information.
"TheSQLGuru" wrote:
> Vyas has some GREAT scripts here too: http://vyaskn.tripod.com/code.htm
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:BF3CF558-1162-4057-ABD8-841A9CF541EB@.microsoft.com...
> > Here's one:
> >
> > http://vyaskn.tripod.com/object_naming.htm
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "memead" <memead@.discussions.microsoft.com> wrote in message
> > news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
> >>I have looked through the Patterns & Practices information and cannot find
> >> anything on naming conventions for SQL Server stored procedures, tables,
> >> and
> >> user-defined functions. Where might this information be found?
> >>
> >> Thanks,
> >>
> >> Mike
> >
>
>sql
anything on naming conventions for SQL Server stored procedures, tables, and
user-defined functions. Where might this information be found?
Thanks,
MikeHere's one:
http://vyaskn.tripod.com/object_naming.htm
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"memead" <memead@.discussions.microsoft.com> wrote in message
news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
>I have looked through the Patterns & Practices information and cannot find
> anything on naming conventions for SQL Server stored procedures, tables, and
> user-defined functions. Where might this information be found?
> Thanks,
> Mike|||Thank you so much for your timely response.
Mike
"Tibor Karaszi" wrote:
> Here's one:
> http://vyaskn.tripod.com/object_naming.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "memead" <memead@.discussions.microsoft.com> wrote in message
> news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
> >I have looked through the Patterns & Practices information and cannot find
> > anything on naming conventions for SQL Server stored procedures, tables, and
> > user-defined functions. Where might this information be found?
> >
> > Thanks,
> >
> > Mike
>|||Vyas has some GREAT scripts here too: http://vyaskn.tripod.com/code.htm
--
TheSQLGuru
President
Indicium Resources, Inc.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:BF3CF558-1162-4057-ABD8-841A9CF541EB@.microsoft.com...
> Here's one:
> http://vyaskn.tripod.com/object_naming.htm
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "memead" <memead@.discussions.microsoft.com> wrote in message
> news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
>>I have looked through the Patterns & Practices information and cannot find
>> anything on naming conventions for SQL Server stored procedures, tables,
>> and
>> user-defined functions. Where might this information be found?
>> Thanks,
>> Mike
>|||Thank you for the information.
"TheSQLGuru" wrote:
> Vyas has some GREAT scripts here too: http://vyaskn.tripod.com/code.htm
> --
> TheSQLGuru
> President
> Indicium Resources, Inc.
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:BF3CF558-1162-4057-ABD8-841A9CF541EB@.microsoft.com...
> > Here's one:
> >
> > http://vyaskn.tripod.com/object_naming.htm
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://sqlblog.com/blogs/tibor_karaszi
> >
> >
> > "memead" <memead@.discussions.microsoft.com> wrote in message
> > news:0C19DC86-D76D-4CE4-833C-0F4DDE8BE492@.microsoft.com...
> >>I have looked through the Patterns & Practices information and cannot find
> >> anything on naming conventions for SQL Server stored procedures, tables,
> >> and
> >> user-defined functions. Where might this information be found?
> >>
> >> Thanks,
> >>
> >> Mike
> >
>
>sql
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 Generating Stored Proceedure
Hi
Does anyone know of any sample stored proceedure code for a random password
generator where I can set the character lenght to 6.
Any help would be much appreciated
Thanks
BenMay be something like
SELECT LEFT(CONVERT(VARCHAR(40), NewID()),6)
?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ben" <Ben@.NoSpam.com> wrote in message
news:uz%23H8hDEFHA.3648@.TK2MSFTNGP10.phx.gbl...
> Hi
> Does anyone know of any sample stored proceedure code for a random
> password generator where I can set the character lenght to 6.
> Any help would be much appreciated
> Thanks
> Ben
>|||Ben wrote:
> Does anyone know of any sample stored proceedure code for a random passwor
d
> generator where I can set the character lenght to 6.
Check out http://vyaskn.tripod.com/code/password.txt
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||There is an old DEC Unix routine that produced pronouncable nonsense
words of 6 to 8 letters. You can probably find it in C.
Then Compuserve used to issue two-word phrases of the form
<adjective><noun> ("vivid*bagpipes") that were easy to remember, but
really hard to hack.
My favorite is a "bingo card" of numbers that you issue to users. The
computer challenges them with a pair of numbers; the user locates them
on his card, then replies with the numbers on the other two corners of
a rectangle marked by the first pair.
You then issue a new bingo card each month for security. Even a 5x5
grid has 600 possible challenges.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1108229446.247063.307450@.g14g2000cwa.googlegroups.com...
> You then issue a new bingo card each month for security. Even a 5x5
> grid has 600 possible challenges.
A bit off topic: Due to symmetry, this Bingo card only has 200 unique
challenges that form rectangles. If you allow the challenge numbers to
come from the same file (row or column) then you have 290 unique
challenges but the response to those extra 90 will be equal to the
challenge.
Regards,
Jim
Does anyone know of any sample stored proceedure code for a random password
generator where I can set the character lenght to 6.
Any help would be much appreciated
Thanks
BenMay be something like
SELECT LEFT(CONVERT(VARCHAR(40), NewID()),6)
?
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"Ben" <Ben@.NoSpam.com> wrote in message
news:uz%23H8hDEFHA.3648@.TK2MSFTNGP10.phx.gbl...
> Hi
> Does anyone know of any sample stored proceedure code for a random
> password generator where I can set the character lenght to 6.
> Any help would be much appreciated
> Thanks
> Ben
>|||Ben wrote:
> Does anyone know of any sample stored proceedure code for a random passwor
d
> generator where I can set the character lenght to 6.
Check out http://vyaskn.tripod.com/code/password.txt
Aaron Weiker
http://aaronweiker.com/
http://www.sqlprogrammer.org/|||There is an old DEC Unix routine that produced pronouncable nonsense
words of 6 to 8 letters. You can probably find it in C.
Then Compuserve used to issue two-word phrases of the form
<adjective><noun> ("vivid*bagpipes") that were easy to remember, but
really hard to hack.
My favorite is a "bingo card" of numbers that you issue to users. The
computer challenges them with a pair of numbers; the user locates them
on his card, then replies with the numbers on the other two corners of
a rectangle marked by the first pair.
You then issue a new bingo card each month for security. Even a 5x5
grid has 600 possible challenges.|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1108229446.247063.307450@.g14g2000cwa.googlegroups.com...
> You then issue a new bingo card each month for security. Even a 5x5
> grid has 600 possible challenges.
A bit off topic: Due to symmetry, this Bingo card only has 200 unique
challenges that form rectangles. If you allow the challenge numbers to
come from the same file (row or column) then you have 290 unique
challenges but the response to those extra 90 will be equal to the
challenge.
Regards,
Jim
Saturday, February 25, 2012
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 tablename to proc from trigger
------------------------
I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.
Can it be done ?
trigger x on table y
for update
as
declare @.table_name
select @.table_name = get underlying table_name 'y' from somewhere
exec stored proc (@.table_name)
where do I find the name of the table the current trigger is defined on ?
thanksI want to make a birthday cake out of nitroglycerin.
Sometimes the question is not "can it be done", but "should it be done".
Don't put this kind of crap in a trigger.|||Why don't you tell us what your trying to acccomplish instead of trying to force a technical solution that doesn't seem to be a "best practice" kind of thing
Blind dude, did they base rat on you?|||Huh? Rat? Base? Huh?
I want to fire a trigger which calls a stored proc, passing the name of the table the trigger is defined on to the proc without having to hardcode.
Can it be done ?
trigger x on table y
for update
as
declare @.table_name
select @.table_name = get underlying table_name 'y' from somewhere
exec stored proc (@.table_name)
where do I find the name of the table the current trigger is defined on ?
thanksI want to make a birthday cake out of nitroglycerin.
Sometimes the question is not "can it be done", but "should it be done".
Don't put this kind of crap in a trigger.|||Why don't you tell us what your trying to acccomplish instead of trying to force a technical solution that doesn't seem to be a "best practice" kind of thing
Blind dude, did they base rat on you?|||Huh? Rat? Base? Huh?
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 variable as input parameter to stored proc
I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
Passing table variable as input parameter to stored proc
I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?
You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks
|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?
You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks
|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
Passing table variable as input parameter to stored proc
I understand you cannot pass a table variable as input parameter to a stored
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
proc.
But what is the alternative in my case?
Current DB standards limit access of linked servers to the execution of
stored procs and functions, no cross server joins.
But I need to get data from the remote server that matches keys on imy local
server. The remote table is too large to bring over to the local server in
its entirety. In order to limit my result set from the remote server, I must
pass it the
keys of the records I want.
My issue is how to pass the keys.
For example, say I have customers and orders databases are on different
servers. From orders I want to get customer information for 200 customers.
Somehow I need to pass 200 custids to customers, execute the query there and
return 200 sets of customer data.
How do I call a proc on orders and pass 200 custids as a parameter?You can always query on a table of some other database on some other
server by specifying server name, db name, owner name and table name.
Something like this:
SELECT * FROM <Server Name>.<Database Name>.<Owner>.<Table>
If you have any other concern which is not covered here, please write.
I will surely try to help you.
Thanks|||Dave
Table-Valued UDFs --
USE Northwind
GO
IF object_id('dbo.get_cust_orders') IS NOT NULL
DROP FUNCTION dbo.cust_orders
GO
CREATE FUNCTION dbo.get_cust_orders
(
@.custid char(5)
)
RETURNS TABLE
AS
RETURN SELECT
*
FROM
Orders
WHERE
CustomerID = @.custid
GO
SELECT OrderID, OrderDate
FROM get_cust_orders('VINET') AS C
"Dave" <Dave@.discussions.microsoft.com> wrote in message
news:BFDF4510-EDA1-4098-BCCA-E2C80EAFFA73@.microsoft.com...
>I understand you cannot pass a table variable as input parameter to a
>stored
> proc.
> But what is the alternative in my case?
> Current DB standards limit access of linked servers to the execution of
> stored procs and functions, no cross server joins.
> But I need to get data from the remote server that matches keys on imy
> local
> server. The remote table is too large to bring over to the local server
> in
> its entirety. In order to limit my result set from the remote server, I
> must
> pass it the
> keys of the records I want.
> My issue is how to pass the keys.
> For example, say I have customers and orders databases are on different
> servers. From orders I want to get customer information for 200
> customers.
> Somehow I need to pass 200 custids to customers, execute the query there
> and
> return 200 sets of customer data.
> How do I call a proc on orders and pass 200 custids as a parameter?
>
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.
>
>
Subscribe to:
Posts (Atom)