Answer Questions

• Corinna Vinschen Maintenance Plans fail on Named Instances?

  Hello all, I think this is a bug in SQL 2005 Maintenance Plans (the graphical designer part under Management\Maintenance Plans that creates an SSIS package), but I want to see if anyone knows for sure or can replicate it. Some items fail on named instances. Example: Whenever you create a Check Database Integrety Task for database HappyTest on server instance SQLMACHINE\SQL2005 it fails every time with a message saying ALTER FAILED ON SERVER SQLMACHINE\\SQL2005 . On top of that, it causes an Exception for the email sending last step of the task with something like An exception has occured, failed sending email, there was an error near SQLMACHINE. Now, it's obvious what the problem is. SQLMACHINE\\SQL2005 doesn't exist and the email ...Show All

• XNA Rockstar SQL Server 2005 SP2 cannot be installed because the Database Engine setup remains in suspended installation mode

  SP2 refuses to install for the Database Engine because “the previous upgrade did not complete”, i.e. it detects a suspended installation. The same thing happened to me when I installed SP1. I then remember opening the maintenance dialog and completing the setup, after which SP1 installed just fine. When I try to do the same thing this time, I’m presented with the ”complete the suspended installation” option, which when I run it reports the installation completed successfully. However, in reality the installation still remains in suspended mode for whatever reason. If I reboot and open the maintenance dialog, I’m still presented with the ”complete the suspended installation” option. I’ve even tried to run it more than once, but it does ...Show All

• Michael Wittenburg SQL Server 2005 Developer Edition

  I am in the early stages of migrating an extensive Access 2003 application to C#/SQL Server. The client already has SQL Server 2005, whereas I am using SQL Server 2000 Developer Edition. It has worked fine for me, but I'd like to upgrade to SQL Server 2005 Developer Edition and have a couple of questions. In Access 2000 I am always using Query Analyzer and Enterprise Manager. Are these features available in 2005 Develper Edition Can more than one user connect to SQL Server at a time That is, for testing concurrency, etc, can two people connect at the same time Is these any restriction on connecting to SQL Srever from another computer or workstation Again for testing, one or two other workstations would be fine. Finally, what is the str ...Show All

• Voltafil How can I format the Date in the SQL Table using a SQL query

  Hi I have a SQL table that contains date in this format :- 2006-07-02 16:20:01.000 2006-07-02 16:21:00.000 2006-07-02 16:21:01.000 2006-07-02 16:22:00.000 2006-07-02 16:22:02.000 2006-07-02 16:23:00.000   The date above contains seconds that I dont want, how can I remove those seconds so that the output looks like :- 2006-07-02 16:20:00.000 2006-07-02 16:21:00.000 2006-07-02 16:21:00.000 2006-07-02 16:22:00.000 2006-07-02 16:22:00.000 2006-07-02 16:23:00.000   Your help will be highly appreciated. That will change the data. If you only want to change the display, then try this ; select replace(convert(varchar(20), columnName, 102), '.', '-') + ' ' + left(convert(varchar(20), columnName, 108), ...Show All

• ChristosX PrimeOutput() and NullReferenceException problems with DataReader Source in SSIS

  Hi I have an integration services script that was working ok until a recent database upgrade. When I run the script in gui debug mode it behaves as follows. The data flow reads in data from an oracle server table VALNREQ & then uses it to populate a sqlserver table (with a little manipulation of the fields in between). For some reason although it populates the destination table with the correct number of rows (& the data looks ok) it errors out with the messages listed below. The DataReader Source dataflow source box turns red & all the other boxes turn green including the destination one. I have run out of ideas ... any suggestions Have I inadvertently changed a property without noticing I can't see anything obvious & t ...Show All

• soconne query help

  Here is a simplified example of what I need to accomplish. I have a table that keeps track of plastic balls in tubs.  Based on a bit column the record is either defining balls added to a tub or taken away.  When the record is defined as adding balls to a tub it will say how many and what color, but when the flag says they have been taken away from a tub I only know the number removed and not the color. There are multiple tubs and multiple colors. So say tub1 has 20 balls in it (50% red, %25 green, and %25 blue). Also say tub2 has 10 balls in it (100% red).  This is our starting point. Now on day one, 5 balls from tub two are put into tub one. So we know that 5 balls of 100% red are put into tub o ...Show All

• DRoden How to do multiple rows insert?

  I want to do bulk insert of data into table. I need to do it using System.Data.SqlServerCe namespace; Already tryed 2 methods. 1) SqlCeCommand command = Connection.CreateCommand(); command.CommandText = "Insert INTO [Table] (col1, col2) Values (val1, val2); Insert INTO [Table] (col1, col2) Values(val11, val22)"; if (Connection.State != System.Data.ConnectionState.Closed) {   Connection.Close(); } Connection.Open(); command.ExecuteNonQuery(); Connection.Close(); Doesn't work because of parsing error. Appearantly semicolon isn't understood in commandText, although if commandText is executed in SQL Management Studio it executes flawlessly. 2) SqlCeCommand command = Connection.CreateCommand(); command.CommandText = "INSER ...Show All

