Transact SQL :: Using EXEC To Execute A Formula Stored In A String

May 31, 2005

Basically, I have a table with a column that stores mathematical formulas in string format.  When my UDF is executed, it needs to select an appropriate formula from this table and evaluate it using values that are stored in local variables. 

Look at the example below:

Suppose I have a string named @vcFormula that contains the following:"@dVar1 + @dVar2 / @dVar2"Now suppose I have a variable named @dVar1 that contains a value of 1.0, and variable @dVar2 contains a value of 2.5.  I can use the REPLACE function to change my original string to look like this:"1.0 + 2.5 / 2.5"

Now I want to execute this string and find the numeric result, placing it in a variable named @dResult.  The following works, but presents a problem:CREATE TABLE #Result (dResult decimal(20, 10))INSERT #Result EXEC('SELECT ' + @vcFormula)SELECT @dResult = dResult FROM #ResultThe problem with using this method comes from the fact that I need to be able to evaluate @vcFormula from within a user-defined function, but temporary tables are not allowed inside UDF's. 

So I attempted to change the temporary table above into an instance of the TABLE data type.  This didn't work either because EXEC cannot be used to populate instances of the TABLE data type.  Then I came up with the bright idea to put the code above in a SP and call the SP from the UDF, but of course UDF's are not allowed to call SP's. Specifically, is there any way to execute a command/formula that is contained within a string other than by using EXEC? 

View 10 Replies


ADVERTISEMENT

Need To Evaluate Stored String Formula

Jun 18, 2007

In 'MyTable' I have the following columns...

TotalNumber (numeric)
Weighting (numreric)
Hours (numeric)
Minutes (numeric)
Formula (nvarchar)

'Formula' column stores a literal string of the formula that may include some, none or all of the previous columns or be NULL.

Here are some examples of the actual strings it stores...

Weighting*Hours
Weighting+(TotalNumber*Hours)
Weighting*Hours)+(TotalNumber*2)

etc etc

All I want to do is create a UDF that will evaluate these strings as math formula and return the value depending on the values of the other columns in the row.

Bear in mind that there may not be a string formula at all for some rows, in which case the value of teh Hours column alone should be the result.

I can do this in vb using the 'Replace' function but am having difficulty in translating it over to T-SQL.

Here is the vb version i use in Ms Access...

getFormula(strDutyType As String, dblTotalNumber As Double, dblWeightingAs Double, dblHours As Double, dblMinutes As String, strFormula As Variant)

If IsNull(strFormula) Or strFormula = "" Then
getFormula = dblHours
Exit Function
End If

'Create the expression string with literal values
strExpression = Replace(strFormula, "TotalNumber", _
CStr(dblTotalNumber), , , vbTextCompare) _
strExpression = Replace(strExpression, "Weighting", _
CStr(dblPF), , ,vbTextCompare)
strExpression = Replace(strExpression, "Hours", _
CStr(dblHours), , , vbTextCompare)
strExpression = Replace(strExpression, "Minutes", _
CStr(dblMinutes), , , vbTextCompare)

'Evaluate the math of the literal expression
getFormula = Eval(strExpression)

-----------

Many Thanks

View 1 Replies View Related

Execute Stored Procedure (with Parameters) With An Exec Command

Feb 21, 2004

Hi everybody, I would like to know if it's possible to execute a stored procedure, passing it parameters, using not CommandType.StoredProcedure value of sqlcommand, but CommandType.Text.

I tried to use this:
sqlCmd.CommandType = CommandType.Text
sqlCmd.Parameters.Add(sqlPar)
sqlCmd.ExecuteNonQuery()

With this sql command:
"exec sp ..."

I wasn't able to make it to work, and I don't know if it's possible.

Another question:
if it's not possible, how can I pass a Null value to stored procedure?
This code:
sqlPar = new SqlParameter("@id", SqlDbType.Int)
sqlPar.Direction = ParameterDirection.Output
cmd.Parameters.Add(sqlPar)

sqlPar = new SqlParameter("@parent_id", DBNull)
cmd.Parameters.Add(sqlPar)

doesn't work, 'cause I get this error:
BC30684: 'DBNull' is a type and cannot be used as an expression.

