Answer Questions

• mitchwardrop best approach for "trigger on commit"?

  (I've searched the forum but haven't found anything that seems to address my specific question) We have multiple tables that get updated as part of a transaction. After all the data is added succesfully there is processing that needs to be done, so we'd like to use a trigger to kick it off automatically. The schema can be thought of as including a "master report" table that gets updated once whenever a new report is submitted and multiple related tables each corresponding to different report sections. The PK-FK relations imply that the master report will be updated prior to the subsidiary sections, but the exact order of updates to the subtables is not defined. I thought of attaching a trigger to the master report table but I can't fi ...Show All

• Andreas Georgsson Service Pack 1 -- account information could not be verified

  When installing Svcpack 1 ontop of my 9.00.1399.06 RTM Developer Edition I get the following error everytime: --------------------------- Invalid Login Information --------------------------- Your account information could not be verified for one or more instances. Ensure that you can connect to all the selected instances using the account information provided. To proceed further deselect the instances to which connectivity cannot be established. --------------------------- OK   --------------------------- I have followed all possible suggestions on the following post http://forums.microsoft.com/MSDN/ShowPost.aspx PostID=358003&SiteID=1 but nothing works.  I'm running as the local admin on t ...Show All

• Andrew E449473 Troubleshooting OSQL problems multiple files

  Hi, I need to run 3 files which will create objects in a database. I have three files a.sql b.sql c.sql a sample of the a.sql is as follows SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO CREATE PROCEDURE [dbo].[Get7DigitSNSeq] @SNSequence Int OUT AS DECLARE @err int; BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; -- Start a transaction to ensure we are the only ones who get this sequence number. BEGIN TRANSACTION; -- Get the first available serial number sequence SELECT TOP 1 @SNSequence = serial_number_sequence FROM available_serial_number WITH(updlock) WHERE available = 1; -- No unallocated sequence numbers left... IF @@ROWCOU ...Show All

• Dany V Calculate Median Value

  I am converting a report created using Crystal Reports 10 to Reporting Services.  The report contains a list of items with dollar values.  The original report displays both the Average and Median value.  I can easily ( using avg(Field1.Value!) ) determine the average but cannot find a function to determine the median. How can I add the Median to the report   Thanks in advance, Greg We ended up creating a Data Warehouse and Analysis Services to get the report we needed. Even then, calculating the Median proved to be difficult, until we found a good posting. Good luck everyone. Greg Hi Greg, Could you please inform me where you are pla ...Show All

• cb3431 query engine error

  Can anyone tell me why I am getting this error when I try to view my report: Query Engine Error: '21000:[Microsoft][ODBC SQL Server Driver][SQL Sever] Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <=,>,>= or when the subquery is used as an True. There are lot's of possibilities what he wants to achieve. A correlated subquery could be one alternative, too. Hard to tell without seeing the original query or requirement first :) This is a problem in the SQL-query. Like the error says, the query has a subquery that returns multiple values (more than one row and column). Subqueries like that can't be in the column list or after the listed oper ...Show All

• James P. Webster how to set column name dynamically?

  HI chaps my scenario is that, i have a table in which i have column such as col01,col02,col03....col31 i want to retrieve the records related to that column by specifying the name dynamically (by using loop) as fist three character are same ( 'col ' ) and i have to do integer increment in last two character ( 01... 31). Is it possible that I can use variable for column name , if yes how or is there any other way to achieve this task waiting for your reply regards i have written the follwoing script DECLARE @CURRENTDATE DATETIME DECLARE @STARTDATE DATETIME DECLARE @LOOPDATE DATETIME DECLARE @NOOFMONTHS INT DECLARE @NOOFDAYS INT DECLARE @DAYCOUNTER TINYINT DECLARE @I INT DECLAR ...Show All

• Avi_harush How to display Rich Text Format in Textbox control in SSRS Report

  I have rich text format as field in dataset e.g <FONT color=#000000><B><FONT color=#00c060>Suspendisse</FONT></B> </FONT>augue diam, blandit quis, elementum sed, cursus at, sem. Cum sociis natoque penatibus et magnis dis parturient montes, nascetur ridiculus mus. Mauris porta vehicula ligula. <STRONG>Curabitur consectetuer</STRONG> <EM>massa</EM> . when this field is dropped in the Textbox it does not show it in rich text in report but displays in html format i.e the way it looks above in SSRS report .Please suggest how it can be handled. Hi Brad, Thanks a lot for the information... Brad ...Show All

• Ah Jack What is a good tool for modeling a SQL Server 2005 database?

  Hello, I need a tool that will let me model a SQL Server 2005 database and then generate the tables, constraints, etc. from the model. I've never used a modeling tool so my knowledge is quite limited. I don't need to model or reverse engineer an application - my sole concern is on the SQL Server database side. I'm not concerned if the tool integrates with Visual Studio. And, of course, price is one consideration. Are there any good tools that I should look at Thanks, BCB I would suggest checking out QUEST Software - they have been around for years and they build great DB Software for all brands of DB's. I was particularly impressed with their Data Modeler Tool; http://www.quest.com/Toa ...Show All

