I have several files about 5k each of CSV data I need to import into SQL Server 2005. This used to be simple with DTS. I tried to use SSIS previously and it seemed to be about 10x as much effort and I eventually gave up. What would be the simplest way to import the csv data into sql server? Ideally, the tool or method would create th...
I have 2 temp tables #temp1 and #temp. Both have a key and date columns. Both have around 25k rows. And I'm left joining them on the basis of the key and date which are unique on all rows. It's taking around 4 minutes for this join to complete. Is there any way to speed it up or any alternative methods? ...
I have a database (Sql Server 2005) where there are dozens of tables, each of which has a number of columns (on average 10-20) with datatype set to nvarchar(max). This is absolutely killing performance (some of these columns are being used for joins and some of the tables have 100K+ rows). I would like to change all of these columns to b...
Hi, I have renamed a table using Exec sp_rename 'table1','dbo.table_new' the table got renamed but when i do select * from dbo.table_new, its saying Invalid object name but when i do select name,* from sysobjects where name like '%dbo.table_new%' i can see the object exists. How do i view the table now ?Do i need some right ? ...
I have a reasonably complex XML document which I want to flatten down to tables in SQL Server 2005. I don't want to have to code each field and table mapping by hand. The data is going into the staging area of a data warehouse so it doesn't really matter that much how the tables are structured. So far I have considered, and rejected t...
I have a stored procedure that queries some data and emails it, I'd like to change the procedure so that any numbers in the thousands get displayed with commas like 1,000. I guess the best way of doing this in SQL is to convert it to a string and add the comma? Thanks ...
I just recently moved a SQL DB from SQL2005 32 bit --> 64 bit. I am having an issue connecting to Oracle using the OraOLEDB.Oracle Provider. I was able to install Oracle 10G Client , ODAC 64 bit. I was also able to add a linked server to the Oracle instance. I am able to run a query using the linked server name directly: SELECT top 1...
I'm trying to test a developer's application against a SQL Server 2005 database (80) instead of the normal SQL Server 2000 database that it would hit. Is anyone aware of issues that could cause this error that are related to SQL? 'DateInterval.minute' is not a recognized dateadd option. Description: An unhandled exception occurred dur...
Some of the tables in my SQL Server 2005 database is named as lu.[TableName] instead of dbo.[TableName]. What does lu stand for? Note: I tried google already ...
I recently took over a project and they have a SQL job setup to run every three hours which rebuilds the indexes found within the ASP.NET Membership database tables. This seems pretty high, to rebuild indexes 8 times a day. I get about 2000 new users every day, and a total of about 2 million registered users. What would you recommend ...
At my work we have a small database (as in two hundred tables and maybe a total of a million of rows or so). I've always expected it to be quite fast in the order of several ten of thousands insertion per second and with querys taking milliseconds once the connection is established. Quite the contrary we are having some performance pro...
Hi, I have a table that must reference another record, but of the same table. Here's an example: Customer ******** ID ManagerID (the ID of another customer) ... I have a bad feeling about doing this. My other idea was to just have a separate table that just stored the relationship. CustomerRelationship *************** ID CustomerID ...
I am currently not in a location to test any of this out but would like to know if this is an option so I can start designing the solution in my head. I would like to create an insert trigger on a table. In this insert trigger, I would like to get values from the inserted virtual table and use them to UPDATE the same table. Would this w...
When I execute a stored procedure first time, it (for example) takes 3 minutes to execute. Once it is precompiled, it takes 1 minute to execute. After 3 months (without using the SP further), when I try to execute it again it takes 3 minutes. My question Is the execution path stored on stack? Or it is due to some other factor?. Corr...
Someone sent me a MDF file that was created in SQL 2008. I have SQL 2005, and the "Attach" function is rejecting the MDF file. How can I import this file? ...
Hi , I am using SSIS 2005 for creating package. I am having a requirement where i need to get data from databse store it in adestination and retrieve only email ids from that store nad send mails to each and evry id that i have received. I am using Expressions in the send mail task where i am getting the mail ids from the record set stor...
Hi. we have a customer that's been experiencing some blocking issues with our database application. We asked them to run a Blocked Process Report trace and the trace they gave us shows blocking occurring between a SELECT and UPDATE operation. The trace files show the following: The same SELECT query is being executed at different iso...
Hi Folks, I've some work to do on a largish database which basically requires calling a stored proc with different parameters for every value in a table (approx. 20k entries). I want to do this without blocking on that table or the other tables involved for each loop for the time it takes the whole process to run. Is is possible from wi...
I'm in the unfortunate situation of developing an app using SQL Server 2005 in the development environment but SQL Server 2000 on the production server. I have a fairly complex SELECT query which works fine on the development/test server but falls over in the production environment: SELECT tbl_questions.Question, tbl_questions.Question...
Hi, Please have a look at the below SQL code. DECLARE @RET TABLE(OID BIGINT NOT NULL,rowid bigint identity); DECLARE @ResultTbl TABLE(OID BIGINT,sOID BIGINT,partkey bigint); DECLARE @PATOID as VARCHAR(4000) SET @PATIENTOID= '95,96,192,253,110,201,201,83,87,88,208,208,208,208' INSERT INTO @RET SELECT OID FROM dbo.FGETBIGINTLIST(@PATOI...