Answer Questions

• Serdalis Current package reference.

  hi Iam developing a custom ssis source component, in that i need to get the reference of the current package where this component is added. I can get get the reference of the current package using this line of code Microsoft.SqlServer.Dts.Runtime. Application app = new Microsoft.SqlServer.Dts.Runtime. Application (); Microsoft.SqlServer.Dts.Runtime. Package pak = app.LoadPackage(@"C\DTS\Package1.dtsx" , null ); Here i need to pass the path of the package. Is there any other way to get it (without the path) Thanks Mani You can iterate variables, but not always. As above, I don't see it as an issue since you can do so at the approptiate times. Yes, if you write some ...Show All

• SPA SubQuery returns more than one row - Update Query

  Hello, I'm trying to update column based upon the results of a subquery. I'm getting the error that my Subquery returns more than one result. I've tried adding the EXISTS or IN keywords and cannot get the syntax right. I can't find any examples of how to write this with an Update query. Here's my query: UPDATE temp_UpdateRemainingHours SET UsedHours = (SELECT dbo.vw_SumEnteredHours_byCHARGE_CD.SumEnteredHours, dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD FROM dbo.vw_SumEnteredHours_byCHARGE_CD INNER JOIN dbo.temp_UpdateRemainingHours ON dbo.vw_SumEnteredHours_byCHARGE_CD.CHARGE_CD = dbo.temp_UpdateRemainingHours.CHARGE_CD) FROM dbo.vw_SumEnteredHours_byCHARGE_CD, temp_Update ...Show All

• JIM.H. SQL Server 2005 | Queries Going Into Suspended Mode

  Hi, We moved to SQL server 2005 (SP1) recently and are observing a strange problem. During our performance tuning runs, some queries would never finish execution. Looking at the traces on the client side we don't get any hint of root cause. We see traces right upto the DB operation API (for instance C#'s ExecuteCommand) and no traces beyond that. The call never returns/errors out despite setting the command timeout to 30 sec. On the server side we see the SPIDs of these queries in suspended state with one of the below mentioned wait types (so far): -resource_semaphore_query_compile -ASYNC_NETWORK_IO -Cxpacket Server config: Winserver 2003 4 dual core Xeon 3.2 Ghz 18 gigs of RAM parallelism level set to 6 Has anyone experienced something si ...Show All

• chance1166 Cannot use anonymous methods inside SQLCLR...

  Hi all, The problem is: when you're trying to call in method MyMethod anonymous method that doesn't use local variables , deployment of the assembly will fail referring that MyMethod tries to store smth. in the static variable. Indeed, looking at the compiled CLR code, you can see that anonymous delegate is cached in the private static delegate and the call looks like: If(ClassName.privateStaticDelegate == null ) ClassName.privateStaticDelegate = new MyDelegate(HiddenMethodName); CallAnonymousMethod(ClassName.privateStaticDelegate); Is there any workaround to fix this problem. P.S. I googled about this problem and found only one article on it: http://www.ayende.com/Blog/default,date,2005-12-26.aspx ...Show All