How can I solve this?
Bye and thanks in advance.

P.S. I would prefer first method to call a stored procedure ('cause I could call it with 'exec sp null' sql command, solving the other problem), but obviusly if it's possible...=)

Sorry for grammatical mistakes.

View 9 Replies View Related

T-SQL And Visual Basic 2005 Codes That Execute A User-Defined Stored Procedure In Management Studio:How To Declare EXEC && Sp?

Jan 17, 2008

Hi all,

In my SQL Server Management Studio Express (SSMSE), I executed the following sql code suuccessfully:
--insertNewRocord.sql--

USE shcDB

GO

CREATE PROC sp_insertNewRecord @procPersonID int,

@procFirstName nvarchar(20),

@procLastName nvarchar(20),

@procAddress nvarchar(50),

@procCity nvarchar(20),

@procState nvarchar(20),

@procZipCode nvarchar(20),

@procEmail nvarchar(50)

AS INSERT INTO MyFriends

VALUES (@procPersonID, @procFirstName, @procLastName, @procAddress,

@procCity, @procState, @procZipCode, @procEmail)

GO

EXEC sp_insertNewRecord 7, 'Peter', 'Wang', '678 Old St', 'Detroit',

'Michigon', '67899', 'PeterWang@yahoo.com'

GO

=======================================================================
Now, I want to insert a new record into the dbo.Friends table of my shcDB by executing the following T-SQL and Visual Basic 2005 codes that are programmed in a VB2005 Express project "CallshcDBspWithAdoNet":
--Form1.vb--

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.SqlTypes

Public Class Form1

Public Sub InsertNewFriend()

Dim connectionString As String = "Integrated Security-SSPI;Persist Security Info=False;" + _

"Initial Catalog=shcDB;Data Source=.SQLEXPRESS"

Dim connection As SqlConnection = New SqlConnection(connectionString)

connection.Open()

Try

Dim command As SqlCommand = New SqlCommand("sp_InsertNewRecord", connection)

command.CommandType = CommandType.StoredProcedure


EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com'


Console.WriteLine("Row inserted: " + _

command.ExecuteNonQuery().ToString)

Catch ex As Exception

Console.WriteLine(ex.Message)

Throw

Finally

connection.Close()

End Try

End Sub

End Class

===========================================================
I ran the above project in VB 2005 Express and I got the following 5 errors:
1. Name 'EXEC' is not declared (in Line 16 of Form1.vb)
2. Method arguments must be enclosed in parentheses (in Line 16 of Form1.vb)
3. Name 'sd-insertNewRecord' is not declared. (in Line 16 of Form1.vb)
4.Comma, ')', or a valid expression continuation expected (in Line 16 of Form1.vb)
5. Expression expected (in Line 16 of Form1.vb)
============================================================
I know that "EXEC sp_insertNewRecord 6, 'Craig', 'Utley', '5577 Baltimore Ave',

'Ellicott City', 'MD', '21045', 'CraigUtley@yahoo.com' "in Line 16 of Form1.vb is grossly in error.
But I am new in doing the programming of T-SQL in VB 2005 Express and I am not able to change it.

Please help and advise me how to correct these problems.

Thanks in advance,
Scott Chang

View 22 Replies View Related

Transact SQL :: GO Statements To Execute Large Stored Procedure In Batches

Jun 19, 2015

I want to include GO statements to execute a large stored procedure in batches, how can I do that?

View 9 Replies View Related

Execute Stored Procedure To Decode A Varchar Hexadecimal String

Nov 20, 2014

how to execute this stored procedure to decode a varchar hexadecimal string? My SQL syntax stills have faded with the sands of time... I want to do a select * from the users table and decode the Password field (but not update) in the process.

CREATE PROCEDURE spPasswordDecode (@hex varchar (100), @passwordtext varchar (100) output)
AS

declare @n int,
@len int,
@str nvarchar(500),
@output varchar(100)

[code]....

View 4 Replies View Related

ONE ZERO ZERO -------&&> One Hundred = Nice String Formula !

Feb 21, 2008



Im trying to build myself a report that recieves a values as worded text e.g

zero zero one zero zero

