Answer Questions

• philmee95 how if esle statement works??

  hi, everybody! I have some problems with understanding how if esle statment in sql works. I'm trying to write my own count() -function and its not working with if else :(( I've already implemented it with case : select sum(case (hiredate) when (null) then 0 else 1 end) as 'Count' from emp It's working perfect. Now I want to have the same but using if else : select sum( if( hiredate is null) begin 0 end else 1) as 'Count' from emp the error message is: Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'if'. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '0'. What is wrong here Artur, You can have an 'if/then/else' statement within your code, it's just that ...Show All

• aaks store data into temp array

  How can you loop over a data reader and save the current row to a temp array of some sort and return this array back to the caller SqlDecimal total = 0; SqlConnection conn = new SqlConnection("Context Connection=true"); SqlCommand cmd = new SqlCommand(@"SELECT column1, column2, column3 FROM table ", conn); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(); while (rdr.Read()) { if (rdr.GetSqlString(1) == "01") { // ADD the current record to a temp list total = total + rdr.GetSqlDecimal(2); } } SqlContext.Pipe.Send(total.ToString()); // RETURN the temp list rdr.Close(); conn.Close(); Hi! Let me clarify: you are writing a CLR stored procedure that has to ru ...Show All

• Asday Running a Stored Proc before report runs

  Hi. I've got a report with 4 different sections - the datasets coming from some tables that are populated via a stored procedure. I'd love it if the the first thing this report did was run that stored procedure and then the data would be available for the actual reporting piece. Is that possible And if so, how do I make it work Thanks! I haven't tried this, but if your dataset's are coming from stored procedures, you could just call your 'data generation' stored procedure at the beginning of your reporting stored procedure. Hope this helps. Jarret Hi, I had quite similar problem with multiple sp-based datasets. The first one populated the global tempor ...Show All

• MSNetDeveloper moving data in ssis from sql server 2000 to 2005 on same server

  hi, does anyone know how I can make a connection to a 2000 db thru ssis I'm running sql 2005 and 2000 on the same server and the db names are the same maybe I'm missing something, but when I use that I can just see my 2005 dbs I guess thats the answer I was looking for. I suppose I will have to detach and attach the db to move the data if both 2000 and 2005 are on the same server. LISA86 wrote: what if they have the same db name. do I need to rename one Wait a minute... How can you have SQL Server 2000 and 2005 running on the same server and expect to connect to them on port 1433 what if they have the same db name. do I need to rename one Use an OLE DB Source. LISA86 wrote: mayb ...Show All

• Brian86 How do I downgrade SQL Server from SP4 to SP3??

  Hi, We have hit performance issues with our SQL server after upgraded it to SP3 from SP3a, is there any way that I can downgrade it back to Service Pack3a Thanks. Hi, refer and follow http://support.microsoft.com/kb/889551 For removing SP3a http://sqlserver2000.databases.aspfaq.com/why-am-i-having-problems-with-sql-server-2000-sp3/sp3a.html http://www.sqlservercentral.com/accessdenied.aspx SourceURL=/forums/shwmessage.aspx forumid=5&messageid=123905 ...Show All

• Javahar Getting started questions, versions, upgrading, etc.

  I just attempted to upgrade to SQL Server Compact Edition (2005 3.1 ). The install appeared to go ok but I have no idea really and judging from the version numbers that appear in the references of my projects, I'd say there could be a problem. First, here is what I installed (and the order I installed it): 1) SQL Server 2005 Compact Edition (SQLServerCE31-EN.msi) 2) SQL Server 2005 Compact Edition Developer SDK (SSCE31SDK-ENU.msi) 3) SQL Server 2005 Compact Edition Tools for Visual Studio 2005 SP1 ( SSCE31VSTools-ENU.exe) The first two installs went normal and said they completed successfully. The VS Tools install was strange... it appeared to install but there was no message saying it was complete or successful. The install dialog just d ...Show All

• xRuntime mscorlib.tlb error

  trying to install SQL2005 Express on a Windows 2000 Server with IIS5 and ASP.NET 2.x. Install of the SQL server and the SSMSE both complete fine. However when I try and Launch SSMSE I get an error that: The File C:\WINNT\Microsoft.NET\Framework\*******\mscorlib.tlb cannot be found. An attempt to repair this condition failed because the file could not be found. Please reinstall the program. (the error message actually has "squares" where the asteriks are instead of a folder name) I have verified that the mscorlib.tlb resides in both the Framework\v1.1.4322 and the Framework\v2.0.50727 directories. I successfully installed this a few weeks back on a test Win2000 server with no issues and the mscorlib.tlb files are identica ...Show All

• l3igmike Merge replication foreign key problem

  Hi there. I'm somewhat new to merge replication, and I've been having an issue with one of the scenarios that I've been trying to get working. I am using SQL 2005 on the server, with 2005 express on the client. I have 2 tables: Photo - which has a PhotoID primary key PhotoData - which has a PhotoDataID primary key, and a PhotoID foreign key both primary keys are int, and set to identity. I only want the Photo table to replicate for the merge, because I want the data in the PhotoData table to only be called by demand through a web service (since the images in that table are too large to be included in the normal replication). However, when a client adds a photo to his local database (which adds a record in the photo table, ...Show All

• MaggieChan filter using count

  SELECT Sheet1$.[Trial ID] AS [Trail ID], CASE Sheet1$.[Trial ID] WHEN 'CSPP100A2344' THEN '0' END AS Extension, SUBSTRING(Sheet1$.[Patient ID], 1, 4) AS [Center Number], SUBSTRING(Sheet1$.[Patient ID], 5, 8) AS [Patient Number], SUBSTRING(Sheet1$.[Patient ID], 1, 4) + '_' + SUBSTRING(Sheet1$.[Patient ID], 5, 8) AS [Patient Identifier], Sheet1$.[Trial ID] + '_' + SUBSTRING(Sheet1$.[Patient ID], 1, 4) + '_' + SUBSTRING(Sheet1$.[Patient ID], 5, 8) AS [Study Number], Sheet1$.[First Name] + Sheet1$.Surname AS [Patient Initials], CASE Sheet1$.NOTES WHEN 'visit 3' THEN '777' WHEN 'visit 11' THEN '778' WHEN 'unscheduled visit' THEN '999' END AS [Visit Report Number], CASE Sheet1$.NOTES WHEN ...Show All

• samssb Bitwise & operator does not work

  I would like to do something like a SELECT * FROM Files WHERE (Attributes & ) but the & operator isn't recognized. I looked at the documentation sample for it and in the sample the & operator isn't used in the WHERE clause so I thought that might be the reason but I still can't get the operator to work. So even the doc sample code for it doesn't work. The code in the documentation sample for & does work for me. I get the expected result, 10, printed on my console. Robert Wishlaw I got this to work by rewriting the WHERE clause as (Attributes & = ). However, the VS query designer can't parse the bitwise operators. ...Show All

• Nicolas Mueggler Server Agent does not run job

  hi! i am able to run the Package through the command line dtexec correctly with the command line parameters but if i try running through the agent it throughs up errors Message Executed as user: ----------. The command line parameters are invalid. The step failed. Any help!!!!!!!!! jasmine Please see this KB for troubleshooting information: http://support.microsoft.com/kb/918760 ...Show All

• yatingg Transact-SQL batch file to import to SQL Server.

  I hope that was the correct subject. That basically it, I need to be able to upload data in a CSV file to SQL SERVER 2005. I know you need the bcp syntax, my question is this. How exactly would that look Another question is, "Does this need the DSN encoded with the batch file code to go through ". If so, how would that look.. You can use bcp.exe or tsql's "BULK INSERT". For bcp.exe, no DSN is needed since you specify credentials and server name using the available switches. I would start by reading books online topic "bcp utility", http://msdn2.microsoft.com/en-us/library/ms162802.aspx . It has an example of how it's used. ...Show All

• Richard_Mason The report server installation is not initialized.

  The report server installation is not initialized. (rsReportServerNotActivated) (rsRPCError) Get Online Help Get Online Help Home Hullo, When i access the reporting Manager, I get the above error message. How can be able to solve this. Ronald Hi, Check this link:http://www.awprofessional.com/articles/article.asp p=357694&seqNum=6&rl=1 Goto program files -> sql server 2005 -> Configuration Tools -> Reporting Services configuration Mention ur machine name,instance name and hit start. If INITIALIZATION is still "X" Try the following : 1. usually done while config ...Show All

• Atanu Maity About Connections and component mappings

  1.After creating pacakages what i am concerned about is that when i move then to different locations do they work just the same as the had been. 2.About the mapping with in the components like lookups,and the variable with in conditional split or etc....do they get disturbed with any change in the location of the textfiles or anything of this kind of location change. 3.Is is adviceable transforming 5 to 6 text file with in one package in parallel Please let me know Thanks Also, for #1, you can use package configurations to allow for easier maintenance of the file paths. 1. They should work the same way. Just make sure the account executing the package has required permissions on all en ...Show All

• jinfeng_Wang Recursive Call -- Joins etc... I'm really confused

  Hi, Firstly, I would admit that I am not an SQL expert. I know SQL but can't write complex queries... for about optimizing the queries. Ok... coming to my query: I have a structure of a tree as follows: TABLE TreeNode { NodeID int IDENTITY(1,1) ParentNodeID int NULL } CONSTRAINTS: PrimaryKey: NodeID ForeignKey: ParentNodeID REFERENCES NodeID I am using this simple structure to define a tree. All the entries with a ParentNodeid = NULL indicate the root of the tree so that I can define multiple trees. Now, given a NodeID, I want all rows that are a part of tree. For example, if I have a structure like: root(id=3) |--> child, id = 4 |--> child, id = 5 |--> child, id=1 |--> child, id=2 |-- ...Show All