Dynamic Filenames
Nov 13, 2007How do I build a dynamic filename using an SSIS package? Do I do this thru Expression Building?
View 10 Replies
ADVERTISEMENT
Altering Logical Filenames
Oct 24, 2001Pros,
I would like to change the logical filenames for a database on my test server. I know this is done with ALTER DATABASE, but I'm not sure of the exact syntax. Can anyone help?
rb
Script Objects To Different Filenames
Oct 10, 2007I'm using SQL2005 and need to script a bunch of Tables (and possibly other objects) to files. I can't make one big file with all the objects in it. I need to create an indiviual file for each object and each filename needs to be something like dbo.objectname.sql. I will be checking each file (object script) into VSS. Can someone tell me how to do this withouth having to manually generate a script for each object? Is there a way to check each object you want scripted and the SSMS spit out a file per object?
Thanks
Variables To Hold Filenames
Feb 8, 2007 I was wondering if it is possible to create a variable that holds the filename as an expression. I am using a For each loop to pick up files and i want to insert the 'Filename' as a colum in to the destination Table so that i can differentiate among the rows in my destination Table.
Using the 'Derived Colum' in DTS I can link the colum to a variable. However i do not know how to create the variable which will dynamically store the filename for each file....
Any Clues...
Would really appreciate the help.....
Or is anyone knows any other simple way of inserting the filename as a column in the destination Table ...Been stuck on this for a while...
Can We Find The List Of Filenames Associated With A DB Using VDI ?
Sep 3, 2007Hi all,
I want to find the list of filenames associated with the SQL server instance programmatically. As VDI is concerned with taking backup of SQL server. Can we find the list of filenames associated with a DB using VDI ? or Is there any other way to know filenames?
Thanks in Advance,
Ram
Select All Filenames That Have ArticleID=int (was SQL Statement)
Mar 13, 2006I have a table which has three fields.
The table name is Filename,and it has three fields:Id,ArticleID and Filename
I have an integer variable int
I want to select all the filenames that have ArticleID=int
I used the following SQL statement but it didn't work
Select Filename from Filename where ArticleID=int
wHAT'S wrong with it
Thanks
Transact SQL :: Pass Filenames To A Loop
Aug 27, 2015I’m working on building a file list from a directory I have and wish to loop through the filenames. I’ve worked out I can get the list using
EXEC
xp_dirtree @path, 10, 1
I’m just not sure out I pass this to my loop 1 element at a time.
E.g. Let’s say there are 10 files in my folder, I wish to loop through each name using my @Filename parameter
DECLARE @Path
varchar(50), @FileName
varchar(20)
SET @Path
= 'C:myFiles'
EXEC
xp_dirtree @path, 10, 1
[Code] ....
Is There A Way To Change Logical And Physical Datebase Filenames?
Aug 23, 2006A database was set up as a test database and then the database name changed but the logical and physical filenames still have test in their name - obviously not a good idea. I have tried to change the physical file name but get the error message that the physical file name cannot be changed once the database has been created. I have also tried detaching the database and renaming the mdf and ldf files but these could not be re-attached so had to revert back to the original names.
View 2 Replies View RelatedRetreiving TranLog Filenames From Windows Directory
Jan 24, 2006Due to circumstances beyond my control, I have to restore a full database backup from October 1, 2005 and then over 4400 transaction logs to bring a database up to Jan 01, 2006 on a development server (storage team lost all daily full backups from 10/02/2005 to 01/01/2006). Since the backups were not taken on this machine originally, the information I would pull from the sysjobhistory tables to get the transaction backup names is not available. Does anyone have a link or a script to pull the filenames from the backup directory into sql server so I can build a dynamic script to apply the tran logs instead of running all 4400+ of them by hand? Thanks, Tom
View 5 Replies View RelatedSQL Server 2008 :: Split Apart Filenames Into A Database?
Oct 11, 2015I am trying to break apart a list of filenames that was inserted into a database. It only breaks out the first one then moves onto the next record. If I do them individually then seem to work but not the whole table when queried. I need to break out each file into a temp table then insert them into a documents field in a database.
my filenames look like so and can have from 1 file name to 10 file names in the string.
test.pdf,test2.pdf,test4.tif,test5.pdf
somedoc.tif,test.docx,test4.pdf
This is my current method, I needed to create a cursor around it to go through all the records, split out the filenames and insert into a temp table. But if there is a better way ill do it. The problem with this is only the first file is getting inserted into the temp table and nothing else even if the filename has 4 files in it.
Create table #tempFiles (OldStrId int, OldPercent int, strfilename varchar(max), RequestId int, OblId int)
declare @OldStr int, @OldPer int, @FileName varchar(max), @intcount int;
Declare filenames CURSOR FOR Select intSTRBonusID, intPercentID, strFileName from tblSTR where strFileName > ''
UNION ALL
Select intSTRBonusID, intPercentID, strFileName from tblSTRHist where intPercentID in (61,62) and strFileName > ''
[code]....
SQL Server 2012 :: Fetch Physical Filenames In Database?
Mar 26, 2015I have a folder with 1000 text(.txt) files in it.
I want to insert the filenames into a table in sql server.
Is it possible in SQL Server 2008..?
Package Configuration For Generic Pathnames And Specific Filenames
Apr 3, 2007It's very easy to make a generic xml configuration file for the connection to the database for all packages my project contains. The connectionstring is the same for all these packages
Now I want to do the same for all flat file sources I have in my project where the connectionstring is not the same for all packages.
For example I use the following flat files in my project:
c:AAA.txt
c:BBB.txt
c:CCC.txt
If I move the packages to the production database the flat file sources are located in another directory.
So in fact for the flat file sources there is a generic part for all files (in this case 'c: ') and a specific part ('AAA.txt', 'BBB.txt' and 'CCC.txt').
Can I indicate this in the package configurations sections? And how?
Thanks,
John
Integration Services :: How To Import Filenames And File Creation Date Into Table
Apr 20, 2015I am importing Differennt Excels Files into table. my require ment is after importing completed I need to insert all these Filenames ,File creation date into table. (for Auditing).
View 9 Replies View RelatedImporting Excel Sheet Which Have Dynamic Column Name And Dynamic Number Of Columns
Aug 25, 2007Hi 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
SSRS 2005 - Email Report On Execution To Dynamic List With Dynamic Parameters = No Schedule
Nov 23, 2007Hi,
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
Merge Replication W/ Dynamic Row Filter - Not 'dynamic' After First Initial Sync?
May 2, 2007If 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?
T-SQL (SS2K8) :: How To Add Inline TVF With Dynamic Columns From CRL Dynamic Pivot
Mar 9, 2015I 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]....
Mixing Dynamic SQL With Non-Dynamic In Stored Proc
Mar 24, 2007I 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.
Dynamic Cursor/ Dynamic SQL Statement
Oct 24, 2004I'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 ':'.
Dynamic Source And Dynamic Destination
Apr 15, 2008I 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
SQL 2012 :: Creating Dynamic SSIS File Format - Dynamic CSV File As Output
Mar 2, 2014I 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.
Dynamic Columns For Dynamic SQL
Mar 9, 2007I 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.
Dynamic SQL
Sep 21, 2007I 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?
Dynamic SQL!
Oct 3, 2007Hi 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
Dynamic SQL
Apr 25, 2004Hello 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
SP With Dynamic SQL
Nov 21, 2004Hello 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,
Dynamic SQL Help
Apr 25, 2005HI !!
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?
Help!!! Dynamic SQL
Dec 20, 1999We 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
Dynamic Sql With @sql
May 9, 2005I 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
Bad Sql Or Dynamic Where
Jan 17, 2006Helping 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
Help With T-SQL Dynamic SQL
Jun 26, 2007Code:
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?
UDF And Dynamic SQL
Oct 13, 2004Hi:
Can anyone tell me if it is possible to use dynamic sql within a user defined function in MS SQL 2000?
Thanks,
Rui Ferreira
Dynamic SQL Job
Oct 25, 2004I 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.