Answer Questions

• ItsMe!!! How to execute dynamically generated XML query?

  How do you execute a dynamically generated xml statement Declare @XML XML set @xml = 'Select td=AcctID,'',td=UserID,'' from tempdb.dbo.dbmailemail for xml path(''tr'')' select @xml How do I get the above statement to execute. I dynamically generate the information in the @xml statement. Thanks! have you tried execute (@xml) You need to make sure any single quote ' is replace by two of them in the set statement i.e. set @xml = 'select ''simon'' ' Hey, That results in error: Implicit conversion from data type xml to nvarchar is not allowed. Use the CONVERT function to run this query. ------------------------------------------------------------- ...Show All

• David Ing Insufficient Memory Errors - 12GB RAM - 64-bit???

  hi, it was suggested to try this forum, so I am hopeful I can get some more ideas. Please see this link where i posted my issue: http://sqlforums.windowsitpro.com/web/forum/messageview.aspx catid=82&threadid=49973&enterthread=y Summary: I have just installed a brand new 64-bit system, with 64-bit SQL Server 2005 sp1, and 12GB of RAM. The same queries and processes that worked on SQL Server 2000 Std Edition successfully, are failing on SQL2k5 64-bit After migrating the databases and jobs, I am frequently getting this msg: 701:Insufficent memory to run this query Nothing else in the logs. I also made sure that the SQL Service account is granted Lock Pages in Memory - no help. First, I thought that SQL 2005 can take all ...Show All

• GR101 Cannot apply value null to property Login:

  Same issue ... "Cannot apply value null to property Login:". The copy process works fine in the designer. But, when the package is installed to another SQL Server 2005 instance, I get the error without any other information about the transfer object process. -ak ...Show All

• Ed Hansberry Chinese garbled in MSSQL2000 - by ASP

  Hi experts, Here I got some problems with my application. (ASP & English Version SQL Server 2000) As we are using English MSSQL Server 2000, we got some new functions and we have to facilitate support of Chinese characters in the DB. I have set the collation for those Chinese fields already and those queries or Stored Procs for Chinese are working fine, ONLY if I execute them in Enterprise Manager. Chinese characters can be displayed in the relevant tables. However here comes the big problem and I got really frustrated. As we will provide user interface in ASP pages, we 'll let users to insert the information which will be sent to the DB. If there's Chinese characters in the query string, the Chinese characters added in the DB ...Show All

• TimmyG15 Delete connection file

  I've inherited a procedure that performs a few transforms and writes to flat files in a working directory using a connection in connection manager. After the manipulation, the system cleans up by deleting the working files. Works fine in development, but from the command line gives: The process cannot access the file <filename> because it is being used by another process.". I suspect this is because connection manager still has the file open. Any ideas Thanks Guy Can u show the code you have written Then i can favor you better. Also, can u solve my problem I have a flat file with name " Employee.txt " (Full url: C:\Employee.txt ) .The File c ...Show All

• ROBSR insert into without repetitions

  I would like to insert new data into an existing table. However, there is the possibility of overlapping data (i.e. new table may contain some data that is already in the existing table). How can I avoid this The following code obviously will not work because the insert into table does not have a from clause. It does, hopefully, explain what I am trying to do. INSERT INTO table1 SELECT * FROM table2 WHERE table2 . MyPrimaryKey <> table1 . MyPrimaryKey --left join INSERT INTO table1 SELECT t2.* FROM table2 t2 left join table1 t1 on t2.MyPrimaryKey = t1.MyPrimaryKey where t1.MyPrimaryKey is null --not exists INSERT INTO table1 SELECT t2.* FROM table2 t2 where not exists ( ...Show All

• MmeBovary Passing parameters to reports

  I have hided one paramerter(Startdate) in my report and given default value(1/1/2000). I am passing this parameter from user end(aspx page) by using URL method.But my report is not processing on the parameter that I am passing from end user.It is taking only default value as the input parameter.Could you please help me how to use the end user parameter as my input parameter. Thanks Can you provide the URL you are using to try to render the report You can't pass parameters via the URL to the /Reports virtual directory. This vdir points to Report Manager and it doesn't support this. You can instead render via the /ReportServer virtual directory using a URL like: ...Show All

• MMMalik Is Service Broker Enabled?

  Hello, I've been trying for two days now to get SQL Cache Dependencies to work. So far, nothing has worked, and I have been around the block a few times now on this one. So now I'm going back to basics, as I think my code and queries are fine. My first question is how to confirm that I have a Service Broker that is up and running. I am using SSX as my database engine. Other posts mention how the "look at the service broker folder", and I don't see a folder anywhere. Can someone tell me what to look for I've added various SP's based on tutorials on web sites, so I can't tell if those SP's are mine or theirs at this point (I've been at this too long). Where is the "folder" the other posts have mentioned Do I need to do ...Show All

