I have two SQL Server 2000 (one is localhost, one is remote with VPN IP 192.168.5.4).
I can select * from [192.168.5.4].db.dbo.test but I can't exec [192.168.5.4].db..spAdd in localhost.
These select and sp is OK for 1 or 2 week without any problem,but it didn't work one day.
Can some one explain why?
I am writing a stored procedure to select some information from two tables and I would also like to Execute a function using the userid information from the processing in the where clause of the Select statement. Is the syntax below possible?? If yes, could you please help me understand exactly what I may be doing wrong here.. If no, can you please help with better syntax. Thanks in advance
SELECT M.UserID, M.FullName, (EXEC calcPoints M.UserID) as 'UserPoints'
FROM MissionUsers M LEFT OUTER JOIN MissionUserInfo MU ON M.UserID = MU.UserID
WHERE M.EMAIL = @UserEmail
Hi All...
if i had the following sp...
*******************************************************
create procedure my_insert (param1 int, param2 int, paramx int)
as
...
complicated insert routine
...
return
*******************************************************
and then i wanted to exec this sp in another procedure i would have
exec my_insert( 1_value, 2_value, 3_value )
My question is how could i exec this will the result set of a select.... something like this
exec my_insert (select 1_value, 2_value, 3_value from another_table).
I know i could have this in an insert result type statement ie...
insert into dest_table (select 1_value, 2_value, 3_value from another_table)
but my insert routine is quite complicated and carries out some other functions so I would like to call (exec) a sp rather than repeating the complication in the select statement
Many Thanks
Gary T
hi,
can somebody tell me how to call SP in another SP select statement..? i've seen ppl doing it something like this..
Select column1, coulumn2, (select EXEC SP_NAme), blabla
thanks in advance... :)
HiI have a dynamically constructed sql query that I want to execute, e.g.exec('select * from ' + @tablename)(1) Can I suppress the output somehow if this returns no values?(2) Can I use the result of this query in another query somehow? e.g.select(3) Can I control the size of the columns in the output somehowThanksF
View 3 Replies View RelatedCode:
exec('select RIGHT(00000 + CAST(dh.zipcode AS varchar(5)), 5) as zip, '+@fieldname +' as distance into #distance from sumplicity.dbo.t_distancetohospital dh')
This runs normally and returns the number of rows created. Yet when I do a 'select * from #distance' query, I get a message that #distance doesn't exist.
Where is it storing all of this and how do I access it?
I'd *like* to execute a sProc within another sProc, because this other one (let's call it dataProc) will provide me with results I need that are calculated based off execing other procs within that proc itself.
I'd like to use this data in a new sProc (we'll call it newProc), but I don't need NEARly all of the columns. The dataProc returns 1 row with 42 columns. I need about 4-5 of those for my newProc.
The dataProc does not have any output variables, and I do not want to change the signature, because several pages/apps use this existing dataProc.
My question:
Is there a way to INSERT INTO table EXEC dataProc
*Without* making a temp table that takes in every single column the dataProc puts out?
Any help is much appreciated.. thanks!
DECLARE @X VARCHAR(10)
DECLARE @NUM_MEMBERS SMALLINT
SELECT @X = 'other_db'
@NUM_MEMBERS = EXEC ('USE ' +@X + '; SELECT COUNT(Member_Name) FROM Logins')
I get an error when I try to store the result in @NUM_MEMBERS. :/ I've been looking all over for the correct syntax. Can anyone help?
ERROR:
Server: Msg 170, Level 15, State 1, Line 6
Line 6: Incorrect syntax near '@NUM_MEMBERS'.
thx n adv
some one can tell if i can make a "where" clause inside the BCP , when i say : select * FROM tbl where flag=N , usually the where flag='N' works with the ' ' , but it gives no error when save the store procedure without it ' ' saves nicely the SP,
but
like this it saves well "SELECT * FROM tbl where flag=N" and
when i execute it, give a sql statement error "Error = [Microsoft][SQL Native Client][SQL Server]Invalid column name N."
i dont now , any help
PS: perhaps at the end of the BCP ---> -c -T , must have some more or less
Hello,Is it possible to EXEC stored procedure from a query?I want to execute stored procedure for every line of SELECT resulttable.I guess it's possible with cursors, but maybe it's possible to make iteasier.Give an example, please.Thank you in advance.Hubert
View 2 Replies View RelatedHello everybody!
As the topic:
Can i get the value "count(*)" from EXEC('select count(*) from xTable')
Any helps will be usefull! Thanks!
Hello, I recently view a webcast of sql injection, and at this moment I created a user, and give dbo to this user, and this same user, is the one I have in the connection string of my web application, I want to create a user to prevent sql injection attacks, I mean that user wont be able to drop or create objects, only select views, tables, exec insert,update, deletes and exec stored procedures.
Is any easy way to do this?
A database role and then assing that role to the user?
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
When I use EXEC in a stored procedure ( after building complex option logic) it produces an returns an error of 'Access denied' on the underlying tables.
All objects are dbo owned and execute permission has been given to all users.
Can ant one help?
Rob
When using a SP for getting a recordset is there any issues with using exec like in: rs.open "exec spWhatever"...
Should I use rs.open "spWhatever" or does it really matter performance wise on the SQL server?
Thanks
I am trying to create a awkward sp, just need to know if i can do this somehow, here i piece of the code...
create procedure IM_SP_TrPreProc /*@TableName Varchar(255),*/ @SystemFileName Varchar(255)
---------------------------------------------
--Param1 = Tablename
--Param2 = Systemfilename
---------------------------------------------
as
declare @TableName Varchar(255);--Just For Testing---DELETE!!
declare @Filename varchar(255); --Store Distinct filename
declare @DSNo Varchar(255);-- Use 'set' to execute Var TableName
declare @SumUnits Varchar(255); --Use 'set' to calculate sum of units
declare @SumValue Varchar(255);
Set @TableName = 'TrDs01' -- Testing Only--DELETE!!
------------------------Set Statements using @TableName Var------------------------------------------
Set @DSNo = 'select distinct DataSupplierNo from ' + @TableName
Set @SumUnits = 'select sum(Units) from ' + @TableName
Set @SumValue = 'Select sum(Value) from ' + @TableName
------------------------------------------------------------------------------------------------------
Insert into TransactionMaster([FileName],DataSupplierNo,ImportFileRecordID,FileLoadDate,
UnitsSum,ValueSum,RecordCount)
Select(@Filename),(exec(DSNo)), ................
Just the Bold and underlined bit "exec(DSNo)"..... is this doable in some way? can i use exec to retrieve the value to insert to data supplier. As far as i know i have to do it like this because im using a variable as the table name...
I need help understanding the syntax of the "exec sql" statement.
i am looking at code that build an sql string such as
sql="exec SOMETHING Session("id")"
or something like that.
then, there is
conn.execute(sql)
My question is the "SOMETHING" in the sql statement...is what? I know it is user defined (object or variable or such), but what exactly is it? i look through the rest of the code and don;'t see SOMETHING defined elsewhere.
i am not sure if i am asking the question right. i don't understand what the SOMETHING is doing, or why it is there.
in particular, the code i am examining is
sql="exec SurveyDelete "&"'" & Session("StudentID") & " ' "
conn.execute(sql)
i understand the this statement will delete a record, but how does it handle "SurveyDelete", how does it know what the is when it is not defined anywhere else in the code?
Hi,
Can I execute my MDX statement as in the exhibit format!
DECLARE @test VARCHAR(MAX)
SET @test = 'WITH test AS (SELECT * FROM merchants)'
EXEC(@test)
SELECT * FROM test
If I don't use that dynamice sql statement, everything work fine.
Thanks,
Myo
Hi,
I have a written a SP to do indexdefrag to all user table indexes in a databases...I had to use dynamic sql so I can reuse this code for any DB...
declare @strsql varchar(500)
set @strsql = ' dbcc indexdefrag('+'''DBName'''+',554556545,3)'
exec (@strsql)
When I execute the above script, I immeaditely see the results in the query analyser like below:
Pages Scanned Pages Moved Pages Removed
------------- ----------- -------------
3 0 0
Looks like the indexdefrag did not happen since the logical fragmentation is still a high number like 30%.....
Just wondering whats goin on..
Thanks.
Ranga
Hi, I have an sql query like this :DECLARE @TableName varchar(200), @ColumnName varchar(200), @EmployeeID varchar(200), @Result varchar(200);SET @TableName = 'Customer';SET @ColumnName = 'First_Name';SET @CustomerID = 28;-- This line return ErrorSET @Result = EXEC ('select' + @ColumnName + ' from ' + @TableName + ' where Recid = ' + @CustomerID + '');Print @Result; I am pretty sure the SELECT statement in EXEC will only return 0 or 1 record. But how to capture result from EXEC? Thanks
View 1 Replies View Relatedis it possible to have a sproc with a input parm of a column name and have this column name be inserted into an exec statement that runs and provides the output as a OUTPUT parm instead of a result set?
i can get the sproc to take the column name as a parm, run the exec, but cannot figure out how to assign the "dynamic sql" output to a OUTPUT variable instead of returning the result set.
How can i get the result from a dynamic sql like - exec('select ' + fieldName
+ ' from ' + TableName)
I found this statement in BOL and it didn't make it to work.Is anybody out there who ha the same problem?
Database is MASTER.
USE master EXEC ("USE pubs") SELECT * FROM authors
Running this dynamic sql construct gives me an error because somehow it does not accept my variable @table or it is recognised differently. If run directly no problem but apparently the single quotes are a problem.
Print @Table (db and table name: opms..transactions)
Select @sql = 'Select * From Payments where not exists (Select * from Hist Where TableName = ' + @Table + ' and sYear = '+ @Year + ' and sMonth = ' + @Month + ')'
Print @sql
EXEC (@sql)
opms..Transactions
Select * From Payments where not exists (Select * from Hist Where TableName = opms..Transactions and sYear = 2003 and sMonth = 12)
Server: Msg 1004, Level 15, State 1, Line 1
Invalid column prefix 'opms.': No table name specified
Any idea?
mipo
I would like to execute something like that with sql6.5 :
select @cmd = 'use ' + quotename(@dbname) + ' exec sp_helprotect'
exec (cmd)
I tried like this but I don't know what's wrong
exec ("USE "+ RTRIM(@dbname) +"exec sp_helprotect")
Thank you
Hi,
I am seeking an expert help for the following issue, please find the code am using first ...the problem mentioned below that...
----------------------------------------------
DECLARE
,@DBName VARCHAR(128)
,@LoginName VARCHAR(128)
,@SQL VARCHAR(2000)
SET @DBName='dbname'
SET @LoginName='loginname'
SELECT @SQL=@DBName+'..SP_EXECUTESQL N''SP_REVOKEDBACCESS ['+@LoginName+']'''
EXEC(@SQL)
IF @@ERROR <> 0
PRINT @@ERROR
ELSE
BEGIN
PRINT 'Revoked database access of [' + @LoginName + '] from the database ['+ @DBName +']
PRINT @@ERROR
END
--------------------------------------------------------------
Suppose I am trying to REVOKE a database access which not exist iw will give me a mesage like ,
Server: Msg 15008, Level 16, State 1, Procedure sp_revokedbaccess, Line 36
User 'Loginname' does not exist in the current database.
But the @@ERROR will return 0 as it was a successfull execution of EXEC(@SQL) .
So How can I retrieve the error value 15008 in a variable ..?
:confused:
Hello,
I'm trying to do something like the following, but it keeps complaining that I need to declare @max, even though I have (and it is of the same type as link_id).
EXEC('SELECT @max=MAX(link_id) FROM '+ @str1)
I've looked into sp_executesql but I'm not entirely sure how that functions. Any suggestions?
How do I attached the file by using xp_sendmail?
exec xp_sendmail
@recipients='myemail@yahoo.com',
@subject='test',
@attach_results='True'
??? 'C:FileName.xls'
I have a stored proc that assigns a value to a field based on user input from an Access front end.
The last part of the stored proc sends an email if certain conditions are met.
It appears that users do not have permission to execute xp_sendmail. I guess this is because it is executed on the master database. Is there a way I can give them permission to this stored proc?
The users are getting this message:
EXECUTE permission denied on bject 'xp_sendmail', 'database master', owner 'dbo'.(#229)
I am creating a dynamic query and using exec to execute it inside of a function. This query will return only one value. How can I get the value the query returns into a variable?
Functions can not call stored procedures, and they can not use temporary tables.
Thanks much
I am trying to do an insert statement utilizing a variable string.
something like:
Set @cString = 'SELECT top 10 *
FROM OPENDATASOURCE(
' + char(39) + 'SQLOLEDB' + char(39) + ',' + char(39) +
'Data Source=' + @lServer + ';User ID=' + @user + ';Password=' + @pword + char(39) + '
).myServer..
Insert into #Temp_table (field1, field2)
select exec @cString
--What is the syntax for this?
OK, I'm fairly new to SQL Server, but I know SQL and databases pretty well. I'm just starting to use the dynamic SQL feature of SQL Server (with EXEC), and am wondering how to return a scalar value from a dynamic SQL expression. I realize I can't use EXEC in a user-defined function, but I want to create a stored procedure with one OUTPUT variable so I can simulate a function. The following code does not work, because EXEC does not return a value:
CREATE PROCEDURE dbo.ExecFunction ( @ScalarSELECTString varchar(250), @@ReturnVal sql_variant )
set @@ReturnVal = ( exec @ScalarSELECTString )
go
So, I was wondering if someone might be able to suggest a way to re-write the above code to achieve the same effect. Thanks in advance.
I need to insert the results into a temp table and i recieve the "MSDTC on server 'servername' is unavailable error".
declare @thestringall varchar(1000)
set @thestringall = 'select statment here'
insert into #temptable exec (@thestringall)
Thanks
Wooanaz