it would be nice if i write some clever bit of code that would convert this to

One Hundred

Has anyone come up against this before, or could point me in the direction of a tutorial, i would appreciate it

thank you in advance

Jonny

View 6 Replies View Related

Transact SQL :: Parsing Formula Text To Float

Apr 27, 2015

There is a column in a table that has values like '23 + 45 + 63', '2 + 54 - 22'... and so on. I want to get the result of this formula as float like 131, 34... and so on. Is that possible with a SELECT statement. I tried:

SELECT CAST('23 + 45 + 63' AS FLOAT)

Didn't work.

View 14 Replies View Related

Formula For Replacing ENTER Key Inside A String

Mar 25, 2008

Hi guys,
I have a large text that can have some ENTER character inside of it. The problem is that the text must be inserted into a txt file in a single row. So what I need is to find those ENTER character and replace them with a space so as to have the whole string in one row.
How can I build that formula in derivide column?
Thanks for your help

View 10 Replies View Related

Transact SQL :: Passing Multiple String Param Values To Stored Proc

Jul 21, 2015

CREATE TABLE Test
(
EDate Datetime,
Code varchar(255),
Cdate int,
Price int
);

[Code] ....

Now I have to pass multiple param values to it. I was trying this but didnt get any success

exec
[SP_test]'LOC','LOP'

View 10 Replies View Related

How To Convert String To Upper Case Using Field's Formula Property

Jul 20, 2005

Hi,I am trying to convert string entered in a field to uppercase usingits formula property.I know it can be done using trigger but still I want to use formulaproperty to achieve the same.Any help will be greatly appreciated.-Max

View 3 Replies View Related

Cannot Execute DDL Sql Batach Via EXEC Or Sp_executesql ???

Mar 26, 2008

Hi There

Ok i have a piece of test ddl sql that is written to a varchar(max) column. Entered by a user.

GO's and missing semi colons seem to break it, if you try to execute it with EXEC or sp_executesql, however the sql can be executed on Management Studio as is, so how can i execute it as is successfully?

In a nutshell i retreive the DDL sql from the column into a nvarchar(max) variable called @SQL , and i need to execute it.

I have tried:
EXEC(@SQL) and sp_executesql @SQL, both return the error , incorrect syntax near 'GO'.

The problem is obviously i have to have the go statements, in order to create some fo the ddl objects correctly. But EXEC and sp_executesql do not like that. I also found that semi colons are required after every statement.

The sql is as follows:

--===============================================================================

--DDL script

CREATE LOGIN TEST_LOGIN WITH PASSWORD = 'Whatever!@(!';

CREATE USER TEST_USER FROM LOGIN TEST_LOGIN;

CREATE TABLE TEST_TABLE (Column1 int NULL);

GRANT INSERT ON TEST_TABLE TO TEST_USER;

CREATE CLUSTERED INDEX TEST_INDEX ON TEST_TABLE (Column1);

GO

CREATE PROCEDURE TEST_PROCEDURES

AS

SELECT GETDATE();

GO

CREATE VIEW TEST_VIEW

AS

SELECT * FROM TEST_TABLE;

GO

--ALTER DDL

ALTER VIEW TEST_VIEW

AS

SELECT GETDATE() AS 'DATE';

GO

ALTER PROCEDURE TEST_PROCEDURES

AS

SELECT * FROM TEST_TABLE;

GO

--DROP DDL

DROP INDEX TEST_TABLE.TEST_INDEX;

DROP TABLE TEST_TABLE;

DROP VIEW TEST_VIEW;

DROP USER TEST_USER;

DROP LOGIN TEST_LOGIN;

DROP PROCEDURE TEST_PROCEDURES;

--===============================================================================

View 5 Replies View Related

Looking For A Way To Refer To A Package Variable Within Any Transact-SQL Code Included In Execute SQL Or Execute T-SQL Task

Apr 19, 2007

I'm looking for a way to refer to a package variable within any
Transact-SQL code included in either an Execute SQL or Execute T-SQL
task. If this can be done, I need to know the technique to use -
whether it's something similar to a parameter placeholder question
mark or something else.


