Answer Questions

• TaylorMichaelL stored procedures, compile or not

  I am writing stored procedures to retrieve data from an OLTP application that will be adding several hundred thousand transactions a day. The user will be presented a screen to select up to a dozen or so search criteria; date range, color, size, processing plant, etc. They may select all criteria, one or two, or none. Currently I am creating a stored procedure "on the fly" by replacing temp vars with search criteria, compiling and executing. My option would be to compile the stored procedure, pass it parameters, and run the precompiled stored procedure. I am wondering, as the database is changing so much, if I am better off to compile the procedure every time it is run. Or if stored procedures are always the best way to ...Show All

• Callavin Need textboxs in a table to show zeros if no record found - not a NoRows message

  I need a way to have the text boxes in a table to show a 0 if there is no record found for the query (not looking for a NoRows message). I've tried setting a default value for the textbox, but it isn't displayed since the query is empty. Is there a way to setup the query to have an if statement that would return a value of zero for the fields as in: If recordcount =0 then set field to 0 Here is an example of how to substitute data on the report when none is available in the database. The"0" is the character returned and displayed on the report. This example is from the layout designer and goes into your column. In this example CB0StockStart is the value from the database being returned. I t ...Show All

• Computer Guy69146 Load data from .DAT file

  Hi All, I am using Bulk Insert task to laod data from .dat file to SQL table but getting an error below. [Bulk Insert Task] Error: An error occurred with the following error message: "Cannot fetch a row from OLE DB provider "BULK" for linked server "(null)".The OLE DB provider "BULK" for linked server "(null)" reported an error. The provider did not give any information about the error.The bulk load failed. The column is too long in the data file for row 1, column 1. Verify that the field terminator and row terminator are specified correctly.". Any help will be appreciated. Thanks. I found the solution. I had a Format file which I fixed i ...Show All

• Stas Kravets How do i XQuery the a Node from Dynamic Hierarchy ?

  Hi, I am very new to XQuery. I'd like to find a node whose id is a certain value. my XML is ====================================================================== <!DOCTYPE root [ <!ELEMENT root ANY> <!ELEMENT joint ANY> <!ELEMENT node ANY> <!ATTLIST node nodeID ID #REQUIRED> ]> <root> <joint ID="1" PCount="1" SCount="0"> <joint ID="2" PCount="2" SCount="0"> <node ID="1" PCount="1" SCount="0" Type="R" nodeID="nodeE"/> <joint ID="3" PCount="2" SCount="0"> <node ID="2" PCount="1" SCount=" ...Show All

• Will Merydith Permisions error

  Hi I have a cube that is developed and ready to be deployed. While preparing documentation and going through various steps I now cannot connect to the cube. I cannot even browse the cube on the AS server - I get this message: The query could not be processed:o OLE DB error: OLE DB or ODBC error: EXECUTE permission denied on object 'sp_trace_create', database 'master', owner 'dbo'.; 42000. The cube can be processed with no errors. Anyone have any ideas Many thanks in advance Steve You are right - when I looked I had set Proactive Caching set. Changed this back to std MOLAP and it worked fine. Thanks By any chance you are using proactive ...Show All

• White-Tiger-Eyes how to write this trigger

  I have table T1 with the fields: ID,Type,Status,F1,F2,F3,F4 in database1. I also have T2 in Database2 which has the same fields and some extra fields. Now based on the T1.Type=Insert or Update, I need to perform either update or Insert in T2 based on where T1.ID=T2.ID and T1.Status<>’Done’. If this is a success, I need to set T1.Status=’Done’ for the updated records. So this should only be updated records. T1 is a frequently inserted table, so there might be more than one record coming there at the same time. How should I write my insert trigger and correctly set T1.Status=’Done’, any example would be greatly appreciated. CREATE TRIGGER SomeTrigger ON Table1 FOR INSERT, UPDATE AS BEGIN --Statement 1 INSERT ...Show All

• David S. Anderson multi step OLE DB error

  Hi All, I have a MS Access DB that I have successfully u/graded to SQL Express 2005. I run my code using a recordset as normal, and all connections to the database work fine, but I receive the multi step OLE DB error when it gets to a line trying to populate an address field which has a datatype of nvarchar(max), null. (Field was Memo in Access version before). This field in SQL2005 has allow nulls. I've tried to add an empty string " " to the variable before being saved, but this still doesn't work. Any ideas Hi, The exact error message received is: -2147217887 - Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done. ...Show All

• MattyBoy4444 How can I get value of XML element from XML column

  I have one simple question. How can I get scalar value of en XML element from XML column ... <tag> value <tag/> ... I wont to get a result: "value" or you could simple do this..seems more concise to me declare @xml nvarchar(100) declare @id int set @xml = '<tag> value </tag>' exec sp_xml_preparedocument @id output, @xml select tagVal from openxml(@id, N'//tag') with (tagVal varchar(100) 'text()') Don't forget the : exec sp_xml_removedocument @Id It is an alternative. I'm currently looking at code to try and determine whether the .nodes methods available are faster/the same/slower than OPENXML. this ...Show All