• Bill Burke sql server trigger

  Dear Sir/madam, I have two tables AccountGroups and AccountGroups_Bal. I want whenever a record is inserted in to the 1st table, a corresponding record should be inserted in the 2nd table. both tables have the "code" column. USE [DealSoft] GO /****** Object: Trigger [dbo].[trgAccountsAdd] Script Date: 03/01/2007 12:38:49 ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE TRIGGER [dbo] . [trgAccountGroupsAdd2] ON [dbo] . [AccountGroups] AFTER INSERT AS INSERT INTO AccountGroups_Bal ( code ) VALUES ( select code from inserted ) the following trigger gives the below mentioned error Msg 156, Level 15, State 1, Procedure trgAccountGr ...Show All

• rwilson06 Need to alter identity property

  I need to alter all our identity columns to be not for replication (currently they do not have this condition). I checked how Microsoft is performing this task by recording a script while doing this manually in SSMS. They simply create another table with identity column not for replication, pour data into new table, drop the old one and sp_rename for new one. This is not the case for us - some our tables have billions of records so we can't afford keeping these tables off-line for so long. Are there any ways to alter it Any wok arounds, except directly updating sys tables which is not recommended Thanks Thanks a lot Dinakar, that's very helpful. Is there similar scripts for check and foreign ke ...Show All

• IBRAHIM ERSOY Cannot enlist with MSX server

  We have a server with the default instance and 2 additional named instances running SQL 2000 SP4. Recently I installed another instance for SQL Server 2005. We also use MSX/TSX approach to manage the servers so I tried to enlist the SQL 2005 instance with the master server using the msdb .. sp_msx_enlist @msx_server_name = N 'SQLMON' , @location = N 'Datacentre. Margin Lending overnight copy' and got this error, Msg 22026, Level 16, State 1, Line 0 The enlist operation failed (reason: SQLServerAgent Error: Unable to connect to MSX 'SQLMON'.) Looking at the text for this procedure it looks like it reads the wrong registry keys. The procedure it hardcoded to read, HKEY_LOCAL_MACHINE\ SOFTWARE\Microsoft\MSSQ ...Show All

• venp use of CASE in script

  I have been working on a job that is supposed to do the following: 1) Imports last name, first name, date of birth, ssn, medical record number, and enterprise id from TAB seperated text file an example of the format would be ENTID<tab>LAST<tab>FIRST<tab>DOB<tab>MEDREC<tab>SSN 2) Looks up the person in the import system and inserts a primary key value that represents the person into another table, along with some id numbers representing the system the file originated from. This is to identify people in one system that have been in the other as well. I have been working on a solution that imports the text file to a new table, then a cursor that reads each record, looks up the person, and then inse ...Show All

• LonelyPixel What is wrong with query?

  I need help on my query please. I get the following error : Incorrect syntax near keyword 'As' Here is my query: Create Table Temp_Total As ( SELECT RIGHT( '0000000000' + REPLACE ( CONVERT ( VARCHAR ( 10 ), Total ), '.' , '' ) , 10 ) FROM [Transaction] ) Thanks are you trying to do a select into SELECT RIGHT('0000000000' + REPLACE(CONVERT(VARCHAR(10),Total),'.', '') , 10) AS Total INTO Temp_Total FROM [Transaction] Denis the SQL Menace http://sqlservercode.blogspot.com/ Hi, As mentioned above, it is better to use SELECT ... INTO NewTableName FROM ... . The problem with your query is that if you want to create a table like this, all columns must have a ...Show All

• Alex Mujar Need to store 10 million record

  hi, I like to store 10 million records in my sqlserver database. what will be the Best way of handling the Database Operations(Insert,Upate,reterive) I am storing data in 26 Table, Please suggest if any other way to get better performance. ...Show All

• Ingenious After an insert, how do I get the primary key of the new row?

  I am using C# and ADO.NET After executing an INSERT, I would like to retrieve the primary key of the last row inserted. I've tried running SELECT @@IDENTITY in a query, but I get an OleDbException with the message: {"Syntax error. in query expression 'SELECT @@IDENTITY'."}. does anyone know what to do Akbar, Select Max(pk_id) seems like a simple way to do this. In fact, I feel stupid for not thinking of it. One followup question to this method: Does this method fail if there are multiple writers writing to this database This fails if someone else writes to the database before you send your second query, correct Hi You can use DataTables and DataAdapter ...Show All

• LSDev_KF AMO. AggregationDesign.EstimatedPerformanceGain

  Hi, why this field is always empty I this a "free" field, that can be set from ISV developer as he want But it is not persistent. (Is this a bug ) What a purpose of the field This is not right. After designing aggregations this field is definitly not set in AMO. public static void DesignAggregation( MeasureGroup pMg, long pOtimizations, long pAggCount, out long oOptimisations, out long oAggCount ) { double lOptimizations = 0 ; double lStorage = 0 ; long lAggCount = 0 ; bool lHasFinished = false ; AggregationDesign lAd; lAd = pMg.AggregationDesigns.Add(cDefaultAggrDesign); lAd.InitializeDesign(); lOptimizations = ...Show All

• SathyBala Cannot shut down VS after using report designer

  Routinely when I use BIDS to work on a report design, I cannot close BIDS because I get a message box that says: Microsoft Visual Studio cannot shut down because a modal dialog is active. Close the active dialog and try again. Of course, there is no visible dialog open. I actually have to terminate the process from task manager. I do not get this problem when using SSIS or SSAS. Only SSRS. Any Ideas Thanks! BobP The problem normally occurs with me when I had used the query designer an has run an query. When continuing the work at some time sql server ask the question whether to keep the result set or not. When VS is not the active window this message is stuck behind the VS screen and you can not stop it. It i ...Show All

• fabien13000 Need help converting a CHAR into a Datetime

  I have a Database which is having a Counterdate time Column stored in the form of Char(24) But i need it to be in form of datetime so that i can use the datetime functions on it..When i use the cast or convert inside the function where i am passing this character it gives me error "Conversion failed when converting datetime from character string." I am done all permutatiions and combinations for this used Set @DE = convert ( datetime, @ts,121) Set @de = cast( @ts as datetime) BUT ALWZ give me same error.... also when i copy the whole of the data table into some other database the error doesnt come.. i converts the character into the datetime.. I DONT understand why the Server is behaving wiered.. Hoping to ...Show All

• srinivas_kv80 Subscriptions

  We are running Reporting Services on an Application server, side by side with a web application. The server exists outside our corporate domain due to the fact that our external vendors need access to the application (not the reports). Our web app uses a third party mail system to funnel the application email thru our corporate exchange server. Since Reporting Services is not connected to our domain, it can not authenticate to send reports via e-mail subscriptions. I am looking for alternatives to make this work from outside the domain. This is partly correct. The schedule is not affected by the encryption key, because it is stored in the msdb database, but the database that actually holds the information ...Show All