Answer Questions

• reichard Updating a table by both inserting and updating in the Data Flow

  I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this. Any suggestions msFlash wrote: Jamie Thomson wrote: msFlash wrote: Thanks for the response; however, I can't seem to get to the link you sent. Well it works fine for me so I can't help you there I'm afraid. -Jamie ...Show All

• ink innovations Loading multiple XML files into SQL Server

  I'm using the For Each loop container to load multiple XML data files into SQL Server, and noticing some peculiar behavior and need some advice. The pattern I'm trying to accomplish is this: Iterate over a collection of XML files in a specific folder, loading each in turn into SQL Server. If the file has already been loaded, delete the records first before the load. After the load succeeds, move the file into an Archive folder. To accomplish this, I've set up a For Eac Loop container using the For Each File enumerator, and retrieve just the file name and extension into a variable. The first task in this is an Execute SQL task that uses a SQL DML statement to delete records based on a field in the table containing the file name (DELET ...Show All

• kkennedy1008 SQL Server 2005 Remote Connectivity

  I am an application developer with an existing system developer in SQL Server 2005 Express and Visual Studio 2005. I have installed the system I wrote at a physician's office, and it is working fine on a local computer utilizing XP operating system. There is a requirement to allow other terminals to connect to this XP terminal to execute the application, so I am exploring possibilities for connecting remotely from the other computers in the physician's office. I am running Virtual Machine for my client computer. I modified SQL Server 2005 on my main computer. I enabled both TCP/IP and Named pipes in the SQL Server Surface Area Configuration utility. I also set SQL Server Browser to start automatically. The local application ...Show All

• barkingdog SQL CLR Stored Proc Reads

  I have written a same stored proc in TSQL and SQL CLR which basically takes an input xml and returns xml document. In SQL Profiler, I am getting reads value about five times more for the CLR. Does anyone has any idea why the CLR is doing more reads than TSQL Thanks in advance. Can you show us some code   Hard to say without knowing what it is that you're doing!   -- Adam Machanic Pro SQL Server 2005, available now http://www..apress.com/book/bookDisplay.html bID=457 --     < Sequel2k5@discussions..microsoft.com > wrote in message news:dc077a90-5e82-48b6-b0c1-914480aad1eb@discussions.microsoft.com ... I have written a same stored pr ...Show All

• Nobody1234 Can unpivot help me here?

  HI, I have a source that looks like this: Col1 Code1 Amt1 Col<n>.....Code2 Amt2.... ABC XY 10 FR 345 What I would like to have is this: Col1 Code Amt Col<n> ... ABC XY 10 ABC FR 345 I know I could achieve this by using SQL and UNION ALL: SELECT Col1, Code1 as Code, Amt1 as Amt, Col<n>... FROM <mySource> UNION ALL SELECT Col1, Code2 as Code, Amt2 as Amt, Col<n>... FROM <mySource> But I was wondering if the built-in unpivot transform could do the job here. Thank you Ccote No, it won't. UNPIVOT would help if your data looked like this: Col1 Col<n> XY_amt FR_amt ABC .... 10 34 ...Show All

• TigerPhoenix query sql server table attached in Access

  Hello, I have attached a Sql Server table in an Access database. This wasn't a problem, I can open my attached table from Access, use it in queries. Now I'm trying to open it from an asp page, for example : SQL="SELECT * FROM myAttachedTable" Set Rs= server.createobject("ADODB.Recordset") Rs.Open SQL, myAccessConnectionString , 3, 4 I get : Microsoft OLE DB Provider for ODBC Drivers error '80004005' [Microsoft][Pilote ODBC Microsoft Access] ODBC -- failed connection to 'My SQL Server'. again if I enter the same sql query directly in Access it all works. What am I missing Thanks a lot This is very likely a permissions or a delegation issue. If you ...Show All

• dsandor Excluding Export Options on Report

  In the rsreportserver.config there is a <ExcludedRenderFormats> element, which allows you to list the rendering formats that are excluded from the export option for all reports. Is this the only way you can change this i.e. can you change this through Management Studio or Report Manager, and also can you exclude the export options for a single report rather than having to do it for all reports Thank you Hi, no this is a serverwide property which can only be administered through the "XML-Interface" <:-) HTH, Jens Suessmeyer. --- http://www.sqlserver2005.de --- To confirm the "XML-Interface" being the rsReportServer.config file ...Show All

• OZIanD Adventureworks db

  I installed adventureworks db onto sqlExpress. It does not show up althought it is in the: C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data I look up the database in the Microsoft SQL Server Management Studio Express, But it's not visible. I've turned the service off andon, but it still is not available. Any ideas what has happened. john welte The installation of the sample database does not mean that you attached it for use in SQL Server. You will need to use the sp_attach procedure or the command which can be found in Management Studio under Right click the Server > Attach Database > specify the location and the name of the database file > Done :-) HTH; Jens Suessmeyer. --- http://www.sqlserver2005.de ...Show All

