Hi all, Question: 1. If the database index size is large than data size then what are the impact of the database system? Data size = 12,789 MB Index size = 72,574 MB 2. What are the possiblilities can caused the index data increasing so huge? Thank you ...more >>

Hi, let say I have record: Year SerialA SerialB 1998 1 3 2000 3 2 1999 2 2 2001 5 3 1998 1 1 1999 2 1 2001 3 ...more >>

Hello all, My Table Schema: Id RunDate Value --------------------------------------------------- 1 06/01/06 00:00:23 2.3 2 06/01/06 00:00:25 5.6 3 06/01/06 00:02:25 5.7 4 06/01/06 00:03:25 5.8 ---- and so on for a ...more >>

Have any way to alter column name of table? Thanks ...more >>

Hello, I have this table users us_name, us_startdate, us_enddate mike 12/15/2006 12/18/2006 tim 10/20/2006 10/24/2006 Now I am looking for a SELECT für SQL 2005 where I get for each us_name all dates extra (like group by). Result table exmaple: row name date 1 mi...more >>

Hello, I built a maintenance plan and now the backups are failing: Why the database should be in single user mode during the maintenance plan? If it is mandatory how do i change from multiple users to single user mode just before the maintenance plan? Thanks -- Dom...more >>

I have a big problem now. I need to write a SQL statement to copy some records from a table to another table. At the process of copying, I want to update a field in both table. The field is to identify whether the record is the 1st, 2nd, 3rd, 4th, ... record that I've copied, which will be in ...more >>

I'm using the BCP OUT command to extract the contents of a SQL table to a text file. When doing this is there an parameter to format the data into a fixed format? Script being used: bcp.exe databasename.dbo.table OUT datafile -t "," -c -CRAW -Sservername -Uuser -Ppassword Thanks ...more >>

Three tables, Parent1 Child1 GrandChild1 Parent1Id Is Int PrimaryKey, identity Parent1 name Nvarchar50, contains a name Child1TableId is Primary key integer, identity Child1Parent1Fk Is foreign key Integer pointing to Parent1 Child1Name Nvarchar(50) Child1Memo Ntext - contains usualyy rtf...more >>

