Answer Questions

• eman1975 Rollback in a trigger??? (explicit/autocommit transactions)

  I’m using triggers for some more advanced integrity check. The problems is that the same trigger can be run from explicit transaction (this is when I start transaction from .NET) and as autocommit transaction ( very rare, only when we do some maintenance directly with SQL statements). Currently if I want to rollback transaction from trigger I only issue RAISERROR statements, then .NET application catches this error and generates rollback. But the problem is if trigger is raised from some SQL statements outside .NET application (normally some maintenance work direct from SQL manager ) in that case error is generated but there is no rollback. Is there any way to distinguish if transaction in trigger is explicit or autocommi ...Show All

• K Sumeet Custom Properties in External Metadata not writeable using the Advanced Editor

  Hello, I'm working on a custom dataflow destination component. It makes use of the External Metadata Collection. I also use Custom Properties with the external metadata collection. When I open the destination component using the Advanced Editor, and select an External Metadata Collection and change the Custom Property it always changes back to the original value. Additionally the method SetExternalMetadataColumnProperty never gets called. Here is a little Test Component that surfaces the problem: [DtsPipelineComponent(ComponentType=ComponentType.DestinationAdapter, DisplayName="Test Destination")] public class Class1 : PipelineComponent { public override void ProvideComponentProperties() { base.ProvideCompo ...Show All

• James_Steven Can you generalize Connection/FTP Manager objects ?

  My goal is to develop packages that get all basic information from an external config file. I would like to be able to deploy my packages in different parts of the enterprise without having to define Connection managers/File Managers/ FTP managers ... at each and every site. It should get it from an external file. All I should have to do is to edit my config/resource file to suit the needs of the target site and I should be good to go. Is that possible now Thx, chiraj Cool. I will check it out in a bit. Thanks. You da man. -chiraj Absolutely, yes. That is core functionality of SSIS. Right-click on the control-flow, select "Configurations& ...Show All

• awperli Updating column values on Insert trigger

  Hi all. I am new at programming ms sql. I have a table with a date field : EventDate DATETIME NOT NULL DEFAULT GetUtcDate(); Now I need to make sure that if anyone tries to insert a null(ie : the object-mapping mechanism) I replace it with GetUtcDate(). I think I remember that Interbase had a Before_xxx trigger, and in that one you looked at the value of the fields and modified it accordingly. Could not find such a thing here. How do you do that in SQL Server 2000 All I need to do is look at the value in the column and if it is null replace it with GetUtcDate(); Thanks very much for your help, EnriqueM Someone may correct me on this, but... If your field is set to not ...Show All

• Chris Hu Backup/Restore Stored Procedure

  Hi there I just want to create Stored Procedure which will do following thing i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter) ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...) I mean to say, the file should be act as the full backup of database, in plain text. The same need to be done in the reverse order,this SP will do following things i)Open the file created by first SP, create new database, with same name as backup in SP one ii) create the tables, by reading the Schema script. iii) Insert all the data in corr ...Show All

• Mike Greiner Variable Value: How do I set it from value in flat file at runtime

  I have a number of DTS packages I am trying to convert and am totally new to Integration Services. I have a flat file that I reference to get a date that I use in some SQL statements in the package. It seems like the best way to do this would be to create a variable to hold the date and set it at run time from the flat file. I've been searching the documentation and the forum and can't figure out how to do this. This is so basic that I feel like an idiot putting it on the forum but I need some help. This whole Integration Services thing is a big shift from DTS (which I eventually became pretty good at) Now I'm back to ground zero. Any help would be appreciated. If you can direct me to any basic training resources that would be cool t ...Show All

• dgolds Maxis 3G connection

  I cannot connect to SQL Server 2000(Remote Server) through Maxis 3G connection. I'm using Windows Application to connect to SQL Server 2000. dim strsqlcon as string = "Server=000.000.000.000;databse=dbname;usr=sa;pws=1234" dim sqlcon as sqlconnection = new sqlconnection(strsqlcon) sqlcon.open() "It's Error : Sql not exist or access denied." But I can connect to SQL Server 2000(Remote Server) through Local Area Connection. Have any solution Thank you. LAN connection is not the same as WAN connection. For a WAN user to connect to an internal LAN, it has to go through a firewall - imagine if there isn't a firewall. Every one out in the ...Show All

