Answer Questions

• Jimmy V Question on OUTPUT feature of DML

  I need to audit inserts/updates/deletes on active tables to audit tables on a set of tables that have foreign key constaints with the update cascade and delete cascade defined. I can explicitly code the delete/update on the parent table to perform an OUTPUT to an audit table, but how do I OUTPUT the cascaded delete/update that happens on the child table because of the FK constraint with delete cascade defined without having to resort to triggers. Thanks, -chiraj Thanks for the response. Using DML triggers is a no-brainer. I have used them all my life. I was wondering if it could be accomplished with the OUTPUT clause. I believe it is a limitation of the OUTPUT clause unless someone can show ...Show All

• vgrigor BULK INSERT "AccessDenied" on remote text file.

  OK, Ive read many posts on this problem but have seen no resolution. Here's the deal. When on the actual SQL box (There's 2 in the cluster) the bulk insert works fine. No errors and the event log on the machine that is hosting the text file shows that the account that SQL runs on has accessed the file. This account is a DOmain account and is in the Local Administrator of the SQL server and the remot box hosting the text file. Thats great if you want your developers testing and accessing your SQL box as Administrators. We don't work that way. Our developers connect via SQL Management Studio and test ther stuff that way. That's where the problem rears it's ugly head. When anyone runs a Bulk Insert command that accesses a text file t ...Show All

• vbzuki Relative Dates

  When attempting to use the 'relative dates' functionality of the Report Builder component, I'm getting an error that states "The report may not be valid or the server could not process the data" then in the detail section it says that the field I'm using (an obvious date field) is being interpreted as an integer. If I use an absolute date, it works just fine, just errors out when I use a relative date. My questions...has anyone else come accross this error Is it a known issue Is there a way around it that anyone knows of I suspect it's a glitch in this new product but really need to know for sure. Any references to MS topics on this would be hugely appreciated as well. Thanks...Ooogy ...Show All

• XNA-Mordt Auto export to Excel

  Is there a way to export directly to an excel .csv file when the report is run without having to preview the report first and then click the export icon I want to be able to schedule a report to run and have it go directly to an excel .csv file. Alright I found it. It's all done on the server side. I was on the client side. I cannot get to the property tab. It's greyed out. Only the location shows. You can use the file delivery subscription. This allows you to automaticly create a csv file at a UNC path. See http://msdn2.microsoft.com/en-us/library/ms157386.aspx for more information. You can specify the export for ...Show All

• Mohan1 Comparing text in WHERE

  Hi everyone, I am compare to text fields like this: CREATE PROCEDURE dbo.PCAttByVal ( @Value text ) AS BEGIN SET NOCOUNT ON SELECT ID FROM PCAtt WHERE Value= @Value END But the environment (I use VS2005) says that the data type is incompatible with the equal operator. Then I tried: CREATE PROCEDURE dbo.PCAttByVal ( @Value text ) AS BEGIN SET NOCOUNT ON SELECT ID FROM PCAtt WHERE Value IN '(' +@Value+ ')' END But nada (nothing). Any ideas I can't change the data type since this field will hold values of different sizes. Thank you for your input in advance! hi, text/ntext datatype does not support this kind of operation/comparison... you can find the "av ...Show All

• Chris Langsenkamp Hostname as filter at profiler 2005

  How is possible to set hostname for sql profiler filtering It is possible with sql profiler 2000. thanks, Bojan This is a common problem. I'll write a blog post about it. In SQL Server 2005, Profiler hides columns by default. Columns such as Hostname and DatabaseID are not shown. You need to do the following to be able to filter on these columns: 1) Open SQL Server Profiler 2) Start a New Trace... 3) Connect to your server 4) Go to the "Events Selection" tab 5) Check the "Show all columns" box <-- this is important! 6) Click "Column filters" Now you'll see Hostname as a property on which you can filter. Paul A. Mestemaker II Program Manager Microsoft SQL S ...Show All

• Rassol Alternative to Merge Replication?

  Is there a simpler alternative to merge replication I am concerned about the splash of GUID across the DB, performance issues, and the complexity of the deployment. Peer 2 Peer does not currently handle conflicts (it will in the next version of sql server), so you can count that out for now. Merge replication does exactly what you need, there are many many references to customers using Merge replication with hundreds, if not thousands, of subscribers. My only suggestion to you is to try it out in a test environment. Yes it does require guid columns on every table as that is the mechanism used to track changes. If you can be patient, you can also wait for OCS to come out early next year with the next re ...Show All

• xluna MSSQL Server 2000 error Setup fail to configure server

  I have searched and could not find an answer for this one I read log and it said Starting Service ... SQL_Latin1_General_CP1_CI_AS -m -Q -T4022 -T3659 Connecting to Server ... driver={sql server};server=KENSHIN;UID=sa;PWD=;database=master [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified driver={sql server};server=KENSHIN;UID=sa;PWD=;database=master [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified driver={sql server};server=KENSHIN;UID=sa;PWD=;database=master [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified SQL Server configuration failed. how do I fix this i fixed it already we ...Show All

• hutchbrew Package fails when I schedule it.

  Two scenarios, 1 worked, 1 didn't: First package I execute SQL (select top 10 * from MyTable) in my SQLServer2005 database. I scheduled it and it ran sucessfully. I changed the execute SQL to run against an external Oracle database. (Select * from SomeOracleTable where rownum <= 10). I preview to make sure the query works and it does return a result set. I execute the package in the Visual Studios IDE and it works. I save with encryptAllWithPassword to the SQL server. I go into the scheduler and select the package from SQL Server (as opposed to the file system). I set the step to execute the package off the server. I enter the security key by clicking on the configuration tab. I assume it saves the password to the package. I cl ...Show All

• pmak SA account (DBA System account) Granting Priveleges but SQL Server 2000 not applying them

  I have been running a script in SQL Server 2000 as sa also as a Active Directory user who has administrator rights (I tested both approaches SQL Server then Windows Authentication) in Query Analyser which grants execute rights to the stored procedures within the database instance and Query Analyser does not give any errors when I run the script. I have made sure that each transaction has a go after it. I then return to Enterprise Manager, check the rights (I apply them to roles so that when we create another SQL Server user we just grant him/her rights to the role) and discover that the role has not been granted the rights. I seems to be occurring only with 2 of the procedures. Is there a known bug that might be causing this yours since ...Show All

• Tryin2Bgood SSIS Checkpoints

  I have a package that uses ssis checkpoints. It works well. However, when I try to setup transactions for some task, the chekpoints aren't used. I read BOL and It states: "If a package is configured to use checkpoints, Integration Services captures the restart point in the checkpoint file. The type of container that fails and the implementation of features such as transactions affect the restart point that is recorded in the checkpoint file ." But, how checkpoints are affected by transactions what relation exists between this two components Hernan93 wrote: Ok, thank you. But when you have a list of task, one after another, and each task has transaction required and the package i ...Show All

• Jonas.S Error when file is saved to PDF

  I am calling a procedure that will save a file to PDF. It runs successfully. When I go to look at the PDF, I get a message, "Acrobat reader could not open 'Doc.pdf' because it is either not a support file or because the file has been damaged." This is how I'm calling the procedure: SaveFile(Dts.Variables( "varSSRS_URL" ).Value.ToString() + " /TestReports/MyReport&rs:Command=Render&rs:Format=PDF" , "C:\" + "MyReport.pdf" ) I had issues with the variables because I had a typo. Now it just won't create the PDF. I'm with Rafael. I cannot find a SaveFile function when searching in an SSIS script componen ...Show All

• Beginer user SQL Server Assertion: File: <lckmgr.cpp>, line=10846 Failed Assertion

  Now I moved Sql Server 2005 sp1 into production, but I've got so many the following "SQL Server Assertion Failed" exception. Who can help me Thanks in advance! =========== Exception INFO from SQL LOG ===================== ===================================================================== BugCheck Dump ===================================================================== This file is generated by Microsoft SQL Server version 9.00.2047.00 upon detection of fatal unexpected error. Please return ...Show All

• Pascal Mignot Reusability & SSIS - issues they do not write in books about

  I have a simple requirement: Each package needs to have Error Handling - which needs to Execute a SQL statement. It's the same Stored Proc - where each package passes in its ID. Ok - I get that part about creating a custom task and so on for reusabilty. But - lets say that after deploying this task in 20 packages - I need to change the name of the Stored Proc. What is going to happen in that case Correct me if I'm wrong - but after deploying the new version of the custom task - do I need to go to each package and update the reference to new version TheViewMaster wrote: Not quite - configuration cannot handle when the processing logic changes. Lets say for instance in addition to ID input of the Stored Proc - the Custom task ...Show All

• drEKO DataAdapter.Update Method Question.

  Hi, I am trying to use DataAdapter.Update to save a file stream into SQl Express. I have a dialog box that lets user select the file: openFileDialog1.ShowDialog(); I want to put openFileDialog1.OpenFile(); Into this .documentTableAdapter.Update( this .docControllerAlphaDBDataSet.Document.DocumentColumn); I am thinking that it might just be some syntax issue, but I looked online, and didn't find much answers. Thanks, Ke Take a look on the links I posted here: http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=622943&SiteID=1 HTH, Jens K. Suessmeyer. --- http://www.sqlserver2005.de --- ...Show All