We've got some code that has been using a SqlCommand with the commandtype
set to Text. It is running a stored procedure by building a StringBuilder
object to string together the parameters and then execute. The problem I am
running into is that if I add a parameter to the commands paramter
collection and designate it as the return value in the "direction"
parameter, I never get the value returned.
I'm assuming it is because when executing a stored proc in this manner
(instead of using commandtype of StoredProcedure) that the stored procedure
is actually considered to be nested within the "procedural" code I'm
executing as text. Does this make sense? I hope that explanation is clear
enough. I really need to be able to access these return codes without
rewriting the world. As it is now they have all their stored procs doing a
"select ##" to send a return code back to their C# code. This is ludicrous
and I cannot reuse any of these storedprocs from another stored proc. I
don't see anyway to get the select results of a nested stored proc...
I'm on a tight deadline here haven't much time to solve this before writing
it over would be faster.
Any help is greatly appreciated!Hi
Did you check out:
http://msdn.microsoft.com/library/d...r />
outas.asp
The return values are only available once all result sets have been
processed.
John
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>|||> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
If you must stick with CommandType.Text for now, you might try passing the
return code value as an output parameter value. At least that will lessen
the immediate code changes needed.
As you probably know, it's generally a bad technique to build literal
strings instead of using parameterized procs and queries. When you get
around to converting to CommandType.StoredProcedure, ditch the StringBuilder
and use input parameters instead as well as the proper return value
parameter.
Hope this helps.
Dan Guzman
SQL Server MVP
"Tim Greenwood" <tim_greenwood A-T yahoo D-O-T com> wrote in message
news:ejnT8RHFGHA.3056@.TK2MSFTNGP09.phx.gbl...
> We've got some code that has been using a SqlCommand with the commandtype
> set to Text. It is running a stored procedure by building a StringBuilder
> object to string together the parameters and then execute. The problem I
> am running into is that if I add a parameter to the commands paramter
> collection and designate it as the return value in the "direction"
> parameter, I never get the value returned.
> I'm assuming it is because when executing a stored proc in this manner
> (instead of using commandtype of StoredProcedure) that the stored
> procedure is actually considered to be nested within the "procedural" code
> I'm executing as text. Does this make sense? I hope that explanation is
> clear enough. I really need to be able to access these return codes
> without rewriting the world. As it is now they have all their stored
> procs doing a "select ##" to send a return code back to their C# code.
> This is ludicrous and I cannot reuse any of these storedprocs from another
> stored proc. I don't see anyway to get the select results of a nested
> stored proc...
> I'm on a tight deadline here haven't much time to solve this before
> writing it over would be faster.
> Any help is greatly appreciated!
>
Showing posts with label code. Show all posts
Showing posts with label code. Show all posts
Thursday, March 29, 2012
getting a stored procedures code
I don't know if this is possible. However, what i am attempting to do is
using C#'s window forms. I open up an excell sheet stored in my windows
form. The excel sheet stores names of the stored procedures in that
database. I want to know if it's possible to click on that stored
procedure to open up a link to display the code of that stored procedure of
course in a read only mode.
using C#'s window forms. I open up an excell sheet stored in my windows
form. The excel sheet stores names of the stored procedures in that
database. I want to know if it's possible to click on that stored
procedure to open up a link to display the code of that stored procedure of
course in a read only mode.
any suggestions...Two possible options: Query the syscomments table (See Books Online for
details) or use the Script method in the SQLDMO API (a COM API, not
..NET).
--
David Portas
SQL Server MVP
--|||using sp_helptext <Stored Procedure Name>
enables you to see the stored procedure code
best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
------------
*** Sent via Developersdex http://www.developersdex.com ***sql
getting a store procedure's result
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.c om...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
|||Use output parameters for SQL procedure from which you wish to assign the
values to the variables. (More information look for: "Returning Data Using a
Return Code" in SQL books online)
HTH
Saleem@.sqlnt.com
"Silvio" wrote:
> Hey guys,
> I have a store procedure that returns a recorset. Here's an example:
> create procedure ABC as
> --some code here that works with @.x and @.y.. and then the last line
> of the proc:
> SELECT @.x,@.y
> That procedure has been used only in a vb code, so they consume the
> result with no problem. Now I need to call that procedure within a
> different proc, and I need to get back the final values of @.x and @.y.
> Is there a way to get these results back in a variable as I call the
> store proc?
> Thanks,
>
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.c om...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
|||Use output parameters for SQL procedure from which you wish to assign the
values to the variables. (More information look for: "Returning Data Using a
Return Code" in SQL books online)
HTH
Saleem@.sqlnt.com
"Silvio" wrote:
> Hey guys,
> I have a store procedure that returns a recorset. Here's an example:
> create procedure ABC as
> --some code here that works with @.x and @.y.. and then the last line
> of the proc:
> SELECT @.x,@.y
> That procedure has been used only in a vb code, so they consume the
> result with no problem. Now I need to call that procedure within a
> different proc, and I need to get back the final values of @.x and @.y.
> Is there a way to get these results back in a variable as I call the
> store proc?
> Thanks,
>
getting a store procedure's result
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.com...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,You could create the sp with output parameters, if you always return only
one row. This way, you can easily call it from an application as well as
from another procedure. If recreating this sp with output parameters is not
an option, then you have to use the INSERT...EXEC syntax to store the data
to a table, and then select from that table.
More info on this at: http://www.sommarskog.se/share_data.html
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Silvio" <silviocortes@.yahoo.com> wrote in message
news:40c887f5.0409130837.7e9e4bfd@.posting.google.com...
Hey guys,
I have a store procedure that returns a recorset. Here's an example:
create procedure ABC as
--some code here that works with @.x and @.y.. and then the last line
of the proc:
SELECT @.x,@.y
That procedure has been used only in a vb code, so they consume the
result with no problem. Now I need to call that procedure within a
different proc, and I need to get back the final values of @.x and @.y.
Is there a way to get these results back in a variable as I call the
store proc?
Thanks,
Monday, March 26, 2012
GetScheduleProperties()
I create a report subscription in code. Everything works fine:
string id = service.CreateSubscription(reportName, extensionSettings,
description, eventType,matchDataParam, parameters );
I get the id back and store it in the database. When I try to pass the id
to GetScheduleProperties() I get the following error:
"The schedule '7acaf6e8-8cb5-499b-b052-c57d3f0b20a6' cannot be found. The
schedule identifier that is provided to an operation cannot be located in
the report server database. "
What's going on? I looked in the Subscriptions table in the ReportServer
database and I see the subscription ID there...You want to call GetSubscriptionProperties, not GetScheduleProperties.
GetScheduleProperties returns information about shared schedules only.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"FNDS" <absolutelynospam@.nodomain_.com> wrote in message
news:%23lBbzT65EHA.2180@.TK2MSFTNGP12.phx.gbl...
>I create a report subscription in code. Everything works fine:
> string id = service.CreateSubscription(reportName, extensionSettings,
> description, eventType,matchDataParam, parameters );
> I get the id back and store it in the database. When I try to pass the id
> to GetScheduleProperties() I get the following error:
> "The schedule '7acaf6e8-8cb5-499b-b052-c57d3f0b20a6' cannot be found. The
> schedule identifier that is provided to an operation cannot be located in
> the report server database. "
> What's going on? I looked in the Subscriptions table in the ReportServer
> database and I see the subscription ID there...
>
string id = service.CreateSubscription(reportName, extensionSettings,
description, eventType,matchDataParam, parameters );
I get the id back and store it in the database. When I try to pass the id
to GetScheduleProperties() I get the following error:
"The schedule '7acaf6e8-8cb5-499b-b052-c57d3f0b20a6' cannot be found. The
schedule identifier that is provided to an operation cannot be located in
the report server database. "
What's going on? I looked in the Subscriptions table in the ReportServer
database and I see the subscription ID there...You want to call GetSubscriptionProperties, not GetScheduleProperties.
GetScheduleProperties returns information about shared schedules only.
--
-Daniel
This posting is provided "AS IS" with no warranties, and confers no rights.
"FNDS" <absolutelynospam@.nodomain_.com> wrote in message
news:%23lBbzT65EHA.2180@.TK2MSFTNGP12.phx.gbl...
>I create a report subscription in code. Everything works fine:
> string id = service.CreateSubscription(reportName, extensionSettings,
> description, eventType,matchDataParam, parameters );
> I get the id back and store it in the database. When I try to pass the id
> to GetScheduleProperties() I get the following error:
> "The schedule '7acaf6e8-8cb5-499b-b052-c57d3f0b20a6' cannot be found. The
> schedule identifier that is provided to an operation cannot be located in
> the report server database. "
> What's going on? I looked in the Subscriptions table in the ReportServer
> database and I see the subscription ID there...
>
GetReportParameters
I am trying to use GetReportParameters method of the reportserver webservice. Here is the code.
ReportingService.ReportingService rService = new ReportingService.ReportingService();
rService.Credentials = System.Net.CredentialCache.DefaultCredentials;
string historyID = null;
bool forRendering = true;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters;
parameters = rService.GetReportParameters
(
"/Demo/TransactionReport",
historyID,
forRendering,
values,
credentials
);
foreach(ValidValue v in parameters[0].ValidValues)
{
Response.Write(v.Label + ": " + v.Value);
Response.Write("<br>");
}
But when i executed the above code, i am getting the following error..
System.Web.Services.Protocols.SoapException: The permissions granted to user 'DPA32W126\ASPNET' are insufficient for performing this operation. -->
I understand there is no problem with the code, but security settings needs to be changed for it to work.
Can anyone guide me on how to make the above code working.
Thankyou,
Regards,
Rajesh Jagadeesan.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If your app is an ASP.NET app, you need to add identity impersonate=true in
our web.config. Otherwise, your app will run as the ASPNET account, not as
your client's account.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:#fTKv5NaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> I am trying to use GetReportParameters method of the reportserver
webservice. Here is the code.
> ReportingService.ReportingService rService = new
ReportingService.ReportingService();
> rService.Credentials = System.Net.CredentialCache.DefaultCredentials;
> string historyID = null;
> bool forRendering = true;
> ParameterValue[] values = null;
> DataSourceCredentials[] credentials = null;
> ReportParameter[] parameters;
> parameters = rService.GetReportParameters
> (
> "/Demo/TransactionReport",
> historyID,
> forRendering,
> values,
> credentials
> );
> foreach(ValidValue v in parameters[0].ValidValues)
> {
> Response.Write(v.Label + ": " + v.Value);
> Response.Write("<br>");
> }
> But when i executed the above code, i am getting the following error..
> System.Web.Services.Protocols.SoapException: The permissions granted to
user 'DPA32W126\ASPNET' are insufficient for performing this operation. -->
> I understand there is no problem with the code, but security settings
needs to be changed for it to work.
> Can anyone guide me on how to make the above code working.
> Thankyou,
> Regards,
> Rajesh Jagadeesan.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||Hi Jenny:
You can use the NetworkCredential class from the System.Net namespace.
Also, my article might be of some help:
http://odetocode.com/Articles/216.aspx
--
Scott
http://www.OdeToCode.com
On Mon, 13 Sep 2004 13:13:08 -0700, "Jenny"
<Jenny@.discussions.microsoft.com> wrote:
>Hi! I am having a similar problem. I am creating a custom interface for
>users to gather parameters and then select reports to view. I want to only
>pass in the parameters that a given report needs rather then all the
>parameters I am gathering. I am getting this error when the
>GetReportParameters runs:
>The request failed with HTTP status 401: Unauthorized.
>I tried adding in the identity impersonate = true but it didn't do anything.
> Can I change the credentials that are passed in? What else can I check? Is
>there another way to check what parameters are needed for a given report?
>Thank you for the help!
>Jenny
>
ReportingService.ReportingService rService = new ReportingService.ReportingService();
rService.Credentials = System.Net.CredentialCache.DefaultCredentials;
string historyID = null;
bool forRendering = true;
ParameterValue[] values = null;
DataSourceCredentials[] credentials = null;
ReportParameter[] parameters;
parameters = rService.GetReportParameters
(
"/Demo/TransactionReport",
historyID,
forRendering,
values,
credentials
);
foreach(ValidValue v in parameters[0].ValidValues)
{
Response.Write(v.Label + ": " + v.Value);
Response.Write("<br>");
}
But when i executed the above code, i am getting the following error..
System.Web.Services.Protocols.SoapException: The permissions granted to user 'DPA32W126\ASPNET' are insufficient for performing this operation. -->
I understand there is no problem with the code, but security settings needs to be changed for it to work.
Can anyone guide me on how to make the above code working.
Thankyou,
Regards,
Rajesh Jagadeesan.
--
Posted using Wimdows.net NntpNews Component -
Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.If your app is an ASP.NET app, you need to add identity impersonate=true in
our web.config. Otherwise, your app will run as the ASPNET account, not as
your client's account.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"SqlJunkies User" <User@.-NOSPAM-SqlJunkies.com> wrote in message
news:#fTKv5NaEHA.2972@.TK2MSFTNGP12.phx.gbl...
> I am trying to use GetReportParameters method of the reportserver
webservice. Here is the code.
> ReportingService.ReportingService rService = new
ReportingService.ReportingService();
> rService.Credentials = System.Net.CredentialCache.DefaultCredentials;
> string historyID = null;
> bool forRendering = true;
> ParameterValue[] values = null;
> DataSourceCredentials[] credentials = null;
> ReportParameter[] parameters;
> parameters = rService.GetReportParameters
> (
> "/Demo/TransactionReport",
> historyID,
> forRendering,
> values,
> credentials
> );
> foreach(ValidValue v in parameters[0].ValidValues)
> {
> Response.Write(v.Label + ": " + v.Value);
> Response.Write("<br>");
> }
> But when i executed the above code, i am getting the following error..
> System.Web.Services.Protocols.SoapException: The permissions granted to
user 'DPA32W126\ASPNET' are insufficient for performing this operation. -->
> I understand there is no problem with the code, but security settings
needs to be changed for it to work.
> Can anyone guide me on how to make the above code working.
> Thankyou,
> Regards,
> Rajesh Jagadeesan.
> --
> Posted using Wimdows.net NntpNews Component -
> Post Made from http://www.SqlJunkies.com/newsgroups Our newsgroup engine
supports Post Alerts, Ratings, and Searching.|||Hi Jenny:
You can use the NetworkCredential class from the System.Net namespace.
Also, my article might be of some help:
http://odetocode.com/Articles/216.aspx
--
Scott
http://www.OdeToCode.com
On Mon, 13 Sep 2004 13:13:08 -0700, "Jenny"
<Jenny@.discussions.microsoft.com> wrote:
>Hi! I am having a similar problem. I am creating a custom interface for
>users to gather parameters and then select reports to view. I want to only
>pass in the parameters that a given report needs rather then all the
>parameters I am gathering. I am getting this error when the
>GetReportParameters runs:
>The request failed with HTTP status 401: Unauthorized.
>I tried adding in the identity impersonate = true but it didn't do anything.
> Can I change the credentials that are passed in? What else can I check? Is
>there another way to check what parameters are needed for a given report?
>Thank you for the help!
>Jenny
>
GetReportDataSources, SetReportDataSources - obsolete in 2005?
Hi,
We've been using the ReportService.asmx?wsdl to generate a web service stub
code and it worked fine, but when we switched to the
ReportService2005.asmx?wsdl entry it's not generating the above mentioned
methods (GetReportDataSources, SetReportDataSources). Are they obsolete now?
Please advise.
Best regards,
MikeI got it. Methods renamed to GetItemDataSources and SetItemDataSources.
"mike" wrote:
> Hi,
> We've been using the ReportService.asmx?wsdl to generate a web service stub
> code and it worked fine, but when we switched to the
> ReportService2005.asmx?wsdl entry it's not generating the above mentioned
> methods (GetReportDataSources, SetReportDataSources). Are they obsolete now?
> Please advise.
> Best regards,
> Mikesql
We've been using the ReportService.asmx?wsdl to generate a web service stub
code and it worked fine, but when we switched to the
ReportService2005.asmx?wsdl entry it's not generating the above mentioned
methods (GetReportDataSources, SetReportDataSources). Are they obsolete now?
Please advise.
Best regards,
MikeI got it. Methods renamed to GetItemDataSources and SetItemDataSources.
"mike" wrote:
> Hi,
> We've been using the ReportService.asmx?wsdl to generate a web service stub
> code and it worked fine, but when we switched to the
> ReportService2005.asmx?wsdl entry it's not generating the above mentioned
> methods (GetReportDataSources, SetReportDataSources). Are they obsolete now?
> Please advise.
> Best regards,
> Mikesql
GetProperties returns undeterminable timeout value
I have the following code:
// Get timeout
Property itemTimeout = new Property();
itemTimeout.Name = "ReportTimeout";
Property[] props = new Property[1];
props[1] = itemName;
props[1] = itemTimeout;
Property[] itemProps = _rs.GetProperties(path_, props);
Problem I'm seeing is that this code returns no output array member
for the timeout if either the system default is being used or if there
is no timeout. How can I figure out which one it is then?Whoops - I tried to edit the code to take out some stuff that didn't
pertain to the problem. The code is actually good, looking like this:
Property itemTimeout = new Property();
itemTimeout.Name = "ReportTimeout";
Property[] props = new Property[1];
props[0] = itemTimeout;
Property[] itemProps = _rs.GetProperties(path_, props);
bspann <bspann@.discussions.microsoft.com> wrote in message news:<29B6FD22-E731-4486-9A9A-300BFD6D426E@.microsoft.com>...
> Derek,
> There is a problem with your code. In .NET all arrays are 0 based. So an
> array with one element should be arrayname[0]. So your code should look like
> this:
> Property[] props = new Property[1];
> props[0] = itemTimeout;
> Property[] itemProps = _rs.GetProperties(_path, props);
>
> "Derek Knudsen" wrote:
> > I have the following code:
> >
> > // Get timeout
> > Property itemTimeout = new Property();
> > itemTimeout.Name = "ReportTimeout";
> >
> > Property[] props = new Property[1];
> > props[1] = itemName;
> > props[1] = itemTimeout;
> >
> > Property[] itemProps = _rs.GetProperties(path_, props);
> >
> > Problem I'm seeing is that this code returns no output array member
> > for the timeout if either the system default is being used or if there
> > is no timeout. How can I figure out which one it is then?
> >|||More on this:
The GetProperty works fine, with -1 being returned if there is no timeout
and no value being returned if it uses the default setting. Problem was with
setting it to the default setting, which I had thought you could do by
setting the value to "0" (I can't find where I saw this). This throws an
"invalid value" exception. Anyway, solution was to not specify a value and
then it gets reset to "use default setting". Thanks.
"Derek Knudsen" wrote:
> Whoops - I tried to edit the code to take out some stuff that didn't
> pertain to the problem. The code is actually good, looking like this:
> Property itemTimeout = new Property();
> itemTimeout.Name = "ReportTimeout";
> Property[] props = new Property[1];
> props[0] = itemTimeout;
> Property[] itemProps = _rs.GetProperties(path_, props);
> bspann <bspann@.discussions.microsoft.com> wrote in message news:<29B6FD22-E731-4486-9A9A-300BFD6D426E@.microsoft.com>...
> > Derek,
> >
> > There is a problem with your code. In .NET all arrays are 0 based. So an
> > array with one element should be arrayname[0]. So your code should look like
> > this:
> >
> > Property[] props = new Property[1];
> > props[0] = itemTimeout;
> >
> > Property[] itemProps = _rs.GetProperties(_path, props);
> >
> >
> > "Derek Knudsen" wrote:
> >
> > > I have the following code:
> > >
> > > // Get timeout
> > > Property itemTimeout = new Property();
> > > itemTimeout.Name = "ReportTimeout";
> > >
> > > Property[] props = new Property[1];
> > > props[1] = itemName;
> > > props[1] = itemTimeout;
> > >
> > > Property[] itemProps = _rs.GetProperties(path_, props);
> > >
> > > Problem I'm seeing is that this code returns no output array member
> > > for the timeout if either the system default is being used or if there
> > > is no timeout. How can I figure out which one it is then?
> > >
>
// Get timeout
Property itemTimeout = new Property();
itemTimeout.Name = "ReportTimeout";
Property[] props = new Property[1];
props[1] = itemName;
props[1] = itemTimeout;
Property[] itemProps = _rs.GetProperties(path_, props);
Problem I'm seeing is that this code returns no output array member
for the timeout if either the system default is being used or if there
is no timeout. How can I figure out which one it is then?Whoops - I tried to edit the code to take out some stuff that didn't
pertain to the problem. The code is actually good, looking like this:
Property itemTimeout = new Property();
itemTimeout.Name = "ReportTimeout";
Property[] props = new Property[1];
props[0] = itemTimeout;
Property[] itemProps = _rs.GetProperties(path_, props);
bspann <bspann@.discussions.microsoft.com> wrote in message news:<29B6FD22-E731-4486-9A9A-300BFD6D426E@.microsoft.com>...
> Derek,
> There is a problem with your code. In .NET all arrays are 0 based. So an
> array with one element should be arrayname[0]. So your code should look like
> this:
> Property[] props = new Property[1];
> props[0] = itemTimeout;
> Property[] itemProps = _rs.GetProperties(_path, props);
>
> "Derek Knudsen" wrote:
> > I have the following code:
> >
> > // Get timeout
> > Property itemTimeout = new Property();
> > itemTimeout.Name = "ReportTimeout";
> >
> > Property[] props = new Property[1];
> > props[1] = itemName;
> > props[1] = itemTimeout;
> >
> > Property[] itemProps = _rs.GetProperties(path_, props);
> >
> > Problem I'm seeing is that this code returns no output array member
> > for the timeout if either the system default is being used or if there
> > is no timeout. How can I figure out which one it is then?
The GetProperty works fine, with -1 being returned if there is no timeout
and no value being returned if it uses the default setting. Problem was with
setting it to the default setting, which I had thought you could do by
setting the value to "0" (I can't find where I saw this). This throws an
"invalid value" exception. Anyway, solution was to not specify a value and
then it gets reset to "use default setting". Thanks.
"Derek Knudsen" wrote:
> Whoops - I tried to edit the code to take out some stuff that didn't
> pertain to the problem. The code is actually good, looking like this:
> Property itemTimeout = new Property();
> itemTimeout.Name = "ReportTimeout";
> Property[] props = new Property[1];
> props[0] = itemTimeout;
> Property[] itemProps = _rs.GetProperties(path_, props);
> bspann <bspann@.discussions.microsoft.com> wrote in message news:<29B6FD22-E731-4486-9A9A-300BFD6D426E@.microsoft.com>...
> > Derek,
> >
> > There is a problem with your code. In .NET all arrays are 0 based. So an
> > array with one element should be arrayname[0]. So your code should look like
> > this:
> >
> > Property[] props = new Property[1];
> > props[0] = itemTimeout;
> >
> > Property[] itemProps = _rs.GetProperties(_path, props);
> >
> >
> > "Derek Knudsen" wrote:
> >
> > > I have the following code:
> > >
> > > // Get timeout
> > > Property itemTimeout = new Property();
> > > itemTimeout.Name = "ReportTimeout";
> > >
> > > Property[] props = new Property[1];
> > > props[1] = itemName;
> > > props[1] = itemTimeout;
> > >
> > > Property[] itemProps = _rs.GetProperties(path_, props);
> > >
> > > Problem I'm seeing is that this code returns no output array member
> > > for the timeout if either the system default is being used or if there
> > > is no timeout. How can I figure out which one it is then?
> > >
>
Friday, March 23, 2012
getdate() in user defined function
Hi,
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................If I recall correctly, a scalar user defined function must return a deterministic value (ie, if you pass in the same parameters, you will get the same results). A non-deterministic function, would take a randomizer (such as GetDate()) and return a different result everytime you called it (even when calling it with the same parameters). I don't believe that this is allowed.
Regards,
hmscott
Hi,
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................|||Create view v_getdate as
Select ThisDate = getdate()
Then reference v_getdate.ThisDate in your function.
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................If I recall correctly, a scalar user defined function must return a deterministic value (ie, if you pass in the same parameters, you will get the same results). A non-deterministic function, would take a randomizer (such as GetDate()) and return a different result everytime you called it (even when calling it with the same parameters). I don't believe that this is allowed.
Regards,
hmscott
Hi,
It is possible to use getdate() in userdefined function. If so, how to do the same ?
The following code throws error :
create function function1
return varchar
DECLARE @.currYYMM VARCHAR(20)
SET @.currYYMM = convert(char(4),getdate(),12)
// Here it says the error 'getdate' can't be used inside functions
............
................|||Create view v_getdate as
Select ThisDate = getdate()
Then reference v_getdate.ThisDate in your function.
Wednesday, March 21, 2012
GetDate inside a Function
Hi,
I tried to use GetDate inside a SQL server 2000 user defined function and
got error:
Invalid use of GetDate within a function
Here is my code snippet:
DECLARE @.Today smallDateTime
SET @.Today = GetDate()
What should I do?
TIAGETDATE() is nondeterinistic and, thus, cannot be used in a function. You
can, however, create a view and use it:
create view Now
as
select getdate () RightNow
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
Hi,
I tried to use GetDate inside a SQL server 2000 user defined function and
got error:
Invalid use of GetDate within a function
Here is my code snippet:
DECLARE @.Today smallDateTime
SET @.Today = GetDate()
What should I do?
TIA|||do this
declare @.date datetime
select @.date =max(last_XXXXX) from master.dbo.sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>|||oops sorry, nasty typo there
declare @.date datetime
select @.date =max(last_bAtch) from sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>|||I was wondering if maybe the developer was having a bad day with the
wife/mother-in-law when he designed the Last_XXXXX column <grin>
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
> oops sorry, nasty typo there
>
> declare @.date datetime
> select @.date =max(last_bAtch) from sysprocesses
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "John" <someone@.microsoft.com> wrote in message
> news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||ROFL!
I must admit, it was the very first thing that caught my eye when I
glanced at Hilary's code snippet. Great Freudian slip there Hilary!
*mike hodgson*
http://sqlnerd.blogspot.com
Dave Frommer wrote:
>I was wondering if maybe the developer was having a bad day with the
>wife/mother-in-law when he designed the Last_XXXXX column <grin>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
>
>
>|||Thanks for pointing that out. Needed a good laugh in the morning. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:eQaWLxWHGHA.1028@.TK2MSFTNGP11.phx.gbl.
.
>I was wondering if maybe the developer was having a bad day with the
> wife/mother-in-law when he designed the Last_XXXXX column <grin>
>|||:-))))))))))))))))
"Dave Frommer" <anti@.spam.com> wrote in message
news:eQaWLxWHGHA.1028@.TK2MSFTNGP11.phx.gbl...
>I was wondering if maybe the developer was having a bad day with the
>wife/mother-in-law when he designed the Last_XXXXX column <grin>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
>|||last_XXXXX? Must be a dog show database. :)
ML
http://milambda.blogspot.com/|||The mask has slipped... ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
oops sorry, nasty typo there
declare @.date datetime
select @.date =max(last_bAtch) from sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>
I tried to use GetDate inside a SQL server 2000 user defined function and
got error:
Invalid use of GetDate within a function
Here is my code snippet:
DECLARE @.Today smallDateTime
SET @.Today = GetDate()
What should I do?
TIAGETDATE() is nondeterinistic and, thus, cannot be used in a function. You
can, however, create a view and use it:
create view Now
as
select getdate () RightNow
go
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
Hi,
I tried to use GetDate inside a SQL server 2000 user defined function and
got error:
Invalid use of GetDate within a function
Here is my code snippet:
DECLARE @.Today smallDateTime
SET @.Today = GetDate()
What should I do?
TIA|||do this
declare @.date datetime
select @.date =max(last_XXXXX) from master.dbo.sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>|||oops sorry, nasty typo there
declare @.date datetime
select @.date =max(last_bAtch) from sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>|||I was wondering if maybe the developer was having a bad day with the
wife/mother-in-law when he designed the Last_XXXXX column <grin>
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
> oops sorry, nasty typo there
>
> declare @.date datetime
> select @.date =max(last_bAtch) from sysprocesses
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "John" <someone@.microsoft.com> wrote in message
> news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
>|||ROFL!
I must admit, it was the very first thing that caught my eye when I
glanced at Hilary's code snippet. Great Freudian slip there Hilary!
*mike hodgson*
http://sqlnerd.blogspot.com
Dave Frommer wrote:
>I was wondering if maybe the developer was having a bad day with the
>wife/mother-in-law when he designed the Last_XXXXX column <grin>
>"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
>news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
>
>
>|||Thanks for pointing that out. Needed a good laugh in the morning. :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Dave Frommer" <anti@.spam.com> wrote in message news:eQaWLxWHGHA.1028@.TK2MSFTNGP11.phx.gbl.
.
>I was wondering if maybe the developer was having a bad day with the
> wife/mother-in-law when he designed the Last_XXXXX column <grin>
>|||:-))))))))))))))))
"Dave Frommer" <anti@.spam.com> wrote in message
news:eQaWLxWHGHA.1028@.TK2MSFTNGP11.phx.gbl...
>I was wondering if maybe the developer was having a bad day with the
>wife/mother-in-law when he designed the Last_XXXXX column <grin>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
>|||last_XXXXX? Must be a dog show database. :)
ML
http://milambda.blogspot.com/|||The mask has slipped... ;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uPsQ4jWHGHA.3728@.tk2msftngp13.phx.gbl...
oops sorry, nasty typo there
declare @.date datetime
select @.date =max(last_bAtch) from sysprocesses
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"John" <someone@.microsoft.com> wrote in message
news:e6ZConVHGHA.532@.TK2MSFTNGP15.phx.gbl...
> Hi,
> I tried to use GetDate inside a SQL server 2000 user defined function and
> got error:
> Invalid use of GetDate within a function
> Here is my code snippet:
> DECLARE @.Today smallDateTime
> SET @.Today = GetDate()
> What should I do?
> TIA
>
Monday, March 19, 2012
Get value of MAX(ID) into a variable
Hi All,
Hope someone can help a newbie!
I have the following code:
/* Get MAXID from tblHotels_Web to form the HotelID */SqlCommand cmdGetMaxID =new SqlCommand("Select MAX(HotelID) from tblHotels_Web");int intMaxID;
How do I get the value of HotelID into my intMaxID variable and populate txtID.Text?
Regards,
Brett
see thia example:
static public int AddProductCategory(string newName, string connString){ Int32 newProdID = 0; string sql = "Select MAX(HotelID) from tblHotels_Web"; using (SqlConnection conn = new SqlConnection(connString)) { SqlCommand cmd = new SqlCommand(sql, conn); try { conn.Open(); newProdID = (Int32)cmd.ExecuteScalar(); } catch (Exception ex) { Console.WriteLine(ex.Message); } } return (int)newProdID;}
get value from SQL server 2005 select statement with datareader
I just want a simple datareader, that i can read the value returned from a select statement executed on a SQL server 2005 db.
The code below should work in, but email[calc]= rdr[0].ToString(); when i want to read some data a get a exception saying:
System.InvalidOperationException was unhandled by user code
Message="Invalid attempt to read when no data is present."
Source="System.Data"
StackTrace:
at System.Data.SqlClient.SqlDataReader.GetValue(Int32 i)
at System.Data.SqlClient.SqlDataReader.get_Item(Int32 i)
at _Default.Login_Click(Object sender, EventArgs e) in d:\My Documents\Visual Studio 2005\WebSites\WebSite1\Default.aspx.cs:line 47
at System.Web.UI.WebControls.Button.OnClick(EventArgs e)
at System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
at System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
at System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
If anybody could advise me where my stupid mistake is then i would highly appreciate it!
SqlConnection conn = new SqlConnection(getConnection());
SqlDataReader rdr = null;
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "SELECT * FROM Customer";
cmd.CommandType = CommandType.Text;
cmd.Connection = conn;
try
{
conn.Open();
rdr = cmd.ExecuteReader();
int calc = 0;
Boolean login = false;
string[] email = new string[100];
object[] password = new object[100];
while (rdr.HasRows) // or rdr.Read();
{
rdr.Read();
email[calc]= rdr[0].ToString();
password[calc] = rdr["Password"].ToString();
if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc]))
{
login = true;
}
calc++;
}
}
finally
{
rdr.Close();
}
thanks...
Try this:
if (rdr.HasRows)// or rdr.Read(); {while(rdr.Read()) { email[calc]= rdr[0].ToString(); password[calc] = rdr["Password"].ToString();if (UserName.Text.Equals(email[calc]) && Password.Text.Equals(password[calc])) { login =true; } calc++; } } Hope this helps.
get trigger schema within CLR code
Hi.
I am trying to get the schema in which the trigger is created within the CLR code.
1)create a new schema MySchema.
2) created a table MySchema.MyTable
3) created the assembly and trigger ( create trigger MySchema.MyTrigger on MySchema.MyTable..... ) Trigger writes to another table MySchema.MyLog.
Code works fine if I hardcode Myschema.MyLog in the CLR but fails when I say just MyLog.
So how do dynamically get the trigger's schema name ?
Thanks for your help.
Nach
There is no way to get the schema of the currently executing trigger from clr. With T-SQL, you code would have to create dynamic sql based on the current trigger, or rely on the implict schema name binding that occurs.
There are alternatives
1. Use execute as user and have the specified user have a default schema of MySchema
2. Create a T-SQL trigger that calls your clr trigger converted to a proc, passing it the @.@.ProcId of the T-SQL trigger as a parameter, and you can then get object_name & schema id from the clr procedure
Monday, March 12, 2012
Get the variable from Execute Process Task to C#
Hi!
I need help with some C# code. I have build a SSIS package with an Execute Process Task. I need to send dynamic variables in to my C# program so I thought it was a good idea to use the StandardInputVariable.
How do I get the variable in my C# code?
Thanks
CarlYour Main methods parameter collection?|||
yes. provided that this functionality has been built into the c# code.|||Thanks for your help.Peter K wrote:
Your Main methods parameter collection?
I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{
for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args
);
Console.ReadLine();
}
Carl|||
did you include the variable as an argument to your c# executable in the execute process task?|||I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.|||Thanks for your help. I tried to get the variable through the main method but it dont work, the only thing I got was the argument.
My test code:
static void Main(string[] args)
{
for (int i=0; i<args.Length; i++)
{
Console.WriteLine(args);
Console.ReadLine();
}Carl
is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?|||I have a static argument and a variable in StandardInputVarable. I put a value in the variable for testing but it will be dynamic.
My intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.
In any case, can I put a dynamic variable in the argument?
Carl
|||the code below is how to execute package in c# code.the red code tell you how to dymamic edit variable.i think this method can get variable.but i didn't try.please try it
//add reference "Microsoft.SqlServer.ManagedDTS"(in microsoft.sqlserver.manageddts.dll)
Imports Microsoft.SqlServer.Dts.Runtime
Dim pkg As String = "package directory"
Dim app As Application = New Application()
Dim p As Pakage = app.LoadPackage( pkg, Nothing )
p.InteractiveMode = true
Dim pty As DtsProperty
'Dim n As Integer = p.Configurations.Count
Dim strPty As String
For Each pty In p.Properties
strPty = pty.Name & ":"
Try
If pty.Get Then strPty &= pty.GetValue( pty ).ToString()
Catch
End Try
Console.WriteLine( strPty )
Next
Dim vir As Variables = p.Variables
vir( "strFile" ).Value = "C:\MyApp2.txt"
Console.WriteLine( p.Execute( Noting, vir, Nothing, Nothing, Nothing ).ToString() )
|||
understoodMy intention was have to have the argument to jump to a special method in the code. The variable will have information about witch rows in the table the code shall read in and treat.
i don't know. did you try?|||In any case, can I put a dynamic variable in the argument? Carl
I tried it but it didnt worked. The only thing I got was the name of the variable.
You wrote earlier:
is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?
What did you mean by that?
|||what are the option settings in the process page of the execute process task editor?|||I tried it but it didnt worked. The only thing I got was the name of the variable.
You wrote earlier:
is this necessary? can't you just use a dynamically updated ssis variable when calling your executable in the execute process task?
What did you mean by that?
RequireFullFileName = True
Executable = M:\Program\Person.exe
Arguments = fakt
WorkingDirectory = M:\Program
StandardInputVariable = User::test
StandardOutputVariable =
StandardErrorVariable =
FailTaskIfReturnCodeIsNotSuccessValue = True
SuccessValue = 0
TimeOut = 0
TerminateProcessAfterTimeOut = True
WindowStyle = Normal
|||what is the value and data type of User::test immediately before the execute process task starts executing? is the value of this variable correct?|||The data type is string and the value is testing.
I have a question for you.
Is't meaning that I shall use the main-method to get the argument and the variable?
Carl
Get the system date format?
Hi!
I'm trying to get hold of the systems date format in my code so that I can change a date I've got. Dates are stored with default format in the database. I'm presenting a date in a messagebox (where it becomes text) and I want to change the format to the system format before I put it in the text. The date must be right with different kind of system settings, swedish, uk...
Can I do that?
I'm using:
to_char(date, 'DD/MM/YYYY')
This works, but of course the format will be uk all the time.
I dont want to write the format, because then i does not mater what my system settings are.
Hope i made my self understandable..Maybee i should have written "get the date format from regional settings".
I'm trying to get hold of the systems date format in my code so that I can change a date I've got. Dates are stored with default format in the database. I'm presenting a date in a messagebox (where it becomes text) and I want to change the format to the system format before I put it in the text. The date must be right with different kind of system settings, swedish, uk...
Can I do that?
I'm using:
to_char(date, 'DD/MM/YYYY')
This works, but of course the format will be uk all the time.
I dont want to write the format, because then i does not mater what my system settings are.
Hope i made my self understandable..Maybee i should have written "get the date format from regional settings".
Friday, March 9, 2012
get the number of rows exported using bcp
Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KR
Why not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegro ups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>
|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf .googlegroups.com...
>
>
>
>
>
> - Show quoted text -
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KR
Why not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegro ups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>
|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf .googlegroups.com...
>
>
>
>
>
> - Show quoted text -
get the number of rows exported using bcp
Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
>
> > Hi,
> > I need to get the number of rows exported through bcp. Is there a
> > simple way to do that?
> > The current code is as follows:
> > DECLARE @.sql varchar(8000)
> > SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> > \output.csv -T -c -t,'
> > EXEC master..xp_cmdshell @.sql
> > Help is greatly appreciated
> > Thanks
> > KR- Hide quoted text -
> - Show quoted text -
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
--
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
>
> > Hi,
> > I need to get the number of rows exported through bcp. Is there a
> > simple way to do that?
> > The current code is as follows:
> > DECLARE @.sql varchar(8000)
> > SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> > \output.csv -T -c -t,'
> > EXEC master..xp_cmdshell @.sql
> > Help is greatly appreciated
> > Thanks
> > KR- Hide quoted text -
> - Show quoted text -
get the number of rows exported using bcp
Hi,
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.
123200@.e9g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
I need to get the number of rows exported through bcp. Is there a
simple way to do that?
The current code is as follows:
DECLARE @.sql varchar(8000)
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t,'
EXEC master..xp_cmdshell @.sql
Help is greatly appreciated
Thanks
KRWhy not redirect bcp's output to another text file? There you will find
information yo need.
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, -o Drive:\path\row_count.txt'
or
SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
\output.csv -T -c -t, >> Drive:\path\row_count.txt'
Regards
Pawel Potasinski
[http://www.potasinski.pl]
Uzytkownik <kraman@.bastyr.edu> napisal w wiadomosci
news:1185830093.770576.123200@.e9g2000prf.googlegroups.com...
> Hi,
> I need to get the number of rows exported through bcp. Is there a
> simple way to do that?
> The current code is as follows:
> DECLARE @.sql varchar(8000)
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t,'
> EXEC master..xp_cmdshell @.sql
>
> Help is greatly appreciated
>
> Thanks
> KR
>|||I ended up doing it using the echo command the output the number of
rows. I used variables to hold the number of rows and then output it
to another text file using the cmd_shell.
Thanks
On Jul 31, 3:02 am, "Pawel Potasinski" <pawel.potasin...@.gmail.com>
wrote:
> Why not redirect bcp's output to another text file? There you will find
> information yo need.
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, -o Drive:\path\row_count.txt'
> or
> SELECT @.sql = 'bcp "exec stored procedure" queryout Drive:\path
> \output.csv -T -c -t, >> Drive:\path\row_count.txt'
> --
> Regards
> Pawel Potasinski
> [http://www.potasinski.pl]
> Uzytkownik <kra...@.bastyr.edu> napisal w wiadomoscinews:1185830093.770576.
123200@.e9g2000prf.googlegroups.com...
>
>
>
>
>
>
>
>
>
> - Show quoted text -
Get the error in dos cmd line
Hi everyone,
if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)
I have a cmd file that executes sql statements:
something like this:
Code Snippet
OSQL -S %sqlServer% -E -b -n -i DATABASE.sql"
IF ERRORLEVEL 1 GOTO error
echo -
...
:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] > %1
GOTO EXIT
if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)
What I want is: instead of sending the %errorlevel% to the txt, I want to send the error description but I don't know the variable that keeps the error description.
Any help please
Thanx in advance
the error is printed int the cmd window.
The errorlevel var just give me the number "1" but I would like to get something like
"An error in database ocurred due to database already exists" or something like that.
Please I'll apreciate any ideias you may have
|||
Instead of overwriting the error log file, I would just append the error to the file instead.
Code Snippet
ECHO OFF
OSQL -S %sqlServer% -E -b -n -i test.sql > %1
IF ERRORLEVEL 1 GOTO error
ECHO "" > %1
EXIT
:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] >> %1
This way you either have a blank file, or you have a file with an error message and errorLevel code.
|||Thanx for the answer ShawnNWF, that will do the trick for sureGet the error in dos cmd line
Hi everyone,
if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)
I have a cmd file that executes sql statements:
something like this:
Code Snippet
OSQL -S %sqlServer% -E -b -n -i DATABASE.sql"
IF ERRORLEVEL 1 GOTO error
echo -
...
:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] > %1
GOTO EXIT
if there is an error, I print the errorlevel in a txt file wich is a parameter in the command (%1)
What I want is: instead of sending the %errorlevel% to the txt, I want to send the error description but I don't know the variable that keeps the error description.
Any help please
Thanx in advance
the error is printed int the cmd window.
The errorlevel var just give me the number "1" but I would like to get something like
"An error in database ocurred due to database already exists" or something like that.
Please I'll apreciate any ideias you may have
|||
Instead of overwriting the error log file, I would just append the error to the file instead.
Code Snippet
ECHO OFF
OSQL -S %sqlServer% -E -b -n -i test.sql > %1
IF ERRORLEVEL 1 GOTO error
ECHO "" > %1
EXIT
:error
@.ECHO An error occured in [%errorLevel%]
echo [%errorLevel%] >> %1
This way you either have a blank file, or you have a file with an error message and errorLevel code.
|||Thanx for the answer ShawnNWF, that will do the trick for sure
Subscribe to:
Posts (Atom)