Answer Questions

• paruchuri How to read sql tasks and so on from a package programmatically??

  Hi everyone, Once I've accesed to package by means of LoadFromSqlServer method how to read its Sql Tasks, for example I'm trying with the Executables property but unsuccessfully results: pkg.Executables.Item(0) Thanks in advance, Below is a simple example which loads a package from the file system, finds the Execute Sql Task, changes one if its properties, and then gives options to persist the changes or execute the changed packaged. More information about the SSIS object model and can be found at: http://msdn2.microsoft.com/en-us/library/ms137709.aspx // using statements using Microsoft.SqlServer.Dts.Runtime; using Microsoft.SqlServer.Dts.Tasks.ExecuteSQLTask; // create in instance of Microso ...Show All

• Xiao Feng CLR Stored Procedure in Reporting Services

  Hi I have created CLR Stored Procedure and my CLR stored procedures are not appearing in the Stored Procedures drop-down list located on the Data tab within the VS2005 Business Intelligence Development Studio. After Creating the dll I have registered the dll like that CREATE ASSEMBLY MY_SP_NAME from 'C:\MY_DLL_PATH\MY_DLL_NAME.dll' WITH PERMISSION_SET = SAFE After registering I have deployed the dll : CREATE PROCEDURE [dbo].[MY_SP_NAME] @dbname [nvarchar](4000), @varTable [nvarchar](4000), .............. .............. .............. AS EXTERNAL NAME [MY_DLL_NAME].[MY_CLASS_NAME].[MY_SP_NAME] GO EXEC sys.sp_addextendedproperty @name=N'AutoDeployed', @value=N'yes' , @level0type=N'SCHEMA',@level0name=N'dbo', ...Show All

