Hi all,
I was just wondering if it could be possible to excecute a statement
which is extracted from variabele, Example:
declare @SqlStatement varchar (100)
set @SqlStatement = 'select * from company'
the @SqlStatement contains the actual sql query, is it possible some
how to straight away convert it into excecution?
I've tried:
exec @SqlStatement
but exec or execute are meant to be for procedures
Any idea?
Hi,
I am trying to cast the parameter returned from the stored procedure to an int32 type. But it is giving me an error message as " Input string is not in the correct format "
my code is
int valid = Int32.Parse(command.Parameters["@Valid"].Value.ToString());
the @Valid parameter is an output parameter and is declared as int in the stored procedure.... the command is a DbCommand object
does anyone have an idea why this error is happening.
Naveen
ALTER PROCEDURE [dbo].[p_sub_agent_Grp_report]
@parent_pay_agent_cd VARCHAR(25) ,
@tagno NUMERIC(18,0) = NULL,
@labFromCLS VARCHAR(10),
@labToCLS VARCHAR(10),
@status VARCHAR(1)
[Code] .....
1. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , 173, '2015-06-01' , '2015-06-25', 'Y'
2. EXEC [dbo].[p_sub_agent_Grp_report] 'BD0003' , NULL, '2015-06-01' , '2015-06-25', 'Y'
2nd Stored procedure executes successfully and 1st stored does not executes where only difference is 3rd parameter value 173 and NULL value.
Is it because " IF @tagno IS NOT NULL" used in stored procedure is not working while @tagno is sent as 173 in sp parameter. Error throwing while executing 1st stored procedure is as below.
Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.
Msg 8115, Level 16, State 6, Procedure p_sub_agent_Grp_report, Line 48
Arithmetic overflow error converting nvarchar to data type numeric.
Note: sub_agent_tag_no column of table Sub_agent_tag_dtl has datatype Numeric(18,0).
Hello All!!
Below is a part of my code. I need to find out if I could execute the below statement.
select @string_sortorder = 'select max(sortorder) from geo_lineobj where pipeid = ' + str(@geo_pipeid)
select @string_sortorder
exec @string_sortorder @max_sortorder
When I select the @max_sortorder the value I'm getting is a null. The sortorder is an int datatype and the @geo_pipeid is also an int datatype.
I need to to get the @max_sortorder to continue my coding. Does anyone know why I'm getting a null @max_sortorder?
Any suggestion would be of great help.
Thank you
Here's a little script to find the currently executing statement. I put this together to get around a couple of limitations in DBCC INPUTBUFFER:
1) The 255 character output limit ... often I would get the "CREATE PROCEDURE" statement and the first couple of variable declarations, and that's it.
2) The fact that, in a multi-statement batch, you get the entire thing. This gives you the currently executing statement, not batch.
So, without further ado ...
/****************************************************/
DECLARE @Handle VARBINARY (85)
DECLARE @SPID INT
SET @SPID = <SPID to be examined>
SELECT @Handle = sql_handle
FROM master.dbo.sysprocesses
WHERE spid = @SPID
SELECT text
FROM ::fn_get_sql (@Handle)
/****************************************************/
Enjoy!
I geek, therefore I am
I have multiple SqlDataSources on a page. I only want them to execute the select query when the user performs a certain action. Is this possible? Or do they all execute as soon as the page is loaded?
View 1 Replies View Related
HELLO I'M NEW OF SQL SERVER. I'VE BEEN CHARGED BY MY COMPANY TO MOVE THE MDB WHICH WE WORKED TILL NOW TO SQL.
OBVIOUSLY I HAVE NOW TO CREATE THE DATABASE'S UPGRADING PROCEDURES WE WERE USING ON ACCESS.
MY PURPOSE IS TO MAKE RUN THE FOLLOWIG PROCEDURE, ONLY IF THE VALUE OF THE FIELD 'UPTGRD11' IS EQUAL AT '1', BELOW THE SQL STATEMENT I'M USING.
THIS OPTION IN ACCESS IS VERY EASY, BY MACRO OR BY VBA, I'M NOT ABEL TO DO IT IN T-SQL
IT DOESN'T RUN LIKE THAT
IF 'dbo.uptgrdt.uptgrd11' = '1'
BUT IT RUNS USING IS NOT NULL
IF 'dbo.uptgrdt.uptgrd11' IS NOT NULL
is there an easy way to do what i'm trying to?
I have been attempting to load a SQL Server table by extracting data from Oracle using a parameterized query. I need to retrieve the Oracle data from views where the key equals a specific value. The values are based on data from other Oracle tables.
I was able to create a file that contains 1 row for each key value in the syntax of "select .... from viewname where key = value". I'd like to be able to loop through the file, execute each statement, and load the resultant row(s) into a SQL Server table.
I looked at the ForEach container, but it appears to only list the files in a directory. I thought I was on the right track using the Execute SQL Task, but I could not figure out how to get the data loaded into SQL.
Any help would be greatly appreicated. Consider me an SSIS novice.
Thanks
I am trying to simulate the <sequence name>.nextval of oracle in SQL Server 2000.
The situation is that i need to be able to run programmatically INSERT statements. In Oracle I am able to do INSERT INTO TABLE_A (ID, NAME) VALUES (TABLE_A_SEQUENCE.NEXTVAL, 'MIKKO') in a single prepared statement in my code.
I know that to recreate this in SQL Server 2000 I need to create a stored procedure and table to set up a way to generate "the next value" to use in my INSERT. but the schema below forces me to do my insert in 2 steps (first, to get the next value. second, to use that value in the INSERT statement), since I cannot execute the stored procedure inside my INSERT statement.
Is there any way for me to generate values within my INSERT statement that would simulate Oracle's <sequence name>.nextval and allow me to execute my INSERT in 1 line of code?
TABLE
-----
CREATE TABLE sequences (
-- sequence is a reserved word
seq varchar(100) primary key,
sequence_id int
);
MS SQL SERVER STORED PROCEDURE:
-------------------------------
CREATE PROCEDURE nextval
@sequence varchar(100)AS
BEGIN
-- return an error if sequence does not exist
-- so we will know if someone truncates the table
DECLARE @sequence_id int
set @sequence_id = -1
UPDATE sequences
SET @sequence_id = sequence_id = sequence_id + 1
WHERE seq = @sequence
RETURN @sequence_id
END
I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.
@Cycle sql_variant = 2
WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication
AS
BEGIN
SET NOCOUNT ON;
declare @execution_id bigint
[code]....
When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.
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
Is this possible, I am trying to execute a stored procedure depending on what parameter is selected, something like this????
Case
when field = 'value' then execute sp_procedure else execute sp_procedure_2 end
case
We are just upgrading a server from 2000 to 2005 and we are getting the message below when we execute a sql statement against a table with a date field with null values:
"Error converting data type DBTYPE_DBTIMESTAMP to datetime."
I have created a trigger on a table that get's too many updates so to analyze what records and what columns get updates I have coded a trigger that inserts into a table the record key and a flag on each column to let me know what is updates. Now that I am presenting the data to the developers I have been asked to provide the update / insert or delete statement
So on my trigger I have tried using dynamic management views but its returning the actual trigger code not the parent SQL Script that triggered it.
This is what I am using.
select@dDate as dDate, dest.[dbid], dest.[objectid], dest.[number], dest.[encrypted],
case when sder.[statement_start_offset] > 0 --the start of the active command is not at the beginning of the full command text
thencasesder.[statement_end_offset]
[Code] .....
Does anyone know if this is a SQL Native Client bug? I've read a previous thread (posted back on Jan. 18th, 2007) about this error, but there are no replies. I am getting this error when I issue the sp_setapprole command using sqlexecdirect() ODBC api call. Is there any way to work around this? Or is there a fix to the SQL Native Client? The error 15002 message text states "The procedure sys.sp_setapprole cannot be executed within a transaction". This is on a new connection so there should be no transactions active at the time. Thanks in advance for any info anyone can provide on this.
View 5 Replies View RelatedHi,
I am planning to develop a single package that will download files from ftp server, move the files to internal file server and upload it in the database. But I want to run this package for multiple ftp file providers. For each provider the ftp server might be different and the transformation to upload the files into a database table might be different.
So can I create a single package and then multiple configuration files (xml), which will contain the details fo the ftp file providers and then pass the xml file as a parameter while executing the package. The reason being that the timings of fetching the files is different for each ftp file provider and hence cannot be combined into one.
Is this possible?
Thanks for your help.
$wapnil
Hi,
I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query window
Please see the image through this url http://kyxao.net/127/ExecutionProblem.png
Any ideas for this issue?
Thanks a lot
Hi i'm trying to do this: SELECT Name, Address1 FROM Stores WHERE Name LIKE %@SearchText%"but it's not working. I assume my syntax is off?Thanks!
View 3 Replies View RelatedHi there,
I am trying to use and SQL statement with a LIKE query with % at the begining and end of the parameter to get any combination of the search parameter i.e. last name.
My piece of SQL looks like this:
WHERE userUsername=@Username AND contactName LIKE @SearchTerm
I need something like this:
WHERE userUsername=@Username AND contactName LIKE %@SearchTerm%
However this does not work.
Any suggestions would be greatly appreciaed.
Regards,
TCM_
Hi,
I have a query that uses a parameter as criteria
e.g. type = :_type (i set p_type = 'Trad')
I want to change this parameter to be a range of values.
However when I change the query to be
type in (:_type) I get nothing returned if I set p_type to be 'Trad,New' but I do get something if it's just Trad.
Can anybody advise on the syntax? I've tried a number of variations.
Hi,I have a web application using Stored Procedure (SP). I see that there's a SP taking long time to execute. I try to capture it by Profiler Tool, and find out that with the same SP on the same db with the same parameter. The duration of executing by my web app is far bigger than the duration of executing on SQl server management studio - query windowPlease see the image attached http://kyxao.net/127/ExecutionProblem.png Any ideas for this issue?Thanks a lot Jalijack
View 2 Replies View RelatedHi all,I am facing an unusual issue here. I have a stored procedure, that return different set of result when I execute it from .NET component compare to when I execute it from SQL Management Studio. But as soon as I recompile the stored procedure, both will return the same results.This started to really annoying me, any thoughts or solution? Thanks very much guys
View 2 Replies View RelatedIs it possible for an AND statement to take mulitple parameters? See below code:
SELECT tblQuestion.Question, tblAnswer.Answer
FROM (tblAnswer INNER JOIN tblQuestion ON tblAnswer.QuestionID = tblQuestion.ID)
WHERE (tblAnswer.StateID = ?) AND (tblAnswer.QuestionID = 14)
Is something like this possible?:
AND (tblAnswer.QuestionID = 14, 26)
Thanks
Hi,I'm trying to run a select statement that takes includes an if/elseclause. I need to select the 'tran_date' between....if the current month is greater than 10 i.e. after OCT then thetran_date will be between '01-Oct' - plus current year or thetran_date is '01-Oct' plus previous year.and the current dateHere is my script so far:SELECT a.resource_code ASSOCIATE, a.tran_date START_DATE,b.description PROJECT_CODE, sysdateFROM actrans a, acactivity b, dual cWHERE a.resource_type = 'E'AND a.acct_category = 'TIME'and a.activity like '9-WW-357852%'and b.activity = a.activityand tran_date betweenif to_char(sysdate, 'MM') > 10)begina.tran_date = '01-Oct' & to_char(sysdate, 'YY')endelsebegina.tran_date = '01-Oct' & to_char(sysdate, 'YY'- 1))EndORDER BY tran_dateCan anyone help me out with this? I need to run it as a job, but notsure whether I should be using a stored procedure etc..Thanks in advance.George
View 1 Replies View RelatedI am trying to pass a parameter to an sql statement that executes a stored proc. Here is my sql statement from the Execute SQL Task component.
USE [master]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[sp_sendSMTPmail]
@To = N'john.r.carter@bankofamerica.com',
@Subject = ?,
@Body = NULL,
@Importance = NULL,
@Cc = NULL,
@Bcc = NULL,
@Attachments = NULL,
@HTMLFormat = NULL,
@From = N'bpm@bankofamerica.com'
SELECT 'Return Value' = @return_value
GO
I have the ByPassPrepare set to True.
Can anyone tell me why I can't get this to work.
Thanks,
JC
Hi,
I try to pass a parameter to a IN statement in SSRS but couldn't get it work. It likes following sample:
select * from T1 where name IN (@nameList)
I passed @nameList like 'aa', 'bb', ''aa'', ''bb''(two single quotations), all didn't work. I couldn't even get it work on "Reporting -> Data" screen.
What is the right way to pass a parameter to IN statement in SSRS? I am using C# and SSRS2005.
Thanks!
I have a select statement that I am using and wanted to know if there is a way to remove a parameter on the fly. What I want to do is remove the @status if the text = "". so it will only search by the date and endate is that possible to do. Here is my code.
I have to specifiy the database name which is supplied from the user (@fixdb). I want to do something like the following 'code'
Declare SysCursor cursor for + 'select Name, ID from ' + @fixdb +'.dbo.sysobjects where xtype = "u"'
but I can't seem to come up with the right statement.
Any help greatly appreciated.
Thanks,
Judith
If you want to return a single value should I use OUTPUT or Scaler which one is more effiecient?
View 1 Replies View RelatedHi,I am trying to Pass Column Name(FieldName) using Parameter in SQLStatement... But i am getting error...how can i pass Column name using parameter???Example:in table i have fieldname ECountry...Select @FName='ECountry'Select @FName from Table...How it works???Thanx in Advance,Regards,Raghu...
View 5 Replies View RelatedHi!
Is it possible to call proc with parameter in openrowset?:
declare @str varchar(100);
select @str = 'EXEC SP_HELPTEXT ''createReport''';
select * from
OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', @str)
AS a;
Incorrect syntax near '@str'.
------------------
select * from
OPENROWSET('SQLNCLI','Server=localhost;Trusted_Connection=yes;', 'EXEC
SP_HELPTEXT ''createReport''') AS a;
Msg 7357, Level 16, State 2, Line 1
Cannot process the object "EXEC SP_HELPTEXT 'createReport'". The OLE
DB provider "SQLNCLI" for linked server "(null)" indicates that either
the object has no columns or the current user does not have
permissions on that object.
Best regards
B. D. Jensen
Is there a way to set a default value for a sp parameter using a select statement(see code bellow)
ALTER PROCEDURE psGetInformationByProduct_Andrei
@col1 int,
SELECT @top = COUNT(col1) FROM Event
I am working on a Function that takes multiple parameters. I have a query that populates a temporary table, and then it processes some logic. My question is, if the parameter is passed as null, I dont want the query to be affected by this null value. Rather, I would like to not pass it at all to the query. So if the parameter is NULL, dont pass it through the query. I have the following but its not compiling right:
SELECT bom.pEngr_BOM_ID , bom.fEngr_Item_ID, det.pEngr_BOM_Detail_ID, 1, bom.Bill_Type, bom.Rev_Ltr, bom.Series_Ltr
FROM dbo.Engr_BOM_Control bom WITH (nolock)
INNER JOIN dbo.Engr_BOM_Detail det WITH (nolock)
ON det.fEngr_BOM_ID=bom.pEngr_BOM_ID
WHERE bom.pEngr_BOM_ID=@v_pEngr_BOM_ID
AND det.fEngr_BOM_ID=@v_pEngr_BOM_ID
CASE WHEN @v_Bill_Type IS NOT NULL THEN
AND bom.Bill_Type=@v_Bill_Type
END