Dynamic ColumnName
Jun 3, 2005
Howdy all,I'm trying to create a sproc that has the column name and search text as input parameters with dynamic SQL... what I have is:-- Create a variable @SQLStatementDECLARE @SQLStatement varchar(255)SELECT @SQLStatement = 'SELECT * FROM myTable WHERE (''' + @ColumnName + ''' = ''' + @SearchText + ''')'-- Execute the SQL statementEXEC(@SQLStatement)The procedure works (at least in Query Analyzer, once I declare/set @columnname and @searchtext) in that I don't get any errors. But it returns a blank table with "0 rows affected". However, if I hardcode the variables, it returns the proper data. What's wrong with my select statement?? Thanks!!JP
View 2 Replies
ADVERTISEMENT
Oct 2, 2007
how to select all column names from sql whose columnvalue is '1' for a specific user
View 4 Replies
View Related
Apr 30, 2004
Hi,
What is the difference between
<Tablename> . . <ColumnName> and <Tablename> . <ColumnName>
this syntax we are using in Storedprocedures.
In SQL 2000, <Tablename> . . <ColumnName> is not working
Eg:
Tablename =a
Column Name = b
Difference between a..b and a.b
Can anyone let me know.
TIA,
Ravi
View 2 Replies
View Related
Mar 20, 2008
Hi
I know the answere is out there, but i cant find it.I would like to have a dropdownlist in my webpage (asp.net/vb.net) to have values dynamicly collected from my database.The values should be the ColumnName of all columns i have in a speciall table.
How can i make the dropdownlist to have the ColumnName from every column i the table?My sqlcommand looks like this down under but i would like me.ColumnName to be a ColumnName value.New SqlCommand("Select * from tblmytable where " & Me.ColumnName & " = 'true'", myConnection) Greatful for help!/ Mitmit
View 5 Replies
View Related
Nov 9, 2006
Using SQL Query Analyzer how do I do something like:WHERE columnName = 'this' AND anotherColumnName IS NOT NULLI.E.SELECT *FROM CES_DATAWHERE DIV = 'MW'AND Not IsNull ROORDER BY RO
View 1 Replies
View Related
Dec 3, 2006
I want to dynamically determine which column I select data from. The table I want to select from looks like this:tblSexualitySexualityID int PKEN nvarchar(20)NL nvarchar(20)DE nvarchar(20)Based on the value of variable @LanguageColumnName I want to select the value from either column "EN", "NL" or "DE"In the sample below the value of @LanguageColumnName is "NL".Unfortunately the value of sexualityname is ALWAYS: "tblSexuality.NL" and not the value from that specific column...how can I alter my procedure so that it does the select the value from that column?ALTER PROCEDURE [dbo].[spFindUsers]@LanguageColumnName nvarchar(3),@startRowIndex int,@maximumRows intASBEGINSET NOCOUNT ON;
declare @tblSexualityName nvarchar(40)set @tblSexualityName='tblSexuality.' + @LanguageColumnName SELECT UserName,UserCode,ThumbNailPicture,BirthDate,ShowAgeOnly,IsMale,NearestBigCity,DistanceToNearestBigCity,Description,IsDonator,IsVIP,SexualityName FROM(SELECT ROW_NUMBER() OVER (ORDER BY UserCode) as RowNum,tblUserData.UserName,UserCode,IsDonator,IsVIP,Description,BirthDate,IsMale,ShowAgeOnly,tblCountries.CountryPicture,@tblSexualityName as sexualityname,tblCountries.CountryName,ThumbNailPicture,LastActivityDate,UserRanking,NearestBigCity,DistanceToNearestBigCity FROM aspnet_Users INNER JOIN tblUserData ON aspnet_Users.UserId = tblUserData.UserID INNER JOIN tblCountries ON tblUserData.Country=tblCountries.CountryID INNER JOIN tblSexuality ON tblUserData.Sexuality=tblSexuality.SexualityID) as MemberInfoWHERE RowNum > @startRowIndex AND RowNum <= (@startRowIndex + @maximumRows)END
View 1 Replies
View Related
Nov 20, 2005
Hello all,
how can I select one or more columns from a table by column-index and NOT by columnname?
e.g.:
SELECT tbl1.[1], tbl1.[2], tbl1.[3] FROM Orders AS tbl1
and NOT like this:
SELECT tbl1.OrderNo, tbl1.ProductNo, tbl1.Price FROM Orders AS tbl1
Is that possible in MS-SQL 2000?
Thanks a lot in advance
kind regards
Otto
View 11 Replies
View Related
May 10, 2007
Hi folks, I have a little challenge for you, I hope :)
The short story is as follows: I have multiple rather well normalized tables, and a view which connect them all togheter. In one of the tables I have an xml column, which I in the view to varchar(4000), since SSIS doesn't like xml all that much. To make the view updateable I have an instead of trigger placed on it, which works quite perfectly from T-SQL. SSIS however, claims that I cannot insert to a read-only column. Does anyone have the slightest idea how I can get around this?
The only solution I can think of is having two columns for the XML data, one as varchar and the other as varchar, then having a trigger updating the xml column when the varchar is updated, but that would require recursive triggers, which I am not too happy with.
Cheers
View 11 Replies
View Related
Oct 20, 2006
I have created a SSIS pkg (datasource and flat file destination)
I need to have the flatfile renamed after each exec to a column name that exists in the table I am running the pkg against.
Where does the code get put to make this change after the file has been created?
Basically if I could run T-SQL after the file creation, I could make it work.
thx..
View 1 Replies
View Related
Aug 25, 2007
Hi Craig/Kamal,
I got your email address from your web cast. I really enjoyed the web cast and found it to be
very informative.
Our company is planning to use SSIS (VS 2005 / SQL Server 2005). I have a quick question
regarding the product. I have looked for the information on the web, but was not able to find
relevant information.
We are getting Source data from two of our client in the form of Excel Sheet. These Excel sheets
Are generated using reporting services. On examining the excel sheet, I found out that the name
Of the columns contain data itself, so the names are not static such as Jan 2007 Sales, Feb 2007 Sales etc etc.
And even the number of columns are not static. It depends upon the range of date selected by the user.
I wanted to know, if there is a way to import Excel sheet using Integration Services by defining the position
Of column, instead of column name and I am not sure if there is a way for me to import excel with dynamic
Number of columns.
Your help in this respect is highly appreciated!
Thanks,
Hi Anthony, I am glad the Web cast was helpful.
Kamal and I have both moved on to other teams in MSFT and I am a little rusty in that area, though in general dynamic numbers of columns in any format is always tricky. I am just assuming its not feasible for you to try and get the source for SSIS a little closer to home, e.g. rather than using Excel output from Reporting Services, use the same/some form of the query/data source that RS is using.
I suggest you post a question on the SSIS forum on MSDN and you should get some good answers.
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
http://forums.microsoft.com/msdn/showforum.aspx?forumid=80&siteid=1
Thanks
Craig Guyer
SQL Server Reporting Services
View 12 Replies
View Related
Nov 23, 2007
Hi,
I have a need to display on screen AND email a pdf report to email addresses specified at run time, executing the report with a parameter specified by the user. I have looked into data driven subscriptions, but it seems this is based on scheduling. Unfortunately for the majority of the project I will only have access to SQL 2005 Standard Edition (Production system is Enterprise), so I cannot investigate thoroughly.
So, is this possible using data driven subscriptions? Scenario is:
1. User enters parameter used for query, as well as email addresses.
2. Report is generated and displayed on screen.
3. Report is emailed to addresses specified by user.
Any tips on how to get this working?
Thanks
Mark Smith
View 3 Replies
View Related
May 2, 2007
If anyone could confirm...
SQL Server 2000 SP4 to multiple SQL Server 2005 Mobile Edition on PDAs. My DB on SQL2k is published with a single dynamic row filter using host_name() on my 'parent' table and also join filters from parent to child tables. The row filter uses joins to other tables elsewhere that are not published to evaluate what data is allowed through the filter.
E.g. Published parent table that contains suppliers names, etc. while child table is suppliers' products. The filter queries host_name(s) linked to suppliers in unpublished table elsewhere.
First initial sync with snapshot is correct and as I expected - PDA receives only the data from parent (and thus child tables) that matches the row filter for the host_name provided.
However - in my scenario host_name <--> suppliers may later be updated E.g. more suppliers assigned to a PDA for use or vice versa. But when I merge the mobile DB, the new data is not downloaded? Tried re-running snapshot, etc., no change.
Question: I thought the filters would remain dynamic and be applied on each sync?
I run a 'harmless' update on parent table using TSQL e.g. "update table set 'X' = 'X'" and re-sync. Now the new parent records are downloaded - but the child records are not!
Question: I wonder why if parent records are supplied, why not child records?
If I delete existing DB and sync new, I get the updated snapshot and all is well - until more data added back at server...
Any help would be greatly appreciated. Is it possible (or not) to have dynamic filters run during second or subsequent merge?
View 4 Replies
View Related
Aug 28, 2007
Is there a difference in performance when using count(*) or count(columnname)?
View 10 Replies
View Related
Mar 9, 2015
I have tried building an Inline TVF, as I assume this is how it would be used on the DB; however, I am receiving the following error on my code, I must be missing a step somewhere, as I've never done this before. I'm lost on how to implement this clr function on my db?
Error:
Msg 156, Level 15, State 1, Procedure clrDynamicPivot, Line 18
Incorrect syntax near the keyword 'external'.
CREATE FUNCTION clrDynamicPivot
(
-- Add the parameters for the function here
@query nvarchar(4000),
@pivotColumn nvarchar(4000),
[code]....
View 1 Replies
View Related
Mar 24, 2007
I have a Stored Procedure for processing a Bill of Material.
One column on the Assembly Table is a Function Name that contains some busniess rules.
OK, now I'm doing a Proof of Concept and I'm stumped.
Huuuuh!
I will ultimately have about 100 of these things. My plan was using Dynamic SQL to go execute the function.
Note: The function just returns a bit.
So; here's what I had in mind ...
if isnull(@FnNameYN,'') <> ''
exec spinb_CheckYN @FnNameYN, @InvLineID, @FnBit = @FnBit output
CREATE PROCEDURE dbo.spinb_CheckYN
@FnNameYN varchar(50),
@InvLineID int,
@FnBit bit output
AS
declare @SQL varchar(8000)
set @SQL = '
if dbo.' + @FnNameYN + ' (' + convert(varchar(31),@InvLineID) + ')) = 1
set @FnBit = 1
else
set @FnBit = 0'
exec (@SQL)
GO
Obviously; @FnBit is not defined in @SQL so that execution will not work.
Server: Msg 137, Level 15, State 1, Line 4
Must declare the variable '@FnBit'.
Server: Msg 137, Level 15, State 1, Line 5
Must declare the variable '@FnBit'.
So; is there a way to get a value out of a Dynamic SQL piece of code and get that value INTO my OUTPUT variable?
My many thanks to anyone who can solve this riddle for me.
Thank You!
Sigh: For now, it looks like I'll have a huge string of "IF" statements for each business rule function, as follows:
Hopefully a better solution comes to light.
------ Vertical Build1 - Std Vanes -----------
if @FnNameYN = 'fnb_YN_B1_14'
BEGIN
if dbo.fnb_YN_B1_14 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
------ Vertical Build1 - Scissor Vanes -----------
if @FnNameYN = 'fnb_YN_B1_15'
BEGIN
if dbo.fnb_YN_B1_15 (convert(varchar(31),@InvLineID) ) = 1
set @FnBit = 1
else
set @FnBit = 0
END
.
.
.
etc.
View 10 Replies
View Related
Oct 24, 2004
I've looked up Books Online on Dynamic Cursor/ Dynamic SQL Statement.
Using the examples given in Books Online returns compilation errors. See below.
Does anyone know how to use Dynamic Cursor/ Dynamic SQL Statement?
James
-- SQL ---------------
EXEC SQL BEGIN DECLARE SECTION;
char szCommand[] = "SELECT au_fname FROM authors WHERE au_lname = ?";
char szLastName[] = "White";
char szFirstName[30];
EXEC SQL END DECLARE SECTION;
EXEC SQL
DECLARE author_cursor CURSOR FOR select_statement;
EXEC SQL
PREPARE select_statement FROM :szCommand;
EXEC SQL OPEN author_cursor USING :szLastName;
EXEC SQL FETCH author_cursor INTO :szFirstName;
--Error--------------------
Server: Msg 170, Level 15, State 1, Line 23
Line 23: Incorrect syntax near ';'.
Server: Msg 1038, Level 15, State 1, Line 24
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 1038, Level 15, State 1, Line 25
Cannot use empty object or column names. Use a single space if necessary.
Server: Msg 170, Level 15, State 1, Line 27
Line 27: Incorrect syntax near ';'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'select_statement'.
Server: Msg 170, Level 15, State 1, Line 33
Line 33: Incorrect syntax near 'select_statement'.
Server: Msg 102, Level 15, State 1, Line 35
Incorrect syntax near 'author_cursor'.
Server: Msg 170, Level 15, State 1, Line 36
Line 36: Incorrect syntax near ':'.
View 2 Replies
View Related
Apr 15, 2008
I have a requirment which i have partly accomplished , but could not get through completely
i have a file which comes in a standard format ending with date and seq number ,
suppose , the file name is abc_yyyymmdd_01 , for first copy , if it is copied more then once the sequence number changes to 02 and 03 and keep going on .
then i need to transform those in to new file comma delimited destination file with a name abc_yyyymmdd,txt and others counting file counting record abc_count_yyyymmdd.txt. and move it to a designated folder. and the source file is then moved to archived folder
what i have taken apprach is
script task select source file --------------------> data flow task------------------------------------------> script task to destination file
dataflow task -------------------------> does count and copy in delimited format
what is happening here is i can accomlish a regular source file convert it to delimited destination file --------> and move it to destination folder with script task .
but cannot work the dynamic pick of a source file.
please advise with your comments or solution you have
View 14 Replies
View Related
Mar 2, 2014
I am trying to create an ssis package with dynamic csv file as output. and out format contains query output.
sample file name:
Unique identifier + query output + systemdate();
The expression is looking like this.
@[User::FilePath] + @[User::FileName] + ".CSV"
-- user filepath is a variable from ssis package. File name is the output from SQL query. using script task i have assigned the values to @[User::FileName] .
When I debugged the script task the value getting properly but same variable am using for Flafile destination. but its not working.
View 3 Replies
View Related
Mar 9, 2007
I have created a dynamic SQL program that returns a range of columns (1 -12) based on the date range the user may select. Each dynamic column is month based, however, the date range may overlap from one year to another. Thus, the beginning month for one selection may be October 2005, while another may have the beginning month of January 2007.
Basically, the dynamic SQL is a derived Pivot table. The problem that I need to resolve is how do I now use this dynamic result set in a Report. Please keep in mind that the name of the columns change based on the date range select.
I have come to understand that a dynamic anything is a moving target!
Please advise.
View 3 Replies
View Related
Sep 21, 2007
I need to pass all values of field of a table into a parameter. My table contains a field called typeID, there are 15 typeID in the table. Currently when a user choses a single type they see a report for that type. I want to give them the ability to run a report on all types. My thinking is that I should just be able to pass all typeID values into a string and pass it to the stored Proc... but being new at sql and this truley being the first thing I have ever done in it... I am missing the mark.
Param is @TypeID
Here is the sql to exec the stored proc:USE [db]
GODECLARE @return_value int
EXEC @return_value = [dbo].[OL]
@TypeID = 2,SELECT 'Return Value' = @return_value
GO
I know this is not the actual SP, I am just trying to get it to work, then I will be able to write the proc. It should be something like @Option = 1 AND 2 or 1 & 2 or something to that nature?
View 6 Replies
View Related
Oct 3, 2007
Hi there,I am trying to create a dynamic sql statement as follows:ALTER PROCEDURE [dbo].[GET_FIS_LONGTITLE] -- Add the parameters for the stored procedure here @TABLENAME VARCHAR(25), @COLUMNNAME VARCHAR(25), @COLUMNVALUE VARCHAR(25), @RETURNVALUE VARCHAR(60) OUTASBEGIN DECLARE @SQL AS VARCHAR(4000) SET @SQL = 'SELECT LONGTITLE FROM ' + CAST(@TABLENAME AS VARCHAR(25)) + ' WHERE ' + CAST(@COLUMNNAME AS VARCHAR(25)) + ' = ''' + CAST(@COLUMNVALUE AS VARCHAR(25)) + '''' execute (@SQL) --''' + CAST(@RETURNVALUE AS VARCHAR(60)) + ''' = END here I am trying to get the long title of an item based on the tablename, columnname, the column value. So the select returns the long title from the table as required. But I want to assign that value to the @RETURNVALUE So I tried:SET @SQL = 'SELECT ''' + CAST(@RETURNVALUE AS VARCHAR(60)) + ''' = LONGTITLE FROM ' + CAST(@TABLENAME AS VARCHAR(25)) +
' WHERE ' + CAST(@COLUMNNAME AS VARCHAR(25)) + ' = ''' + CAST(@COLUMNVALUE AS VARCHAR(25)) + ''''
It does not work. I do not know what is missing here.Any help would be greatly appreciated.thanks,Murthy here
View 2 Replies
View Related
Apr 25, 2004
Hello All,
I'm trying to build a SQL statement dynamically based on what was selected.
I'm not sure how set strSQL equal to all the items checked in a checklist.
Also, I don't know how to add the item to strSQL once it's determined that it has been checked.
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim strSQL As String = 'dynamic SQL statement
Dim li As ListItem
For Each li In cklCheckList.Items
If li.Selected = True Then
'add item to strSQL
End If
Next
End Sub
Thank you!
Tina
View 17 Replies
View Related
Nov 21, 2004
Hello Pros
Is it possible to author a stored procedure with dynamic sql inside,
I need the SP for search purposes, take that i have a number of search criterions
represented by a number of text boxes in the front-end web app, so if the users fills up the text box i will include it in the search condition ....
It's so easy to do it in the code-behind file by the means of a dynamic string with the search clauses appended to it, but i wanna do it with SPs
Any help will be highly appreciated,
View 2 Replies
View Related
Apr 25, 2005
HI !!
We are creating an Ad Hoc query tool for our Voters Profile system. Voters Profile Table has voting history for person. It has columns like : G04 P04 G03 P03 G02 P02 G01 P01 G00 P00 and History
G = general election P = primary election xx = represent year
G04 etc columns has value = "Y" if person voted in that year else its null
If Person A voted in General Election in year 2004 and Primary Election in 2003 then
History Column = G04P03
Now, we need to create a Ad Hoc Query where people ask for:
"Give me a list of People who votes in All 4 general election"
OR
"Give me a list of people who voted 2 times in Primary Election"
In order to do such query what we should do? Can some one help?
View 7 Replies
View Related
Dec 20, 1999
We are currently using SQL Server 7.0. Following are the Dynamic SQL I
try to run
declare
@ls_sql nvarchar(500),
set @ls_sql = 'select count(*) from Member where MemberID'
execute sp_executesql @ls_sql
and it works fine. Now I need the results (Total Counts) into a
varianle. Does any one know the correct syntax? I try following aways,
and it is not work
declare
@ls_sql nvarchar(500),
@ll_count int
set @ls_sql = 'select count(*) from Member where MemberID'
execute sp_executesql @ls_sql, @ll_count
select @ll_count
Thanks
View 1 Replies
View Related
May 9, 2005
I have a where clause which I dynamically build that looks like this.
SET @orderstatus = ' (t.fieldvalue in ( ''PEND'', ''REDR'') or (t.fieldvalue=''NOTA'' and pr.isactive = 0)
or ((t.fieldvalue=''RXAP'' and m.formularymed = 0))
or (t.fieldvalue=''NOTA'' and pr.deano = @deano)) '
I then do @tsql = 'select * from table where ' + @orderstatus
The problem is I get the literal value @deano not the number that is stored in @deano
View 2 Replies
View Related
Jan 17, 2006
Helping a developer with an sp from a .net program which will feed it several parameters (any of which can be null). In other words the where clause is somewhat dynamic. I am posting the select statement (not the parameters) he used. Currently this sp runs very slow. I was considering building some type of dynamic where clause to check for the parameters to create that, but was afraid that in itself might be just as bad. Any better way to handle it?
SELECT *
FROM vGrievances
WHERE
coalesce(GrievanceID,-1) = coalesce( @GID, GrievanceID, -1)
AND coalesce(Member, 'x') = coalesce( @M , Member, 'x')
AND coalesce(Subject,'x') LIKE coalesce( '%' + @S + '%' , Subject,'x')
AND coalesce(GrievDesc,'x') LIKE coalesce('%' + @GD+ '%', GrievDesc,'x')
AND coalesce(OwnerID, -1) = coalesce( @OID, OwnerID, -1)
AND coalesce(CreatorID, -1) = coalesce( @CRID, CreatorID, -1)
AND coalesce(AssignedToID, -1) = coalesce( @ATID, AssignedToID, -1)
AND DueDate >= coalesce(@DDS, DueDate)
AND DueDate <=coalesce(@DDE, DueDate)
AND coalesce(ContractID, -1) = coalesce(@COID, ContractID, -1)
AND coalesce(GrievTypeID, -1) = coalesce(@GTID , GrievTypeID, -1)
AND coalesce(StatusName,'x') = coalesce(@SN, StatusName,'x')
AND CreatedDate >= coalesce(@CDS, CreatedDate)
AND CreatedDate <= coalesce(@CDE, CreatedDate)
AND coalesce(StatusChangedDate,'1/1/1900') >= coalesce(@SCDS , StatusChangedDate,'1/1/1900')
AND coalesce(StatusChangedDate,'1/1/1900') <= coalesce(@SCDE, StatusChangedDate,'1/1/1900')
AND coalesce(Division,'x') = coalesce(@D, Division,'x')
AND coalesce(PriorityTypeID, -1) = coalesce(@PTID, PriorityTypeID, -1)
AND coalesce(OutcomeID, -1) = coalesce(@OcID, OutcomeID, -1)
AND ContractID IN (
(SELECT CONVERT(INT,CONTRACT_ID)
FROM SEC_CONTRACT_SELECT
WHERE SU_USER_ID = @USERID
AND SS_SYS_NAME = 'RESOLVE'))
AND GrievanceID between 1 AND 500
END
GO
View 1 Replies
View Related
Jun 26, 2007
Code:
INSERT INTO StagingFinal(col1, col2,
col3,col4, col5, col6, col7, col8)
SELECT stag.id,
REPLACE(stag.col1,stag.col1,'User' + stag.col1)
, us.id , ua.Users
, (CASE WHEN UPPER(Job) = 'Accounting' THEN 0 ELSE 1 END)
, stag.EndDate ,
CONVERT(INT, REPLACE(stag.earnings,'.000',''))
, stag.rep
FROM Staging stag ,
dbo.Users us ,
dbo.UserAccs ua
WHERE stag.cus = ''
AND us.id = ua.id
AND REPLACE(ua.AccountNumber,'-','') = stag.AccountNumber
I have the above code that I want to put into a variable and execute it via EXEC.
I try:
Code:
SET @SQL = ' INSERT INTO StagingFinal(col1, col2,
col3,col4, col5, col6, col7, col8)
SELECT stag.id,
REPLACE(stag.col1,stag.col1,''User'' + stag.col1)
, us.id , ua.Users
, (CASE WHEN UPPER(Job) = ''Accounting'' THEN 0 ELSE 1 END)
, stag.EndDate ,
CONVERT(INT, REPLACE(stag.earnings,''.000'',''''))
, stag.rep
FROM Staging stag ,
dbo.Users us ,
dbo.UserAccs ua
WHERE stag.cus = ''''
AND us.id = ua.id
AND REPLACE(ua.AccountNumber,''-'','''') = stag.AccountNumber '
But no luck... Any help?
View 3 Replies
View Related
Oct 13, 2004
Hi:
Can anyone tell me if it is possible to use dynamic sql within a user defined function in MS SQL 2000?
Thanks,
Rui Ferreira
View 2 Replies
View Related
Oct 25, 2004
I am trying to create a sql job that has multiple steps, emails out the error on a failure, and will continue to the next step. We can not put Outlook on our servers so we can not use SQL Operators.
Basically I would like every step in the job to jump to a failure step that emails out an error message and returns to the step after the step that failed. I could have a failure step for each actual step, but this job has a number of steps and being the anal programmer I am, I don't want to have multiple steps that do the same thing.
I have figured out how to Dynamically update the current sql Job by using sp_update_jobstep and job tokens [STEPID] and [JOBID]. For example the following code will update the job so it will quit job with failure if the step finishes successfully.
declare @tmpJobID as uniqueidentifier
declare @tmpStepID as integer
set @tmpJobID = (convert(uniqueidentifier, [JOBID]))
set @tmpStepID = [STEPID]
exec sp_update_jobstep @job_id=@tmpJobID, @step_id=@tmpStepID, @on_success_action=2
The problem is that the job is not updated until it is completed. So, the changes will not take affect until the next time it is ran. Is there a way to reload the job into memory, or force the job to use the updates?
I have also tried directly updating the sysjobsteps table but that didn't work either.
Thanks much.
View 2 Replies
View Related
Dec 7, 2004
I am dynamically building SQL strings within my code and using EXEC () to excute. My quetions is as follows: Can I assign the ouput of
Exec (@sqlstring) to a variable
i.e
@myvar=exec (@sqlstring)
note: @sqlstring return a single number, count of records.
View 11 Replies
View Related
Oct 14, 2005
Hi All,
Following is the situation:
DECLARE @iCount INT
DECLARE @strQry AS VARCHAR(4000)
DECLARE @strEmp AS VARCHAR(200)
SET @strEMP = '993,1040,1061'
SET @strQry = 'SELECT @iCount =COUNT(id) FROM tableA WHERE status=91 AND Employee IN ('+ CONVERT(VARCHAR(4000),@strEmp)+')'
EXECUTE (@strQry)
PRINT @iCount
This statement does not execute, I tried to declare @iCount as VARCHAR(10), still it does not work. Actually I wanted to get the count in @iCount and manipulate later.
Can anyone help?
Regards
qa
View 1 Replies
View Related
Mar 9, 2006
Hello,
I've tried to convert a static SQL to dynamic SQL but I'm always getting a NULL value
My SP is:
CREATE PROCEDURE usp_dataList
@text nvarchar(100)
AS
DECLARE
@list varchar(8000),
@SQL varchar(600)
SELECT @SQL = 'SELECT '+ @list +'='+COALESCE(@list + ',', '') + 'CAST(data_received.data_data AS varchar(15))
FROM data_received
WHERE data_received.data_data LIKE N'''+Replace(@text,' ',' OR data_received.data_data LIKE N''')+''''
exec(@sql)
SELECT @list AS data_list
GO
What's wrong here?
I've tried to search google and find the problem my self in the past 3 hours but I couldn't find anything wrong (I'm new in SP)
I also tried to change to this:
SELECT @SQL = 'SELECT '+ isnull(@list, '') +'='+COALESCE(@list + ',', '') + 'CAST(data_received.data_data AS varchar(15))
I'm getting this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.
Please help me...
Thanks,
BuildHome
View 6 Replies
View Related
