@@FETCH_STATUS

Hi there..
I have this problem: I run my stored proc alot of times for testing, but one
time it want work.
It just give me back this message: The command(s) completed successfully.
I checked the @.@.FETCH_STATUS and it became -1, why is that when it worked
before..
Can any body help me'
THX@.@.FETCH_STATUS is for cursors, you want to use @.@.ERROR instead if it's 0
then there is no error
http://sqlservercode.blogspot.com/
"Hussain Al-Mousawi" <hal_mousawi@.hotmail.com> wrote in message
news:uv6dVVTRGHA.2532@.TK2MSFTNGP10.phx.gbl...
> Hi there..
> I have this problem: I run my stored proc alot of times for testing, but
> one time it want work.
> It just give me back this message: The command(s) completed successfully.
> I checked the @.@.FETCH_STATUS and it became -1, why is that when it worked
> before..
> Can any body help me'
> THX
>

.net Security Exception

I am trying to access a stored proc from windows app (VS 2005 running on windows 2003 server).

The code is

SqlConnection conn = new SqlConnection(sCn);

SqlCommand command = new SqlCommand(sCmd, conn);

SqlDataAdapter adapter = new SqlDataAdapter(command);

DataSet ds = new DataSet();

adapter.Fill(ds, "Location");

this.dg.DataSource = ds;

this.dg.DataMember = "Location";

I get the following exception!!!

System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet)
at System.Security.PermissionSet.Demand()
at System.Data.Common.DbConnectionOptions.DemandPermission()
at System.Data.SqlClient.SqlConnection.PermissionDemand()
at System.Data.SqlClient.SqlConnectionFactory.PermissionDemand(DbConnection outerConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at System.Data.SqlClient.SqlConnection.Open()
at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
at WindowsApplication1.Form1.Form1_Load(Object sender, EventArgs e)
at System.Windows.Forms.Form.OnLoad(EventArgs e)
at System.Windows.Forms.Form.OnCreateControl()
at System.Windows.Forms.Control.CreateControl(Boolean fIgnoreVisible)
at System.Windows.Forms.Control.CreateControl()
at System.Windows.Forms.Control.WmShowWindow(Message& m)
at System.Windows.Forms.Control.WndProc(Message& m)
at System.Windows.Forms.ScrollableControl.WndProc(Message& m)
at System.Windows.Forms.ContainerControl.WndProc(Message& m)
at System.Windows.Forms.Form.WmShowWindow(Message& m)
at System.Windows.Forms.Form.WndProc(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.OnMessage(Message& m)
at System.Windows.Forms.Control.ControlNativeWindow.WndProc(Message& m)
at System.Windows.Forms.NativeWindow.Callback(IntPtr hWnd, Int32 msg, IntPtr wparam, IntPtr lparam)
The action that failed was:
Demand
The type of the first permission that failed was:
System.Data.SqlClient.SqlClientPermission
The Zone of the assembly that failed was:
Internet

What am I doing wrong?

Thanks

SqlCommand command = new SqlCommand(sCmd, conn);

SqlDataAdapter adapter = new SqlDataAdapter(command);

what exactly is command refering to?

Secondly, if it's refering to a networked application, you probably have to publish the assemblies via caspol on the server.

Based on the error, you need to add a key for the Internet codebase.

.Net 2.0 and datetime

Hi,

Here's my problem.

So using a stored proc and some parameters I want to update a datetime field

So I define @.theDate as datetime in my stored proc

in my vb.net app I have

dbcmd.Parameters.AddWithValue("@.theDate", SelectedDate)

where SelectedDate is

Dim SelectedDate as Nullable(of Datetime)

Now if SelectDate is a value all is good with the world.

However if SelectDate is Null I get an error.

How do I pass a null date to the stored proc ?

You need to add code like this:

dbcmd.Parameters.AddWithValue("@.theDate", (SelectedDate==null) ? DbNull.Value : SelectedDate)

DbNull.Value is a null for the database.

.exe from stored procs

Hey guys.

I am wondering if it is possible to execute an .exe from a stored proc. I have an application that calls a stored proc and I am wanting for that stored proc to call up an app to show certain results. I have no way of doing this on the application itself as I dont have the source code. So, since I do have access to the SQL I am wondering if it could be done there.

Thanks
tiboryou can launch an exe from a proc with xp_cmdshell. you can't display any of it's output though, except for whatever it writes to stdout.

if it's a GUI app, there's no point to it. Also, remember that this app would have to be located on the server.

Be very careful with this. If you enable xp_cmdshell, it opens up a fairly large security hole on your db server. Make sure only appropriate logins have perms to execute it. don't give any clients permissions to execute it directly or you may find that a criminal has stolen all your data.|||It's not advisable

You could bring down the server from what I understand

But if you want to try you need to use master..xp_cmdshell

But if you don't know about the sproc, then I'd really advise against it|||Thanks guys

Turns out, I may be able to avoid the project in its entirety but this is good info to know for the future.

Again, very much appreciated :)|||just be careful if you ever decide to use it. xp_cmdshell is a cross between a loaded gun and a vial of nitroglycerin.|||just be careful if you ever decide to use it. xp_cmdshell is a cross between a loaded gun and a vial of nitroglycerin.

pffffftttttt

The real danger is trying to launch an application with it

Hey DROP Database is kinda dangerous as well|||the real danger is that you can use xp_cmdshell to do anything you could do from a command prompt, which is pretty much anything.

if the wrong people have rights to execute xp_cmdshell, you are in trouble. so best left disabled.|||if the wrong people have rights to execute xp_cmdshell, you are in trouble.

Of course, would you give a gun to a toddler?

so best left disabled.

Couldn't disagree more|||Tibor, don't listen to Brett.

he's a wild vocalist (http://www.rockdetector.com/artist,5489.sm) for an all-girl 80's hair-metal band. everyone knows such types can't be trusted.

:)|||belly aching laughter. on the floor. verge of tears.