• Whoisit SQLAgent forces a Cmd Window

  I am installing SP4 on a previously installed SQL2000 Ent running on a Win2000 Enterprise SP1. When I start SQLAgent, a CMD window with no statements opens up. If I close the CMD window, the SQLAgent stops. So, to keep SQLAgent running I have to minimize the CMD window and leave it up all the time. Is there any way I can run the SQLAgent without having the CMD window open all the time I am clicking on Start in the SQL2000 Service Manager when SQLAgent is showing as the service to start. You think the exe for SQLAgent is not registered as a service Is there any way I can fix it What did you start, the service or the executable SQL Server Agent is implemented as a servcie and should be started as this. It can be found in ...Show All

• Walter30140 MDX Dynamic Query - how to convert a string to date using Left, DateValue, Format...

  Hello, Basically I have a string parameter for a date (and I do not want to use DateTime parameter type). The entry format is yyyyMMdd. I would like to parse that, transform it in a date, subtract "delta" months of it (where delta is another report parameter), and format is as yyyyMM to get the month key, and finally take the descendants of it. I hardcoded the following query in a test dataset, which works fine: MEMBER [Measures].[testDate] AS ' Format( DateAdd("m", -1, DateValue( Left("20060915", 4) + "-" + Mid("20060915", 5, 2) + "-" + Right("20060915", 2) ) ), "yyyyMM" ) ' Also, the following dynamic query works nice too: set [Ship ...Show All

• Thona SQL Reporting Services With AS400/DB2

  Hi, Has anyone successfully created a RS 2000 report using ODBC datasource on an AS400/DB2 connection If so what driver did you use to connect to DB2 for the ODBC setup I tried to use IBM iSeries Access for Windows (11.00.02.00) but when I created a dataset for the new report to do a select statement , it gave me an error IBM iSeries Access ODBC Dribver UDB SQL0204 - .....*File not found. Thanks in advance for any info. Apparently IBM driver doesn't work well... This has some potential solutions: http://www.thescripts.com/forum/thread183001.html cheers, Andrew ...Show All

• kevinrj0314 SQL Query Analyzer: Open a new query window with script

  I'm trying to figure out how to open a SQL Query Analyzer window from another window. Yes, I know about Ctrl-N and Ctrl-O. However, we have 25+ SQL servers running upwards of 1,000 databases. I want to write a user-defined function (or something similar) which will read configuration information out of one specific database that describes all of our other databases, and open a Query Analyzer window directly connected to that box. I should be able to be sitting in Query Analyzer and open another window, but I can't figure out how to do this. We've written a simple C program that uses OLE to open a Query Analyzer window, but it's a pain to have to launch a separate program to read the database and then have that open another Query Analyzer w ...Show All

• Kur Lan Subquery Question

  I'm trying to write a very specific subquery. My table data looks like this. jobname1 complete destinationA jobname1 complete destinationB jobname1 not-complete destinationC jobname2 complete destinationA jobname2 complete destinationB jobname2 complete destinationC jobname3 complete destinationA jobname3 complete destinationB jobname3 complete destinationC The result I need is the job name of all unique job names whose records are ALL complete. So, in the case above, I need the names of jobname2 and jobname3. Here's what I have so far, but itt is so far returning no results: Select jobname from table where NOT EXISTS (Select * from table where table.job_status != 'complete' ...Show All

• turczytj SSRS Slow waking up on first report

  Greetings, I continue to see this similar post all over the place, but no resolutions. We have SSRS installed and operational in production. There are reports that get accessed from either an aspx page or from the built in IIS site for SSRS. First time access is the performance problem. Here are two scenarios: 1) from your browser, pull up the front page to SSRS: http://myserver/reports . This simple operation will take about 2 minutes to come up. Drill to the report in question and it runs fairly well first time through. Throw a different query at it and it runs faster. If you wait a while (maybe 15 minutes +) the whole thing will spin back down and take 2 minutes to come up again. If you exit the website and come back ...Show All

• NLCandyman Strange ID created by the Subscriber

  Hi all, I have a table (Products), and the table is like this: - Product_ID (Primary Key), Not Null, Identity Specification: Yes, Increment 1, seed 1 - Name ... - Price ... When i insert in the publisher (PC, Sql Server 2005), it inserts correct ex: |Product_ID | Name | Price| Inserted by | | 1 | P1 | X | Pc | | 2 | P2 | Y | Pc | Then i replicate to Subscriber(Pocket PC, SqlMobile ), when i insert in Subscriber, it creates strange and big id's, is it normal is this to avoid conflict inserts Ex: |Product_ID | Name | Price| Inserted by | | 1 | P1 | X | Pc | | 2 | P2 | Y | Pc | | 3000 | P3 | Z | PPC | ...Show All