• JonM Reporting Services in SQL Server Express

  The SQL Server Express can be downloaded with Advanced Services and thereby Reporting Services are included. I wonder if this is the same Reporting Services as in SQL Server 2005 Standard I have heard something that it is a stripped version that is included in the Express download. Is it so And if so, what are the differences That is correct, it is a stripped down version. You might want to check out this document, you can get what is different between the editions. http://download.microsoft.com/download/E/8/B/E8B42814-6A0C-40EB-911F-E7ADEC87F5D5/UpgrExptoWkgStd.doc Jarret Is it possible to install SQL Express and RS (for SQL Express) on a windows 2003 server and then host an asp ...Show All

• SQLme Is there a way to 'peek' the items in a queue?

  ... or some other way to prolong their presence on the queue. The scenario I'm dealing with requires me to receive a batch of messages and fire them off (as a batch) to a legacy Unix application via TCP. What I need to do is keep those messages on the queue (or at least somewhere else in the database) until I know for certain that the legacy app has received them. Comments If your design calls for intentional message rollbacks, then you have a problem. This is never correct, and in addition is guaranteed to trigger poison message detection. The sender's transaction does not equate a transaction on the target. I.e. sender can SEND 3 messages in one transaction, target can RECEIVE 2 of them be ...Show All

• Emy_P Simple flat file import

  Boy, do I need HELP! Have a simple csv file that I need to import. Worked fine in sql2000; I put it into dts to execute on a monthly basis. Makes connection, db connection, table creation fine, but stops at validation of flat file Basically, I want to go out and get a flat file, drop the existing table, and create the table, and import the information from the flat file. Not a complicated table of about 30,000 records. Create table [db].[dbo].[tblPatient] ( [patientID] into not null, [chartID] varChar(15) null, [doctorID] int null, [birthdate] datetime null, [sex] varchar(1) null, [raceID] int null, [city] varchar(100) null, [state] varchar(2) null, [zip9] varchar(9) null, [patientTypeID] int null, [patName] varchar(100) null) Be ...Show All

• Greenwu missing SQL Server Agent

  Hi, I'm learning SQL 2005 and installed the express ADV edition to my pc. But I miss the Agent service. Is this included in express Has it another name after SQL server 2000 thanks majid Looking at the edition comparison page (http://www.microsoft.com/sql/prodinfo/features/compare-features.mspx), it looks like SQL Agent Job Scheduling Service only comes with Workgroup, Standard, and Enterprise. Thanks, Sam Lester (MSFT) oh, I see, Thanx majid ...Show All

• kirupa Where to put DISTINCT in a CTE?

  CTE's are surprisingly useful for paging data, but I'm running into a problem where I have to add a distinct clause. The idea is to pull topic records for a user's forum posts, but only one topic record even if there are multiple posts in the topic. Here's what I have so far: CREATE PROCEDURE pf_PagedTopicsByUser( @StartRow int, @PageSize int, @UserID int ) AS DECLARE @Counter int SET @Counter = (@StartRow + @PageSize - 1) SET ROWCOUNT @Counter; WITH Entries AS ( SELECT ROW_NUMBER() OVER (ORDER BY IsPinned DESC, LastPostTime DESC) AS Row, pf_Topic.TopicID, pf_Topic.ForumID, pf_Topic.Title, pf_Topic.ReplyCount, pf_Topic.ViewCount, pf_Topic.StartedByUserID, pf_Topic.StartedByName, pf_Topic.LastPostUserID, pf_Topic.LastPostName, ...Show All

• Dietz How to Restore the msdb DatBase

  Hi, i have reinstalled the SQL Server before it i have taken backup of both master and msdb database.Because there were many scduled jobs. After Installation i want recreate those scduled job. That i think i can get by restoring msdb. But it is not restoring how can i restore it. Or there is any way to get the all scduled job list so that i can recreate it manully. Regards Sanjay Sorry, to come back that late. As greg already said, you have a problem concerning your different versions of your database (might have a service pack or hotfix installed meanwhile ). This is what a kn article is telling about the problem: http://support.microsoft.com/kb/264474/EN-US/ Another option would be t ...Show All

• davidgsteadman Aggregate functions not allowed in the dataset filter

  Hi, I was trying to filter a dataset based on a condition like this: Fields!SalesAmt.value <= Sum(Fields!SalesAmt.Value)*0.05. This is nothing but it filters those SalesAmt that are less than 5% of the total sales amount. However SSRS doesn't allow to use aggregate functions in the dataset and data region filter. Is there another way to do this Sincerely, --Amde Aggregates are not supported in filter expressions (as you have seen), but there are few ways that you can work around this. Here are the first two that come to mind. 1. Filter the data returned directly in the SQL query. This can make the query more complicated. See http://msdn2.microsoft.com/en-us/library/ms179270.aspx for mo ...Show All