but really if your shop is not locked down with regards to db server security, shame on you. Although I have been somewhere that refused to be locked down. So I left.

I find xp_cmdshell extremely useful especially combined with osql. I do my database builds that way by doing a fresh dump from source control and letting those rip with a little cursor and some logging. A little Redgate afterwards to be sure and everything is right as rain.|||Hi,

I've quite the same Problem as Tibor.
I also try to call a .exe from a trigger.
The .exe is a VB.Net command line application, that shall invoke a ETL script on a third party BI Application. The VB application first calls the getObject Function and then calls the Reload Function of the BI application.
This application works fine, when I start it in the command line.
But when I try to call it with the xp_cmdshell proc the execution stucks and after a while an exception is thrown ("ActiveX component couldn't be started" or so on)
Until now, I haven't found any way to fix this problem.
Now I'm searching for another way of calling an .exe from a trigger. I don't know, if this helps. Maybe you have any suggestions to deal with this problem
Thanks in advance

Gojo|||you want to call an exe from a trigger? pardon me, but that's a less than good idea. It's quite bad actually.

triggers need to have a very constrained scope - they shouldn't be launching other processes, sending emails, writing data to remote servers, calculating your horoscope, or anything like that.

if you do somehow figure out how to launch an exe from a trigger, the perf or your application will suffer big time.

what are you trying to achieve with this? why do you think launching an exe is the way to do it?|||triggers need to have a very constrained scope - they shouldn't be launching other processes, sending emails, writing data to remote servers, calculating your horoscope, or anything like that.

If you're using SQL Server 2005, you can use the Service Broker to do such "crazy" stuff without ending up with long running transactions.|||Thanks for the fast replies

