Answer Questions

• cgraus how to add rows with Script Component?

  Why am I getting this design-time error from my script: 'Microsoft.SqlServer.Dts.Pipeline.ScriptBuffer.Protected Sub AddRow()' is not accessible in this context because it is 'Protected' Here's my script: ' Microsoft SQL Server Integration Services user script component ' This is your new script component in Microsoft Visual Basic .NET ' ScriptMain is the entrypoint class for script components Imports System Imports System.Data Imports System.Math Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper Imports Microsoft.SqlServer.Dts.Runtime.Wrapper Public Class ScriptMain Inherits UserComponent Public Overrides Sub Input0_ProcessInputRow( ByVal Row As Input0Buffer) Dim outp ...Show All

• nghianghesi Multiple counts in table

  I've got a table containing (among some other columns) 3 different type columns. TABLE xxxx as int type 1 as string type 2 as string type 3 as string date as Date First i'd like to select the number of counts each of these appear in my table. I realized that it can be done by union 3 select statements each grouped by a type. select type1, count(type1) from table group by type1 union select type2, count(type2) from table group by type2... Now this is time consuming and not what I would like to do Secondly i'll try to do something like SELECT type1 , SUM(CASE WHEN MONTH(date) = 1 THEN 1 END) AS 'Januar' ,SUM(CASE WHEN MONTH(date) = 2 THEN 1 END) AS 'Februar' ,SUM(CASE WHEN MONTH(date) = 3 THEN 1 END) AS 'Mars' ,SUM( ...Show All

• Jim Perry Filtering by date on a chart

  Hi all, I have a datasource with three columns. NAME / DATE / COST The dates run from January 2005 to March 2007. They appear in the following format in the query window: 03/01/2005 00:00:00 All I want to do is put the dataset in to a graph and filter the date so it only shows any cost from January 2006 onwards. What ever date I type in the filter I get the following error "Cannot compare date of types System.Datetime and System.String" Anyone know what I am doing wrong or how to solve the problem Cheers, Rob. It's complaining that you're comparing a date to a string, so conver the string to date. For your expression, try =CDate("1/1/2006"). ...Show All

• Stéphane Beauchemin record counts

  Hi, I have 10 databases created. each data base has arround 100 tables . i need to keep track of the number of records in each table of 10 databases and the last modification date on that table. The solution should be programatically by running a T-sql program or any stored procedures or any other but mechinical. the output should specify the following: Table #number_of_recs Last_date database_name.Table_name ###,###,### DD/MM/YY I will appreciate any assistance in this regard. thanks, create a tracking table and then create an insert,update and delete trigger on each table that updates a tracking tabke ...Show All

• Jim Cordwell calling a procedure from another procedure

  Maybe this isn't possible, but I'm trying to figure out how to call a procedure in a select statement where I can pass a new id each record found. Here is the procedure code that returns the desired results for any given ID: CREATE PROCEDURE proc_amEnrollmentTrackingDetailGet_cst @syStudentID INTEGER AS DECLARE @SATACT DATETIME, @HSTranscript DATETIME, @AcceptLetter DATETIME, @FAFSA DATETIME, @FinAidApp DATETIME --Get the last DateDue for each document type SELECT @SATACT = CASE WHEN cmDocTypeID IN (81,110) THEN DateDue END, @HSTranscript = CASE WHEN cmDocTypeID IN (83) THEN DateDue END, @AcceptLetter = CASE WHEN cmDocTypeID IN (126) THEN DateDue END, @FAFSA = CASE WHEN cmDocTypeID IN (150) THEN DateDue END, @FinAidApp = CASE ...Show All

• JLLO IIS and SQL Server 2005 Express Edition

  hi Currently have SQL Server 2005 Express Edition installed. Wanted to install reporting services, but wouldn't let me re no IIS as I only have Windows XP Home Edition. My question is therefore: Is there any way of installing IIS to allow me to install Reporting Services for SQL Server 2005 Express Edition Many thanks Tim Leicester, UK There is no need to start everything from scratch if you have not installed IIS prior to installing SQL Server. There is a need to configure ASP.NET on IIS once you have installed it though because it doesn't happen automatically unless you install the .NET Framework 2.0 after IIS, which you didn't do. Instructions on how to register ASP.NET on IIS can be ...Show All

• ducky242 MSDASQL error requires SQL Service restart

  I am having trouble with a linked server using MSDASQL.  I'm connecting to a PostgreSQL database and pulling over data.  This process has been working fine. In trying to pull data from a different client database (same schema), I received an error that the MSDASQL couldn't read the column names. The actual problem I want help on is that after this happens, I am no longer able to make valid connections to any of my Linked Servers using MSDASQL.  The only way I can get my other linked servers to work again is to restart the SQL Service.  Usually this is impossible for me to do because of the number of active users. Two questions: 1) Is there another way to restart a more targeted service or sub-set to reset MS ...Show All

