EXEC (@SQLString) Problem.
Mar 18, 2004
Hi,
I am getting an error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near ','.
This is my code. What is wrong here?
CREATE TABLE #TotalsTemp (InvoiceNum varchar(25),
ShipperNum varchar (20),
InvoiceDate datetime,
PickupTransDate datetime,
ShipperName varchar(50),
ShipperName2 varchar(50),
ShipperAddr varchar(50),
ShipperCity varchar(50),
ShipperState varchar(6),
ShipperZip varchar(15),
bName1 varchar(100),
bName2 varchar(50),
bAddr1 varchar(50),
bCity varchar(50),
bState varchar(6),
bZip varchar(15),
bCountry varchar(50),
bPhone varchar(50),
TrackingNum varchar(20),
CustRef1 varchar(50),
CustRef2 varchar(50),
UPSZone varchar(3),
ServiceLevel varchar(50),
Weight int,
Lading varchar(70),
SMPCodeDesc varchar(255),
GrossCharge decimal(12,2),
Incentive decimal(12,2),
NetCharge decimal(12,2),
AccessorialTotal decimal(12,2),
CodeRefDesc varchar(50),
HundredWeight varchar(3))
--Inbound
SET @LadingType = 'inbound'
SET @SQLStr = 'INSERT INTO #TotalsTemp ' +
'SELECT ' + @ReportData + '.InvoiceNum, ' +
@ReportData + '.ShipperNum, ' +
@ReportData + '.InvoiceDate, ' +
@InvoiceData + '.PickupTransDate, ' +
@AddrData + '.aName1, ' +
@AddrData + '.aName2, ' +
@AddrData + '.aAddr1, ' +
@AddrData + '.aCity, ' +
@AddrData + '.aState, ' +
@AddrData + '.aZip, ' +
@ReportData + '.bName1, ' +
@AddrData + '.bName2, ' +
@AddrData + '.bAddr1, ' +
@ReportData + '.bCity, ' +
@ReportData + '.bState, ' +
@AddrData + '.bZip AS, ' +
@AddrData + '.bCountry, ' +
@AddrData + '.bPhone, ' +
@ReportData + '.TrackingNum, ' +
@InvoiceData + '.CustRef1, ' +
@InvoiceData + '.CustRef2, ' +
@ReportData + '.UPSZone, ' +
'tblLegendServiceLevel.ServiceLevel, ' +
@ReportData + '.Weight, ' +
'tblLegendLading.Lading, ' +
'tblLegendSMPCodes.[Desc], ' +
@InvoiceData + '.GrossCharge, ' +
@ReportData + '.Incentive, ' +
@ReportData + '.NetCharge, ' +
@ReportData + '.AccessorialTotal, ' +
'tblCodeRef.[Desc], ' +
@InvoiceData + '.HundredWeight ' +
'FROM' + @ReportData +
' INNER JOIN ' + @InvoiceData + ' ON ' + @ReportData + '.DataID = ' + @InvoiceData + '.DataID ' +
'INNER JOIN ' + @AddrData + ' ON ' + @ReportData + '.DataID = ' + @AddrData + '.DataID ' +
'INNER JOIN tblLegendServiceLevel ON ' + @ReportData + '.ServiceStandard = tblLegendServiceLevel.ServiceStandard ' +
'INNER JOIN tblLegendLading ON ' + @ReportData + '.LadingCode = tblLegendLading.LadingCode ' +
'INNER JOIN tblLegendSMPCodes ON ' + @ReportData + '.SMP2 = tblLegendSMPCodes.SMPCode ' +
'INNER JOIN tblCodeRef ON ' + @InvoiceData + '.ComRes = tblCodeRef.Code ' +
'INNER JOIN tblShipperNumberLookUp AS LookUp ON ' + @ReportData + '.ShipperNum = LookUp.ShipperNumber ' +
'INNER JOIN tblOrg_Unit ON LookUp.OU_ID = tblOrg_Unit.OU_ID ' +
'INNER JOIN tblOrg_Unit_Hier ON tblOrg_Unit.OU_ID = tblOrg_Unit_Hier.child ' +
'INNER JOIN tblOrg_lvls ON tblOrg_Unit_Hier.child_level = tblOrg_lvls.OrgLvl ' +
'WHERE(' + @ReportData + '.InvoiceDate BETWEEN ''' + CAST(@startdate AS varchar) + ''' AND ''' + CAST(@enddate AS varchar) + ''') AND ' +
'(tblOrg_Unit_Hier.parent = ' + CAST(@Parent AS varchar) + ') AND ' +
'(tblOrg_lvls.Root = ' + CAST(@Root AS varchar) + ') AND ' +
'(tblOrg_lvls.[Name] = ''' + @OrgLvl + ''') AND ' +
'(tblLegendLading.LadingType = ''' + @LadingType + ''')'
EXEC (@SQLStr)
View 12 Replies
ADVERTISEMENT
Jul 15, 2003
I want to pass a database parm to enlarge the maxsize for around 500 databases. Here is the primary script:
--====================================
declare @stringData varchar(200),
@stringLog varchar(200),
@databaseName varchar(25),
@returnCodeSize int
select @databaseName = 'ABC'
select @stringData = 'alter database ' + @databaseName +
' modify file (name = ABC_Data, maxsize = 1500MB, FILEGROWTH = 10%)'
print @stringData
exec (@stringData)
It works in above way.
But it did not work if
I use @returnCodeSize =exec(@stringData)
how could I get a return code from this exec. I can't use cmdshell since it is not an external operating command.
thanks
David
View 5 Replies
View Related
Jun 2, 2005
Hello, I have been working around this issue, but couldn't yet find any solution.I have a stored procedure that calls a method to do a certain repetitive work.In this function, I have a dynamic query, which means, that I am concatinating commands to the query depending on the input of the function.for example, there is an input for a function called "Id"Inside the function, if Id = 111I need to add " and ID <> 1" and if Id has another value I need to add " and ID = c.ID" something like that.Now, inside the function, I need to return a value by executing the above @SQLString as follows:EXEC @SQLStringWhen I need is something likeEXEC @SQLString, @Total OutputReturn (@Total)Are there any ideas ?regards
View 1 Replies
View Related
Mar 19, 2008
How do we do this in SS2k5?
in
EXEC(sqlstring)
sqlstring wants to pass back a resultset to the caller.
- Local temp tables are out of scope.
- Global temp table works but is a bad idea.
- Table variables not supported as OUTPUT parameters for EXEC.
Regards, Nick
View 4 Replies
View Related
May 19, 2005
Hello,
The SqlString variable; if a null value is assigned, will Value return the text null? Also, if I use another type such as SqlInt32, when I do ToString(), if the value is null, will it return the text null, so that it will work with a SQL string? I was wondering if that was the case.
Brian
View 2 Replies
View Related
Jun 6, 2005
Anyone know how to return a sql command from a sql server stored procedure using asp.net c# and sql server 2000?I'm trying to debug the stored proc and thought the easiest way would be to return the insert command and debug it in the query analyzer.Thanks.Doug.
View 2 Replies
View Related
Jun 3, 2006
Hi,
How can I check return value from
EXECUTE sp_executesql(SQLString)
Thanks,
View 4 Replies
View Related
Aug 21, 2006
Hi All,
Create proc sproc_Insert
@TableName varchar(50),
@InsertColumns varchar(1000),
@InsertValues varchar(2000),
@WhereCondition varchar(200)
as
Begin
Declare @CheckStr nVarchar(2000)
Declare @RetVal int
Set @checkStr = 'Select * from '+ @TableName + ' '+ @WhereCondition
execute sp_executesql @checkStr,@RetVal output
print @RetVal
End
I am not able to retrieve the return value in the above procedure. For example if data exists then 1 else o
Thanks & Regards
Bijay
View 3 Replies
View Related
Oct 31, 2007
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?
View 5 Replies
View Related
Feb 29, 2000
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
View 1 Replies
View Related
Jul 22, 2003
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
View 4 Replies
View Related
Nov 1, 2007
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...
View 2 Replies
View Related
Apr 28, 2006
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?
View 2 Replies
View Related
Feb 17, 2006
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
View 1 Replies
View Related
Mar 28, 2006
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
View 3 Replies
View Related
Aug 29, 2007
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 Related
Nov 7, 2000
is 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.
View 1 Replies
View Related
Oct 17, 1999
How can i get the result from a dynamic sql like - exec('select ' + fieldName
+ ' from ' + TableName)
View 2 Replies
View Related
Aug 23, 2000
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
View 1 Replies
View Related
May 13, 2003
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
View 2 Replies
View Related
Aug 22, 2002
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
View 1 Replies
View Related
Jun 25, 2005
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:
View 9 Replies
View Related
Aug 11, 2005
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?
View 3 Replies
View Related
Aug 12, 2005
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'
View 3 Replies
View Related
Sep 20, 2004
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)
View 1 Replies
View Related
Oct 15, 2004
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
View 1 Replies
View Related
Oct 24, 2005
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?
View 3 Replies
View Related
Jan 12, 2004
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.
View 8 Replies
View Related
Jan 21, 2004
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
View 2 Replies
View Related
Mar 23, 2004
hi guys
maybe an easy one for you
in stored procedure I create follving select
@cmd = 'select ' + @column_name + 'from ticket_dump_datawarehouse '
execute (@cmd)
problem is thant I want to gave return value from this select
something like
set @return = execute(@cmd)
but I recieve error
Incorrect syntax near the keyword 'execute'
Can I do that some other way?
View 1 Replies
View Related
Mar 23, 2004
hi guys
maybe an easy one for you
in stored procedure I create follving select
@cmd = 'select ' + @column_name + 'from ticket_dump_datawarehouse '
execute (@cmd)
problem is thant I want to gave return value from this select
something like
set @return = execute(@cmd)
but I recieve error
Incorrect syntax near the keyword 'execute'
Can I do that some other way?
View 3 Replies
View Related
Feb 20, 2007
this might sound dumb or impossible, but is there a way to run exec commands forcing them to not return results
View 6 Replies
View Related
Apr 19, 2007
I need get the o/p of a system sp into a table. I am doing the following,
insert #repl_monitor
exec [distribution].sys.sp_replmonitorhelpsubscription @publisher =
N'FGRWA0508', @publisher_db = N'DB_Name', @publication = N'publication'
Code is really not important. Any sys SP can replace the above code.
I am getting the following error
Msg 8164, Level 16, State 1, Procedure sp_MSload_tmp_replication_status, Line 80
An INSERT EXEC statement cannot be nested.
I have seen the following link which discuss this issue,
http://www.sommarskog.se/share_data.html
But there is no solution there.
I tried with sp_executesql and EXEC(), but unable to get the result. Can anyone put some light?
------------------------
I think, therefore I am - Rene Descartes
View 8 Replies
View Related
