Last month we upgraded our SQL Server 2000 production database to SQL Server
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Server
> 2005. It also has SP1 applied to it. I was just checking the jobs, making
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>> Last month we upgraded our SQL Server 2000 production database to SQL
>> Server
>> 2005. It also has SP1 applied to it. I was just checking the jobs,
>> making
>> certain that everything was running OK. We have 7 jobs defined, when I
>> clicked on the View Job History link on each of the job's property page,
>> I
>> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really
>> nothing
>> else mentioned. Here is the text of the error message:
>> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
>> For help, click:
>> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
>>
>> Going to the link didn't help me.
>> So, how do I find out what's wrong and fix it, please?
>> Rod
>>
>>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
> > Hi Rod
> >
> > Does this happen on every job? If it does then how did you upgrade the
> > jobs?
> >
> > You may want to manually see what is in the sysjobhistory table for that
> > job.
> >
> > You may want to use SQL Profiler to see what SQL is being called and check
> > that there is no errors.
> >
> > Try running DBCC CHECKDB on MSDB.
> >
> > If that does not work try scripting the job and creating it as a different
> > one, then disable the original.
> >
> > John
> >
> > "Rod" wrote:
> >
> >> Last month we upgraded our SQL Server 2000 production database to SQL
> >> Server
> >> 2005. It also has SP1 applied to it. I was just checking the jobs,
> >> making
> >> certain that everything was running OK. We have 7 jobs defined, when I
> >> clicked on the View Job History link on each of the job's property page,
> >> I
> >> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really
> >> nothing
> >> else mentioned. Here is the text of the error message:
> >>
> >> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> >>
> >> For help, click:
> >> http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476
> >>
> >>
> >> Going to the link didn't help me.
> >>
> >> So, how do I find out what's wrong and fix it, please?
> >>
> >> Rod
> >>
> >>
> >>
> >>
> >>
>
>
Thursday, March 29, 2012
Getting a syntax error involving Microsoft.SqlServer.SmoEnum
Last month we upgraded our SQL Server 2000 production database to SQL Server
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job
.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Serv
er
> 2005. It also has SP1 applied to it. I was just checking the jobs, makin
g
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothi
ng
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...[vbcol=seagreen]
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
>
>
2005. It also has SP1 applied to it. I was just checking the jobs, making
certain that everything was running OK. We have 7 jobs defined, when I
clicked on the View Job History link on each of the job's property page, I
got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothing
else mentioned. Here is the text of the error message:
syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
For help, click:
http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
Going to the link didn't help me.
So, how do I find out what's wrong and fix it, please?
RodHi Rod
Does this happen on every job? If it does then how did you upgrade the jobs?
You may want to manually see what is in the sysjobhistory table for that job
.
You may want to use SQL Profiler to see what SQL is being called and check
that there is no errors.
Try running DBCC CHECKDB on MSDB.
If that does not work try scripting the job and creating it as a different
one, then disable the original.
John
"Rod" wrote:
> Last month we upgraded our SQL Server 2000 production database to SQL Serv
er
> 2005. It also has SP1 applied to it. I was just checking the jobs, makin
g
> certain that everything was running OK. We have 7 jobs defined, when I
> clicked on the View Job History link on each of the job's property page, I
> got a syntax error involving Microsoft.SqlServer.SmoEnum, but really nothi
ng
> else mentioned. Here is the text of the error message:
> syntax error (ackup_SystemDBs_Weekly'']) (Microsoft.SqlServer.SmoEnum)
> For help, click:
> http://go.microsoft.com/fwlink?Prod...er&LinkId=20476
>
> Going to the link didn't help me.
> So, how do I find out what's wrong and fix it, please?
> Rod
>
>
>|||John,
Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
cause this error to occur. The 7th does work.
Rod
"John Bell" <jbellnewsposts@.hotmail.com> wrote in message
news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...[vbcol=seagreen]
> Hi Rod
> Does this happen on every job? If it does then how did you upgrade the
> jobs?
> You may want to manually see what is in the sysjobhistory table for that
> job.
> You may want to use SQL Profiler to see what SQL is being called and check
> that there is no errors.
> Try running DBCC CHECKDB on MSDB.
> If that does not work try scripting the job and creating it as a different
> one, then disable the original.
> John
> "Rod" wrote:
>|||Hi Rod
How important is the history? If not then try to re-create the job.
John
"Rod" wrote:
> John,
> Of the 7 jobs that were in SQL Server 2000 before the upgrade, 6 of them
> cause this error to occur. The 7th does work.
> Rod
> "John Bell" <jbellnewsposts@.hotmail.com> wrote in message
> news:4226D126-BA6B-4E45-A6ED-553F767C2E7A@.microsoft.com...
>
>
Getting a stored procedures return value -- URGENT !
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!
>
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!
>
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,
Getting a step in a job to start another job in another database...
Hi, can anyone help me by telling me how to get a step in
a job to start another job in another database please...'
Cheers,
NikThere are no jobs at the database level. Did you mean, starting a job on
another server?
All you need is the job name, and pass it to msdb..sp_start_job. If the job
is on a different server, then you could add a linked server to that server
and call ServerName.msdb..sp_start_job
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
Hi, can anyone help me by telling me how to get a step in
a job to start another job in another database please...'
Cheers,
Nik|||Spot on thanks. Its a job on another server that I am
trying to start. I have taken a look at books online to
see the syntax, might give you another shout here if I get
stuck!
Thanks a bunch,
Nik
starting a job on
msdb..sp_start_job. If the job
server to that server
a job to start another job in another database please...'
Cheers,
NikThere are no jobs at the database level. Did you mean, starting a job on
another server?
All you need is the job name, and pass it to msdb..sp_start_job. If the job
is on a different server, then you could add a linked server to that server
and call ServerName.msdb..sp_start_job
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
Hi, can anyone help me by telling me how to get a step in
a job to start another job in another database please...'
Cheers,
Nik|||Spot on thanks. Its a job on another server that I am
trying to start. I have taken a look at books online to
see the syntax, might give you another shout here if I get
stuck!
Thanks a bunch,
Nik
quote:
>--Original Message--
>There are no jobs at the database level. Did you mean,
starting a job on
quote:
>another server?
>All you need is the job name, and pass it to
msdb..sp_start_job. If the job
quote:
>is on a different server, then you could add a linked
server to that server
quote:
>and call ServerName.msdb..sp_start_job
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
>news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
>Hi, can anyone help me by telling me how to get a step in
>a job to start another job in another database please...'
>Cheers,
>Nik
>
>.
>
Getting a step in a job to start another job in another database...
Hi, can anyone help me by telling me how to get a step in
a job to start another job in another database please...'
Cheers,
NikThere are no jobs at the database level. Did you mean, starting a job on
another server?
All you need is the job name, and pass it to msdb..sp_start_job. If the job
is on a different server, then you could add a linked server to that server
and call ServerName.msdb..sp_start_job
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
Hi, can anyone help me by telling me how to get a step in
a job to start another job in another database please...'
Cheers,
Nik|||Spot on thanks. Its a job on another server that I am
trying to start. I have taken a look at books online to
see the syntax, might give you another shout here if I get
stuck!
Thanks a bunch,
Nik
>--Original Message--
>There are no jobs at the database level. Did you mean,
starting a job on
>another server?
>All you need is the job name, and pass it to
msdb..sp_start_job. If the job
>is on a different server, then you could add a linked
server to that server
>and call ServerName.msdb..sp_start_job
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
>news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
>Hi, can anyone help me by telling me how to get a step in
>a job to start another job in another database please...'
>Cheers,
>Nik
>
>.
>
a job to start another job in another database please...'
Cheers,
NikThere are no jobs at the database level. Did you mean, starting a job on
another server?
All you need is the job name, and pass it to msdb..sp_start_job. If the job
is on a different server, then you could add a linked server to that server
and call ServerName.msdb..sp_start_job
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
Hi, can anyone help me by telling me how to get a step in
a job to start another job in another database please...'
Cheers,
Nik|||Spot on thanks. Its a job on another server that I am
trying to start. I have taken a look at books online to
see the syntax, might give you another shout here if I get
stuck!
Thanks a bunch,
Nik
>--Original Message--
>There are no jobs at the database level. Did you mean,
starting a job on
>another server?
>All you need is the job name, and pass it to
msdb..sp_start_job. If the job
>is on a different server, then you could add a linked
server to that server
>and call ServerName.msdb..sp_start_job
>--
>HTH,
>Vyas, MVP (SQL Server)
>http://vyaskn.tripod.com/
>Is .NET important for a database professional?
>http://vyaskn.tripod.com/poll.htm
>
>"nik" <nik.hall@.deletethisalstons.co.uk> wrote in message
>news:888301c3e97e$79aa7070$a501280a@.phx.gbl...
>Hi, can anyone help me by telling me how to get a step in
>a job to start another job in another database please...'
>Cheers,
>Nik
>
>.
>
Getting a SQL Express Database to work on a SQL Server
Hi,
I'm getting ready to deploy an ASP.NET application to a server that does not support SQL Express, but does support MS Access and SQL Server. Is there any easy way to convert my SQL Express code to either Access or regular SQL Server code, without having to change very much code in my application. I really like the integration that SQL Express has with Visual Web Developer, and would like to be able to keep that sort of integration if at all possible.
Thanks,
Drew
Try this blog and if it works let me know
http://weblogs.asp.net/scottgu/archive/2005/08/25/423703.aspx
Getting a SQL Database Error...any ideas SQL experts?
I am trying to insert a value into a field in a database named ASPNETDB.MDF. The table name is "profiles_BasicProperties" and the field name is "UserID". I get an error when I attempt to do this. See the code I am using to try to do this below...and then the error that I get which is further down in this post. Note...both the code and the database are on my laptop. I can connect to the database just fine using Server Explorer in MS VS 2005. Thanks in advance for any help anybody can offer...
Here is the code I am using:
<%@. Page Language="VB" MasterPageFile="~/Master02.master" Title="Create Your Free Account" Debug="true"%>
<%@. Import Namespace="System.Data.SqlClient" %>
<%@. Import Namespace="System.Web.Configuration" %>
<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder_Main" Runat="Server">
<script runat="server">
Sub CreateUserWizard_CreatedUser(ByVal sender As Object, ByVal e As EventArgs)
Dim CWZ As CreateUserWizard
CWZ = CType(Me.LoginView1.FindControl("CreateUserWizard"), Wizard)
CreateUserProfile(CWZ.UserName)
Private Sub CreateUserProfile(ByVal UserName As String)
Dim conString As String = WebConfigurationManager.ConnectionStrings("Main").ConnectionString
Dim con As New SqlConnection(conString)
Dim cmd As New SqlCommand("INSERT profiles_BasicProperties (UserName) VALUES (@.UserID)", con)
cmd.Parameters.AddWithValue("@.UserID", UserName)
Using con
con.Open()
cmd.ExecuteNonQuery()
End Using
End Sub
</script>
...and here is the error and stack trace (the offending Line 49 is in bold):
Server Error in '/Site_Dev' Application.
------------------------
An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.
Source Error:
Line 47: cmd.Parameters.AddWithValue("@.UserID", UserName)
Line 48: Using con
Line 49: con.Open()
Line 50: cmd.ExecuteNonQuery()
Line 51: End Using
Source File: C:\Users\mdcragg\Documents\Website\Site_Dev\User_Create.aspx Line: 49
Stack Trace:
[SqlException (0x80131904): An attempt to attach an auto-named database for file ~\App_Data\ASPNETDB.MDF failed. A database with the same name exists, or specified file cannot be opened, or it is located on UNC share.]
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +736211
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1959
System.Data.SqlClient.SqlInternalConnectionTds.CompleteLogin(Boolean enlistOK) +33
System.Data.SqlClient.SqlInternalConnectionTds.AttemptOneLogin(ServerInfo serverInfo, String newPassword, Boolean ignoreSniOpenTimeout, Int64 timerExpire, SqlConnection owningObject) +237
System.Data.SqlClient.SqlInternalConnectionTds.LoginNoFailover(String host, String newPassword, Boolean redirectedUserInstance, SqlConnection owningObject, SqlConnectionString connectionOptions, Int64 timerStart) +374
System.Data.SqlClient.SqlInternalConnectionTds.OpenLoginEnlist(SqlConnection owningObject, SqlConnectionString connectionOptions, String newPassword, Boolean redirectedUserInstance) +192
System.Data.SqlClient.SqlInternalConnectionTds..ctor(DbConnectionPoolIdentity identity, SqlConnectionString connectionOptions, Object providerInfo, String newPassword, SqlConnection owningObject, Boolean redirectedUserInstance) +170
System.Data.SqlClient.SqlConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningConnection) +359
System.Data.ProviderBase.DbConnectionFactory.CreatePooledConnection(DbConnection owningConnection, DbConnectionPool pool, DbConnectionOptions options) +28
System.Data.ProviderBase.DbConnectionPool.CreateObject(DbConnection owningObject) +424
System.Data.ProviderBase.DbConnectionPool.UserCreateRequest(DbConnection owningObject) +66
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +496
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
ASP.user_create_aspx.CreateUserProfile(String UserName) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:49
ASP.user_create_aspx.CreateUserWizard_CreatedUser(Object sender, EventArgs e) in C:\Users\Matthew\Documents\Group 02 - Politicore\Site_Dev\User_Create.aspx:30
System.Web.UI.WebControls.CreateUserWizard.OnCreatedUser(EventArgs e) +105
System.Web.UI.WebControls.CreateUserWizard.AttemptCreateUser() +341
System.Web.UI.WebControls.CreateUserWizard.OnNextButtonClick(WizardNavigationEventArgs e) +105
System.Web.UI.WebControls.Wizard.OnBubbleEvent(Object source, EventArgs e) +453
System.Web.UI.WebControls.CreateUserWizard.OnBubbleEvent(Object source, EventArgs e) +149
System.Web.UI.WebControls.WizardChildTable.OnBubbleEvent(Object source, EventArgs args) +17
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
------------------------
Version Information: Microsoft .NET Framework Version:2.0.50727.312; ASP.NET Version:2.0.50727.312
Looking around potential solutions seem to be:
1. Open SQL Server Management Studio and attach the .mdf file manually to the SQL Server. (Select the 'Databases' node, Right click and select 'Attach')
Now change your web.config and change the connection string to point to the pre attached database instead of auto attach.
2. SQLExpress creates a directory per user in "c:\Documents and Settings\[user name]\Local Settings\Application Data\Microsoft\Microsoft SQL Server Data\SQLEXPRESS" to store some information. Delete this directory and try again.
3. The windows account "ASPNET" must have write permission on the folder. If it is a Windows 2003 machine, provide write access to the account "NETWORK SERVICE" on the folder, instead of account ASPNET.
4. You may want to disable the "User Instance=true" and try.
5. Are you using beta versions of VWD, Visual Studio 2005 or SQL Server 2005 Express? Uninstall them andinstall the released versions.
6. User Instance=True will work only if your SQL Server 2005 Express is using Windows Authentication mode. It will fail if you are using mixed mode. (Select the computer name in the SQL Server Management Studio, right click and select 'Properties'. Select the node 'Security' to view/change the authentication mode)
7. Make sure your connection string is correct. It must be something like below:
"Data Source=.\SQLExpress;Persist Security Info=True;Integrated Security=SSPI;Initial Catalog=YourUserId_DatabaseName"
When you useIntegrated Secuiryt=SSPI in the connection string, make sure you are using<identity impersonate="true" /> in the<system.web> section of the web.config and also make sure to specify a valid windows account for the "Anonymous User" access section in IIS.
Getting a single value from SqlDataSource
Hi,
I have a SqlDataSource(named SQLDS1) which retrieves 4 value from database(ProductName,ProductCost,ProductID). I Have a DropDownList(DDL1) control and its DataSource SQLDS1.
DDL1 Selected data field todisplay is ProductName
DDL1 Selected data field tovalue is ProductCost
I did all this in Visual Part without any line of code. But in the code behind , When i select an item from DDL1 i need its ProductName,ProductCost and Also ProductID. It is simple to get first two. But how can i get the ProductID. Is there anyway to get ProductID from SQLDS1.
Happy Coding
Hi
You will be able to obtain only the values that you bind to the drop down list. if you want non bound value then you will have to fire another query based on what you select from the drop down.
For example,
select productid from Table_name where product name = 'prodname you selected from ddl' and productcost= cost you got from ddl.
Then use productid as you need
HTH
Getting a simple function to run in the CLR . Please help :-(
Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonHello Simon,
The attachment didn't work here. Please contact me at ktegels@.develop.com
about this one.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonHello Simon,
The attachment didn't work here. Please contact me at ktegels@.develop.com
about this one.
Thank you,
Kent Tegels
DevelopMentor
http://staff.develop.com/ktegels/|||Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon
Getting a simple function to run in the CLR . Please help :-(
Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simp
le
like return a simple string it works fine. However when I try and use a conn
ection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
****************************************
**********
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
****************************************
**********|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception
.
Force of habit really, just so I can see what the exception was a bit easier
.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.com...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon
Getting a simple function to run in the CLR . Please help :-(
Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
Simon
Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.co m:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception.
Force of habit really, just so I can see what the exception was a bit easier.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon
|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.co m...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>
|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon
sql
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
Simon
Simon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.co m:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
|||Niels my man, you sir are a rutting stallion!
It was that I was trying to output to the console when catching an exception.
Force of habit really, just so I can see what the exception was a bit easier.
All fixed now.
Sincerest thanks to you and Kent
Kindest Regards
Simon
|||Instead of writing to the console, you could trow an cutom error, and that
would be catched by the calling program.
-Mark
"Simon Harvey" <nothanks@.hotmail.com> wrote in message
news:7c72785b12aa8c7bcd0aa78c851@.news.microsoft.co m...
> Niels my man, you sir are a rutting stallion!
> It was that I was trying to output to the console when catching an
> exception. Force of habit really, just so I can see what the exception was
> a bit easier.
> All fixed now.
> Sincerest thanks to you and Kent
> Kindest Regards
> Simon
>
|||Hi Mark
I know I could do it that way. It's realy just during debugging I sometimes
find it helpful. It's not a huge issue but thanks though
Kindest Regards
Simon
sql
Getting a simple function to run in the CLR . Please help :-(
Hi everyone,
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
--
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
If anyone can help me with the following, I would be very greatful.
I want to create a pretty basic function under sql server 2005's runtime.
I've made the assembly and attached it. And when I do something totally simple
like return a simple string it works fine. However when I try and use a connection
object I get an exception as follows:
A .NET Framework error occurred during execution of user defined routine
or aggregate 'GetRolesString':
System.Security.HostProtectionException: Attempted to perform an operation
that was forbidden by the CLR host.
The protected resources (only available with full trust) were: All
The demanded resources were: UI
It seems to be suggesting that everything is protected and so I can't do
anything. That would be fine but I havent a clue how to unprotect them.
The things I've tried are:
1. Making the database property Trustworthy equal to true
2. Marking the Permission_Set for the assembly as EXTERNAL (or whatever it
is)
These things havent helped
A huge thank you to anyone who can tell me how to unprotect me crap :-)
Thanks gain
SimonSimon Harvey <nothanks@.hotmail.com> wrote in
news:7c72785b10da8c7bcafc4d67937@.news.microsoft.com:
> Hi everyone,
> If anyone can help me with the following, I would be very greatful.
> I want to create a pretty basic function under sql server 2005's
> runtime. I've made the assembly and attached it. And when I do
> something totally simple like return a simple string it works fine.
> However when I try and use a connection object I get an exception as
> follows:
> A .NET Framework error occurred during execution of user defined
> routine or aggregate 'GetRolesString':
> System.Security.HostProtectionException: Attempted to perform an
> operation that was forbidden by the CLR host.
> The protected resources (only available with full trust) were: All
> The demanded resources were: UI
> It seems to be suggesting that everything is protected and so I can't
> do anything. That would be fine but I havent a clue how to unprotect
> them.
>
Using the connection object shouldn't be a problem. The error you're
getting looks like it has something to do with UI, in other words are
you trying to do a Console.WriteLine or something similar?
If you post your code for the method it'll be easier for us to see
what's wrong.Anyway, below follows some code snippets for the
connection:
public static void GetData() {
SqlConnection conn = new SqlConnection("Context Connection=true");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "Select some data from somewhere";
SqlContext.Pipe.ExecuteAndSend(cmd);
}
Notice that in the code above it is not entirely necessary to use the
connection at all, as you are calling ExecuteAndSend on the SqlPipe
class.
Niels
--
**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@.no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************
getting a set of values from xml
i have imported xml into an xml datatype variable. here is a tiny version of my xml file.
<Root>
<TOP>
<USERS>
<USER>
<USER>
<USERNAME>jukkaw</USERNAME>
</USER>
<USER>
<USERNAME>v-derekn</USERNAME>
</USER>
</USERS>
</TOP>
</Root>'
I need to pullout just the username, so the query method is out as it will return it in xml format. how do i just get a column containing all of the usernames?
You could use nodes table-value function:
create table #xml_table
(
xml_col xml
)
go
insert into #xml_table values('<Root>
<TOP>
<USERS>
<USER>
<USERNAME>jukkaw</USERNAME>
</USER>
<USER>
<USERNAME>v-derekn</USERNAME>
</USER>
</USERS>
</TOP>
</Root>
')
select x.value('.[1]','varchar(100)')
from #xml_table t cross apply xml_col.nodes('/Root/TOP/USERS/USER/USERNAME/text()') as tab(x)
Getting a row count from a DataSource vs GridView
There HAS to be an easier way to do this...
I have 2 seperate SqlDataSources for 2 distinct filters. How do I get a simple row count for each SqlDataSource? It seems the only way is by using the ObjectDataSource and going through that whole mess (paging, etc.). And unfortunately, you can't simply get the number of rows in the GridView that represents each DataSource if AllowPaging is true for the GridView.
This is frustrating.
Dave
You gotta setup the Selected Event
protected void SqlDataSource1_Selected( object sender, SqlDataSourceStatusEventArgs e )
{
e.AffectedRows;
}
Can you give me a little more insight...
Thanks,
Dave
|||Not sure how much more detailed I can get. When you setup your SqlDataSources, put
<asp:SqlDataSourceID="SqlDataSource1"runat="server"OnSelected="SqlDataSource_Selected" ... />
<asp:SqlDataSourceID="SqlDataSource2"runat="server"OnSelected="SqlDataSource_Selected".../>
They both should be able to use the same event since you want them both to do the same thing.
Then in your code-beside, setup the event as follows
protectedvoid SqlDataSource_Selected(object sender,SqlDataSourceStatusEventArgs e )
{
// Do something with e.AffectedRows
Response.Write( e.AffectedRows );
}
Getting a ROLAP cube to refresh when a dimension changes
Have 1 ROLAP cube in an AS DB with other MOLAP cubes. The ROLAP cube is derived from a SQL database view and uses dimensions that are also used by the MOLAP cubes. The aggregation storage of the source view is defined as ROLAP. Updates to the Fact table cause the ROLAP cube to refresh while updates to the dimensions do not cause a refresh of the ROLAP cube.
When for the ROLAP cube ,set storage mode to rolap
and in proactive caching, change settings to Real-time ROLAP
click on "Options" and make sure Enable proactive caching is marked
if now you change a measure in your fact table you will see the change right away
without processing, but the problem is we can not have the same thing working for dimensions
in the same caching setting in Storage Options enabling the check box "Apply settings to dimensions"
doesn't really solve the problem, when you check off this setting
any changes to the dimensions will not update the cube until the dimensions are processed.
What can be done to enable an update of a dimension used by the ROLAP cube to cause the ROLAP cube to be refreshed without reprocessing the MOLAP cubes also using the dimension?
Thanks
If the same dimensions are used in the other MOLAP cubes, you should keep storage mode of your dimensions as MOLAP.
If you wanted to go with storage mode as ROLAP for all of your dimensions, you should create new dimensions that are going to be used only in this ROLAP cube.
You should also take a look whether sizes of your data will allow you to keep the same level of performance- ROLAP will perform slower in general when compared to MOLAP mode.
Edward.
--
This posting is provided "AS IS" with no warranties, and confers no rights
There should be no need to reprocess the ROLAP cube when you just process the dimension. As long as you are not deleting records from your dimension table, you should just be doing a processAdd or processUpdate on your dimension. This will leave all your cubes in a queryable state and the proactive caching feature should keep your ROLAP partitions "fresh" as new facts arrive.
If you have been doing a processFull on your dimension, it will have been clearing out all the cubes that use the dimension in question as a processFull rebuilds the dimension structure from scratch which requires that dependant cubes have their storage structures rebuilt also.
Getting a return value from a Stored Procedure
Hi all,
Is there anyway to get a returned value from a called Stored Procedure from within a piece ofSQL? For example, I have the following code...
So, in your big SP you would get the OUTPUT parameter as follows:
DECLARE @.outParm VARCHAR(50)
EXEC SP_Name , ...(input parameters), ... @.outParam (output parameter)
print @.outParam
Hope that helps ,
Regards
|||Hi, and thanks for the reply. Its seems I got what I needed from using the following line of code...
DECLARE @.testval AS INT
EXEC @.testval = u_checknew_dwi_limits @.varval
Is this a more efficient way of doing thing?
Tryst
Is there anyway to get a returned value from a called Stored Procedure from within a piece ofSQL? For example, I have the following code...
DECLARE @.testval AS INT
SET @.testval = EXEC u_checknew_dwi_limits '163'
IF (@.testval = 0)
BEGIN
PRINT '0 Returned'
END
ELSE
BEGIN
PRINT '1 Returned'
END
...whichas you can see calls a SP called 'u_checknew_dwi_limits'. This SP(u_checknew_dwi_limits) actually returns a value (1 or 0), so I want toassign that value to the '@.testval' variable (as you can see in mycode) - but Query Analyser is throwing an error at me. Is this thecorrect way to do this?
Thanks
Tryst
So, in your big SP you would get the OUTPUT parameter as follows:
DECLARE @.outParm VARCHAR(50)
EXEC SP_Name , ...(input parameters), ... @.outParam (output parameter)
print @.outParam
Hope that helps ,
Regards
|||Hi, and thanks for the reply. Its seems I got what I needed from using the following line of code...
DECLARE @.testval AS INT
EXEC @.testval = u_checknew_dwi_limits @.varval
Is this a more efficient way of doing thing?
Tryst
Getting a Return value from a Function.
Im a self proclaimed newb and Im stuck on returning a value from a function. I want to get the AttendID that the SQL statement returns and dump it into strAttendID:Response.Redirect("ClassSurvey.aspx?Pupil=" & strAttendID)
I cant seem to accomplish this. It returns nothing. Please help.
TIA,
Stue
<code>
Function Get_AttendID(ByVal strAttendIDAsString)As SqlDataReader
Dim connStringAsString = ConfigurationSettings.AppSettings("ClassDB")
Dim sqlConnAsNew SqlConnection(connString)
Dim sqlCmdAs SqlCommand
Dim drAs SqlDataReader
The best way would be to use executescalar method and return the value. excuse the sample code because it is C#
|||Thanks Mansoorl! I tried that and it worked. In response to your question about the datareader, the reason I went this route is because I have another function wich requires pulling 2 values. So i was in that mindset. I didnt know about the ExecuteScalar though so thanks for educating me.
Do you mind explaining how i might go about returning 3 values via the data reader if:
Select FirstName, LastName, Company from TBClassSurvey Where AttendID=@.AttendID and SchedID=@.SchedID and survey = '0'";
Thanks again,
Stue
|||publicvoidAttendID()
{
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings("ClassDB"));
string strSQL = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'";
SqlCommand myCommand =new SqlCommand(strSQL, myConnection);
myCommand.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.FirstName").Value = tbFirstName.Text;
myCommand.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.LastName").Value = tbLastName.Text;
myCommand.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8);
myCommand.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString();
SqlDataReader myReader = myCommand.ExecuteReader();
myReader.Read();
string FirstName = myReader["FirstName"].ToString();
string LastName = myReader["LastName"].ToString();
string Company = myReader["Company"].ToString();
myReader.Close();
myConnection.Close();
}
The above code assumes you got something back in result of the query. If there is a possiblity for blank records make sure you use if (myReader.Read()) constuct.
Cheers,|||Thanks again mansoorl! I appretiate you educating me.
Take care,
Stue
I cant seem to accomplish this. It returns nothing. Please help.
TIA,
Stue
<code>
Function Get_AttendID(ByVal strAttendIDAsString)As SqlDataReader
Dim connStringAsString = ConfigurationSettings.AppSettings("ClassDB")
Dim sqlConnAsNew SqlConnection(connString)
Dim sqlCmdAs SqlCommand
Dim drAs SqlDataReader
sqlConn.Open()
Dim strSQLAsString = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'"
sqlCmd =New SqlCommand(strSQL, sqlConn)
sqlCmd.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50)
sqlCmd.Parameters("@.FirstName").Value = tbFirstName.Text
sqlCmd.Parameters.Add("@.LastName", SqlDbType.VarChar, 50)
sqlCmd.Parameters("@.LastName").Value = tbLastName.Text
sqlCmd.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8)
sqlCmd.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString
dr = sqlCmd.ExecuteReader()
dr.Close()
sqlConn.Close()
Return dr
EndFunction
</code>
The best way would be to use executescalar method and return the value. excuse the sample code because it is C#
publicstring AttendID()
{
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings("ClassDB"));
string strSQL = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'";
SqlCommand myCommand =new SqlCommand(strSQL, myConnection);
myCommand.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.FirstName").Value = tbFirstName.Text;
myCommand.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.LastName").Value = tbLastName.Text;
myCommand.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8);
myCommand.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString();
return myCommand.ExecuteScalar().ToString();
}
|||Thanks Mansoorl! I tried that and it worked. In response to your question about the datareader, the reason I went this route is because I have another function wich requires pulling 2 values. So i was in that mindset. I didnt know about the ExecuteScalar though so thanks for educating me.
Do you mind explaining how i might go about returning 3 values via the data reader if:
Select FirstName, LastName, Company from TBClassSurvey Where AttendID=@.AttendID and SchedID=@.SchedID and survey = '0'";
Thanks again,
Stue
|||publicvoidAttendID()
{
SqlConnection myConnection =new SqlConnection(ConfigurationSettings.AppSettings("ClassDB"));
string strSQL = "Select AttendID from attendees Where FirstName=@.FirstName and LastName=@.LastName and classbegdt = @.classbegdt and survey = '0'";
SqlCommand myCommand =new SqlCommand(strSQL, myConnection);
myCommand.Parameters.Add("@.FirstName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.FirstName").Value = tbFirstName.Text;
myCommand.Parameters.Add("@.LastName", SqlDbType.VarChar, 50);
myCommand.Parameters("@.LastName").Value = tbLastName.Text;
myCommand.Parameters.Add("@.classbegdt", SqlDbType.DateTime, 8);
myCommand.Parameters("@.classbegdt").Value = calBegDate.SelectedDate.ToShortDateString();
SqlDataReader myReader = myCommand.ExecuteReader();
myReader.Read();
string FirstName = myReader["FirstName"].ToString();
string LastName = myReader["LastName"].ToString();
string Company = myReader["Company"].ToString();
myReader.Close();
myConnection.Close();
}
The above code assumes you got something back in result of the query. If there is a possiblity for blank records make sure you use if (myReader.Read()) constuct.
Cheers,|||Thanks again mansoorl! I appretiate you educating me.
Take care,
Stue
Getting a reference to a Script task in VB.net
I am trying to get a reference to a script task so I can manipulate it's properties. However I can't seem to figure it out? I have a reference to Microsoft.SqlServer.ManagedDTS and Microsoft.SqlServer.Dts.Design and based on BOL they show
Imports System
Imports System.Collections.Generic
Imports System.Text
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask
Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask
Imports Microsoft.SqlServer.Dts.Runtime
But I get errors saying
Imports Microsoft.SqlServer.Dts.Tasks.BulkInsertTask
Imports Microsoft.SqlServer.Dts.Tasks.FileSystemTask
can't be found? What do I need to reference or Import to be able to see the Tasks and/or Task types so I can convet InnerObject to a ScriptTask type and manipulate?
What am doing wrong?
Dim pkgPath As String = "C:\Program Files\\ETL\ODS\Policy\"Dim pkgName As String = "LoadOdsCountryCodes"
Dim pkg1 As String = pkgPath + pkgName + ".dtsx"
Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"
Dim app As Application = New Application()
Dim pkg As Package = app.LoadPackage(pkg1, Nothing)
Dim x As Executable
For Each x In pkg.Executables
Dim t As TaskHost = CType(x, TaskHost)
If t.Name = "SCT Set Global Variables" Then
Dim sct As ? = ctype(t.InnerObject,?)
End If
Next
You cannot manipulate properties of objects in the package directly from script within the package. Period.
What you CAN do is put expressions on properties which affect them at execution-time.
-Jamie
|||
I am not trying to do it from within a package. this is outside of SSIS in the vb.net IDE.
I have figured it out.
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask
Module SSISPackage
Public Sub main()
Dim pkgPath As String = "C:\Program Files\Insurity\Reporting Decisions\ETL\ODS\Policy\"
Dim pkgName As String = "LoadOdsCountryCodes"
Dim pkg1 As String = pkgPath + pkgName + ".dtsx"
Dim pkg2 As String = pkgPath + pkgName + "2.dtsx"
Dim app As Application = New Application()
Dim pkg As Package = app.LoadPackage(pkg1, Nothing)
Dim x As Executable
For Each x In pkg.Executables
Dim t As TaskHost = CType(x, TaskHost)
If t.Name = "SCT Set Global Variables" Then
Dim sct As ScriptTask = CType(t.InnerObject, ScriptTask)
Dim cp As ScriptTaskCodeProvider = sct.CodeProvider
Dim Moniker As String = "dts://Scripts/" & sct.VsaProjectName & "/" & sct.VsaProjectName & ".vsaproj"
Dim code As String = cp.GetSourceCode(Moniker)
End If
Next
But still does not seem to show the user text added by the developer? Strange.
|||I had to add a reference to:
Microsoft.SqlServer.ScriptTask
Microsoft.SqlServer.VSAHosting
to my project and then this would work:
Imports Microsoft.SqlServer.Dts.Tasks.ScriptTask
|||Ah OK. Sorry, my bad!
Glad you got it working.
-Jamie
Getting A Query Parameter from Elsewhere
I have a query on a table. This table has a datetime column.
I have another table. This table also has a datetime column. It has only one
row. It basically to store some simple configuration data.
There is no relation between the tables.
I want to be able to limit the entries returned by the query on the first
table by only returning rows that have a date that is less than the date in
the row in the second table.
I'm actually having trouble getting this to work properly. I've tried
subqueries, passing it through the report as a parameter, etc... None are
working.
How should i attack this?It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
possibility for you. I am sure there are much better ways, but this is off
the cuff:
Select
MyDate, Field1, Field2 from table1
Group by
MyDate,Field1,Field2
HAVING MyDate < (Select LookUpDate from Table2)
You could always do it in a stored procedure and call in the value of Table2
into a variable @.LokUpDate that yo could compare. I assume that the
LookUpDate in my example to match your description changes on a regular
basis?
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
> one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
> in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||You really do not have to add an aggregate function. You can always set a
variable and use that in the Where clause if you do not want to use the
GROUP BY. Something like ( and I added CREATEDATE to the WHERE clause also)
:
Declare @.CUTOFFDATE as DATETIME
SELECT @.CUTOFFDATE=CUTOFFDATE from CONFIGDATA
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID ='acm')
AND (SALESTABLE.SALESSTATUS = 1) AND CREATEDATE < @.CUTOFFDATE
You may have to make this a stored procedure if it will not work on the IDE
for reporting Services.
Rodney Landrum -Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:211E0BCB-A461-485A-B888-333A33E33162@.microsoft.com...
> If I use GROUP BY, will that not require aggregation of the results of the
> query?
> Perhaps it would be helpful to see the existing query:
> SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
> QTY,
> (SELECT SUM(MARKUPTRANS.VALUE)
> FROM MARKUPTRANS
> WHERE SALESTABLE.RECID => MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
> MARKUPTRANS.MARKUPCODE => 'Freight') AS FreightValue,
> (SELECT SUM(INVENTSUM.POSTEDVALUE)
> FROM INVENTSUM
> WHERE SALESLINE.INVENTDIMID => INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
> FROM SALESTABLE INNER JOIN
> SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
> WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID => 'acm')
> AND (SALESTABLE.SALESSTATUS = 1) >
> That is table one. I want to limit on a column called CREATEDDATE.
> The other query looks like:
> SELECT CUTOFFDATE from CONFIGDATA
> "Rodney Landrum" wrote:
>> It would be possible to use a GROUP BY / HAVING in the SELECT if that was
>> a
>> possibility for you. I am sure there are much better ways, but this is
>> off
>> the cuff:
>> Select
>> MyDate, Field1, Field2 from table1
>> Group by
>> MyDate,Field1,Field2
>> HAVING MyDate < (Select LookUpDate from Table2)
>> You could always do it in a stored procedure and call in the value of
>> Table2
>> into a variable @.LokUpDate that yo could compare. I assume that the
>> LookUpDate in my example to match your description changes on a regular
>> basis?
>> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
>> http://www.apress.com
>> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
>> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>> >I have a query on a table. This table has a datetime column.
>> >
>> > I have another table. This table also has a datetime column. It has
>> > only
>> > one
>> > row. It basically to store some simple configuration data.
>> >
>> > There is no relation between the tables.
>> >
>> > I want to be able to limit the entries returned by the query on the
>> > first
>> > table by only returning rows that have a date that is less than the
>> > date
>> > in
>> > the row in the second table.
>> >
>> > I'm actually having trouble getting this to work properly. I've tried
>> > subqueries, passing it through the report as a parameter, etc... None
>> > are
>> > working.
>> >
>> > How should i attack this?
>>|||I might be missing something but this looks like the following to me:
select a.* from maintable a, configtable b where a.datetimecolumn <
b.datetimecolumn
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?
I have another table. This table also has a datetime column. It has only one
row. It basically to store some simple configuration data.
There is no relation between the tables.
I want to be able to limit the entries returned by the query on the first
table by only returning rows that have a date that is less than the date in
the row in the second table.
I'm actually having trouble getting this to work properly. I've tried
subqueries, passing it through the report as a parameter, etc... None are
working.
How should i attack this?It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
possibility for you. I am sure there are much better ways, but this is off
the cuff:
Select
MyDate, Field1, Field2 from table1
Group by
MyDate,Field1,Field2
HAVING MyDate < (Select LookUpDate from Table2)
You could always do it in a stored procedure and call in the value of Table2
into a variable @.LokUpDate that yo could compare. I assume that the
LookUpDate in my example to match your description changes on a regular
basis?
Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
> one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
> in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||If I use GROUP BY, will that not require aggregation of the results of the
query?
Perhaps it would be helpful to see the existing query:
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID =MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE ='Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID =INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID = 'acm')
AND (SALESTABLE.SALESSTATUS = 1)
That is table one. I want to limit on a column called CREATEDDATE.
The other query looks like:
SELECT CUTOFFDATE from CONFIGDATA
"Rodney Landrum" wrote:
> It would be possible to use a GROUP BY / HAVING in the SELECT if that was a
> possibility for you. I am sure there are much better ways, but this is off
> the cuff:
> Select
> MyDate, Field1, Field2 from table1
> Group by
> MyDate,Field1,Field2
> HAVING MyDate < (Select LookUpDate from Table2)
> You could always do it in a stored procedure and call in the value of Table2
> into a variable @.LokUpDate that yo could compare. I assume that the
> LookUpDate in my example to match your description changes on a regular
> basis?
> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
> http://www.apress.com
> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> >I have a query on a table. This table has a datetime column.
> >
> > I have another table. This table also has a datetime column. It has only
> > one
> > row. It basically to store some simple configuration data.
> >
> > There is no relation between the tables.
> >
> > I want to be able to limit the entries returned by the query on the first
> > table by only returning rows that have a date that is less than the date
> > in
> > the row in the second table.
> >
> > I'm actually having trouble getting this to work properly. I've tried
> > subqueries, passing it through the report as a parameter, etc... None are
> > working.
> >
> > How should i attack this?
>
>|||You really do not have to add an aggregate function. You can always set a
variable and use that in the Where clause if you do not want to use the
GROUP BY. Something like ( and I added CREATEDATE to the WHERE clause also)
:
Declare @.CUTOFFDATE as DATETIME
SELECT @.CUTOFFDATE=CUTOFFDATE from CONFIGDATA
SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
QTY,
(SELECT SUM(MARKUPTRANS.VALUE)
FROM MARKUPTRANS
WHERE SALESTABLE.RECID = MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
MARKUPTRANS.MARKUPCODE = 'Freight') AS FreightValue,
(SELECT SUM(INVENTSUM.POSTEDVALUE)
FROM INVENTSUM
WHERE SALESLINE.INVENTDIMID = INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
FROM SALESTABLE INNER JOIN
SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID ='acm')
AND (SALESTABLE.SALESSTATUS = 1) AND CREATEDATE < @.CUTOFFDATE
You may have to make this a stored procedure if it will not work on the IDE
for reporting Services.
Rodney Landrum -Author, "Pro SQL Server Reporting Services" (Apress)
http://www.apress.com
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:211E0BCB-A461-485A-B888-333A33E33162@.microsoft.com...
> If I use GROUP BY, will that not require aggregation of the results of the
> query?
> Perhaps it would be helpful to see the existing query:
> SELECT SALESLINE.LINEAMOUNT AS INVOICEAMOUNT, SALESLINE.QTYORDERED AS
> QTY,
> (SELECT SUM(MARKUPTRANS.VALUE)
> FROM MARKUPTRANS
> WHERE SALESTABLE.RECID => MARKUPTRANS.TRANSRECID AND MARKUPTRANS.DATAAREAID = 'acm' AND
> MARKUPTRANS.MARKUPCODE => 'Freight') AS FreightValue,
> (SELECT SUM(INVENTSUM.POSTEDVALUE)
> FROM INVENTSUM
> WHERE SALESLINE.INVENTDIMID => INVENTSUM.INVENTDIMID AND SALESLINE.DATAAREAID = 'acm') AS COGS
> FROM SALESTABLE INNER JOIN
> SALESLINE ON SALESTABLE.SALESID = SALESLINE.SALESID
> WHERE (SALESTABLE.DATAAREAID = 'acm') AND (SALESLINE.DATAAREAID => 'acm')
> AND (SALESTABLE.SALESSTATUS = 1) >
> That is table one. I want to limit on a column called CREATEDDATE.
> The other query looks like:
> SELECT CUTOFFDATE from CONFIGDATA
> "Rodney Landrum" wrote:
>> It would be possible to use a GROUP BY / HAVING in the SELECT if that was
>> a
>> possibility for you. I am sure there are much better ways, but this is
>> off
>> the cuff:
>> Select
>> MyDate, Field1, Field2 from table1
>> Group by
>> MyDate,Field1,Field2
>> HAVING MyDate < (Select LookUpDate from Table2)
>> You could always do it in a stored procedure and call in the value of
>> Table2
>> into a variable @.LokUpDate that yo could compare. I assume that the
>> LookUpDate in my example to match your description changes on a regular
>> basis?
>> Rodney Landrum - Author, "Pro SQL Server Reporting Services" (Apress)
>> http://www.apress.com
>> "Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
>> message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
>> >I have a query on a table. This table has a datetime column.
>> >
>> > I have another table. This table also has a datetime column. It has
>> > only
>> > one
>> > row. It basically to store some simple configuration data.
>> >
>> > There is no relation between the tables.
>> >
>> > I want to be able to limit the entries returned by the query on the
>> > first
>> > table by only returning rows that have a date that is less than the
>> > date
>> > in
>> > the row in the second table.
>> >
>> > I'm actually having trouble getting this to work properly. I've tried
>> > subqueries, passing it through the report as a parameter, etc... None
>> > are
>> > working.
>> >
>> > How should i attack this?
>>|||I might be missing something but this looks like the following to me:
select a.* from maintable a, configtable b where a.datetimecolumn <
b.datetimecolumn
--
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Hunter Hillegas" <HunterHillegas@.discussions.microsoft.com> wrote in
message news:3B202F07-F86B-43FB-9CCA-F7F27F74869F@.microsoft.com...
> I have a query on a table. This table has a datetime column.
> I have another table. This table also has a datetime column. It has only
one
> row. It basically to store some simple configuration data.
> There is no relation between the tables.
> I want to be able to limit the entries returned by the query on the first
> table by only returning rows that have a date that is less than the date
in
> the row in the second table.
> I'm actually having trouble getting this to work properly. I've tried
> subqueries, passing it through the report as a parameter, etc... None are
> working.
> How should i attack this?
Getting a proper COUNT
Hi,
I am new to MDX, so apologies if I am missing anything obvious and any help is greatly appreciated.
I have built a cube designed to display information on patient appointments. My only two measures are [Appointment Minutes] and [Appointment Count]. My clients want information presented in the following format:
Measures [Current Time Period] [Comparative Time Period] [% Change]
Hours Booked a hours b hours c percent
Patients Seen d patients e patients f percent
I have produced the first line of data with the following query:
WITH
MEMBER [Start Date].[Month Hierarchy].[Current] AS
'Aggregate(NonEmpty({[Start Date].[Month Hierarchy].[Date].[2006-10-11 00:00:00]:
[Start Date].[Month Hierarchy].[Date].[2006-11-11 00:00:00]}))'
MEMBER [Start Date].[Month Hierarchy].[Comparison] AS
'Aggregate(NonEmpty({ParallelPeriod([Start Date].[Month Hierarchy].[Year], 1,
[Start Date].[Month Hierarchy].[Date].[2006-10-11 00:00:00]):
ParallelPeriod([Start Date].[Month Hierarchy].[Year], 1,
[Start Date].[Month Hierarchy].[Date].[2006-11-11 00:00:00])}))'
MEMBER [Start Date].[Month Hierarchy].[% Change]
AS '([Current] - [Comparison])/[Comparison]', FORMAT_STRING = '#0.0#%', SOLVE_ORDER = 3
MEMBER [Measures].[Booked Hours] AS
'Measures].[Appointment Minutes])/60', SOLVE_ORDER = 2
SELECT {[Current], [Comparison], [% Change], [Group Average], [Benchmark %]} ON 0,
{[Booked Hours]} ON 1
FROM [Diary]
WHERE [Branch].[Branch Name].[Head Office]
This works fine. I run into trouble, though, when trying to count the patients properly in the current and comparison time columns. There is a [Patient] dimension with an [ID] attribute, and what I really want is a distinct count of how many patients have one or more appointments booked in each time column. So far, all my attempts with Distinct(), Count(), Filter(), NonEmpty() and NonEmptyCrossJoin have come to nothing. If anyone can help here, then I would be really grateful.
If you're using AS 2005, and there is a [PatientID] foreign key in the fact table, you could create a "distinct count" measure like [Patient Count] on the [PatientID] field.|||Thanks for replying, Deepak.
That is what I have been trying to do, but I must be getting the MDX wrong.
MEMBER [No Of Patients] AS 'DISTINCTCOUNT(Filter([Patient].[Public ID], [Measures].[Appointment Count] > 0))' just returns an error. Changing DistinctCount to Count just returns 1, when I know that 12 patients should be returned by the example.
'COUNT(Filter(NonEmpty({[Patient].[Public ID].CHILDREN}), [Measures].[Appointment Count] > 0))' returns a number (the wrong one) and takes a long time to run.
Any suggestions on an expression I could use that would work?
Many thanks,
Ed.
Edit: 'COUNT(Filter(NonEmpty({[Patient].[Public ID].CHILDREN}), [Measures].[Appointment Count] > 0))' does in fact return the right result (apologies - there was an error in the test code), but takes 1 minute, 40 seconds to run. My clients are never going to accept that. I have cut dimensions and attributes down as far as I can. Can anyone suggest a way of querying the count more efficiently?
Any help greatly appreciated,
Ed.
Getting a pool timeout error
I have pages which are using a master page.
An example page on my site would be the homepage, it makes 3 connections to mssql.
1) Get the keywords of the page
2) Get a list of news articles
3) Get the content of the page.
When I visual web express to debug the site it's giving me a pool error message, neither can I get the site to load directly via IIS. It says theres been a pool timeout.
I've read on the internet about making sure connections are closed when you are finished and I've checked that all database connections are closed using
finally
{
conn.Close();
}
does anyone have any idea why I would be having this problem?
How can I see what connections the site is opening, or maybe theres a limit on my server?
I'm using my own test server running windows 2003 and IIS
Don't increase the connection timeout unless there is a REAL need (15 sec by default).
Here are good links will help you hopfully:
http://blogs.msdn.com/angelsb/archive/2004/08/25/220333.aspx
http://www.15seconds.com/issue/040830.htm
http://kb.seekdotnet.com/ViewArticle.aspx?ID=35
Good luck.
|||As far as I can see I have NO leaking connections.
And I'm sure this is relating to the bug in visual studio because this happens when I try to access the page directly via IIS.
Does anyone else have any ideas?
Is there some way I can view connections and their state when I'm debugging the site?
|||Apply the latest service pack.
Yes, you can know the status by using one of the connection object proerity (con.status or something).
Good luck.
|||Checkout this link:http://geekswithblogs.net/chrishan/archive/2007/07/18/114030.aspx
I hope it will help you.
Good luck.
|||Thanks, I dont seem to be any further forward though.
One thing I have noticed is that in the Output window of Visual Studio I get the following error repeated constantly.
"A first chance exception of type 'System.Data.SqlClient.SqlException'' occurred in System.Data.Dll"
|||Surely someone can help with this?
Like I say, I don't believe this problem is with Visual Studio, as it occurs when I run the site directly on the server.
Here is the procedure which seems to be causing the problem, when I dont include this function the site runs fine, however when I include it it wont run and I get the error in my above post in the output window.
1protected void setConfigKeywords()2 {3// Define data objects4 SqlConnection conn;5 SqlCommand comm;6 SqlDataReader reader;78// Read the connection string from web.config9string connectionString = ConfigurationManager.ConnectionStrings["AWT"].ConnectionString;1011// Initialise the connection12 conn =new SqlConnection(connectionString);1314// Create command15 comm =new SqlCommand("SELECT * FROM Config WHERE ConfigID = 1", conn);1617try18 {19// Open the connection20 //conn.Open();2122 // Execute the command23 reader = comm.ExecuteReader();2425while (reader.Read())26 {27string title = reader["ConfigMetaTitle"].ToString();28string keywords = reader["ConfigMetaKeywords"].ToString();29string description = reader["ConfigMetaDesc"].ToString();30 }3132//Page.Title = title;33 //HtmlHead head = (HtmlHead)Page.Header;34 //Cls_Meta.setHeaderInfo(head, description, keywords);3536 // Close the reader37 reader.Close();3839 }40catch (Exception ex)41 {42string errorPage = ConfigurationManager.AppSettings["errorPage"];43string errorMsg ="Problem getting general keywords from database : " + ex;44 Server.Transfer(errorPage +"?errormsg=" + errorMsg);45 }46finally47 {48// Close the connection49 conn.Close();50 }51 }
Getting a permission denied - but not using that user?
In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Con
nection=False"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Con
nection=False"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
Getting a permission denied - but not using that user?
In my database I created a SQL Server user - malphaTest
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Connection=Fa lse"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?
No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
sql
In my WebConfig.xml
<add key="SQLConn"
value="Data Source=HIDSCFILE002;Initial Catalog=Membership;User
ID=malphaTest;Password=12345;Trusted_Connection=Fa lse"/>
</appSettings>
The error returned when press the search button to query the database is:
EXECUTE permission denied on object 'qMemberSelect', database 'Membership',
owner 'dbo'
Shouldn't it refer to malphaTest? not dbo?
No, the owner of the object that you aquired is dbo. So you have to
grant the user malphaTest EXECUTE rights on the procedure
/dbo.'qMemberSelect') to get around this error.
HTH, Jens Suessmeyer.
sql
Getting a parameter to depend on another parameter.
I'm putting together a crosstab showing some facts for a year, compared to the year before (so we're showing 2 years). Years in the columns and a dimension in the rows. Nothing fancy, but we do want to put in a parameter so the users can choose which year they want to see. So I put a range and get two parameters. We now want the first parameter, the "from", to take the value of the "to" - 1. I can get the same value in there, but appearantly getting the previous year isn't as simple as just taking the parameter - 1.
A helping hand would be great. This all seems a bit overkill for what to me feels like an easy report, so I feel like I'm missing something.
A helping hand would be great. This all seems a bit overkill for what to me feels like an easy report, so I feel like I'm missing something.
We're building this report on a cube.
Hi,
In the Report Parameters dialog, set the Default value of your second parameter to Non Queried and enter the following in the Expression Editor
Code Snippet
=CINT(Parameters!<Param1>.Value) - 1
Replace with the name of your parameter and this should achieve what you want to do.
HTH.
Cheers,
Leigh
|||That gives me an error while previewing. More precisely, when I select the "to" in preview, I get:
Code Snippet
An error occured during local report processing.
Error during processing of of "FromYear" report parameter.
Getting a Package's ExecutionID
Hello everyone
I'm logging the execution of my package into a SQL SERVER table. Everything works right. I'm also executing this package using a web service, I'd like to retrive the package's executionId after it is executed. By the way, i'm using a Microsft.SqlServer.Dts.Runtime.Package object but i haven't found any property that could give this.
any clue?
thanks a lotHave you searched this forum for executionID? There has been many threads around this topic and the current version of SSIS' shortcomings with respect to logging the execution ID.|||There is a system variable ExecutionId. Try that for now, not sure if the value is available post execution, but would seem sensible.|||I mean there is a system variable called ExecutionInstanceGUID which is the same value as you get logged in the sysdtslog90 table.
Getting a Major error.....
When i try to launch SQL 2005, I'm all of a sudden getting the following error:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
JJ
Maybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJ
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
JJ
Maybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJ
Getting a Major error.....
When i try to launch SQL 2005, I'm all of a sudden getting the following error:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
--
JJMaybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJ
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
--
JJMaybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJ
Getting a Major error.....
When i try to launch SQL 2005, I'm all of a sudden getting the following err
or:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
JJMaybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJsql
or:
"SqlWb.exe - Application Error
The application failed to initialize properly (0xc0000034). Click on OK to
terminate the application."
Any suggestions or recommendations? Tried to search the knowledgebase, but,
couldn't find anything.
Thanks.
JJMaybe I am wrong but looks like SqlWb is Management Studio so perhaps SQL
Server is really running. Try connecting from another client like Query
Analyzer to see if the instance is running.
With no additional information perhaps what I would try is rebooting the
computer and, if the problem is still there, reinstalling the client tools.
Regards,
Ben Nevarez
"Jeff" <Jeff@.discussions.microsoft.com> wrote in message
news:658463FC-52F7-493E-9FF7-7DA363D12681@.microsoft.com...
> When i try to launch SQL 2005, I'm all of a sudden getting the following
> error:
> "SqlWb.exe - Application Error
> The application failed to initialize properly (0xc0000034). Click on OK to
> terminate the application."
> Any suggestions or recommendations? Tried to search the knowledgebase,
> but,
> couldn't find anything.
> Thanks.
> --
> JJsql
getting a list of user created tables ONLY
sql server 2k
I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.
I am currently using the following. Isn't there a more built in way to do
this?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'Here's one way...
--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
(Table_Qualfier varchar(100),
Table_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100),
Table_Count numeric(9))
--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables
--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||See view information_schema.tables and function objectproperty.
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go
AMB
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||Thanks to the two of you.
Alejandro, that was the ticket. Gracias!!
"Alejandro Mesa" wrote:
> See view information_schema.tables and function objectproperty.
> Example:
> use northwind
> go
> select
> *
> from
> information_schema.tables
> where
> table_type = 'base table'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
> AMB
> "kevin" wrote:
>
I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
each case I also get a table called dtproperties and, in neither case, is
there a logical way to tell one apart. I am also adverse to using
undocumented system tables seeing as sql server 2005 is just around the
corner and upgrading is more than likely... and its a bad idea.
I am currently using the following. Isn't there a more built in way to do
this?
SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
FROM INFORMATION_SCHEMA.TABLES
WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'Here's one way...
--Get all the dbo-owned Tables together and exclude system, view, and tables
begining with 'ARCH_' (Archive tables)
Create table #IntermediateTableList
(Table_Qualfier varchar(100),
Table_Owner varchar(100),
Table_Name varchar(100),
Table_Type varchar(100),
Remarks varchar(100),
Table_Count numeric(9))
--Create table #IntermediateTableList (Table_Name varchar(100), Table_Count
numeric(9))
Insert into #IntermediateTableList (Table_Qualfier, Table_Owner, Table_Name,
Table_Type, Remarks) Execute sp_Tables
--Exclude non-dbo-owned tables, system tables, views, and tables begining
with 'ARCH_' (Archive tables)
Select Table_Name, Table_Count into #FinalizedTableList from
#IntermediateTableList where (Table_Type <> 'system table' and Table_Type <>
'view' and Table_Name NOT LIKE 'ARCH_%' and TABLE_OWNER = 'dbo')
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||See view information_schema.tables and function objectproperty.
Example:
use northwind
go
select
*
from
information_schema.tables
where
table_type = 'base table'
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsUserTable') = 1
and objectproperty(object_id(quotename(table
_schema) + '.' +
quotename(table_name)), 'IsMSShipped') = 0
go
AMB
"kevin" wrote:
> sql server 2k
> I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in
> each case I also get a table called dtproperties and, in neither case, is
> there a logical way to tell one apart. I am also adverse to using
> undocumented system tables seeing as sql server 2005 is just around the
> corner and upgrading is more than likely... and its a bad idea.
> I am currently using the following. Isn't there a more built in way to do
> this?
> SELECT TABLE_SCHEMA + '.' + TABLE_NAME AS USERTABLE
> FROM INFORMATION_SCHEMA.TABLES
> WHERE table_type = 'base table' AND TABLE_NAME <> 'dtproperties'|||Thanks to the two of you.
Alejandro, that was the ticket. Gracias!!
"Alejandro Mesa" wrote:
> See view information_schema.tables and function objectproperty.
> Example:
> use northwind
> go
> select
> *
> from
> information_schema.tables
> where
> table_type = 'base table'
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsUserTable') = 1
> and objectproperty(object_id(quotename(table
_schema) + '.' +
> quotename(table_name)), 'IsMSShipped') = 0
> go
>
> AMB
> "kevin" wrote:
>
Getting a list of user access to which databases - help
Hi ,
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgs
Hi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||You can check the stored procedure sp_helplogins
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
i know there's a view "sxyslogin" in Master database that is able to show a
list of user with the default database that they have access to
however , i like to get a list of users with all the databases that they are
able to access, how can i do that with the rights that they have in these
databases as well ?
for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
has the access to these users
appreciate any advise
tks & rdgs
Hi,
Execute the system procedure sp_helplogin to get all the users with
associated access to databases.
For displaying object level previlages for the user , execute sp_helprotect.
See the reference of both procedures in books online.
Thanks
Hari
SQL Server MVP
"maxzsim" <maxzsim@.discussions.microsoft.com> wrote in message
news:455416BC-1ED3-4F69-B9DB-E17A078A7C79@.microsoft.com...
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show
a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they
are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that
userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||You can check the stored procedure sp_helplogins
best Regards,
Chandra
http://chanduas.blogspot.com/
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
|||Here you go this query will match up the sysdatabases which is the list of
databases you need with the sysusers information which will give you a
results set of databases and the user for that database. Then if you want to
go a little further and match that sid with sysxlogins if you need some
information from there. The in clause makes it where you dont have to see
all the user information for sql internal usage.
Hope this helps.
Select a.name,
b.[name],
b.[UID],
b.[SID],
b.[ISSQLROLE],
CASE WHEN b.[ISSQLUSER] = 1
THEN 1
ELSE 0
END AS issqluser
from [master].[dbo].[sysdatabases] a ,
[master].[dbo].[sysusers] b
WHERE b.[name] NOT IN (
'public',
'db_owner',
'db_accessadmin',
'db_securityadmin',
'db_ddladmin',
'db_backupoperator',
'db_datareader',
'db_datawriter',
'db_denydatareader',
'db_denydatawriter',
'dbo',
'guest',
'INFORMATION_SCHEMA',
'system_function_schema'
)
"maxzsim" wrote:
> Hi ,
> i know there's a view "sxyslogin" in Master database that is able to show a
> list of user with the default database that they have access to
> however , i like to get a list of users with all the databases that they are
> able to access, how can i do that with the rights that they have in these
> databases as well ?
> for example userA has access to DB1 , DB4 , DB5 , i need to show that userA
> has the access to these users
> appreciate any advise
> tks & rdgs
Subscribe to:
Posts (Atom)