FYI - I've been able to successfully execute Transact-SQL statements
within the Execute SQL task, so I don't think the Execute T-SQL task
is even necessary for this purpose.

View 5 Replies View Related

Error With Execute DTS 2000 Package Task On 64-bit With 32-bit Exec

Dec 17, 2007

Hello all,


I am running a package on a 64-bit server using the 32-bit dtexec. It contains an embedded Execute DTS 2000 package. I deployed the package to the server using the sa account. I set up a SQL Agent job that runs under an account that should have complete admin privileges. The network guys tell me that Legacy components have been installed (although I believe that shouldn't be necessary because SSIS is installed). This is SQL Server 2005, SP2.


When I execute this job, I receive this error message:


Executed as user: Domainuser. ...age Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 11:13:18 AM Error: 2007-12-17 11:13:35.65 Code: 0xC0010018 Source: Execute DTS 2000 Package Task Description: Error loading a task. The contact information for the task is "Execute DTS 2000 Package Task;Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". This happens when loading a task fails. End Error Error: 2007-12-17 11:13:35.71 Code: 0xC0010026 Source: Execute DTS 2000 Package Task Description: The task has failed to load. The contact information for this task is "Execute DTS 2000 Package Task;Microsoft Corporation; Microsoft SQL Server v9; ? 2004 Microsoft Corporation; All Rights Reserved;http://www.microsoft.com/sql/support/default.asp;1". End Error Error: 2007-12-17 11:13:35.71 Code: 0xC0024. The step failed.


Is there any way to get this to run?


Thank you very much,
Jessica

View 7 Replies View Related

Exec @string

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

EXEC String With Datediff

Mar 7, 2001

I am using the EXEC function in order to specify which view to access. However I can not get the datediff to work with it....does anyone have any clues?

View 1 Replies View Related

EXEC String Problem

Aug 21, 1998

Hi All,

I am using SQL Server 7 Beta 3 and I am having trouble with using a variable
in an EXEC statement.

I have a stored procedure that is designed to retrieve a serial
number from a table and then increment it by one(1) and return the
original serial number to the calling function.

As long as the table name is hardcoded the following code works fine:


CREATE PROCEDURE IncrementSerialIDNo
(@serial_num_temp varchar(15), @serial_num_output varchar(15) output)

AS
BEGIN TRANSACTION
BEGIN


SELECT @serial_num_temp = tblSerialNo.SerialNo
FROM tblSerialNo

SELECT @serial_num_output = @serial_num_temp

UPDATE tblSerialNo
SET tblSerialNo.SerialNo =
CONVERT(varchar(15),(CONVERT(integer,@serial_num_t emp ) + 1))

IF (@@error = 0)

COMMIT TRANSACTION

ELSE

ROLLBACK TRANSACTION

END

RETURN


However, if I change the table name to a variable that is passed to the
stored procedure as a argument, I need to use the EXEC statement.

My problem is that for the EXEC statement to work I need to escape the
variables and I run into a problem with the " = " ( equal sign ).

Example:


DECLARE @SerialTable as varchar(30), @CompanyNo as varchar (3)

SELECT @CompanyNo = `001`

SELECT @SerialTable = `tblSerialNo` + @CompanyNo

EXEC ( `SELECT ` +@serial_num_temp + `= SerialNo FROM ` +
@SerialTable )


Following is the error message that I keep getting:

Server: Msg 170, Level 15, State 1
[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax
near `=`.



No amount of testing with different qoutes or doublequotes and such,
have made any difference.

I have tried numerous variations on the above SELECT statement with the
same end result of the error on the "=" sign.

I cannot seem to assign one variable to another using the "=" (equal sign)
in an EXEC statement.

Does anyone have example code of using the equal sign to assign one variable
to another in an EXEC statement or can someone point me in the right direction?

Thanks in advance for any and all help.



jeff alerta
jeff@nestworks.com

View 5 Replies View Related

Linked Servers And String Exec

Dec 17, 2003

Hi.

I have been trying to find a solution to this for some time now. Was wondering if some1 had done is earlier and has a solution.

I have a 2 server machines.
Namely: ServerOne and ServerTwo

ServerOne (main server, On 1 machine.)
Table - Foofoo

ServerTwo (secondary server, on another machine)
Table - Booboo

I want to be able to link these two servers and work with them.

At the moment I do something like this.
NB. My Stored Procedure is on ServerOne

declare @server varchar(100)

Select @server=Servername from ServerOne.systemsettings where name='secondary'
-- @server is not equal to 'ServerTwo'
declare @str varchar(8000)
set @str = '
select *
from Foofoo f
join ' + @server + '.myDB.dbo.Booboo b on b.id = f.id '

exec(@str)

My problem is that this works fine but I do not like working with long strings and then executing them at the end.

I have also been told that SQL's performance on this is not entirely that well as normal select's would be.'

Another thing that could be used is SQl's own linked servers method but apparently out system was designed some time ago and a lot of things have been developed around the current technic.

Our server names also change quite frequently making hadcoding server names difficult.

Using the string exec convention also hides from sql when you do a dependency search of a particular table.

Is there a way I can save the server name on @server and then just add it to the select stmt without using the long stringing idea.

Any feedback with ideas and solutions will be greatly appreciated.

Bhit.

View 1 Replies View Related

Confused Exec With A Compound Sql String

Aug 1, 2000

I am trying to make up a SQL string which will be executed with the Exec command

I want to add a return column that is not in the table, and the table columns:

something like
Select @Ten As Ten, @Tablename.* From @Tablename (
where @Ten has an integer value. )
The statement was originally:
select @SQL = 'select * from ' + @TempName
exec (@SQL)
which had no problem. Then I needed to add an extra column of static data to all the returned rows, and confusion !!!!!

Thanks,
Judith

View 1 Replies View Related

Complex Concatenated String For An 'exec(@sql) '

Aug 10, 2000

I have:

<<Select @SQL = 'Select Keyword, SICCode From zlk_SICCodeKeyword Where Keyword like ''%' + @KeywordOrSIC + '%''' + ' order by Keyword'

exec(@SQL)>>
which works fine, but I want to modify it to do this

<<Select Replace(Keyword,',',' ') AS Keyword, SICCode From zlk_SICCodeKeyword Where Keyword like 'real%' order by Keyword >>

which works from the query window but I can not get the right combination around the 'replace section' to make up a string for the exec.

All help greatly appreciated
Judith

View 1 Replies View Related

Set Local Variables When EXEC-ing A Sql String

Nov 23, 2006

Hey guys, needless to say I'm new at this.

What I'm trying to accomplish is to execute a SQL string via exec and inside it set the value of a local variable. I understand that I cannot do this the way I'm currently doing it because an Executed string runs in a scope of its own so local variables are invisible. And sure enough this is the error I get. So how do I make this work?

Code snip:




declare @ErrMessage as varchar(1000)
set @Sql = 'if exists ( select * from ' + @TargetTable + ' where (' + @ValueField + '=' + '''' + @NewValue + ''''
set @Sql = @Sql + ' and ' + @TagField + '= ' + '''' + @Tag + '''' + '))' + @CRLF
set @Sql = @Sql + 'set @ErrMessage = ''Insertion could not be performed. ' + @NewValue + ' is already an entry in the table. '''