@. jezemine
I know, the idea sonds weird. My problem is, that I want to keep my data in the dashboard event triggered.
In this case a new DB entry is a event. But when I'm constantly polling the table (as I do now) the solution isn't event triggered. The only way to achieve this, is to call the .exe (which calls the load script), when there is a new entry in the table (therefore the trigger).
Do you have another idea how to accomplish that (I mean how to tell my .exe that there's a new entry, so that the .exe can call the load script)?

@.kaffenils
Unfortunately I have to use the 2000 version

thanks
gojo|||you could write an app that polls the table every so often, and when there's a new row then it executes your load script.

I don't understand what you mean by "the solution isn't even triggered" btw.|||Currently I poll the table every 5 secs. If there's a new row, I execute my script. This works fine, but as I wrote before, this solution is not what I actually want.
I don't want a permanent polling. I want the new Table entry to be the "initiator" of the script execution. When I poll the table, the poll query is the "initiator".
I hope I could make clear, what I want.

thanks
gojo|||Maybe my Problem could be solved another way.
Is there a possibility in SQL Server 2000 to access a class which is written in .NET and which is "stored" in the Global Assembly Cache (GAC)?
Then I could access the reload command.

Thanks gojo|||I'd probably create a SQL Agent job that did whatever "heavy lifting" was required (running the ETL process, etc), then use either sp_start_job (http://msdn2.microsoft.com/En-US/library/aa260289(SQL.80).aspx) or some closely related form of trickery to actually start the job itself.

-PatP|||note that would fail if the job is already running. you can't have two instances of the same job running at once.|||Thanks for the replies.
I've found an interesting site.
http://sqljunkies.com/Article/C5A500EB-B8BE-42C0-B23B-258A342CAAAB.scuk
I think that's exactly what I want.
I hope it works.

thanks gojo|||invoking that kind of thing from a trigger still counts as a no-no in my book.|||I agree, that this shouldn't be implemented in practice.
In my case this solution is more a feasibility study, if that kind of idea (new entry as trigger of a new Data Load) can be implemented in a technical way.|||note that would fail if the job is already running. you can't have two instances of the same job running at once.Failure to start the second instance would be a bad thing? I can't imagine a case where you'd want two separate ETL loads running at once. I suppose that you could queue the subsequent jobs somehow if there was any chance that you really wanted that to happen.

-PatP|||I have no idea if that would be a bad thing or not - depends on OP's requirements. I just stated is as a fyi for the OP.|||Ok so I have been playing around some with xp_cmdshell(yes, I know...not good, lol) but when I run the .exe, my query analyzer hangs up for about 10 minutes.

exec master..xp_cmdshell 'C:\test.exe'

It is just a little vb app i created to test with but it hangs up and does not execute.

I know that I have permissions and such as i can do a simple dos directory seach with it.

Any ideas as to why it hangs up?

Thanks|||It seems to be running it in the processes list of task manager but not actually running the application. Is it possible to actually run the app?|||If your application is expecting someone to click an OK button, you will be waiting a long time.|||Yeah, I just found out that it wont allow it...so i withdraw my question. heh|||If your application is expecting someone to click an OK button, you will be waiting a long time.. ;)|||If your application is expecting someone to click an OK button, you will be waiting a long time.

really? whenever I write a trigger, i have it pop a message box to ask the user if they really want to commit the data. it works great!

:)|||In order to pass a moderate SOX audit, you would have to ask that question twice. "Are you sure you want to commit that data?" "Really sure?"

(re)using a temporary table in a stored proc (was "Confusion")

Hi folks, I have a procedure that pefroms some action and creates the outputs to a temporary table #mytable. I want to call this procedure and take the results from #mytable within the procedure. Can i. If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?

Howdy!Yes..the local temp table is only around for the length of process...

A global temp table sticks around as long as it's referenced by any process...

So you can do something like this...basically wrapping your sprocs with a driver sproc

USE Northwind
GO

CREATE PROC mySproc00
AS
BEGIN
UPDATE ##myGlobalTemp99 SET CustomerId = 'VINET'
END
GO

