Hi everyone,
I'd like to change in order to improve the maintenance of our packages.
I've got lots of packages running with values such as "path" and "filename" inside variables.
Let me know how can I set xml file for that.
Thanks a lot,
HELP!!!! ARGHHH!!!!!
I can't get this to return the rows, I keep getting 0 rows...
declare @mytime datetime
declare @num_rec integer
set @mytime = getdate()
set @num_rec = (select count(*) from mytable where enter_time <= @mytime)
I've tried '@mytime' also.
No success. Please help
Thanks,
Brian
Similar to a previous thread, but not exactly. I have a batch file that takes
two date parameters. I have a SQL script that gets the values I want and assigns
them to variables, but I don`t know how to pass these to the batch.
I`m thinking of something like:
declare @myvar1 datetime, @myvar2 datetime
select @myvar1 = <some date value>
select @myvar2 = <some date value>
exec xp_cmdshell "c:mssqlscriptsMYBATCH.BAT" @myvar1 @myvar2
This just errors out when it reaches the variables in the last line. If I move
the variables inside the quotes, it passes them as literal text strings, not as
the assigned datetime values.
Any ideas? Is this possible?
Thanks,
Robert
I have a 'file system task' moving files from one server to another for processing. I have defined the path and filename as separate variables. When I attempt to pass them together into the task I receive an error.
Source Path & Filename:
@[User:: DataSourceFolder]+"\"+ @[User::CD_PaidClaimSource]
Outputs:
\umrdwh2FTPCLAIMDAILY.DATA
Destination Path Now: (Filename not specified per another thread.)
@[User::WorkingFolder]
Original Destination Path: (Which did not work.)
@[User::WorkingFolder]+"\"+ @[User::CD_PaidClaimSource]
I receive validation errors:
Error 1 Validation error. CLAIMDAILY Move : Failed to lock variable "\umrdwh2FTPCLAIMDAILY.DATA" for read access with error 0xC0010001 "The variable cannot be found. This occurs when an attempt is made to retrieve a variable from the Variables collection on a container during execution of the package, and the variable is not there. The variable name may have changed or the variable is not being created.". ClaimLoading_MASTER.dtsx 0 0
How do I overcome this without hard coding the path in the task?
when using a raw file destination it would be nice to be able to use an environment variable for the filename property.
like
%my_extract%data.txt
instead of
c:my_extractdata.txt
[Microsoft follow-up]
In the Raw File Source component when I set AccessMode='File name from variable' and select the drop down for 'FileNameVariable' property the first thing I see is all the system variables. What's the point in displaying those? They are completely useless in this context. Would it really have been that hard to remove them?
Can this be changed please?
-Jamie
I'm reading a Flat File.
The 1ste record containts special Info that is needed for first preparing the database on a buld insert of the remaining lines.
How could i realize this, just read 1 line, store some of that data into variables, execute some proc's and then read the rest of the file ?
Kind Regards.
Hello,
We have an existing OLD System in SQL Server 2000 DTS Packages.
The Whole application runs on DTS.
There are several Packages which are called from a Master Package. Each Child packages have their own Global Variables. Most of them are the File Location variables to have the Source Location of the Input Data, mainly from the Excel Files.
Now, even if the Global Variables are there to change whenever they want to change the Locations of the Files, they have to goto each child package and change the variable themselves.
To resolve this issue, they want a configuration File (INI) / Table which would store those Variables. My thought is to read from that File/Table and Update all the packages' global variables through an ActiveX Script as the First Step of the Master package. That would eliminate the need of changing anything in the existing System.
But the Problem is the management (PM / DBAs / Team members) have different views to store the Configuration data.
1. Some wants it into a Different Database, having one table for this application so in future they can also add another table for some other application.
2. Some wants to store it in a Table in the Same Database of this Application.
3. Some wants to save it as a INI file.
As i'm the one who's going to really implement it, they have asked me to research for a best solution out there.
so I request to help me to decide which is a good solution and why.
Best Regards,
Deepak
I've got a SQL Server hardware Replacement to do. This server has a merge replication setup on it which was setup by a contractor.
Do i have to do the configurations manually when moving the replication bits across two servers or is there a different process involved ? I am just not sure how to approach this.
Hello all,
I get some dates entered manually in french format in a varchar(255) field and want to convert that field in datetime. Since, Enterprise manager is warning me about a risk to loose information, I'm thinking about a way to update the french text in the corresponding english text before doing the conversion.
Example:
25-fév-08 (varchar(255)) has to become 25-feb-08 (datetime)
Could anybody help me, please?
Hello All!
I have a job that we wrote in SQL 2000 that loops through and finds all the orders pending verification and distributes them via emal to 100+ sales people every night. Each email is sent to the individual sales rep. I did this using sp_makewebtask and attaching the html file to an email and using a WHILE statement to loop thorugh all pending entries in sql.
I recently learned that sp_makewebtask is not going to be around for the next release and that I should use RSS for all future development.
Question: How can I achieve the same result outlined above using RS? How can I dynamically create and distribute HTML files?
I know that RS is xml based, but some pointer on how/where to start would be much appreciated.
Many Thanks....
I wish to replace column2 'ABC' value become blank.
EXAMPLE TABLE VIEW
column2
-------
ABC 123, Alt. ABC 456, Alt ABC 789
select replace(replace(B.[column 2],A.[column 1],''),'ABC','')
from TABLE A,TABLE B
where A.id = B.id
After executed
column2
-------
123, Alt. 456, Alt. 789
But not expected result, because i jz wan ABC become blank not Alt. ABC.
So what should i modify with my logic and script?
Dear all,I need some help from all my Transact SQL Guru friends out there..Here is the scenario in its most simplified form.. ..I have two tables.. A(Lookup table) and B(Transaction Table)TableA FieldsEmployeeLocationIDEmployeeLocation (This could have values say"B","BO","BOM","C","CA","CALC") etc...TableB FieldsEmployeeIDEmployeeName.......EmployeeLocationID (will have null initially when rows are populatedfirst time)EmployeeLocation (This could have values"BA123","BOMBAY","BOTS123","BRACK".... etc)I hope you get where I am leading this to, from my examples..Requirement is to populate the EmployeeLocationID in Table B withEmployeeLocationID from TableA by matching the field EmployeeLocationin both tables.Please note that table B's EmployeeLocation could be A'sEmployeeLocation + some additionalcodes like "123","RACK" etc in theabove example...Therefore, this is what I had wrote initially..update Bset B.EmployeeLocationID =A.EmployeeLocationID
Quote:
Help, I have a vendor file with the vendors name. I need to add afield that has alternate captionseg. :In my vendor file I have records for AT&T with three differentnames/spellings for AT&T.AT&TLucent TechnologiesA T & TI want to add a new field that would have AT&T for the above records.I know I could hardcode a queryUpdate F_VENDORSet VENDOR_CAPTION_ALT = 'AT&T'where VENDOR_CAPTION like '%Lucent%' orVENDOR_CAPTION like '%A T & T%'however, how can I use another table that has the my search keywordsfor the "like" and the replacement captions so I dont need literalqueries to update all the diffent vendors?I have identified 96 instances of where I want to have an alternativename that I can use for a type of grouping. This gets really wildwith the military such as Army and Navy.TIARob
View 4 Replies View Related
Hi,
Is there any replacement for BCP in Sql Server 2005 ?
Thanks
Altaf Hussain Nizamuddin
In the BOL, it states to replace DB_ID() with a valid database name when the compatibility level is 80 or below.
The original statement is:
SELECT
object_id AS objectid,
index_id AS indexid,
partition_number AS partitionnum,
avg_fragmentation_in_percent AS frag
INTO #work_to_do
FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL , NULL, 'LIMITED')
WHERE avg_fragmentation_in_percent > 10.0 AND index_id > 0;
I replaced DB_ID() as follows but none worked:
DB_ID(N'pubs')
pubs
What should the syntax look like?
I have a requirement to create many SSIS packages and no datatransform is required so the BCP task looks a good contender providing it can do both import & export
is it possible to parse the values in bold as package variables into the BCP task. If so how?
BULK INSERT ipcs_wvg.dbo.extract
FROM 'D:IPCSextract.csv'
WITH (FORMATFILE = 'D:ipcsqueryextract.xml');
Thanks in advance
Dave
I have a global database called ETL Configuration for all my SSIS packages that uses a single table. So I can create three global variables
USE [ETLConfiguration]
CREATE TABLE [dbo].[SSIS Configurations](
[ConfigurationFilter] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValue] [nvarchar](255) COLLATE Latin1_General_CI_AS NULL,
[PackagePath] [nvarchar](255) COLLATE Latin1_General_CI_AS NOT NULL,
[ConfiguredValueType] [nvarchar](20) COLLATE Latin1_General_CI_AS NOT NULL
) ON [PRIMARY]
Here is the data I would put in here
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:IPCSextract.csv'
PackagePath = 'Package.Variables[User::gsFileName].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'D:ipcsqueryextract.xml'
PackagePath = 'Package.Variables[User::gsFormatFile].Properties[Value]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'ipcs_wvg.dbo.extract'
PackagePath = 'Package.Variables[User::gsTableName].Properties[Value]'
ConfiguredValueType = 'String'
--Database connection info
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = '.mssql2005'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[ServerName]'
ConfiguredValueType = 'String'
ConfigurationFilter = 'MySSISPackageName'
ConfiguredValue = 'MyDatabaseName'
PackagePath = 'Package.Connections[MyDatabaseName].Properties[InitialCatalog]'
ConfiguredValueType = 'String'
I have looked at lots of options to automaticly create SSIS packages and have a hunch that that simple can be better: All these solutions look way to complex to what I want to achieve--
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=17484&SiteID=1
http://lakshmik.blogspot.com/2005/05/how-to-programmatically-create-ssis.html
Create DataFlow Package Sample
http://msdn2.microsoft.com/en-us/library/ms161541.aspx
SMOTableList Sample
http://msdn2.microsoft.com/en-us/library/ms161564.aspx
I've deployed my ssis pkg to the server and created a sql job to run this pkg. So far, everything is fine. Today, I got a request to change some variables inside the package which is part of the .dtsconfig.  I want to edit the deployed .dtsConfig but it won't allow me and always complained  that this file has been opened by another program. I am sure i've closed my ssis designer and other notpad, why can't I edit and save .dtsconfig file?
View 4 Replies View RelatedIn VS 2003 I used SQLDMO (Com Object) to list all available SQL Servers. Is in SQL Server 2005 a managed .net Component that can do that task?Thanks,Rainer.
View 2 Replies View RelatedBeen meaning to post this for a while. It does a very limited job of only allowing [0-9], but could be extended to allow negative numbers, or numeric values that are suitable for numeric types other than INT, but avoiding the pitfalls of IsNumeric() which might allow through data not suitable for some of the numeric datatypes
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = object_id(N'[dbo].[kk_fn_UTIL_IsINT]') AND xtype IN (N'FN', N'IF', N'TF'))
DROP FUNCTION dbo.kk_fn_UTIL_IsINT
GO
CREATE FUNCTION dbo.kk_fn_UTIL_IsINT
(
-- String to be tested - Must only contain [0-9], any spaces are trimmed
@strINTvarchar(8000)
)
RETURNS int-- NULL = Bad INT encountered, else cleanedup INT returned
/* WITH ENCRYPTION */
AS
/*
* kk_fn_UTIL_IsINTCheck that a String is a valid INT
*SELECT dbo.kk_fn_UTIL_IsINT(MyINTColumn)
*IF dbo.kk_fn_UTIL_IsINT(MyINTColumn) IS NULL ... Bad INT
*
* Returns:
*
*int valueValid integer
*NULLBad parameter passed
*
* HISTORY:
*
* 30-Sep-2005 Started
*/
BEGIN
DECLARE@intValueint
SELECT@strINT = LTRIM(RTRIM(@strINT)),
@intValue = CASE WHEN @strINT NOT LIKE '%[^0-9]%'
THEN CONVERT(int, @strINT)
ELSE NULL
END
RETURN @intValue
/** TEST RIG
SELECTdbo.kk_fn_UTIL_IsINT('123'),IsNumeric('123')
SELECTdbo.kk_fn_UTIL_IsINT(' 123 '),IsNumeric(' 123 ')
SELECTdbo.kk_fn_UTIL_IsINT('123.'),IsNumeric('123.')
SELECTdbo.kk_fn_UTIL_IsINT('123e2'),IsNumeric('123e2')
SELECTdbo.kk_fn_UTIL_IsINT('XYZ'),IsNumeric('XYZ')
SELECTdbo.kk_fn_UTIL_IsINT('-123'),IsNumeric('-123')
SELECTdbo.kk_fn_UTIL_IsINT('-'),IsNumeric('-')
**/
--==================== kk_fn_UTIL_IsINT ====================--
END
GO
Kristen
shailendra writes "Hi,everbody
lets come to my problem.I have one variable of type "ntext" which contain character "". i want to remove it or replace it with a blank character.
very urgent.....
waiting for the reply
shail"
Table A
200 Rows, each row: X, has a unique value. Has 2 extra columns: C1 and D1.
Table B
4000 Rows, each row: Z, has a value that corresponds to Table A's unique values. Has 2 extra columns: C2 and D2.
What I am looking to do is update all 4000 rows in Table B to match the data set in Table A. C1 -> C2, D1 -> D2 following the criteria that X = Z.
All the update attempts I've made so far only update 200 rows within Table B.
Thank you for your help.
Hi,It seems to be simple, however, it stumbles me.how to replace all the double quotes (") within the followingsentence (or a column) with single quotes ('),colA = this is a freaking "silly" thing to dointocolA this is a freaking 'silly' thing to doSelect Replace(colA,'"',''')[color=blue]>From tblXYZ[/color]won't work,Select Replace(colA,'"',"'")[color=blue]>From tblXYZ[/color]won't work neither.How come? Thanks.
View 4 Replies View RelatedJust wondering if anyone has any suggestions for a replacement for Access Forms once I move the tables etc to SQL 2005?
Does SQL 2005 have any form building functionality like Access?
Hi,
Is there a work around for the following query on sql server compact edition
IF(Some Condition) --
BEGIN
INSERT INTO @TEMP
SELECT 1, XYZ_ID FROM My_Table
END
Thanks,
Dp
Hi,
We had to migrate from MS Access 2003 to SQL Server 2005 due to some project requirements.
We had used Forms, earlier, to enter data to the Access database.
I understand SS2005 doesnt support forms.
Can you suggest any other way of creating an equivalent entity as that of forms, so as to enable users to enter data to the SS 2005 DB easily.
Thanks in Advance for your help,
Regards,
Sundar
With this discussion here http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=70328
I started to thinkn about Microsoft really calculated checksum value.
This code is 100% compatible with MS original. That is, the result is identical.
You can use it "as is", or you can use it to see that MS function does not produce that unique values one could expect.
With text/varchar/image data, call with SELECT BINARY_CHECKSUM('abcdefghijklmnop'), dbo.fnPesoBinaryChecksum('abcdefghijklmnop')
With integer data, call with SELECT BINARY_CHECKSUM(123), dbo.fnPesoBinaryChecksum(CAST(123 AS VARBINARY))
I haven't figured out how to calculate checksum for integers greater than 255 yet.CREATE FUNCTION dbo.fnPesoBinaryChecksum
(
@Data IMAGE
)
RETURNS INT
AS
BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ SUBSTRING(@Data, @Index, 1),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296
RETURN @SUM
ENDActually this is an improvement of MS function, since it accepts TEXT and IMAGE data.CREATE FUNCTION dbo.fnPesoTextChecksum
(
@Data TEXT
)
RETURNS INT
AS
BEGIN
DECLARE@Index INT,
@MaxIndex INT,
@SUM BIGINT,
@Overflow TINYINT
SELECT@Index = 1,
@MaxIndex = DATALENGTH(@Data),
@SUM = 0
WHILE @Index <= @MaxIndex
SELECT@SUM = (16 * @SUM) ^ ASCII(SUBSTRING(@Data, @Index, 1)),
@Overflow = @SUM / 4294967296,
@SUM = @SUM - @Overflow * 4294967296,
@SUM = @SUM ^ @Overflow,
@Index = @Index + 1
IF @SUM > 2147483647
SELECT @SUM = @SUM - 4294967296
RETURN @SUM
END
Peter Larsson
Helsingborg, Sweden
Hi everyone, I have some code that we need to migrate to SQL Server2005 from 2000, and I have a webpage that upon viewing, fires a queryto the SQL server using ISS and ISAPI. The result set is formatted fordisplay using XSLT. But since ISAPI is deprecated in SQL 2005, I waswondering how to migrate this.Thanks,
View 5 Replies View RelatedHello everyone,
i'm using an excel source where i get my excel rows using a query, I'd like to replace possible null values with some other data(a zero value or a empty string for example), that's because i'm performing a transformation into a sql server table wich doesn't accept null values for some columns.
Is there any function to convert a null value to another one? I used the sql server's CASE function, but it didn't work. Any suggestions?
thanks a lot.
Hey folks,
In Sybase SQL Any where, we have a function called Number (*) which will in turn generate serial numbers like ex..
Select Number (*) from x
The output will be
Number(*)
1
2
3
4
5
6
and so on..
I need a equivalent function in SQL Server 7.0 in which if i do select on that particular function with a table i should return above values..
Can any one solve this issue...
Please help me in this....
Urs
VJ
I have a table with the following data;
CREATE TABLE #Tab (Data Varchar(100))
INSERT INTO #Tab (Data)
Select 'Apple=5,Orange=10,Banana=11' UNION ALL
Select 'Apple=10,Orange=1033,Banana=0' UNION ALL
Select 'Apple = 120,Orange = 1,Banana = 112'
Select * from #Tab
How do I replace every value before the '=' but leave the comma.
Here is what the final output should look like
CREATE TABLE #TabFinal (Data Varchar(100))
INSERT INTO #TabFinal (Data)
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana' UNION ALL
Select 'Apple,Orange,Banana'
Select * from #TabFinal
Hello,
We recently upgraded from MS SQL 2000 to MS SQL 2005 and unfortunetly Speed Ferret (current version) is not compatible with 2005.
I wrote Black Moshannon Systems and it looks like we are months away from a release that will work with MS SQL 2005.
Does anyone know of a good search utility that works with MS SQL 2005?
Subject says it all, really. I want to start using Token Replacement,but do I break anything by enabling it? Do jobs that don't use tokensrequire any changes? I saw somewhere that it can't be turned off, soI'm paranoid about enabling it. Anything that I should be aware of?Many thanks.
View 2 Replies View Related