May I get a sample DTS program to do the following task? Read the records from a table X and export the data part by part into excel files untill all records are exported. Eg. Export 100 records in each file. The file name should be like Samplefile_RecNo_1_to_100.xls, Samplefile_RecNo_101_...more >>

Hello All, I am having problems with the contention caused by checkpoints as I migrated from SQL Server 2000 to SQL Server 2005. The same code, the same database, tha same machine, the same load, but much worse performance. Do you know of any changes in the way checkpoints are performed in ...more >>

Hi I need some help to create a script that add a number to each entry in several tables. Hope some of you experts can help me ! Case: I have 10 tables . Each table have a column called "Runningnumber" (the column is of type varchar) Every table have aprox 5000 entries. In some way ...more >>

I have a user defined data types whcih depends on few columns of a table. Using SSMS, how can you find whcih columns are those? I tried looking the dependencies of that user defined data type using SSMS. However, SSMS only shows the tables as dependencies but not the columns of that table....more >>

I have a query in which an average function is used on one of the column in the select statement. Thw query looks liike. SELECT Column1, AVE(Column2) FROM Table1 Where Column3 = 5 Question I have is to improve the above query I have to create an index on Column3 and include Column1 in th...more >>

I have a column which is named Names It stores names like that Row1 -- Hans Michael Joe Natalie Hans Row2 -- Michael Jenna Hans Natalie Each name stored as name + space + name + space I want to query all my Names Column To get name counts. For example Query Joe should return ...more >>

I'm a bit new to SQL2005 SISS Packages. I need to reopen and modify a package I have saved to the database msdb database. How do I do this? thanks ...more >>

Yesterday, I applied SP4 & Hotfix 2187 SQL 2000 Ent. edition into production server.. after applying these, users complain that application is running so slowly, when I check, I found that one query blocking itself even though it is only SELECT, this behaviour has never happened prior to ap...more >>

I want to create a view that uses a GROUP BY on an int field that can return a sum of strings (varchar) in the grouped record. For example, I have started the view as follows: SELECT dbo.File_Mst.CheckedOut, dbo.tblStaff.LoginName FROM dbo.File_Mst INNER JOIN ...more >>

Hi, I would like to know if it possible to publish a view to a subscribing table in SQL2000. Any hints would be highly appreciated. Thanks in advance Christian ...more >>

I am trying to rename a file on our 64-bit SQL 2005 server using either CLR or xp_cmdshell MS SQL 2005 service is running as a domain admin account. The same account has been given full priviladges to the D: Drive. The same Account has been added to the local Administrators group on the s...more >>

On May 23, 3:27 pm, "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > > However, my remote server is a linked server, and as soon as I try to > > query a table with a "timestamp" field, I get this error message: > > > "OLE DB provider 'MSDASQL' supplied inconsistent me...more >>

Hi all, I have a SQLexpress database out at a location. Since there is no SQLAgent or option to create a maint plan, I need to create a script to backup my Databases. I created a backup thru my SQL 2005 and scripted it out for all the paramaters. I noticed that there is no longer remove ol...more >>

I am trying to copy record rows into a linked server via the following SQL syntax where XMLCol is an xml column type (note this is running in SQL Express SP2): INSERT INTO OPENDATASOURCE('SQLNCLI','Data Source=MACHINENAME\SQLEXPRESS;Integrated Security=SSPI').[MyDatabase].dbo.MyTable ...more >>

Hi all, I have a strange problem: during random parts of the day a particular query runs extremely slow. If I capture that query from the profiler and execute in my Mangement studio it executes in about 1 second as apposed to 30 seconds. Server resources are nominal the whole time. Any ide...more >>

I need to finish a report for a customer. Need a little query help please. The script is as follows. SELECT payhistory.number AS 'Acct', dbo.payhistory.datepaid AS 'Payment_Date', payhistory.paytype AS 'Paytype', payhistory.paid1 AS 'Amt_Paid', payhistory.fee1 AS 'Total_Fees', payhistory...more >>

On May 23, 2:28 pm, "Tibor Karaszi" <tibor_please.no.email_kara...@hotmail.nomail.com> wrote: > This is exactly what the "rowversion" (aka timestamp) data type is all about. > > -- > Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi ...more >>

I am trying to create a field where if records are added OR updated, the field is automatically updated. At this point, I really don't care if it is a date, datetime, or Guid type. I know that I can create a field of type "uniqueidentifier" where the Default value is "(newid())" and the IsRow...more >>