CREATE PROC mySproc99
AS
BEGIN
SELECT * INTO ##myGlobalTemp99 FROM Orders

SELECT TOP 10 * FROM ##myGlobalTemp99

EXEC mySproc00

SELECT TOP 10 * FROM ##myGlobalTemp99

DROP TABLE ##myGlobalTemp99

END
GO

EXEC mySproc99
GO

DROP PROC mySproc00
DROP PROC mySproc99
GO|||Hi, sir. thanx for helpin around. there's a little problem though. The procedure will be executed by many users concurrently; i get error; table already exists when older session persist and another executes the procedure.

Any other guidlines!

Howdy!|||post what the table looks like...and what the sproc is suppose to be doing...|||Instead of Temp table, declare table type variable and work with that.

Madhivanan|||I believe he needs to pass and store data between processing threads...table variables won't let you do that.

Why I asked for the Table Layout is this.

Each Process gets it's own unique spid.

I would create a permanent table, and and a column for the spid.

Capture the spid, from a driver sproc, and for every row in the process make sure you use that spid.

Just make sure you clear out the rows for your spid before you start the process.|||Yes, Brett; that idea helped. thanx. :)

Howdy!|||Depending on the volum of activity...I might consider a partitioned view using the spid as the partioning range in different files and on different drives...

I'll take a look into this...|||I hate when people are so dogmatic in their statements. What would happen if you run this code? DON'T RUN IT, just answer the questions first!

create proc sp_1 as selct * from #t
go
create proc sp_2 as
ceate table #t (f1 int null)
insert #t select 1 union select 2
exec sp_1
go
exec sp_2
go
drop proc sp_1, sp_2
go

Here, regardless of how many users execute sp_2 simulteneously, they will always see only their temporary tables.|||Doooh...it's the same spid

As long as it's all referenced in the same driver your temp table is isolated..I got confused that it was with separate threads

Even with that...what I suggested wouldn't buy you anything anyway

Thanks for pointing that out...|||Doooh...Even with that...what I suggested wouldn't buy you anything anyway...So why did you suggest it? :D|||Cause I'm a moron...

It's like flying down the freeway...I missed the exit

You could use that method to pass data to a trigger though...|||So if that is not causing the problem he is having, then what is?

If i call #mytable after executing the procedure; won't work.

This seems to indicate he wants to access the table through a different connection...|||If i call #mytable after executing the procedure; won't work. Means that the table gets dropped and doesn't prolong for the session?Actually it looks like the same connection, unless his definition of a "session" is different...|||It's like flying down the freeway...I missed the exit...Don't feel bad, I use turn-arounds very often :D|||Then maybe he should post his code, 'cause something must be going wrong...|||He's probably creating the temp in the nested proc

The temp needs to be created in the driver

I imagine this is what's going on

create proc sp_2 as
create table #t (f1 int null)
insert #t select 1 union select 2
go

create proc sp_1 as
EXEC sp_2
select * from #t
go

exec sp_1
go

drop proc sp_1, sp_2
go|||Hi folks; u've been so kind! Sorry for getting back late;

I've a proc proc1 that performs some routine and outputs a temporary table #tab1. I want this table be accessed through other procs or within a batch that calls this procedure; WITHIN THE SAME CONNECTION(SESSION,SPID :))

The only way around i could find was the SPID idea into a permanent table.

The proc1 is a standard routine and many other procs or scripts need the output from it; i wonder if i could use function that returns a table... that could be used further.

Howdy|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua|||actually i need to collect the record from a few join table. and then calculate the score and group the date in weekly basic.

so now i able to select all the data from the source, and then use case to create a column call weekGroup. but i fail to group weekGroup for the source. so i plan to create temporary table and store all the record that i select from the join table and then do again the select statement from the temporary table to group the data again.

anyone can help me?:eek:

regards
terence chua

First read Brett's sticky on top of the forum.|||...and start a new thread rather than resurrecting this old one...