• easy right Aggregate Count Zero or NULL - multiple measuregroups vs. single measuregroup

  Hi Quess this fact table (100.000 Records a Day) Dim1 | M1 | M2 ---------------- A | 1 |NULL B |NULL| 2 C |NULL|NULL D | 3 | 4 I create a cube on top with four measures - for each a LastNonEmtpy and a count measure the result is Dim1 | M1*| M2* *)=Count measure ---------------- A | 1 | 0 B | 0 | 2 C | 0 | 0 D | 3 | 4 Is there a way to get a null value instead of a zero value in SSAS2005 (Build 2153) I have already thought about create a measuregorup for each meausre with a view related which filters the null value rows - but I guess the cubesize will increate much if I have a measuregroup per fact measure, because the dimension information needs storage too - I guess I need the count measure for calculati ...Show All

• Tovdb Foreign Key NOT referencing PK

  This book 'MS SQL Server 2000 Bible' says the following: 'The foreign key can reference primary keys, unique constraints, or unique indexes of any table' I've never heard of this before, I thought that the FK always established referential integrity by referencing the PK in the parent table. Does anyone have a further explanation of this thx, Kat Thanks Kenneth... It makes sense, I've just never seen a foreign key reference anything but the primary key to enforce RI. I guess it is all in the design, and hopefully in the documentation! Kat Your assumption is correct, the FK does reference the PK, that's it's purpose for being assigned as a FK. Still, you must understand that a primary key is an abstract en ...Show All

• Si B SSIS package refresh

  Hi, I have a little problem with an xml package. I'm a newbie in the XML field and in the new features of SSIS....so help me! I want to import a complex xml file in SQL Server via SSIS; I've created a package that map the xml source files to many different destination table. When I try to execute the package, I've one error about data type specified on XSD (for example unsignedShort instead of unsignedLong). At this point I fix the XSD file, settings the correct data type, but I don't find a way to explain to the package that it have to reload the xsd file. The only way is to make a new package.... I'm looking for a way to refresh the package without rebuild it... many thanks lavez OK, tha ...Show All

• PK_VBE05 Unable to set variables from script task

  Hi, I have a SSIS package that copies a file from one location and puts in in another with a datetime suffix on the end. This works fine in the script task. I've then added some additional code to the script task to set a variable with this new file location. The variable value gets set in the code, as I have a messagebox displaying the value, but doesn't get set to pass to the next step. Public Sub Main() Dim Filename As String Dim FileLocation As String Filename = CStr (Dts.Variables( "AgencySpendFileNameWithDateTime" ).Value) FileLocation = CStr (Dts.Variables( "FileLocation" ).Value) FileIO.FileSystem.CopyFile(FileLocation + "AgencySpend.txt" , FileLocation + ...Show All

• uwcodeman SQL Server Mobile Replication Performance

  Hello, I have been experiencing some difficulties with poorly performing synchronizations using replication from SQL Server 2005 to SQL Server Mobile running on Windows Mobile 5 devices. Currently there are two main databases (each client will only use one of them), the 1st one has around 500,000 rows, and the 2nd has about 1,200,000 rows. The initial synchronization for the 1st database takes around 45 minutes, and for the 2nd, around 2.5 hours. This is quite long, but we have comforted our clients by saying that this is a one time delay, and that further synchronizations will be much quicker. Well, synchronizing the data after this is usually quite speedy, however, things get bad rather quickly when the number of changes increases. In no ...Show All

• Funtowatch Dynamic Dimension Buckets

  Hi, I have a measure [count of bookings] and dimensions for [trucking company], [calendar], [booking type]. I need to create a dimension for buckets based on the count of bookings. The buckets I need to create are ([> 500],[201 - 500],[101 - 200],[51 - 100],[21 - 50],[11 - 20],[1 - 10]) based on the count of bookings. From this dimension I am hoping to create a measure that will show the count of trucking companies in each group (using the calendar dimension), and I am hoping to use the existing count of bookings measure to see how many bookings fall into each category by trucking company. Eg. For the [> 500] member there may be 1 trucking company, with 857 bookings, 666 bookings that are of a specific booking type in the a ...Show All

• RSUser08 SQL Server Manager Studio: Error when loading

  All of a sudden I am getting this error when I run SQL Express Server Manager Studio. any Ideas whay and how to fix it ------------------------------------------------------------------- See the end of this message for details on invoking just-in-time (JIT) debugging instead of this dialog box. ************** Exception Text ************** System.BadImageFormatException: An attempt was made to load a program with an incorrect format. (Exception from HRESULT: 0x8007000B) at Microsoft.Win32.Win32Native.SHGetFolderPath(IntPtr hwndOwner, Int32 nFolder, IntPtr hToken, Int32 dwFlags, StringBuilder lpszPath) at System.Environment.GetFolderPath(SpecialFolder folder) at Microsoft.SqlServer.Management.UI.ConnectionDlg.Personalization.get_ ...Show All