On rendering Excel, i want to make a new sheet which contains the RS
parameters of the rendering.
So, the end user get an XLS data and the input parameters.
Is there any way to make this "parameters" sheet ?
Thanks
AntoineTry putting your parameters on a rectangle, in the body of the report, not
the header. Then go to the properties on the rectangle, indicate to have a
page break before or after the object. when the report display in your
browser, the first page will just be any headers and your parameters but,
when you export to excel, a separate tab will have been created for your
parameters. For the online view, you probably will want to indicate that the
rectangle should repeat based on your detailed data area. Hope this helps!
--
ChrisinPA
"tonio932" wrote:
> On rendering Excel, i want to make a new sheet which contains the RS
> parameters of the rendering.
> So, the end user get an XLS data and the input parameters.
> Is there any way to make this "parameters" sheet ?
> Thanks
> Antoine
>sql
Showing posts with label parameters. Show all posts
Showing posts with label parameters. Show all posts
Thursday, March 29, 2012
Tuesday, March 27, 2012
Create a string from report parameters, return checksum
Hi,
We want to add an extra checksum parameter to our RS report, and make the
report work only if the correct checksum is entered/passed based on all the
other parameter fields, as a simple security check when rendering reports
from a system with its own security system (users not in AD/domains).
1. How do I construct a stored procedure that creates a string consisting of
all but the last parameter and returns a checksum ? (checksums we know how to
create from strings)
2. How do I filter my report based on that ?
"where ... and @.checksum = checksum_proc.checksum" ?
Report and parameters ex.
report1: product_string, checksum
report2: customer_string, department_string, date_string, checksum
Or is there a better way ?
/JeromeOn Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
wrote:
> Hi,
> We want to add an extra checksum parameter to our RS report, and make the
> report work only if the correct checksum is entered/passed based on all the
> other parameter fields, as a simple security check when rendering reports
> from a system with its own security system (users not in AD/domains).
> 1. How do I construct a stored procedure that creates a string consisting of
> all but the last parameter and returns a checksum ? (checksums we know how to
> create from strings)
> 2. How do I filter my report based on that ?
> "where ... and @.checksum = checksum_proc.checksum" ?
> Report and parameters ex.
> report1: product_string, checksum
> report2: customer_string, department_string, date_string, checksum
> Or is there a better way ?
> /Jerome
Here is another option you might want to consider. You might want to
have a report parameter that has a string datatype and is used as a
password. You might pass the password entered by the user back to the
stored procedure and if the password matches one in a list somewhere
(table, etc) you send the complete dataset back to the report;
otherwise, you send back no data or a single line of all nulls or a
text message of "you do not have correct permissions to access this
report" to the report. Also, to enforce security, you might create the
stored procedure using "with encryption" that way noone can look at
the logic -or- you could add a certain number of characters to the
true password in a table and just remove them when you do the table
lookup for the passwords. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks,
The report must be started with a URL (cant render reports from our system)
and should only be allowed for a certain combinations of parameters set by
our program, ex product 100 with department A. The user must not seconds
later go to the reportserver and manually enter product 100 with department B
with the same password.
If using a "one-time" password parameter, will this mean we should store all
parameters in a table as well ? If we delete the stored password in the
procedure, is it possible for the user to re-render the report to Excel etc
?
A last checksum question: Is there a function to be used in a stored
procedure that gets current report's parameter 1, 2 ... ?
/Jerome k
"EMartinez" wrote:
> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
> wrote:
> > Hi,
> >
> > We want to add an extra checksum parameter to our RS report, and make the
> > report work only if the correct checksum is entered/passed based on all the
> > other parameter fields, as a simple security check when rendering reports
> > from a system with its own security system (users not in AD/domains).
> >
> > 1. How do I construct a stored procedure that creates a string consisting of
> > all but the last parameter and returns a checksum ? (checksums we know how to
> > create from strings)
> >
> > 2. How do I filter my report based on that ?
> > "where ... and @.checksum = checksum_proc.checksum" ?
> >
> > Report and parameters ex.
> > report1: product_string, checksum
> > report2: customer_string, department_string, date_string, checksum
> >
> > Or is there a better way ?
> >
> > /Jerome
>
> Here is another option you might want to consider. You might want to
> have a report parameter that has a string datatype and is used as a
> password. You might pass the password entered by the user back to the
> stored procedure and if the password matches one in a list somewhere
> (table, etc) you send the complete dataset back to the report;
> otherwise, you send back no data or a single line of all nulls or a
> text message of "you do not have correct permissions to access this
> report" to the report. Also, to enforce security, you might create the
> stored procedure using "with encryption" that way noone can look at
> the logic -or- you could add a certain number of characters to the
> true password in a table and just remove them when you do the table
> lookup for the passwords. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||What do you propose to do to reject the user's request if it is invalid by
the rules (see thread: "Am I crazy or is there no form validation" in this
forum). I'm not arguing with you, just wondering what you think is the best
strategy here.
IAC, if I were faced with your requirement, I would probably have the users
submit their reporting URL to a small proxy web application that did the
validation you require against the user's credentials, etc. If the tests
passed (whatever they are) then the proxy would submit the request to the
report server and return the server's response.
By "small" I mean that you could probably get away with a simple APX page
for this, and in addition you would have the ability to return whatever type
of user feedback you wanted if the tests failed.
>L<
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:8A257C82-B978-446C-A2B7-0FFAC3773B32@.microsoft.com...
> Thanks,
> The report must be started with a URL (cant render reports from our
> system)
> and should only be allowed for a certain combinations of parameters set by
> our program, ex product 100 with department A. The user must not seconds
> later go to the reportserver and manually enter product 100 with
> department B
> with the same password.
> If using a "one-time" password parameter, will this mean we should store
> all
> parameters in a table as well ? If we delete the stored password in the
> procedure, is it possible for the user to re-render the report to Excel
> etc
> ?
> A last checksum question: Is there a function to be used in a stored
> procedure that gets current report's parameter 1, 2 ... ?
> /Jerome k
> "EMartinez" wrote:
>> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
>> wrote:
>> > Hi,
>> >
>> > We want to add an extra checksum parameter to our RS report, and make
>> > the
>> > report work only if the correct checksum is entered/passed based on all
>> > the
>> > other parameter fields, as a simple security check when rendering
>> > reports
>> > from a system with its own security system (users not in AD/domains).
>> >
>> > 1. How do I construct a stored procedure that creates a string
>> > consisting of
>> > all but the last parameter and returns a checksum ? (checksums we know
>> > how to
>> > create from strings)
>> >
>> > 2. How do I filter my report based on that ?
>> > "where ... and @.checksum = checksum_proc.checksum" ?
>> >
>> > Report and parameters ex.
>> > report1: product_string, checksum
>> > report2: customer_string, department_string, date_string, checksum
>> >
>> > Or is there a better way ?
>> >
>> > /Jerome
>>
>> Here is another option you might want to consider. You might want to
>> have a report parameter that has a string datatype and is used as a
>> password. You might pass the password entered by the user back to the
>> stored procedure and if the password matches one in a list somewhere
>> (table, etc) you send the complete dataset back to the report;
>> otherwise, you send back no data or a single line of all nulls or a
>> text message of "you do not have correct permissions to access this
>> report" to the report. Also, to enforce security, you might create the
>> stored procedure using "with encryption" that way noone can look at
>> the logic -or- you could add a certain number of characters to the
>> true password in a table and just remove them when you do the table
>> lookup for the passwords. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>
We want to add an extra checksum parameter to our RS report, and make the
report work only if the correct checksum is entered/passed based on all the
other parameter fields, as a simple security check when rendering reports
from a system with its own security system (users not in AD/domains).
1. How do I construct a stored procedure that creates a string consisting of
all but the last parameter and returns a checksum ? (checksums we know how to
create from strings)
2. How do I filter my report based on that ?
"where ... and @.checksum = checksum_proc.checksum" ?
Report and parameters ex.
report1: product_string, checksum
report2: customer_string, department_string, date_string, checksum
Or is there a better way ?
/JeromeOn Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
wrote:
> Hi,
> We want to add an extra checksum parameter to our RS report, and make the
> report work only if the correct checksum is entered/passed based on all the
> other parameter fields, as a simple security check when rendering reports
> from a system with its own security system (users not in AD/domains).
> 1. How do I construct a stored procedure that creates a string consisting of
> all but the last parameter and returns a checksum ? (checksums we know how to
> create from strings)
> 2. How do I filter my report based on that ?
> "where ... and @.checksum = checksum_proc.checksum" ?
> Report and parameters ex.
> report1: product_string, checksum
> report2: customer_string, department_string, date_string, checksum
> Or is there a better way ?
> /Jerome
Here is another option you might want to consider. You might want to
have a report parameter that has a string datatype and is used as a
password. You might pass the password entered by the user back to the
stored procedure and if the password matches one in a list somewhere
(table, etc) you send the complete dataset back to the report;
otherwise, you send back no data or a single line of all nulls or a
text message of "you do not have correct permissions to access this
report" to the report. Also, to enforce security, you might create the
stored procedure using "with encryption" that way noone can look at
the logic -or- you could add a certain number of characters to the
true password in a table and just remove them when you do the table
lookup for the passwords. Hope this is helpful.
Regards,
Enrique Martinez
Sr. SQL Server Developer|||Thanks,
The report must be started with a URL (cant render reports from our system)
and should only be allowed for a certain combinations of parameters set by
our program, ex product 100 with department A. The user must not seconds
later go to the reportserver and manually enter product 100 with department B
with the same password.
If using a "one-time" password parameter, will this mean we should store all
parameters in a table as well ? If we delete the stored password in the
procedure, is it possible for the user to re-render the report to Excel etc
?
A last checksum question: Is there a function to be used in a stored
procedure that gets current report's parameter 1, 2 ... ?
/Jerome k
"EMartinez" wrote:
> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
> wrote:
> > Hi,
> >
> > We want to add an extra checksum parameter to our RS report, and make the
> > report work only if the correct checksum is entered/passed based on all the
> > other parameter fields, as a simple security check when rendering reports
> > from a system with its own security system (users not in AD/domains).
> >
> > 1. How do I construct a stored procedure that creates a string consisting of
> > all but the last parameter and returns a checksum ? (checksums we know how to
> > create from strings)
> >
> > 2. How do I filter my report based on that ?
> > "where ... and @.checksum = checksum_proc.checksum" ?
> >
> > Report and parameters ex.
> > report1: product_string, checksum
> > report2: customer_string, department_string, date_string, checksum
> >
> > Or is there a better way ?
> >
> > /Jerome
>
> Here is another option you might want to consider. You might want to
> have a report parameter that has a string datatype and is used as a
> password. You might pass the password entered by the user back to the
> stored procedure and if the password matches one in a list somewhere
> (table, etc) you send the complete dataset back to the report;
> otherwise, you send back no data or a single line of all nulls or a
> text message of "you do not have correct permissions to access this
> report" to the report. Also, to enforce security, you might create the
> stored procedure using "with encryption" that way noone can look at
> the logic -or- you could add a certain number of characters to the
> true password in a table and just remove them when you do the table
> lookup for the passwords. Hope this is helpful.
> Regards,
> Enrique Martinez
> Sr. SQL Server Developer
>|||What do you propose to do to reject the user's request if it is invalid by
the rules (see thread: "Am I crazy or is there no form validation" in this
forum). I'm not arguing with you, just wondering what you think is the best
strategy here.
IAC, if I were faced with your requirement, I would probably have the users
submit their reporting URL to a small proxy web application that did the
validation you require against the user's credentials, etc. If the tests
passed (whatever they are) then the proxy would submit the request to the
report server and return the server's response.
By "small" I mean that you could probably get away with a simple APX page
for this, and in addition you would have the ability to return whatever type
of user feedback you wanted if the tests failed.
>L<
"/jerome k" <jeromek@.discussions.microsoft.com> wrote in message
news:8A257C82-B978-446C-A2B7-0FFAC3773B32@.microsoft.com...
> Thanks,
> The report must be started with a URL (cant render reports from our
> system)
> and should only be allowed for a certain combinations of parameters set by
> our program, ex product 100 with department A. The user must not seconds
> later go to the reportserver and manually enter product 100 with
> department B
> with the same password.
> If using a "one-time" password parameter, will this mean we should store
> all
> parameters in a table as well ? If we delete the stored password in the
> procedure, is it possible for the user to re-render the report to Excel
> etc
> ?
> A last checksum question: Is there a function to be used in a stored
> procedure that gets current report's parameter 1, 2 ... ?
> /Jerome k
> "EMartinez" wrote:
>> On Mar 5, 6:04 am, /jerome k <jero...@.discussions.microsoft.com>
>> wrote:
>> > Hi,
>> >
>> > We want to add an extra checksum parameter to our RS report, and make
>> > the
>> > report work only if the correct checksum is entered/passed based on all
>> > the
>> > other parameter fields, as a simple security check when rendering
>> > reports
>> > from a system with its own security system (users not in AD/domains).
>> >
>> > 1. How do I construct a stored procedure that creates a string
>> > consisting of
>> > all but the last parameter and returns a checksum ? (checksums we know
>> > how to
>> > create from strings)
>> >
>> > 2. How do I filter my report based on that ?
>> > "where ... and @.checksum = checksum_proc.checksum" ?
>> >
>> > Report and parameters ex.
>> > report1: product_string, checksum
>> > report2: customer_string, department_string, date_string, checksum
>> >
>> > Or is there a better way ?
>> >
>> > /Jerome
>>
>> Here is another option you might want to consider. You might want to
>> have a report parameter that has a string datatype and is used as a
>> password. You might pass the password entered by the user back to the
>> stored procedure and if the password matches one in a list somewhere
>> (table, etc) you send the complete dataset back to the report;
>> otherwise, you send back no data or a single line of all nulls or a
>> text message of "you do not have correct permissions to access this
>> report" to the report. Also, to enforce security, you might create the
>> stored procedure using "with encryption" that way noone can look at
>> the logic -or- you could add a certain number of characters to the
>> true password in a table and just remove them when you do the table
>> lookup for the passwords. Hope this is helpful.
>> Regards,
>> Enrique Martinez
>> Sr. SQL Server Developer
>>
Tuesday, March 20, 2012
CREATE / EXECUTE Stored Procedure (SQL Server 7.0)
Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
CREATE / EXECUTE Stored Procedure (SQL Server 7.0)
Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>sql
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regardsI guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
--
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>sql
CREATE / EXECUTE Stored Procedure (SQL Server 7.0)
Hi,
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regards
I guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
I create one stored procedure to return some objects, this procedure accepts
two parameters and this parameters have a default value.
When i execute the procedure with no values the default parameters are used
and every thing goes fine, when i need to input these parameters the
following error appears.
--exec procedure_name db_name, user_name
Error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'user_name'.
Can you help me.
Best regards
I guess it should be
exec procedure_name 'db_name', 'user_name'
Roji. P. Thomas
Net Asset Management
https://www.netassetmanagement.com
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
> accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
> used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
|||If quoting the parameters doesn't fix it, then post the SP source so we can
see.
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"CC&JM" <CCJM@.discussions.microsoft.com> wrote in message
news:4A3D9388-DB65-4CAA-9F29-13C2D5B735AE@.microsoft.com...
> Hi,
> I create one stored procedure to return some objects, this procedure
accepts
> two parameters and this parameters have a default value.
> When i execute the procedure with no values the default parameters are
used
> and every thing goes fine, when i need to input these parameters the
> following error appears.
> --exec procedure_name db_name, user_name
> Error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'user_name'.
> Can you help me.
> Best regards
>
create / alter a store procedure
hello,
i want to create a stored procedure which i can use to create / alter a
other stored procedure.
The store procedure should have 2 parameters name and content of the stored
procedure
I think this could be made with dynamic sql...
Any ideas how to do this.
I want to use the this to alter stored procedures from inside asp.net
calling the new procedures with the adequate parameters:
thanksHi
> The store procedure should have 2 parameters name and content of the
> stored
> procedure
Why? Don't you know a name of SP nor parameter's name?
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:3CEB6694-87D2-40D5-A53B-E84410E35B5F@.microsoft.com...
> hello,
> i want to create a stored procedure which i can use to create / alter a
> other stored procedure.
> The store procedure should have 2 parameters name and content of the
> stored
> procedure
> I think this could be made with dynamic sql...
> Any ideas how to do this.
> I want to use the this to alter stored procedures from inside asp.net
> calling the new procedures with the adequate parameters:
> thanks
>|||hi Xavier,
are you think over the possibility to use templates from QA?
regards,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:
> Hi
>
> Why? Don't you know a name of SP nor parameter's name?
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:3CEB6694-87D2-40D5-A53B-E84410E35B5F@.microsoft.com...
>
>|||i try to explain better
If i want to create a new SP -
Name:SalesReport
SPContent: is the string value of a tested StoredProcedures in the query
analyser
example
CREATE PROCEDURE MyNewSP
--@.ParSlotid int
AS
Declare @.PSlot int
Declare @.actionNr int
Declare @.slotid int
Declare @.actionId int
DECLARE @.StartDate Varchar(10)
DECLARE @.EndDate Varchar(10)
DECLARE @.NrRows int
set @.actionNr=1254
SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
.....
After i know the name and the content of the SP i want to call al a SP
example
exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that the
'SalesReport 'sp is created
My question is what must be the content of "CreateNewSP " to realize this
thanks
"Xavier" wrote:
> hello,
> i want to create a stored procedure which i can use to create / alter a
> other stored procedure.
> The store procedure should have 2 parameters name and content of the store
d
> procedure
> I think this could be made with dynamic sql...
> Any ideas how to do this.
> I want to use the this to alter stored procedures from inside asp.net
> calling the new procedures with the adequate parameters:
> thanks
>|||Xavier
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
>
What version are using?
The above statement you cannot use in SQL Server 2000 and you are about to
replace it with dynamic sql , I'm sure you are aware ofsome drawbacks using
dymanic sql in the production enviroment.
I don't know your business requiremnts ,so I make a guess , why not to
have a stored proedure for reporting? I mean , why do you want to supply a
DDL for stored procedure as a parameter? Or perhpas I did not understand
you, did I?
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that
> the
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||Rather than doing this from your ASP.NET application directly by connecting
to SQL Server, use SQLDMO. This provides a decent object model over the
database and you can use it to create/modify and delete objects of various
types. Here is an article that should help you:
http://www.sqljunkies.com/Article/1...BCA1D8C97D.scuk
By using SQLDMO you avoid creating a procedure in the database to churn out
other procedures. This way your solution can move easily between databases
if need be.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that
> the
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||No vull tocar el collons però segueixo sense entendre-ho
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Xavier" wrote:
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that t
he
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||hello SriSamp,
exactly that i want to do for administrative scopes...
Because on my IIS - server there is no instance of SQL - i can not use
SQLDMO?!
So my idea was to create a SP - which creates new SPs
thanks
"SriSamp" wrote:
> Rather than doing this from your ASP.NET application directly by connectin
g
> to SQL Server, use SQLDMO. This provides a decent object model over the
> database and you can use it to create/modify and delete objects of various
> types. Here is an article that should help you:
> http://www.sqljunkies.com/Article/1...srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
>
>|||
> What version are using?
i use SQL2k amd ASP.Net on a separat server
>you are about to replace it with dynamic sql
but how looks the SP which can use dynamic sql - have you a simple example
> why not to have a stored proedure for reporting?
because this SP are - not very complex and only valid for 2-6 w
s - and
maintened by some user who did not have access over QA - but only over
Web-frontend with impersonation.
thanks|||have you any example how can this be done?
thanks
"Enric" wrote:
> hi Xavier,
> are you think over the possibility to use templates from QA?
> regards,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Uri Dimant" wrote:
>
i want to create a stored procedure which i can use to create / alter a
other stored procedure.
The store procedure should have 2 parameters name and content of the stored
procedure
I think this could be made with dynamic sql...
Any ideas how to do this.
I want to use the this to alter stored procedures from inside asp.net
calling the new procedures with the adequate parameters:
thanksHi
> The store procedure should have 2 parameters name and content of the
> stored
> procedure
Why? Don't you know a name of SP nor parameter's name?
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:3CEB6694-87D2-40D5-A53B-E84410E35B5F@.microsoft.com...
> hello,
> i want to create a stored procedure which i can use to create / alter a
> other stored procedure.
> The store procedure should have 2 parameters name and content of the
> stored
> procedure
> I think this could be made with dynamic sql...
> Any ideas how to do this.
> I want to use the this to alter stored procedures from inside asp.net
> calling the new procedures with the adequate parameters:
> thanks
>|||hi Xavier,
are you think over the possibility to use templates from QA?
regards,
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Uri Dimant" wrote:
> Hi
>
> Why? Don't you know a name of SP nor parameter's name?
>
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:3CEB6694-87D2-40D5-A53B-E84410E35B5F@.microsoft.com...
>
>|||i try to explain better
If i want to create a new SP -
Name:SalesReport
SPContent: is the string value of a tested StoredProcedures in the query
analyser
example
CREATE PROCEDURE MyNewSP
--@.ParSlotid int
AS
Declare @.PSlot int
Declare @.actionNr int
Declare @.slotid int
Declare @.actionId int
DECLARE @.StartDate Varchar(10)
DECLARE @.EndDate Varchar(10)
DECLARE @.NrRows int
set @.actionNr=1254
SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
.....
After i know the name and the content of the SP i want to call al a SP
example
exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that the
'SalesReport 'sp is created
My question is what must be the content of "CreateNewSP " to realize this
thanks
"Xavier" wrote:
> hello,
> i want to create a stored procedure which i can use to create / alter a
> other stored procedure.
> The store procedure should have 2 parameters name and content of the store
d
> procedure
> I think this could be made with dynamic sql...
> Any ideas how to do this.
> I want to use the this to alter stored procedures from inside asp.net
> calling the new procedures with the adequate parameters:
> thanks
>|||Xavier
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
>
What version are using?
The above statement you cannot use in SQL Server 2000 and you are about to
replace it with dynamic sql , I'm sure you are aware ofsome drawbacks using
dymanic sql in the production enviroment.
I don't know your business requiremnts ,so I make a guess , why not to
have a stored proedure for reporting? I mean , why do you want to supply a
DDL for stored procedure as a parameter? Or perhpas I did not understand
you, did I?
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that
> the
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||Rather than doing this from your ASP.NET application directly by connecting
to SQL Server, use SQLDMO. This provides a decent object model over the
database and you can use it to create/modify and delete objects of various
types. Here is an article that should help you:
http://www.sqljunkies.com/Article/1...BCA1D8C97D.scuk
By using SQLDMO you avoid creating a procedure in the database to churn out
other procedures. This way your solution can move easily between databases
if need be.
--
HTH,
SriSamp
Email: srisamp@.gmail.com
Blog: http://blogs.sqlxml.org/srinivassampath
URL: http://www32.brinkster.com/srisamp
"Xavier" <Xavier@.discussions.microsoft.com> wrote in message
news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that
> the
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||No vull tocar el collons però segueixo sense entendre-ho
--
Please post DDL, DCL and DML statements as well as any error message in
order to understand better your request. It''''s hard to provide information
without seeing the code. location: Alicante (ES)
"Xavier" wrote:
> i try to explain better
> If i want to create a new SP -
> Name:SalesReport
> SPContent: is the string value of a tested StoredProcedures in the query
> analyser
> example
> CREATE PROCEDURE MyNewSP
> --@.ParSlotid int
> AS
> Declare @.PSlot int
> Declare @.actionNr int
> Declare @.slotid int
> Declare @.actionId int
> DECLARE @.StartDate Varchar(10)
> DECLARE @.EndDate Varchar(10)
> DECLARE @.NrRows int
> set @.actionNr=1254
> SELECT TOP 1 @.actionId=dbo.TFB_Gas .........
> .....
> After i know the name and the content of the SP i want to call al a SP
> example
> exec CreateNewSP 'SalesReport' 'Content new SP for SalesReport' so that t
he
> 'SalesReport 'sp is created
> My question is what must be the content of "CreateNewSP " to realize this
> thanks
>
>
>
> "Xavier" wrote:
>|||hello SriSamp,
exactly that i want to do for administrative scopes...
Because on my IIS - server there is no instance of SQL - i can not use
SQLDMO?!
So my idea was to create a SP - which creates new SPs
thanks
"SriSamp" wrote:
> Rather than doing this from your ASP.NET application directly by connectin
g
> to SQL Server, use SQLDMO. This provides a decent object model over the
> database and you can use it to create/modify and delete objects of various
> types. Here is an article that should help you:
> http://www.sqljunkies.com/Article/1...srinivassampath
> URL: http://www32.brinkster.com/srisamp
> "Xavier" <Xavier@.discussions.microsoft.com> wrote in message
> news:2E896DE6-D90D-4F90-B81B-637F8A5F1640@.microsoft.com...
>
>|||
> What version are using?
i use SQL2k amd ASP.Net on a separat server
>you are about to replace it with dynamic sql
but how looks the SP which can use dynamic sql - have you a simple example
> why not to have a stored proedure for reporting?
because this SP are - not very complex and only valid for 2-6 w
s - andmaintened by some user who did not have access over QA - but only over
Web-frontend with impersonation.
thanks|||have you any example how can this be done?
thanks
"Enric" wrote:
> hi Xavier,
> are you think over the possibility to use templates from QA?
> regards,
> --
> Please post DDL, DCL and DML statements as well as any error message in
> order to understand better your request. It''''s hard to provide informati
on
> without seeing the code. location: Alicante (ES)
>
> "Uri Dimant" wrote:
>
Monday, March 19, 2012
CR11 - How do I choose a NULL value (or ALL RECORDS) in a dynamic parameter?
We are using CR11 and our own OLE DB provider that, in turn, uses our SQL Server database.
We are creating dynamic parameters that allow the user to pick from all the distinct values that a field can have. All but NULL, and we need to allow the user to pick NULL if there's any in the data.
In reality, we just need to find a way to let the user pick ALL values. However, if we create a dynamic parameter that doesn't accept multiple values, the user is forced to pick one of the available values or, if he doesn't, then CR will say the value is not valid when you hit OK to the parameter selections. If instead we create a parameter that accepts multiple values instead, the user has the option to pick all the available values, which at first seems to solve the problem, but these wouldn't include NULLs and those records would be filtered out in the results.
Thank you in advance.Anybody?|||You can try using two separate parameters in your Crystal Report (http://www.shelko.com). One can be a choice between all or specific and the second could be your list of specifics. Then base your selection criteria on the combination of these two parameters.
We are creating dynamic parameters that allow the user to pick from all the distinct values that a field can have. All but NULL, and we need to allow the user to pick NULL if there's any in the data.
In reality, we just need to find a way to let the user pick ALL values. However, if we create a dynamic parameter that doesn't accept multiple values, the user is forced to pick one of the available values or, if he doesn't, then CR will say the value is not valid when you hit OK to the parameter selections. If instead we create a parameter that accepts multiple values instead, the user has the option to pick all the available values, which at first seems to solve the problem, but these wouldn't include NULLs and those records would be filtered out in the results.
Thank you in advance.Anybody?|||You can try using two separate parameters in your Crystal Report (http://www.shelko.com). One can be a choice between all or specific and the second could be your list of specifics. Then base your selection criteria on the combination of these two parameters.
Subscribe to:
Posts (Atom)