• JD Li XML / XML Data Type Question

  I am pretty much a neophyte when it comes to XML. Here is my question. Assume the simple XML fragment below is assigned to an SQL 2005 XML data type variable, could someone tell me how I could programmatically accomplish the following 1) Determine how many rows/records there are in this fragment 2) How I can extract the value of say 'UserType' for any particular row/record Thanks very much - Amos <row ItemType="UserCount" UserType="Corporate" UserCount="0"/><row ItemType="UserCount" UserType="External Billable" UserCount="0"/><row ItemType="UserCount" UserType="External Non-Billable" UserCount="9"/><row ...Show All

• vdv_phuong Linked Server

  I have an oracel linked server that I use openqueryset statements to read the oracle tables. However, I want to update some data in a couple of these oracle tables. The linked server is setup using a readonly user. I'd like to be able to call an Oracle Set Role to grant me update capability and then perform the update. Can anyone help me out with some possibilities Well... does not really apply. What I am thinking about doing is calling an oracle stored procedure that grants the linked server account update privledges on the table. Then submit an openquery statement to do the update. However, I am not sure if the privledge granted from the prcale stored procedure will still be in effect when th ...Show All

• Paul Yau Calling commands from the cmd prompt

  I have a series of commands that I need to run which decrypt, unzip, and rename They are all one line commands when entered on the cmd prompt, is there an easy way to do this in ssis or should i write an exe and call them from there Thanks I'm using gzip to decompress files. the command I'd like to run is gzip -dc filetounzip > filenametosaveto I'm having no problem running everything before the ">" character. This normally pipes the output to location filetosave to.  Without the filenametosave to it dumps the output to the standard out. I tried to put the filenametosave to in a variable in the standardoutputvariable property w/ no avail. The reason I'm using gzip is because I'm unzipping a . ...Show All

• EDV Gradl Date Range filter

  Hi guys, I need to filter result in MDX query by passed Start Date and EndDate I come up with following solution What service pack are you using There's been some fixes in SP2 that may apply to this case. This is confirmed: It worked fine with SSAS v9.00.247.00 but with SSAS v 9.00.3042.00 return null for all rows and columns Does anyone has idea how to make it works Actually it worked on SQL 2005 RTM. After I applied SP2 I start getting the empty cells. Without specifics on what you're doing, it's hard to suggest a specific course of action. (What is the full query, what tool, etc.). Some suggestions on how to proceed: Validate that your filter "set" is in fact correct. I'd look at th ...Show All

• Rudemusik Problem for Calling A Stored Procedure, Please help.

  I am writing a Stored Procedure for other server (using C++ to receive the output values) as below, where @Total ， @balance， @A are output values create proc [MaxTime] @number varchar(30), @numbera varchar(30), @numberb varchar(30) as begin declare @balancefloat declare @table varchar(20) declare @freetotal varchar(20) declare @SQL nvarchar(4000) declare @A float declare @Total float select @balance = balance, @table = table, @freetotal = freetotal from info where number = @number SELECT @SQL = 'select @A = A FROM' + @table + ' WHERE LEFT(code, 1) = ' + LEFT(@incomingcode, 1) + ' AND CHARINDEX(LTRIM(RTRIM(code)), ' + @incomingcode+ ') = 1' + ' ORDER BY LEN(code) DESC' exec sp_executesql ...Show All

• JesperStaal SSIS package

  Guys I've migrated a SQL Server 2000 DTS package over to SSIS (which seems to have worked), its a really simple package with just one item, it first checks for a tables existance and deletes if necessary, then recreates the table and then inserts data from a selection of other tables, except its only creating the table no data is being inserted, yet the script to insert data works as I've tried it in a query. Any ideas Thanks inadvance I think it was down to the order in which the ssis package was being run, there was a copy sql server object in the package which copied the populated table to another database, there doesn't seem to be a way to place a executing order on objects so I guess it wa ...Show All

• sugrhigh Import/Export Wizard Connection Error

  Please help with the following error - I had been able to import/export without problem until I just encountered this error: =================================== An error occurred which the SQL Server Integration Services Wizard was not prepared to handle. (SQL Server Import and Export Wizard) =================================== Exception has been thrown by the target of an invocation. (mscorlib) ------------------------------ Program Location: at System.RuntimeMethodHandle._InvokeConstructor(Object[] args, SignatureStruct& signature, IntPtr declaringType) at System.RuntimeMethodHandle.InvokeConstructor(Object[] args, SignatureStruct signature, RuntimeTypeHandle declaringType) at System.Reflection.RuntimeConstructorInfo.Invoke(Bindi ...Show All