Hi All, I have written some no of .sql scripts in sql server 2000. In oracle, I can save all those .sql scripts and call them from a Master scripts.Mean, I can execute the master script which indeed call & execute all those .sql scripts. Can we have the same functionality in sql server 2000...more >>

Dear All I am facing problem of recurring corruptions with sql 2000 server sp3a windows 2000 advanced server failover cluster. is it wise to apply sp4? or is it risky at this stage rgds Biju ...more >>

All, Pls do bother to read the following: Insert into <table name> ( col1, col2, ......., coln ) select ( <value>, <value>, ..........., XXX ...more >>

Hi there, I am trying to start the SQL Server 2000 in single user mode by using command prompt. The process starts succesfully until this point, "Recovery Complete". But it stops forever by stating "SQL global counter collection task is created". How can I resolve this? Thanks...more >>

Hi all, I need to alter a temp table to add column, and then access that column in next line.As it cant be possible in a same batch , I need to put a GO between the lines.But the problem is, these are the lines of a stored proc,and as soos as query analyzer(sqlserver2000) finds a GO,it takes...more >>

Hi, Where can I find the color code chart for tsql syntax statements? Like the blue, red, grey ...etc! I have looked everywhere. I can't believe someone not being interested in knowing what they mean? This is crazy! Pardon me, but I can't believe I couldn't find it on the internet. Thank...more >>

Hi, I wish to create a table like this: create table test { hostname varchar(10), whenTime datetime ) But I wish to declare the table such that by defaut: select * from test Always displays the results in reverse whenTime order, as though I had = typed: select * ...more >>

Hello. I was wondering, is it possible to write multiple SQL commands on 1 same line statement? Something like: USE master GO sp_grantlogin 'NETDOMAIN\John' GO sp_defaultdb 'NETDOMAIN\John', 'courses' GO USE courses GO sp_grantdbaccess 'NETDOMAIN\John' GO sp_addrolemember 'Professor', 'NE...more >>

I'm trying to write a quick program to allow me to move database files around. The idea is to quickly move from one drive to another. I'm using the Microsoft.SqlServer.Smo namepace's Server object to do the detach and attach. Everything is fine until I attempt to move the log files. When...more >>

SQL2000 Std Ed. Customers table 4 million records Users search by phone# - during periods of heavy use the cpu will peg to 100% and searches will take 20+ seconds. Here's the facts: - where cluase uses '... where fPhone like @TelNo ' - No blocking, stored proc uses (nolock) isol. lev...more >>

I have a large table where I'm doing millions of updates and inserts nightly, which are currently being throttled in chunks of about 10k to prevent long table locks. Since this table is being searched against 24x7 this is still causing some performance issues. I have been considering changing th...more >>

Hi, I'm trying to scramble the ssn#s within our database. I would need a 9 digit number to be converted into another 9 digit number in our dev database. Example #1: ssn: 123456789 converts to 987654321 Also there is a catch, there is a possibility that there could be duplicate ssn wi...more >>

I need help. I am fairly new in declaring variables & can be put into one SP. --> Update the [Status] aliased as OLD to the [NEW] value pulled from a CASE statement. [MyTable] table to update [NEW] (PULLS NEW VALUES BY USING CASE EXPRESSIONS) [OLD] (VALUE IN MYTABLE NEEDING UPDATING) [Ex...more >>

Hi, I'm trying to scramble the ssn#s within our database. I would need a 9 digit number to be converted into another 9 digit number in our dev database. Example #1: ssn: 123456789 converts to 987654321 Also there is a catch, there is a possibility that there could be duplicate ssn wi...more >>

How do I most efficiently remove duplicate elements from a string. Space will be word / character delimiter. eg. OriginalStr = 'A ONE B TWO C THREE A FOUR B ONE ABC A B C' CleanStr = 'A ONE B TWO C THREE FOUR ABC' Thanks, Rakesh ...more >>

Previously posted to sqlserver.xml (no response), so reposting here. ------------------------ BOL defines the format for OPENXML as: OPENXML(idoc int [in],rowpattern nvarchar[in],[flags byte[in]]) [WITH (SchemaDeclaration | TableName)] Is the "flags" parameter supposed to be a TINYINT or am...more >>

Hi there, Is there a way I can change my database name? If not, what is the best way of doing it? will sp_attachdb help?...more >>

Hi, I have a MS access program running on a sql2000 database and noticed that the dates are missing in one table named "tblwithdrawels", however the table also has a field named "refNo" in which thier is a numeric number. now the same field "refno" is also in a table named "tbltransactions"...more >>

Hi, I have a table full of records, some of which are duplicates and I need to go through the table and remove the duplicate records. My technique works, but it is extremely slow, and I was wondering if anyone can tell me a faster method. Here is a description of my curren...more >>

I am trying to get log shipping (on SS 2000) to work and have only - I hope! - one issue to go. When I am going through the wizard, I get the following error: “database temcmtest2 is not in standby mode. Temcmtest2 must be in standby mode for logging shipping to work”. The script t...more >>

I am getting an error since morning n my Log File. I really dont know why is it - SQL Server has encountered 1 occurrence(s) of IO requests taking longer than 15 seconds to complete on file [C:\Program Files\Microsoft SQL Server\MSSQL\data\tempdb.mdf] in database [tempdb] (2). The OS file ha...more >>

Hi, I am using SQL 2000, if it matters. Paging results when the table has a single-column primary key is easy. Consider: CREATE TABLE [dbo].[Person] ( [PersonID] [uniqueidentifier] NOT NULL , [Name] [varchar] (30) NOT NULL ) ON [PRIMARY] ALTER TABLE [Person] ADD CONSTRAINT [P...more >>

Can anyone comment as to whether this Technet article on BLOBs is still relevant to SQL Server 2005, or have the rules of the game changed for SQL 2005? Technet article: http://www.microsoft.com/technet/prodtechnol/sql/2000/reskit/part3/c1161.mspx?mfr=true ...more >>

I have a large table filled with product serial numbers. Similar to how a datagrid in .net works, I would like to be able to query the single column, but have it return the data in 3 columns. I have no problem having 3 unique column names returned. For example: If the data was 1,2,3,4,...more >>

We have a MS SQL 2005 server I have and Access2000 table with 9 million records. (HISTORY) I created a table on the SQL and linked it into an Access .MDB (HISTORY_OLD) When ever I use an append query to copy History into history_old I get errors. I tried to upload a million records at a ti...more >>

Hi All I am trying to make a pivot table with dynamic columns. I have included below all the queries i am using. How do I change the query so that the columns for Tr1, Amt1, [Description1], Duedate1, Tr2, Amt2, [Description2], Duedate2 etc. will display based on what is available instead o...more >>

I have downloaded two different ASP tutorials from Microsoft which require the use of SQL 2005 Express. In both cases I am getting the error: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be close...more >>

Hello all: I have a package that I am executing from code. The relevent snippet: Private pk As Microsoft.SqlServer.Dts.Runtime.Package (declared at form level) Sub ExecutePackage Dim app As New Microsoft.SqlServer.Dts.Runtime.Application pk = app.LoadPackage(PACKAGEPATH, Nothing) ...more >>

hello am a newbie here... i hope you could help me.. foxpro code goes like this, select id from employee into cursor CurEmployee * Select id from AppTable where id In (Select id from CurEmployee) in short am still studying on how to make a cursor using T-SQL your help is highly apprec...more >>

Hi, Is there an sql query I can use to return the following data.. january 2006 february 2006 march 2006 etc december 2006 Until now I have just been writing sql to add these values to a table variable one by one. Is there a query that will return all the 12 values at once and can ...more >>

Hi, We've migrated an Oracle table to SQL and old processes calling this table also needs conversion. One such SELECT stmt, shown below, extensively uses the INSTR Oracle string function. SELECT SUBSTR(ft_reference, INSTR(ft_reference,''['', INSTR(ft_reference,''CHECK #'', 1, 1), 1)...more >>

I need some query help and I would really appreciate any help. Below is my query: Select AdminAdvertiserList.Company, AdminAdvertiserList.City, AdminAdvertiserInvoices.[Contract Date], AdminAdvertiserInvoices.[Start Date], AdminAdvertiserInvoices.[End Date], AdminAdvertiserInvoices.Amount, A...more >>

my database is 90% full. how would I increase the size of the size? when the database full does it mean to increase or make a new datafile? ...more >>

Dear Sir, My db is sql2000, I have a table, which used as a queue for data that will be fetched by client application per 1 sec. The data is just to let user know what message has been processed. There would be 3x equipment controller applications(EID) that will do something , logging and i...more >>

<snip> CREATE TABLE Colors ( Color VARCHAR(32) ) GO SET NOCOUNT ON INSERT Colors SELECT 'red' INSERT Colors SELECT 'orange' INSERT Colors SELECT 'blue' INSERT Colors SELECT 'green' GO DECLARE @colors VARCHAR(1024) SELECT @colors = COALESCE(@colors + ',', '') + Color FROM...more >>

Is it possible to copy diagrams from one sql 2005 db to another 2005 db? I could do this in 2k by importing data from the dtProperties table to the target db's dtProperties table. what should I do in 2005? Thanks. -- moondaddy@noemail.noemail ...more >>

Hi all, I have created the following schema to illustrate my question: CREATE TABLE [dbo].[tblCustomerProperty] ( [cpcusID] [int] NOT NULL , [cpproID] [int] NOT NULL , [cpValue] [varchar] (50) COLLATE Latin1_General_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[tblCustomers...more >>

if we must have to update millions of records at a time, what is the possible ways to avoid table lock. Thanks Ken ...more >>

sql time out error when running sql query in asp.net how can i rectifive this error please any one help me by sam *** Sent via Developersdex http://www.developersdex.com ***...more >>

Hallo, I have a Stored Procedure with DECLARE myString varchar(MAX) Now when I set a text which is bigger than 8000 Bytes, it gets cut. Why? I thougt SQL 2005 has now varchar(max)? Thanks for any help! Regards Andreas Klemt ...more >>

I need to select from a datetime column based on time of day. I don't see any functions to do this. Did I miss a section in BOL? Is the best way to do this a function That takes the datetime, from and through values and return a yes or no? where IsInTimeFrame(DateTimeColumn, FromTime, Thro...more >>

I have a database where I need to change all period to post information back 1 year. There are approx 400 tables and most of them have a perpost field where the Period to post information is stored. I need to take this information back 12 months for all perpost fields. e.g. Perpost in APDO...more >>

Is there a way of documenting a database - like what was available in access - analyze, documenter utility ? i could just link up in access, just wondering if there was another way cheers mark ...more >>

Hello, In SQL Server 2005 there is the PIVOT command that you can apply to your SELECT statement to pivot data as you do in Excel. However, unlike Excel, in SQL you need to know the number and names of the column headings in order to pivot. Is it possible to dynamically pivot a result set...more >>

Hi group, I have a table: table1 if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Table1] GO CREATE TABLE [dbo].[Table1] ( [id] [char] (10) COLLATE Latin1_General_CI_AS NOT NULL , [remark] [ntext...more >>

I have two tables like Contracts Table Id LawyersName InitiatedBy 1 SING PETE 2 SING HAWT 3 HAWT PETE 4 HAWT PETE ContractDependents Table Id EMPNAME 1 SING 3 SING 2 HAWT 2 PETE Contract Table is related to ContractDepend...more >>

Hi: I'm trying to retrieve a large record and I'm getting the following error. Plz can anyone help... Regards, R Y Nayak Server Error in '/fostering' Application. -------------------------------------------------------------------------------- Could not insert a row larger than the pag...more >>