In SQL Server 2005, do we have to restart the server when create the new maintenance plan for system/user databases? Farrukh...more >>

Hi all, I'm joining two tables that have some of the same field names, like so: ParentTable ID Note DateCreated ChildTable ID ParentID Note DateCreated I then retrieve this data in a SQLDataReader (Using VB2005, .Net 2.0, MS SQL 2000) using a statement ...more >>

How do I join two tables on a date which is slightly different in either table? I have 3 tables which look like this: tInterest ( LoginId Int, DateInterested DateTime ) CONSTRAINT PK_LoginId_DateInterested PRIMARY KEY CLUSTERED ( LoginId ASC, DateInterested ASC ) tLogin ( ...more >>

On May 23, 11:13 am, "Krip" <a...@kynetix.com> wrote: > Alex, > > Works great - thanks for that. Timing wise, it's very close to the T-SQL > UDF approach. Here's what I have: > > select myReturn > , (select exp(sum(log(1 + myReturn))) > from Returns r2 > ...more >>

I'm trying to create a parameter list for date selections, today, yesterday, current week, last week, etc. Can someone point me in the right direction for calculating the current week ,Sunday to Saturday? I've tried all I know but cannot get the syntax correct. Many thanks. ...more >>

I have posted several times on this and have never quite gotten an answer that matched what I was asking (although I appreciate all responses). I’ve got a 2000 Server (SP4) sitting on top of Win 2000 Advanced Server SP4 that has 8G of physical memory. This is a dedicated box, i.e. all it i...more >>

I’ve got a 2000 Server (SP4) sitting on top of Win 2000 Advanced Server SP4 that had 4G of physical memory. Our sysadmins last night added an add’l 4g of memory so now there is 8G. I changed AWE and max memory with the following script: sp_configure 'show advanced options', 1 RECONFI...more >>

I am having couple of tables as follows. I have given the SQL query to create, insert and select. Please execute the following query. create table rsm_project(projseqnum int, projectid varchar(8),projecttype varchar(4)) create table rsm_projectstate(projseqnum int,region varchar(15),seqnum ...more >>

when a stored procedure is called, does it locks the tables on which it works or not. if not then is it possible to do so. in my problem a stored procedure uses two tables. first it adds a new row in table1 and then pick the identity column of table1 and insert it in table2. i am using IDENT...more >>

I have an insert trigger and I want to capture the user name of the user that's committing the transaction that's being captured. What's the best way to do this? ...more >>

Hi We use sp_OACreate in several places in our application to call a COM component that returns some tax information. Due to security concerns, we have been asked to look for an alternative method that does not use sp_OACreate . Is there an alternative that someone else has implemented ? ...more >>

I have trying to copy a file from one location to another server and am getting 2 errors. The first is Invalid drive specification (which i read that I should create a share) So I created a share on the destination server which was previously using a path (\\servername\folder\folder\) Now I...more >>

Given the sql code listed below, will the server recreate the execution plan EVERY time this sproc is executed? The sproc deletes data by a reference ID and I feed it the tablename, ID value, and Column to check so that this proc works for 99.9% of the tables in our db. But, will the ser...more >>

Store procs are compiled in DB. Say for example, code can be in the form of DLL / exe so in case of stored proc when its compiled in which format it is in DB. ...more >>

I am VB/VC++ developer. and some work in .NET. Mostly work on Web/Windows development. I have used databases as a back end and n-tierd programming. I am preparing for intvw. Someone asked me a simple question but I could not answered though I have worked on SQL Server 2000 and SQL Server 2005....more >>

I have a table with the following fields - Street, Area, County, State, Country (among other attributes). I need to get the first non-value for each of the columns for all the rows in the table. This will enable me to see if a particular address field is specified by the user or not and based ...more >>

I'm trying to create a function that splits up a column by spaces, and I thought creating a function that finds the spaces with CHARINDEX and then SUBSTRING on those values would an approach. I get an error saying that the I have an Invalid column 'Course_Number'. Not sure why but I am very ne...more >>

On May 23, 3:36 am, "Krip" <a...@kynetix.com> wrote: > I've got a query that returns compounded return for each row in the set, and > know that my method is doing more work than necessary. Since it's a query > that's run thousands of times, it could benefit from a speed improvement. > Could CTE...more >>

can we combine two if statements using AND. like this: if exists(select ...) and if exists(select ...) begin ...... ..... end ...more >>

I am trying to make a case statement work in a view. It is meant to deliver 0 if orig_miles is null and there is no matching record or deliver the value of orig_miles if the record exists. After using this code it still returns a null when the record does not exist. SELECT SUM(dbo.tblEvent...more >>

On May 23, 2:16 pm, "Russell Fields" <russellfie...@nomail.com> wrote: > Highlight the keyword(s) in a query window and press Shift-F1. Thanks but what about if the keyword is in a Word document? I'd like to be able to highlight the keyword and launch the BOL search. I can write the macro but...more >>

i want transfer data from source to desitnation so that i created source, conversion and destination. and then i execute one sp in source like(exec EM_GetAgentData '05/21/2007','05/21/2007') at that time i got so many errors 1)[OLE DB Source [1]] Error: There was an error with output column "...more >>

On 23 Maj, 15:19, ML <M...@discussions.microsoft.com> wrote: > The most likely cause IMHO is the difference between the way the values for > the modification are prepared. > > In statement 1 the preparation is done inside the update statement - the > database engine has to calculate and store t...more >>

Is it possible to launch SQL Server 2005 Books OnLine (BOL) supplying a keyword, rather than open BOL, click the Search tab, type/paste the keyword and hit Search? Yes, I'm lazy :) Thanks. ...more >>

Hi. I've been trying to figure this out for a couple weeks now and have = posted various pleas for help, but so far I've not been able to find the = magic I need to make this happen. This can't be too uncommon of a = scenario. Certainly someone has done this before. What I'm trying to do is th...more >>

Can anybody tell me why sql statement 1 is about 7 times slower than sql statement 2? sql statement 1: declare @count int, @starttime datetime, @endtime datetime set @count = 0 set @starttime = getDate() while @count < 1000 begin update customers set companyname = 'Alfreds Futterk...more >>

Hi all, I have large tables (100M rows) that I want to join on the basis of 4 or 5 variables, using outer joins. I would like to speed up this process by creating indexes, based on the join variables. Since it concerns static tables (no further data will be added), I was hoping to save some...more >>

Hi There, Is there anyway can i see the out once the sql executed, i've the following code code in my procedure, running it from query analyser print getdate() delete from Holdings print getdate() exec InsertCashToHoldings print getdate() exec InsertIStockToHoldings print getdat...more >>

Hi, We have written quite a few stored procedures that can take up to 30 minutes to run, these are called from a vb.net application. However, when running it makes the application look like it's hung. Is there any way to provide feedback to the vb.net application so we can give the user...more >>

On May 23, 1:25 pm, Jean-Nicolas BERGER <JeanNicolasBER...@discussions.microsoft.com> wrote: > Hello, > I've got a serious performance issue with a stored procedure that recompiles > on each call. > This stored procedure doesn't deal with temporary tables, but has got table > variables and cur...more >>

Hello, I've got a serious performance issue with a stored procedure that recompiles on each call. This stored procedure doesn't deal with temporary tables, but has got table variables and cursors using these table variables in its select statment (but with the KEEPFIXED PLAN hint). This sto...more >>

Hi, I have my database plan set such that transaction logs are to removed after 3 days. However, they never get automatically removed. What should I look for to resolve this. thanks Jack...more >>

I've got a query that returns compounded return for each row in the set, and know that my method is doing more work than necessary. Since it's a query that's run thousands of times, it could benefit from a speed improvement. Could CTE's do the job? Here are the details: (Simplifying struc...more >>

I stumbed upon a typo in SS2005 BOL: In the followoing example, BEGIN and END define a series of Transact-SQL statements that execute together. I wonder how it got away with spell check. Do they ever spell check? ...more >>

Hi, I do a lot with optimization on SQL2000/2005 and I noticed something: LEFT JOIN with WHERE clause is in many cases 10 times faster than INNER JOIN on the same data? SELECT * FROM table a INNER JOIN table b ON a.ID=b.ID is usually much slower than: SELECT * FROM table a LEFT JOIN tabl...more >>

CASE WHEN tVandvaerk_Aflaes.Vandm3 IS NULL THEN NULL ELSE (SELECT SUM(Vandm3) s FROM tVandvaerk_Aflaes WHERE Mnd=tVandvaerk_Aflaes.Mnd AND Aar=tVandvaerk_Aflaes.Aar) END AS RaavandM3 is to be part of a GROUP BY. But how is it supposed to look in the GROUP BY? If I take till "END" I get the err...more >>