So what I want is check if a certain table has entries...what table? I don't know, there are tens that this check will apply to. And if that tavle has an entry that satisfies the where clause then assign the appropriate error message to @ErrMessage.

I understand sp_executesql might do the trick because it allows passing local params back and forth.

Any ideas on how to make this work? I appreciate the effort.

View 3 Replies View Related

Formula In Stored Procedure

Feb 28, 2007

Newbie problem: Working with SQL Server 2000 (web admin),  I have a table named OrderItems created with this query:
 
CREATE TABLE dbo.OrderItem
            (fkOrderID     int            NOT NULL,
            fkMenuItemID int            NOT NULL,
            ItemSize            nvarchar(50)  NOT NULL,
            ItemName            nvarchar(50)  NOT NULL,
            Quantity          int            NOT NULL,
            LineValue       money            NOT NULL)
 
I have created a second table that will contain summary data (total quantity for each item):
 
CREATE TABLE dbo.OrderItemTotal
            (fkMenuItemID            int            NOT NULL,
            ItemName            nvarchar(50)  NOT NULL,
            TotalQuantity  int            NOT NULL)
 
I want to draw the fkMenuItemID and ItemName directly from the OrderItem table and have the TotalQuantity column dynamically sum the total number of items for each fkMenuItemID so that as orders are added, the OrderItemTotal table is automatically updated. 
 
