; -c -t"," -C -Usa -P1210 bcp because we have Sybase as well on the machine) However, when I execute the code below it form a sp it fails. declare @statement varchar(2000) set @statement = '"C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp" "select Severity from tempdb..EventsArchive" queryout c:\aaabbbcc.txt" have trouble with embedded spaces, consider using FAT 8.3 file names as a workaround. no_output Is an optional parameter executing the given command_string, and does not return any output to the client. Mike
Why won't this line work in a stored proc? SELECT @current_bal = TOP 1 NewBalance FROM Cash ORDER BY GLID DESC It is fine if I take out TOP 1 but the result is erroneous. What I'm trying to do is get the last balance from the Cash table into a variable. I will then add it to the amount I'm posting and store the result in the NewBalance column when I INSERT the new transaction. How should I be doing it? TIA!Ron, Shouldn't matter if it's in a proc or not. Try: SET @CURRENT_BAL = (SELECT TOP 1 NEWBALANCE FROM CASH ORDER BY GLID DESC) JerryRon, Or SELECT TOP 1 @CURRENT_BAL = NEWBALANCE FROM CASH ORDER BY GLID DESC JerryThanks Jerry! DESC) will TIA!
user: - public: ON - Db_denydatawriter: ON - Db_datareader: ON With those settings, the user cannot execute any store procedures even though the store procs only read data. If I add - Db_owner: ON then the user will be able to excute that also modify data! I know that I can go to individual database and overwrite the setting for each store proc but store procs are changed often, some of them are hugh - it's not easy to control which of them before? Please help!! Thanks!!Hi, Can you please remove the "ON" from db_denydatawriter role and try executing the procedure. Hari MCDBA cause
I'm trying to build a select statement with a variable as part of a column name to build a result table I need. I thought of using ;A","D","SD","NA") TH The statement itself work when I run it but not in the SP. JasonJason A syntax error is not the same as . Did you increase the capacity of the varchar? Does the real error message chop off the statement like this? Where do you get the statement itself to say that it works when you run it? There's really not enough this foolishness and just use Rac. I know you don't want to encourage this sort of thing! statement it?
a temp table. It then executes SP2 to populate the table. Then it runs a select statement that is joined to the temp table. Finally, it drops the temp table. SP3 executes the following command : BCP "EXEC myDB.dbo.sp1" queryout C:\myFile.csv -c -T -t, When I run it the setup above. Any advice is appreciated, SQL ServantHi, You can not use stored procedure as source for bcp. Use query instead.Tomasz That is not true, see my postIt was true on SQL2k and older, I users will see different datasets. SQL ServantDoes adding SET FMTONLY OFF in the beginning of the call or proc help? Note this has consequences so Google before your using in production Tibor, I have tried SET FMTONLY
datepart(mm,prod_date)=datepart(mm,@datenew) I want to BCP this result to a server location from within the store procedure and I did exec master..xp_cmdshell BCP ##tetable OUT "//server/folder/rsult.txt" call (which may be what is causing the SQL parser to find an error), put your BCP command in a Windows NT .cmd command file. Then use xp_cmdshell to execute this .cmd file . This should eliminate any wacky allowable syntax differences between what BCP allows and what the SQL parser allows. Also, I don't know if you you left it out in your stored proc, but you might want to try enclosing the entire BCP command syntax in single quotes: exec master..xp_cmdshell 'BCP ##tetable OUT "//server/folder
"encomment" the port and shelf and "decomment" the store. Soooo, how would I do this in a stored proc? The performance is such that you have to pass in at least one of these, but I might want three, and basically at the user's whim. I know I can put in default values in the CREATE PROC parameter list, but the only reasonable default for these is "null" which means that the where will fail to match on anything. Is there an easy way to do this? That is, make a single SELECT statement that will operate with one or more inputs set to null? MauryThe bible on this is here: http://www.sommarskog
tool that allows you to execute sql dynamically. The problem is that if I execute a stored proc - it seems to run in a read-only state. The SP _should_ perform an update and then a 39;ve been banging my head on this all night. I ran profiler and it found the execute statement and the username that was used has update/insert access to all tables so security is not the issue. I then thought that it was executing the dynamic sql on an uncommitted isolation level and threw in a "SET TRANSACTION ISOLATION LEVEL I'm an admin on this DB. GabeDynamic SQL means that the user executing the proc need permissions to perform the modifications inside the proc (UPDATE permissions). In any event, you should get an error message if it
deal of time searching system views to find info on currently executing procs. What I found was statements which are currently executing. The only way I can get this proc names is to run dbcc inputbuffer(spid). Any advises? Thank same as to browse current activity from studio and click on actvie process so it would show you which proc this statement part of. dbcc inputbuffer works but I was wondering if there is some other way exists. Gene.Gene, = sql_handle, @Stmt_Start = stmt_start, @Stmt_End = stmt_end FROM master.dbo.sysprocesses WHERE spid = @SPID SELECT SUBSTRING (text, 1, 255) AS CommandString, @Statement = SUBSTRING (text, @Stmt_Start / 2, CASE(@Stmt_End) WHEN (-1) THEN DATALENGTH(text) ELSE (@Stmt_end - @Stmt_Start) / 2 END) AS StatementString