• Ori' 'Could not allocate space for object 'dbo.SORT temporary run storage'

  Hello all, I’m trying to transfer/transform some data from one table to another, both on the same database. The source table holds 92M records. To do so I'm using the next statement: INSERT INTO [dbo].[Messages1] ([Time] ,[ID] ,[ResponseTo] ,[MessageMD5] ,[source] ,[dest] ,[arln_err] ,[ErrorDescription] ,[ErrorNumber] ,[ErrorSource] ,[ErrorType] ,[HttpCall] ,[HttpStatus] ,[QuoteAmount] ,[ReservationPickupLocation] ,[RatePickupLocationDescription] ,[RateReqCarType] ,[RateReqPickupLocation] ,[RejectMessage] ,[ReservationAmount] ,[ReservationCarType] ,[ReservationCarTypeDescription] ,[RatePickupLocation] ,[resp1] ,[ResultNum] ,[strRejectMessage] ,[strResultNum]) SELECT [Time] ,[ID] ,[ResponseTo] ,[MessageMD ...Show All

• Brian Foley Encryption of Column

  Hi, Is there any possible way, that while I transferring a table from another table, I can specifically encrypt a column Is there any step on how can I do this Need Help Badly Thanks Hi is CryptoTask a third party Where Can I Find it Is there any possible way I mean more simplier way Thanks There is no stock encrypt task or transform in this release. However, that needn't stop you. Kirk Haselden's SSIS book has a CryptoTask which can be adapted from a custom task to a custom transform, or more easily, a script transform. The options for column encryption right now, are to write code for in-pipeline transforms, or to have an auto-encrypting destination. ...Show All

• pedalo decryptbykey multiple session issue

  Hi I'm having some issues using the decryptbykey method via multiple connections. When I run the below test script simultaneously on two machines the sum function is always less then the known amount (ie 14945490 and 36382777). Does anyone know of any locking method or alternative way to sum an encrypted column Thanks in advance Waz open symmetric key HR01 decryption by password = 'yes' DECLARE @Bonus decimal DECLARE @Salary decimal DECLARE @Errors int DECLARE @Success int DECLARE @LoopCount int SET @Errors = 0 SET @Success = 0 SET @LoopCount = 0 WHILE ( @LoopCount < 40 ) BEGIN SELECT @Bonus = SUM ( convert ( float , convert ( varchar ( 80 ), decryptby ...Show All

• pmak CTP version and release version

  we have a backup of a database from a release version with version number 9.00.1399 and we wanted to restore it to a server with version number (9.00.1116) CTP release. we were issued a version incopatibility message. is it possible to backup from a 9.00.1399 release with a backup file compatible for a CTP release, is there an available option during backup thanks! thanks kevin CTP versions are no longer supported. You should be using the released version of the product. Ideally, you should be at SP1, as we fixed quite a few bugs there. Backup is a physical operation. If the structure of the database pages has changed in any way, we have no way to do the conversion during restore. For moving up, we have the logic in the ...Show All

• ink innovations Calling user-defined functions in OLE DB Command transformation

  Hi We have a user-defined function that can be called directly via SQL (in SQL Server Management Studio) without error. We would like to use this function to populate a column, whist data is being processed within Integration Services. Using an OLE DB Command transformation to achieve this would seem the most appropriate. The following was inserted for the SQLCommand property: EXEC = dbo.GetOrderlineStatus(@dt_required = , @dt_invoice = , @dt_despatch = , @ch_status = , @si_suffix = , @re_quantity = , @vc_invoice_id = , @vc_order_id = ) However, when the Refresh button is pressed we are presented with the error below: Error at Load Orderline [OLE DB Command [15171]]: An OLE DB error has occurred. Error code: 0x8004E14. An OL ...Show All

• perstam SP2 Report Builder and default save location

  Prior to SP2, when you clicked the save button it would take you to the root of Report Server. With SP2 (in old-style Report Manager mode, not Sharepoint integrated mode) it takes you to the /Models directory by default when you click save. Is there a way around this If I launch Report Builder via the following URL, it does fix the problem. But it doesn't let me pick table/matrix/chart. I would like to override the default save location AND be able to let the user pick table/matrix/chart: http://localhost/reportserver/reportbuilder/reportbuilder.application model=/Models/MyModel&savelocation= Suggestions Sorry if the information I gave you does not work. I don't have a better answer for you, but I can give you some mor ...Show All

• gdylp sp_MScheck_uid_owns_anything equalent in SQL Server 2005

  Hi Is there any undocumented Sp's in 2005 which we can use. Thank you every body for the reply. Actually that proc was very useful one with no extra effort we can get all UID owners. now that we are introduced to Schemas and having # of users in a database they own their objects. and if I have to change the ownership of the objects we are foced to use the alter schema or alter authorization but not sp_changedbowner as its been deperaciated feature. was just wondering if there is any such sp "sp_MScheck_uid_owns_anything" which was undocumented which could have been documented by now or change of name... any info. would be much appreciated. Thanks once again ~Venkat Raman Gosala Why do you ne ...Show All

• Rahul Saxena How should I name my history tables ?

  Hi, I want to backup an important table every week in creating some history tables. I would like to create a Dts job or script to create every week a table with the day and month in its name. ( like : [important_table_09-07] , [important_table_09-14],... ) Any idea Thanks. Thanks, but how can I create a stored procedure which create a table with a name depending of the current month/day (or like TableName_YYYYMMDD). I would like to store the creation table date in its name. Is this possible Thx Hi, you will have to use dynamic SQL for that: SET @Command = 'CREATE TABLE ' + @Tablename + '_' + CONVERT(VARCHAR(8),GETDATE(),112) + (...Columnshere) HTH, Jens K. Suessmeyer. --- http://www ...Show All