This sort of thing is easy in a spreadsheet using formulas, so I assume that there must be comparable methods in T-SQL.  From browsing the archives I get the sense that I may need a calculated formula within a stored procedure.  This is what I have so far, which does not contain a calculated formula for the TotalQuantity column.
 
CREATE PROCEDURE dbo.usp_InsertOrderItemTotal
                        @MenuItemID                       int,
                        @ItemName               varchar(50),
            AS
                        INSERT INTO OrderItemTotal
                        VALUES(@MenuItemID, @ItemName)
 
Am I on the right track with my thinking?  Where do I go from here?  Any help with this would be greatly appreciated.

View 4 Replies View Related

Insert Records Using EXEC Syntax In Transact

Sep 14, 2004

Hi all,

I have to insert records using transact iin the stored procedure. I have some thing like:

DECLARE @Err varchar(100)
DECLARE insertQ varchar(1000)

SET @Err = 'Insertion data'

SET @insertQ =('INSERT INTO dbo.T_ERRORLOG (ERROR_DESCR) VALUES(' + @Err + ')')

EXEC insertQ

But it don't work. Can you help me to solve the problem plz?

Thanks a lot

TT

View 4 Replies View Related

Reporting Services :: Add Formula To Formula Bar In Excel

Sep 1, 2015

In SQL reporting, How do I add the formula in the Formula bar?All the data is coming from a sproc.

View 3 Replies View Related

Transact SQL :: Why Store Procedure Not Return Any Value / Result After Using Exec

Jul 22, 2015

I use new query to execute my store procedure but didnt return any value is that any error for my sql statement??