I have a table of Contacts (it is really a view but that shouldn't matter). There are many contacts per contract but I need a query that returns only 1 record/contact per ContractID. When there are multiply contacts it doesn't matter which one is returned. The table/view looks like this Co...more >>

The purpose of the SP below is to increment the field OrderInList used to order @TableName records while adding new records. If @TableName is empty 1 is added in the OrderInList field, else max(OrderInList)+1 is added. The SP passes the syntax check and executes w/ no problem in Query Analyzer...more >>

Hi friends, I m writing some stored procedures. Pls tell me, how can we use IF..ELSE, nested IF..ELSE in stored procedure. means i want to pass a int parameter to stored procedure & according to this value, we will execute my queries. eg if i=1 then insert, if i=2 then update or if i=3 t...more >>

Hi friends, I m writing some stored procedures. Pls tell me, how can we use IF..ELSE, nested IF..ELSE in stored procedure. means i want to pass a int parameter to stored procedure & according to this value, we will execute my queries. eg if i=1 then insert, if i=2 then update or if i=3 t...more >>

in sql server 2000+ is there some stored procedure that shows your current disk space usage? ...more >>

I have a "tagged" aplication, my model is something like: -Item- Id_Item Item_Name -Tag- Id_Tag Tag_Name -Item-Tag- Id_Item Id_Tag I want a stored procedure that returns me all items names in a column and in other column all tags (separated by commas) Item_Name ...more >>

I have a project where I'm trying to "patch" something that probably needs a rewrite. It currently has some code set in a dll that gets called in order to give the app calling it the appropriate code to pull info from a database. Rewriting it is not an option at this point. I had the id...more >>

I'm still a novice in Integration Services, and i need to calculate the time that our tickets are in a Pending Status. The problem that i'm having is that the Pending Start time, and the Pending End time, are not in the same record. The table that need to query is like the following Ticket_...more >>

I have looked everywhere for a definitive answer to this question but I have yet to see one. If you have XML data or Relational Data in Pure Text Delimited Files, what is the best way to automate the process of inserting that data into MS SQL 2005? Is the best setup to create an SPROC that ...more >>

I have been given a text file with a stored procedure script and I noticed a portion at the top of the script that I have never seen before. I tried googling aspects of the text in question but I was unable to find any additional information. The text in question is as follows: if exists (s...more >>

Hi! I'm trying to find a way to parse Transact-SQL. Does anyone know where to find a Transact-SQL parser component to integrate to my application? The more open sourced the better ;) ...more >>

Hello. I have an asp.net application which currently connects to a local instance of sql server without any issue. When I connect to a remote sql server (same database and userid and password are valid) there is an error indicating 'access denied.' But, when the same code and connection ...more >>

So, I was having just the most frustrating bug a few months ago, and my boss says, "Oh, yeah, you should never assign a new value to an IN parameter in SQL Server." He couldn't give me a reference -- I'm happy enough not to do it, and it solved that problem, but I really can't suggest to _other ...more >>

I am using the looping code by Allen Mitchell. The web link is below. http://www.sqldts.com/default.aspx?246 I had a package running last night to load multiple files. It was working beautifully. It uses the filesystem object to get all the files loaded, then archives to another folder when...more >>

Hi all, I am using Sql Server 2000. I need to create a very simple app which when clicked would fire the execution of a dts package on the sql server. The app itself would sit on the client machine. Is this possible? If so, how? Thanks in advance. ...more >>

Here is my query run on one server against a Linked server: UPDATE [Linked-Name].dbname.dbo.[Table name] SET TimeStamp = '1900-01-01 00:00:00' WHERE ISDATE(TimeStamp) = 0 I get the following error message: Could not open table '"dbname"."dbo"."Table name"' from OLE DB provider 'SQLOLEDB'...more >>

Is any concern to use the following approach instead of temp table create a normal table with column spid which stores @@spid always add where spid=@@spid when use the table I need to insert a big amount temporarilly into a table and temp table will eat all the memory.......more >>

Using SS2000. In an alter table statement,is there a way to check if a column exists before you try to add it? Thanks, -- Dan D....more >>

Hi, I have a table that has a field set to decimal data type and the following query needs to update it to a varchar type if it meets the criteria. Why am I still getting the "Error converting varchar to numeric" error? Thanks! UPDATE [Activities Data] SET [Activities Data].[SUMMARY...more >>

We are doing an upgrade in about a month and changing the account structure in one of our tables. I am trying to write a routine that will check to see if we are using the old format or the new format. So I wrote the following stored procedure: CREATE PROCEDURE [dbo].[sp_Account_Info] AS ...more >>

while (Select Row_Number() over(order By DateSearched DESC) as rownum from dbo.tblSearch where StaffID=@StaffID) >5 begin delete dbo.tblSearch where StaffID = @StaffID end Why does this throw this error? Subquery returned more than 1 value. This is not permitted when the subquery follow...more >>

Hi, I'm trying to use MAXERRORS option (-m switch) to force loading to continue even though errors occur, but the load stop at the first error occur, all batch is commited and no error is logged in error file (-e hint) here is the commande I'm using : exec master.dbo.xp_cmdshell 'bcp "DBN...more >>

Hi I have a table that consists of a single row for each error in an app, each of which contains a row date field. I need to figure out how to query the database in a way that returns the number of errors per date over the last 30 days. The catch is, I can't use a view. I'm thinking about usi...more >>

Let's say I want to have a Web page that will be showing live data change in a SQL table? Is it possible to have on SQL side some kind of listener that will propogate changes to the Web page? Thanks...more >>

I have a table with client id's and I want to be able to select ids between a-m then n-z. The client ids are similar to this: A01 A02 .. .. Z01 Z02 If I do select * from table where clientid between 'a' and 'z', the clients that begin with 'z' are omitted. How do I make sure that the ...more >>

Sirs/ Ma'ams I have an issue with an accounting database where hours (Units) are charged toward a project. If hours are charged incorrectly, they are backed out by accounting & the correct hours (Units) are charged. This will result in records residing for entry, reversal & entry again. ...more >>

Hope someone can answer this one for me: Can you use an extended property to customize an error message? Like, I have two fields that define a unique key constraint on a table. When an attempt to insert duplicate row data into that table, I get the standard, "Violation of UNIQUE KEY constraint...more >>

I'm designing a medical record system and am considering using XML in the database (native SQL Server XML datatype). I'm looking for advise and opinions as to whether my rational is good, and to get pros and cons of different strategies. The application is an ASP.Net 2.0 application writte...more >>

If i run the query with test as (select *, row_number() over (order by user_name()) as cnt from table1) select * from test order by cnt Will i always get rows in same order every time when I run the query? ...more >>

Hi, new guy here... I want to search multiple columns of a database table. So far my code looks like this... myCommand = new SqlCommand("SELECT lastName + '| ' + firstName AS Name FROM directory WHERE lastName = @lName OR firstName = @fName ORDER BY lastName, firstName", myConnection); //c...more >>

Hi all, I'm facing an extremly weird problem here. Hope that anyone will be able to help me... :-) First of all I'm running a SQL Server 2005 on a Win 2003 box. I'm having a table which contains several varchar-columns. Some of the rows in the table contains non-breaking space - that is row...more >>

SQL2005 I have a temp table that has three fields a varchar, an integer and a bit Name Value Credit 1234 96 1 what i want is to run a select statement that returns the following when Credit = 1 Name DebitCount CreditCount IsCredit 1234 ...more >>

Hi, I need to convert this Access code to a Sql Case statement, but I'm not sure exactly how.... IIf([Conversion Detail Table]!TXTDT<>0,DateSerial((Int(Left([Conversion Detail Table]!TXTDT,4))),(Int(Mid([Conversion Detail Table]!TXTDT,5,2))),(Int(Right([Conversion Detail Table]!TXTDT...more >>

Hi Friends, I have to define a new job, to take the backup automatically, at the SQL Server 2000. on the Local Network if i login using the Machine name (Only Default instance is installed), it works fine. But in the following scenarios it does not work - --- If i login using IP Addre...more >>

Hi, I'm using 2005's management studio, and I was wondering if there's anyway to script the "generate scripts - script wizard" so that when I want to take a snapshot of my database structure I can just run a quick script instead of having to use the wizard. The fact that it's a "wizard" leads ...more >>

We have been testing an application that makes heavy use of transact SQL functions and have noticed vast improvements. (14 hours SQL Server 2000 SP4) to (40 minutes SQL Server 2005 SP1). All testing was done on exactly the same hardware etc etc... The UDF's are performing string compar...more >>

Hi, I'm working with visual c++ and usign MFC ( ODBC ) . I have a problem with varbinary(max) type of sql server 2005 express. I can read a varbinary (n ) but when i try to read a varbinary(max) fails. CDBVariant cvarValor; m_tablaDeConsulta.GetFieldValue ( campo, ...more >>

Dear all, I have a huge table which stored 90 million records and my application need to select multiple records from it randomly. For faster access, I will add a pre-generated random seed to the table and assign an unique random number to it. Let say I need to select 100 records randomly ...more >>

After moving from SQL-2000 to SQL-2005 we have problems with sorting on websites: vwK2W: SELECT TOP (100) PERCENT .. FROM ... ORDER BY ... When viewed in Server Management Studio the sorting is fine when returning the rows. But when shown on web the sorting fails. It seems to be sortin...more >>

Hello there I have two jobs that run at the same time. after both jobs will be finished i need to run job3 how can i do that? ...more >>

If I would like to sum ( (A.total + sum(B.amount)) * C.ratio) group by part_id that provided table A: part_id, line_id, date, total table B: line_id, seq, amount How can I code this? Could anybody please help? Thanks in adv. SC ...more >>

Hello there is ther a way to know which sql Service pack is installed on my computer with code? ...more >>

My Database Log file is nearly 4 GB. When i ran DBCC SHRINKFILE(MyLog,100), it says "Cannot shrink log file 2 (myLog) because all logical log files are in use". When i change targent size to 200, it says "Cannot shrink file '2' in database 'DBName' to 25600 pages as it only contains 14048 ...more >>