• the emrah Sql Script

  hello I need to script my Db, tables and storedprocedures. so i can create another same DB Dim con As New SqlConnection(DBClass.Config.DBString) Dim cmdName As String = File.OpenText("pro.sql").ReadToEnd() Dim cmd As New SqlCommand(cmdName, con) cmd.CommandType = CommandType.Text con.Open() Try cmd.ExecuteNonQuery() MessageBox.Show("DB Created") Catch ex As Exception MessageBox.Show(ex.Message) End Try con.Close() Any suggestions So your fine now and everything works Are there any TableAdapters in vb.net 2003 Hi man sorry for bothering Your Code: Dim sr As StreamReader = New StreamReader(&qu ...Show All

• WRBehning Inserting counts into a table

  Hi: I would like to count repeating field values in a table, and to insert the counts into the same table. I have managed by having a temp_table, into which I insert the values: insert into temptable values (select count(*) as Count, fieldname from table group by fieldname) I then do a join on table.fieldname=temptable.fieldname, and update table.count with temptable.count. Is very cumbersome, and does not update counts when table changes. Is there a way to put in a calculated member, or to put the vaues of auto stats into the count field TIA Kar hi karfast, If that's the case use an update trigger to update the rowcount then hth Hi bonskijr, Thanx for the response. I can u ...Show All

• Thomas Olsson How do i find all subscriptions

  Hi There I need to get rid of all subscriptions (including cleaning up all associated sql jobs) for RS 2000 and 2005. We have hundreds of reports , going through them 1 by 1 is not an option. When i look at the system tables, i join Subscription to Catalog, but i find reports with subscriptions in the system tables but when i go to Report Manger there is no subscription for the report. In a nutshell , how can i see all valid subscription for all reports in RS, how can i delete all of them and make sur eall sql jobs associated etc are deleted. Thanx Hi Dietz, try it with the object-model of RS: rs = new rs2005. ReportingService2005 (); rs.Credentials = System.Net. Cred ...Show All

• Zaid Papa Encryption Question - Urgent!

  Hi, I encrypt a column in a table.  I am able to decrypt/encrypt the same successfully.  However, when I copy the encrypted data to a new database and try to decrypt using the same certificate, it doesn't work.  I have created the same "Master Key" and certificates on the new DB .... So, is it possible to decrypt the encrypted data that is transferred from one DB to another   If not, are there any alternatives I have tried opening the master key on the new DB using the following: OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd'; ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY; CLOSE MASTER KEY; Thanks.! How do you encrypt/decrypt and what error message ...Show All

• Leon Tayson Job Duplicate Notifications

  Hi All, I recently installed SQL Server 2005 Standard Edition SP1. Wanting everything to be perfect before upgrading our Production site, I created jobs and maintenance plans with notifications when the job/plan completes. My problem I have is that I receive duplicate notifications for each execution of a job/plan (even those manually started) - what/where am I going wrong Any help would be appreciated ...Show All

• Will Merydith SQL Query

  Hi, i have an SQL query as follows: -- SELECT GRAPH_INCIDENTS_LIST.INCIDENT_DATE AS [Date], Count(GRAPH_INCIDENTS_LIST.ID) AS [Count] FROM GRAPH_INCIDENTS_LIST WHERE 1 = 1 GROUP BY GRAPH_INCIDENTS_LIST.INCIDENT_DATE UNION SELECT [EventDate] AS [Date], t_Events.Count FROM t_Events ORDER BY [Date]; -- which produces results like: Date | Count 04/01/2006 | 1 14/01/2006 | 0 14/01/2006 | 14 20/01/2006 | 5 21/01/2006 | 6 22/01/2006 | 12 ... notice that is has two enteries for date 14/01/2006.. one from the event table and one from the incidents query.... how can i narrow this union to only have one entry for 14/01/2006: Date | Count 04/01/2006 | 1 14/01/2006 | 14 20/01/2006 | 5 21/01/2006 | 6 22/01/2006 | ...Show All

• sGurpreet How to determine mirroring role?

  Once I have identitied that mirroring is enabled on database via SMO Database.IsMirroringEnabled, I need to determine the mirroring role. I noticed an enumerated type - MirroringRole, but no SMO method/property to access it. I have tried to query the sys.database_mirroring table directly, but this fails with an exception on the mirror database - as it is being mirrorred :-( What is the recommended way to determine the mirroring role Thanks, Nick Not sure what you mean, I have tried querying the database ... String query = String .Format( "SELECT mirroring_role FROM sys.database_mirroring WHERE mirroring_guid = '{0}'" , db.MirroringID.ToString()); DataSet dsResultSet = db.Ex ...Show All

• Hardrock302 XQuery Help

  I have this xml on a table: <BookData xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema"> <DataVersion> <Name>ABC</Name> <Data xsi:type="xsd:string">DEF</Data> </DataVersion> </BookData>' I want to modified the xml with this xquery: UPDATE LibraryOverride SET XMLConfig . modify ( 'declare namespace ns="http://www.w3.org/2001/XMLSchema-instance"; 'replace value of (//ns:BookData/DataVersion/Data)[1] with "TEST"' ) Where PropertyOverrideID = 1 I am getting this error: XQuery [LibraryOverride.XMLConfig.modify()]: The target of 'replace value of' must be a non-metadata attribute or an element w ...Show All

• Jubber Performance problem: 2000 vs. 2005

  I copied a 2000 database onto 2005 on another box. Running the exact same SELECT query, with 7 tables joined, is running in less than 2 minutes on 2000 and almost 3 hours on 2005. The 2005 box is "bigger and better". I've compared the sp_configure output. There are differences in max worker threads, cost threshold for parallelism, and priority boost. I don't think any one of these is significant. I checked the server properties and the database properties and, again, I did not see what I would consider any significant differences. The 2005 instance has 4GB memory, as opposed to 2GB, but not using that much. In fact, the Total Server Memory (KB) for the instance indicates around 1,230,000 for some reason. We need to reso ...Show All

• Hans Erik Lange Update XML-source attribute with database field value?

  Hi! A question just struck me that I cannot test right now (don’t have vs2005 on this computer)...Is it possible to add a database-field value to an Xml-Source object attribute For instance <Object id=1 name="void" description="" /> is my souce xml...  in this case I want to replace the description attribute value (string.Empty)  with a value from my db...  can this be done Regards Erik B.   You'll need to do it in the Control flow using the Xml Task. Or you could do it in a Script Component using the System.Xml namespace... ...Show All

• Jimmy Q Programmatically set a Flat File Connection Manager

  Hello, I need to know how I can programmatically set a Flat File Connection Manager's Column Delimiter value. The Data Warehouse project I am working on, receives daily information feeds that could contain one of two delimiters. Which is just dumb...anyways, as it is now we have two seperate Data Flow Tasks which handle these two delimiters. Currently we have a script taks that "sneak previews" each incoming flat file to determine which delimiter it has, and direct our flow to the correct Data Flow Task to handle it. I do not want to have to maintain 2 DFTs. How can I get around this problem Even if there is a way to do this by passing variables/setting expressions in the Flat File Connection manager, I would do ...Show All

• Amy__ Drillthrough returning sets of duplicate rows

  Drillthrough appears to be showing multiple rows for each single record that should be displayed. For example, in my fact table, there is a single record for each trip. The following MDX query returns a single cell with a trip count of three. SELECT NON EMPTY { [CP Date].[Date by Month].[Year].&[2002] } ON COLUMNS , NON EMPTY { [Commodity].[Commodity by Type and Group].[Commodity Type].&[Other] } ON ROWS FROM [Barge Trip] WHERE ( [Measures].[Trip Count] ) 2002 Other 3 If I drill through on that cell, exactly three records should be returned. However, in actuality, 12 rows are returned - three sets each having four identical rows. To drill through, I am simply adding the D ...Show All

• Michael Miller Exporting Data from Database to a csv file

  Hi, How do I export data from my database table into a Comma separated value file format. I am using SQL Server 2005 with vb.net Thanks you can try bcp.exe. Try bcp.exe or import/export wizard, as mentioned above. It would use the SQL Server Import / Export wizard , it is more comfortable than the BCP command, although in some cases like automatic commandline export thats the only choice. Right click the database and choose the Export... command. You will be guided though a wizard for exporting. HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- Hi Greg, Thank you for the re ...Show All