USE [Pharmacy_posicnet]
GO
/****** Object: StoredProcedure [dbo].[usp_sysconf] Script Date: 22/07/2015 4:01:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER procedure [dbo].[usp_sysconf]

[Code] ....

View 6 Replies View Related

Error While Executing A A Query String Using EXEC Statement

Sep 18, 2007

Hi,
I have written a stored proc to bulk insert the data from a data file.
I have a requirement that i need to insert the data into a table of which the name is not known. I mean to say that the table name will be passed as a parameter to the stored proc. And also i need to insert the date that will also be passed as the parameter to the stored proc

The follwing statement works fine if i give the table name directly in the query



Code Snippet

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

INSERT INTO Category
SELECT MSISDN, @LastUpdate FROM OPENROWSET( BULK '\remotemachinedatafile.txt',
FORMATFILE = '\remotemachineFormatFile.fmt',
FIRSTROW = 2) AS a



To satisfy my requirement ( i.e passing the table name dynamically , and the date) , i have formed the query string ( exact one as above ) and passing it to EXEC statement. But its failing as explained below






Code Snippet

@Category - Will be passed as a parameter to the stored proc


DECLARE @vsBulkSQL VARCHAR(MAX)

DECLARE @LastUpdate varchar(20)

SET @LastUpdate = 'Dec 11 2007 1:20AM'

SELECT @vsBulkSQL ='INSERT INTO '+ @Category + ' SELECT MSISDN, ''' + @LastUpdate +''' FROM OPENROWSET ' + '( BULK ' + '''' + '\remotemachinedatafile.txt'+ ''''+ ' ,' +
+ ' FORMATFILE ' + '=' + ''''+ '\remotemachineFormatFile.fmt'+ ''''+ ',' +
' FIRSTROW ' + '=' + '2' + ')' + ' AS a'

Print @vsBulkSQL - This prints the folliwing statement


INSERT INTO Category SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineDataFile.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a


Exec @vsBulkSQL - This statement gives the following error

The name 'INSERT INTO Sports SELECT MSISDN, 'Dec 11 2007 1:20AM' FROM OPENROWSET ( BULK '\remotemachineSecond.txt' , FORMATFILE ='\remotemachineFormatFile.fmt', FIRSTROW =2) AS a' is not a valid identifier.






Can any one please point out where am i doing wrong? Or do i need to do anything else to achive the same

~Mohan

View 4 Replies View Related

Transact SQL :: How To Store Result Of Exec Command Into Temp Table

Apr 7, 2013

I wanted to insert the result-set of a Exec(@sqlcommand) into a temp table. I can do that by using:

Insert into #temp
Exec(@sqlcommand)

For this to accomplish we need to define the table structure in advance. But am preparing a dynamic-sql command and storing that in variable @sqlcommand and the output changes for each query execution. So my question is how to insert/capture the result-set of Exec(@sqlcommand) into a temp table when we don't know the table structure.

View 17 Replies View Related

How Do I Use A @variable To Hold On Value Return From An Exec (&#39;string Command&#39;) Statement.

Sep 19, 2000

How do I use a @variable to hold on value return from an exec ('string command') statement.

Example for:

declare @OldID int
declare @cmd varchar(255)
declare @db varchar(25)
declare @OldOwner varchar(25)

set @db = 'DBNAME'
set @OldOwner = 'USERNAME'

select @cmd = 'select uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
exec (@cmd)

How can I use @OldID to hold on UID return from this statement.

When I try use:
select @cmd = 'select @OldID = uid from ' + ltrim(rtrim(@db))'..sysusers where name = ' + "'" + ltrim(rtrim(@OldOwner)) + "'"
then I'm getting a error message: @OldID not declare.

Thanks.

View 2 Replies View Related

Transact SQL :: Search And Return String After Searched String

Sep 1, 2015

Is there way to search for the particular string and return the string after that searched string

SalesID
Rejection reason
21812

[code]....

The timeout period elapsed hence disqualified

View 3 Replies View Related

Transact SQL :: How To Get String Array In String Variable

Jul 28, 2015

I have a string variable and following data.

Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'

when I assign values in @ServiceID  in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.

View 8 Replies View Related

Execute SQL Task: Executing The Query Exec (?) Failed With The Following Error: Syntax Error Or Access Violation. Possible F

Jan 23, 2008

Hi,
I'm having an SSIS package which gives the following error when executed :

Error: 0xC002F210 at Create Linked Server, Execute SQL Task: Executing the query "exec (?)" failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.Task failed: Create Linked Server

The package has a single Execute SQL task with the properties listed below :

General Properties
Result Set : None

ConnectionType : OLEDB
Connection : Connected to a Local Database (DB1)
SQLSourceType : Direct Input
SQL Statement : exec(?)
IsQueryStorePro : False
BypassPrepare : False

Parameter Mapping Properties

variableName Direction DataType ParameterName

User::AddLinkSql Input Varchar 0


'AddLinkSql' is a global variable of package scope of type string with the value
Exec sp_AddLinkedServer 'Srv1','','SQLOLEDB.1',@DataSrc='localhost',@catalog ='DB1'

When I try to execute the Query task, it fails with the above error. Also, the above the sql statement cannot be parsed and gives error "The query failed to parse. Syntax or access violation"

I would like to add that the above package was migrated from DTS, where it runs without any error, eventhough
it gives the same parse error message.

I would appreciate if anybody can help me out of this issue by suggeting where the problem is.

Thanks in Advance.

View 12 Replies View Related

Stored Procedures Using EXEC

Aug 8, 2007

Hi: I would like to know if I invoke a stored procedure using   "EXEC [database].[user].[StoredProcedure] param1, param2..." is just like to use the ADO.NET objects: SqlCommand myCommand = new SqlCommand("StoredProcedure", myConnection);myCommand.CommandType = CommandType.StoredProcedure;myCommand.Parameters.Add("@param1", param1);myCommand.Parameters.Add("@param2", param2); .....  Thanks  

View 3 Replies View Related

Exec Stored Procedure

Nov 5, 2007

Hello
Which is faster :
to write a a big stored procedure with if conditions, or to separate them and call them using exec??
i.e:
if @id=1  insert into ....else if @id=2 update...-----------------------orif @id=1   exec InsertProcedureelse if @id=2   exec UpdateProcedurePlease help
 

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved