Answer Questions

• Mark Macumber Query Results to Text

  With Query Analyzer I would run queries that returned text results that I would output to a text file. When I run the same queries using SQL Server 2005, the text output files are twice as large and the are non-printable characters visible between each printable character. It turns out that it's inserting a hex 00 between each charcter. How can I turn this off I need straght ASCII text output files. Thanks in advance,, rdomo Yeah, I know what you mean. Unicode is a bit of a pain when (like me) you never need it. But I understand why it is default too. Thanks Louis, that corrected the problem exactly. Kind of a pain to go though these additional steps/mouse clicks to get the job done. I thought ...Show All

• Bluehunter Web Tests of reports working for anyone?

  Has anyone been successfull in creating a Web Test with Team System to test a Reporting Services report I can record the test just fine but I can't run the test without errors being reported. I suspect that the session state has to be parameterized but I have no clue how to do this. Why is this so hard Why aren't there samples for this kind of thing I've been all over the SSRS and Team Test forums and the MSDN site and can't find anything about Reporting Services reports being tested with Team System Web Tests. I believe the problem has to do with our using the custom security extension. Web Tests for reports in the sample AdventureWorks database work fine for me and these do not use custom security. Has anyone who uses custo ...Show All

• Bagles1 Easiest and performance effective way to store blob into varchar column

  Hi, My package dumps the errors into a table. The problem is, it couldnt dump Error Output column to a varchar field. I have added an script component in between to transform to string but no success. I tried ErrorOutput.GetBlobData(0, ErrorOutput.Length) but when I query the database, it says "System.Byte[]' I will appreciate the responses to this post. Thankyou, Fahad This post helped me, Thanks Phil Actually the Source was Unicode and I was using ASCII string in Script Component. Okay, so you redirect error rows out of a Flat File Source. The columns that get output have the same data type as defined in the flat file source. I don't understand where the talk of BLOB and BYTE comes in. Fahad349 wrote: I d ...Show All

• Timmy0614 Decipher waitresource

  I am trying to analyze a dead lock trace. I see this in the waitresource waitresource=KEY: 9:72057594065256448 (ef008dce9cbe) How can I find the exact key thats causing the problem Thanks! Look at the Trace Flag 1222 Example in SQL Server 2005 Books Online topic: Detecting and Ending Deadlocks http://msdn2.microsoft.com/en-us/library/ms178104.aspx For example: waitresource=KEY: 6:72057594057457664 dbid=6 objectname=AdventureWorks.dbo.T2 associatedObjectId=72057594057457664 keylock hobtid=72057594057457664 ...Show All

• GlennZarb database recovery if no backup available

  Hi,    If we delete a Database accidentally (no backup exists), Is it possible to recover. How   Thanks ven-- Hi Venp, As Satya said it very crucial to take backup daily basis other wise you will lose your important data, but some third party tolls like Stellar can recovery the files , how ever i only tried with demo version but not for the SQL Database Files, this is FYI only. Hemantgiri S. Goswami If you have dropped the database and with no backup it is not possible to recover, thats why BACKUP is an important arm on the basis availability. You can try some third-party file recovery tools to attempt to un-delete the files, and then attach them to the instance. Y ...Show All

• like_antani rows into columns

  I am converting rows into columns : Schedule_S1 contains more than 1 rows with different FROM_DATE and TO_DATE The query should merge 2 rows into 1 row with 2 different FROM_DATE AND TO_DATE The following query works fine when there are more than 1 records. When there is only 1 record, it should still return the record (with FROM_DATE_2 and TO_DATE_2 as nulls). Thanks for any help. SELECT ml1.P_ID, ml1.S_ID, ml1.CURRENT_DAY, ml1.FROM_DATE AS "FROM_DATE_1", ml1.TO_DATE AS "TO_DATE_1", ml2.FROM_DATE AS "FROM_DATE_2", ml2.TO_DATE AS "TO_DATE_2" FROM Schedule_S1 ml1 JOIN Schedule_S1 ml2 ON ml2.FROM_DATE > ml1.FROM_DATE WHERE ml1.P_ID = 59014 AND ml1.S_ID = 25691 AND ml1.CURRENT_DAY = ...Show All

• birdman82 No Debugging and logging when a single script task is executed

  Hi, I cannot execute a script task in the VBA code window. I cannot debug or log if I run a single script task from the right click Execute Task . Every time I have to run the entire package in order to be able to debug. What am I missing appreciate a help. Gulden try disabling all the other tasks and/or containers except the script task. then, execute the package. Thank You.. I guess it is the only way.. Gulden wrote: Thank You.. I guess it is the only way.. If memory serves correctly, yes, it IS the only way. We hope this will change in the future. It certainly SHOULD be changed! -Jamie ...Show All

• mzander Getting results with stored procedure from single textbox searching table with multi columns

  I admit I am a novice to queries and stored procedures but understand stored procedures are more secure, so I am choosing to challenge myself with a stored procedure and have not been successful at all. What I am looking for is a stored procedure which would query the single table named Hospital, which would search from a single textbox the end user would enter the text they are searching for on a Windows VB application but is dependant on a few end user selected items. 1. The end user can select a radio button either "Starts with" or "Contains" 2. The end user can select from 6 checkboxes (Hospitals, Address, City, State, Zip, Type) to limit or expand their search from the columns in the table. The table has ...Show All

• Bittarman Data Flow doesn't run

  I have a simple data flow inside of a ForEach container. The ForEach container uses an ADO recordset. Suddenly it seem it isn't running. The first two SQL tasks go green, and then the for each container itself turns green, but nothing happens to the data flow task inside of the loop, but I get the message saying it is done. I have a simple Sybase ODBC source, which when I preview the data in the source object itself it previews fine. When I try to preview the data in the destination it is blank. I have linked up the source and destination objects, and I haven't disabled anything. The mappings in the source and destination look ok--I"m moving the data here as is, nothing fancy. Not much to go on I know, but how do I troubleshoot ...Show All

• NikiB How to select top N number per date group?

  I have 1 table named BookMe, with fields $Date, $Div, $Name I want to select the top N number of Div's (ie highest Div value) per distinct Date. i know how to get the distinct date like this: SELECT DISTINCT $Date FROM BookMe ORDER BY $Date How do I combine a TOP keyword to get the TOP n rows per distinct date Thx! You can also write the query using standard SQL like:   use Northwind go -- Get 2 latest orders for each customer: select o1.* from Orders as o1 join (select o2.CustomerID, max(o2.OrderDate) as od1 from Orders as o2 group by o2.CustomerID) as o3 on o3.CustomerID = o1.CustomerID and o1.OrderDate in (o3.od1   , (select max(o4.OrderDate) &nbs ...Show All

• milkshake NoLock vs ReadPast

  I have been experiencing deadlock errors with two stored procs that I am using. SP1 is a read query that gets all unprocessed orders (status = 0) SP2 is an insert query that inserts new orders (Status = 0) uses a transaction. I have a multithreaded application and once in a while the read query (SP1) will try to read a new row that has just been inserted but not committed yet hence the deadlock arises. If i use a hint "With(NoLocks)" this will be a dirty read and still read the uncommitted insert from SP2 - is this correct Where as if I use hint "With(ReadPast)" this will now only read committed rows and hence the deadlock should not arise - it will not read any uncommitted rows - Correct So I think th ...Show All

• Farhan H Soomro How to deal with the problem? "Errors in the OLAP storage engine: "

  Who can tell me how to result the problem Errors in the OLAP storage engine: The attribute key cannot be found: Table: FactSalesSummary, Column: ProductKey, Value: 212. Errors in the OLAP storage engine: The attribute key was converted to an unknown member because the attribute key was not found. Attribute Product of Dimension: Product from Database: Teradata Adventure Works Test, Cube: Teradata Adventure Works, Measure Group: Fact Sales Summary, Partition: Fact Sales Summary, Record: 1. Errors in the OLAP storage engine: The process operation ended because the number of errors encountered during processing reached the defined limit of allowable errors for the operation. Errors in the OLAP storage engine: An error occurred while process ...Show All

• harlequinben Custom assembly : Retrieving data from from configuration file.

  Hi all, my problem is that: I do read informations (e.g. connectionString) from configuration file by one custom assembly. My environment is RS2005, VS.NET 2005 Prof.Edition, WIN2003 Server SP1, .NET Framework 2.0.50727 The scenario is that: A text box into my report require a function contained into MyCustom.dll. This code do read information from MyCustom.dll.config. This file contains: < xml version="1.0" encoding="utf-8" > <configuration> <connectionStrings> <add name="IS2009cfg" connectionString="data source=servername\instancename;initial catalog=dbname;user id=xx;password=yy;" /> </connectionStrings> <appSettings> <add key="DefaultHeaderLog ...Show All

• Andrew Qualia MDX Question

  I have this Problem with an MDX in Reporting Services, but in the Reporting Services forum, noone can answer it. So I hope, this is the right forum: Consider the following example: SELECT non empty { [Measures].[Amount], [Measures].[Budget Amount] } on columns, non empty { [Products].[Product Hierarchy].[Level4].ALLMEMBERS } on rows FROM [Datawarehouse] WHERE ( [Material].[Material].[All Materials], [Date].[October 2006], [Company].[Company Scenarios].[Scenario].&[Vienna2market] ) I want to select not only [Vienna2Market], also [Salzburg2Market] should be selected, and both as one sum, not 2 separate columns. As I know, the WHERE part needs a Tupel, and each dimension can only be one time in a tupel. The Scenario Dimens ...Show All

• Mark Benningfield sql server 2000 OPENXML to insert

  Hi, Trying to use OpenXML to insert the xml data into table. As you can see in the sql code I am first trying to see if the select * works but nothing is returned. The idea is to eventually get this to do the insert into the table. can you please see what i am doing wrong Here is the xml sample. Next you will see the SQL Thanks declare @doc varchar (1000) declare @idoc int SET @doc = ' <marketData> <date>21-Feb-2007 11:26:39</date> <producedBy>Dominic Harris</producedBy> <isDifferential>True</isDifferential> <forwardCurves> <forwardCurve code="CU LME_FUT"> <maturities> <maturity> <contractMonth /> <date>30-Dec-1899</date ...Show All