• Lee Copestake BULK INSERT fails with Msg 511 - Cannot create a row of size x which is greater than the allowable maximum of 8060.

  I am running the following BULK INSERT statement: BULK INSERT WEBLogs.dbo.WebLogEvent FROM 'H:\mydirectory\myfile.txt' WITH (BATCHSIZE = 100000, DATAFILETYPE = 'char', FIELDTERMINATOR = '\t', MAXERRORS = 1000000, TABLOCK) The insert failes with this error: Server: Msg 511, Level 16, State 1, Line 1 Cannot create a row of size 8666 which is greater than the allowable maximum of 8060. The statement has been terminated. There is nothing I can do about the bad data, but I would like for INSERT to simply ignore the bad record and move on. Is there a setting that will enable such behaviour Thank you! No. There is no setting to enable such behavior. This is the physical row size limitation in ...Show All

• K.Kong FTP task - StopOnFailure not working as I expect?

  Hi I'm using an FTP task in a loop to download files from a WAN based FTP server. I've got a script to generate the name of the file I require and place into a variable. Problem is sometimes some of the remote files haven't been published so the FTP component errors and the package fails. I tried setting StopOnFailure to False - all I get is the following errors [FTP Task] Error: File represented by "User::RemotePath" does not exist. [FTP Task] Error: Attempt to read message string for 0x80020918 failed with error 0xc02090f3. Make sure all message related files are registered. What I want to do is ignore the error and perform the next loop iteration Is this a bug, or am I missing something Dave ...Show All

• Sobia User is not associated with a trusted SQL Server connection

  I set up SQL Express and Visual Studio Team System for the first time yesterday. I created a web service (on my localhost IIS) and added an MDF file to my App_Data folder. I added a table with five fields. I'm trying to connect to the database in my web service, but when my web service gets to the connection line it returns the error System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection. When I add in my domain name and password to the connection string I get the error System.Data.SqlClient.SqlException: Login failed for user 'redmond\danmor'. The user is not associated with a trusted SQL Server connection. I've queried these SQL forums, and the advice is u ...Show All

• RAYMOND KNIGHT passing parameter to SQL task variable

  I am trying to exectue SQL task as below by passing a parameter If I try.... @v1 datetime set @v1 = convert(datetime, ,103) it fails with below error " failed with the following error: "Syntax error or access violation". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly. however the below code works well delete from t1 where last_update = convert(datetime, ,103) What could be the problem Cool, Glad it worked out for you. Hi hellomahesh, I've been looking at the problem you are having and have a few questions: Is it an OLEDB or ADO.Net co ...Show All

• Afr0 Assembly name for SSIS built-in UITypeEditors?

  I have a custom PipelineComponent that accepts a string of SQL. I don't have a custom UI, all I need is the Advanced Editor. Currently the SQL property is just the standard line of text that can be entered on the Advanced Editor. I would like to use the popup multi-line editor that the built-in components use for editing SQL. I was hoping it was the System.ComponentModel.Design.MultilineStringEditor but that is definitely not it (and that one is insufficient for entering more than a few lines of SQL). I'm assuming it must be a UITypeEditor that was shipped as part of SQL 2005, but I haven't been able to track down the qualified assembly name for it anywhere. I tried debugging in VS to get down to a IDTSCustomProperty90 that I could look at ...Show All

• Andre's SQL 2005 SP1 install Failure

  I have attempted to install SP1 on to my SQL 2005 Server numerous times and cannot get it to be successful. It failes on database services everytime and the log and event log report that my account cannot change permissions on the Data directory. My system is running Server 2003 R2 Ent x64. I have a Cluster setup with no second node at this time (future additon). I am also using a mount point inside the Data directory for logs. I have monitored the install using FileMon and do not see any file access issues during the installation process. I have also looked at the files and directories within the Data directory and all security ACLs seem fine. I am running the install with an account that has admin permission on the box. The service accou ...Show All

• Neelam Kothari BCP IN column limit

  I am encountering a limit when attempting to import/load/read a file using the BCP functions in SQL Server 2000. The import fails when I have around 200 columns. Is there a similar limit in SQL Server 2005 Stated differently, what is the maximum number of columns I can import with bcp Are there other maximums I should be investigating My incoming record length is about 2K. Hi, I'm having the same problem, but the limit seems to be about 100-120 columns in BCP from SQL Server 2000. Did you ever get a resolution for your issue I'd appreciate if you'd share any findings. Cheers RichardS71 Ok, I've cracked my problem at least. Whilst SQL Server allows ...Show All

• Sputnik_Johan Search Stored Proc

  Does anyone know a way to seach through all databases on a SQL 2000 server looking for a particular stored proc by name What would you suggest as a good source code control option Thanks guys, that was the answer I needed.  The n00b can see better now.  Do you mind explaining what the '~' does in that query   Maybe walk me through the statement so I can understand it better   exec sp_MSforEachDb 'select ''~'' as [Database Name], [name] from ..sysobjects(nolock) where [name] = ''targetObject'' ', '~' I have always been under the impression that sp_MSforEachDB is an undocumented ...Show All