• RichardW411084 Importing related data

  I need to recreate a SQL 2005 db in a SQL 2000 server. So far I was able to recreate database schema, but I don't know how to import the raw data. I tried copy-pasting table data to the new db using SQL Server Management Studio Express, but there's a problem - the identity columns do not retain the correct values. For example: Old db table ID Username ------------- 15 Peter 45 James When pasting the same data to teh new db, I would get this: ID Username ------------- 1 Peter 2 James You can see the ID field values become different (since the ID attribute is an identity and the table in the new db is empty, the db picks the values 1 and 2 for the two records) and now when pasting data into related tables, I get the error because table ...Show All

• Majicas Change partition Source through XMLA

  Hi everyone, I've got a small problem that I hope that someone can help me with. I am quite new to datawarehousing, so when I started out I didn't take partitioning measuregroups into consideration. Now my problem is taht the actual processing of my cubes takes to much time. Therfore I decided to start creating partitions, but since I have quite a lot of measuregroups I wanted to create them in SSIS. This has worked out fine and the partitions are created, but since we are using standard edition of ssas 2005 I can't craete more than 3 partitions a measuregroup. Therfore I decided to alter my design a bit more and actually have one working partition and one history partition. My questions are: 1. Since my old partition is ba ...Show All

• slaprade Getting Return type from Stored Procedure

  Hi all, I am trying to figure out a way to analyze a stored procedure to deduce its output type. A stored procedure can return values throught one of the following: 1) Using the Return keyword 2) Returning a recordset 3) Throught its Output parameters 4) A combination of 1, 2 or 3 Using the Information_Schema, you can access the SQL code of the stored procedure from, say, VB.Net. From there, I want to know what output type the stored procedure has. A) for 1), I guess I can parse the code for the 'Return' keyword, but that wouldn't be efficient, for the 'Return' word could be inserted into string values... B) for 2), it would be difficult to analyze the stored procedure's code to know FOR SURE whether a recordset ...Show All

• dragoncells System.Data.SqlServerCE.dll reference causes error ...

  Hello All ... I am attempting to create a program that will run on the PocketPC 2003 environment. I have upgraded Visual Studio to SP1, I have installed SQL Server Compact Edition on my development machine and I have installed SQL Server Compact Edition Tools For Visual Studio on the development machine. I have created a new project Visual Basic - Smart Device - Pocket PC 2003. I have created a form for user input. I build and deploy the form to the Symbol Pocket PC to test - no connection to data and it works. I then add a reference to System.Data.SqlServerCE.dll and rebuild and redeploy the application to the handheld. When I attempt to open the form I receive the following error: psmPocket.exe NotSupportedException System.D ...Show All

• Zoran Bebic Missing parameter, no chart displayed

  I've got this problem: (with Reporting Services 2005) I've got a report with a chart with some values that are needed to get the chart displayed. If one of this parameters is missing (not selected) the program doesn't display the report (it shows a blank page) Is it possible in this case to get instead a textbox with a message requiring to select the missing parameter (if I insert a textbox below the chart in the layout, this textbox isn't displayed if one parameter is missing) ps sorry for my poor english thanks in advance for your help If what you want is a message to be displayed to the user to choose a value, then you should be able to accomplish this by unchecking the "Allow blank value" o ...Show All

• Kevin Rodgers SQL Server 2005 to SQL Server 2000

  I have a slight problem: I have a database, that needs copying from SQL Server 2005 on one machine to SQL Server 2000 on another. This database is about 300+MB and contains about 100 tables, 51 views and various other bits and bobs. It is crucial that we have a simple procedure for doing this, and since the backup/restore facility doesn't work from 2005 to 2000 SQL Servers, I am left with a script to generate a skeleton database, and then an SSIS package, that I have built using the wizard, to do the rest, only the SSIS package doesn't work properly with more than about 16 tables. It would seem that with less than 16 tables, the SSIS package stays simple, containing the data about each table to copy, but with more than 16, it crea ...Show All

• Fippy Instead of Insert, Update Trigger calling a stored procedure Question

  I have to control my business rules in a Instead of Insert, Update Trigger. Since the Control Flow is quite complicated I wanted to break it into stored procedures that get called from within the trigger. I know that Insert Statements embedded in a Instead of Trigger do not execute the Insert of the trigger you are calling. But... If I embed stored procedures that handle my inserts in the Instead of Insert trigger call the trigger and put in a endless loop or are the stored procedure inserts treated the same as trigger embedded inserts. David: I have never called stored procedures from instead of triggers but I have had a couple situations like what you describe in which I was forced to have regular triggers cal ...Show All

• Linusguy Dynamic Query using Multi-Value Parameters

  I am having problems using multi-value parameters within dynamic queries. For e.g. I have a parameter YR which can have more than one yr values : 2003, 2004, .... In my where Clause I have Where yr IN (Parameters!Yr.Value), this gives me an error, however if I give an index lets say Parameters!Yr.Value(0) then it returns the correct report for that year in the above example for 2003, however I need to include all the years not just 1. How can this be achieved Please Help! Thanks, Hi MJ, Use the Join method. = Join(Parameters!MySelection.Value) ...Show All