hi i want to create the sp to recreate the indexes of all the table. here is what i need: CREATE UNIQUE CLUSTERED INDEX [table_name$32$0_idx] ON [dbo].[table_name$32$0] ([bucket], [f2], [f5402], [f47], [f8]) WITH DROP_EXISTING in order to get all the <table name> (columns) i use s...more >>

Hi there, what is the disadvantage of using select * from table in a cursor even if u have to select all the columns in that table? Is that going to affect the performance of the query or memory that needs to hold the data? Please explain. THanks John ...more >>

I need to delete records in 1 table based on matching data in a 2nd table. Below is my syntax. Is this a good syntax for this? DELETE dbo.InternalCSIAnswers FROM dbo.InternalCSIQuestions WHERE (dbo.InternalCSIAnswers.InternalCSIID = dbo.InternalCSIQuestions.InternalCSIID) AND (dbo.Interna...more >>

Using a web interface, I am placing text into a SQL database. From time to time, I would like to synchronize one of my other tables in the database with the table that I am inserting content into. What is the SQL command to synchronize these tables? I will be initiating this command through a we...more >>

I've shceduled a job under sa account (I've changed the password though:)),this job access local folder of the box running SQL server.I have added sa to the sysadmin role and it dose have access to the local folder ..How is it possible as *sa* is a sql server account and how it has the enoug...more >>

We have a SOX requirment that disallows the storing of passwords as plain text... they must be encrypted in the database. Does SQL Server handle this natively? ...more >>

exec xp_sqlmaint N'-D database1 -BkUpDB c:\database1.bak' I login as sa, I ran the above command, I got errors (28 row(s) affected) Server: Msg 22029, Level 16, State 1, Line 0 sqlmaint.exe failed. What happen? I don't know what this error is.. Do I have a wrong syntax? ...more >>

Hi All, How can I check the records that are exactly ONE year OLD, base on ONE date field? Suppose the table is Emp and the Date Field is HireDate. Then how do i check the employee those are ONE Year OLD. (> 1 Year) Shall I Use the DateDiff or DateAdd for Exact Result. Please suggest with...more >>

I'm trying to use the following case statement to return a date field if it's not null, otherwise return 'N/A', but I get the error: Syntax error converting character string to smalldatetime data type: CASE WHEN AlreadyReceivedDate IS NOT NULL THEN AlreadyReceivedDate ELSE 'N/A' END AS [Date...more >>

Hi, We have a requirement here to search on the last 6 characters(digit) of the primary key. The data to search span multiple tables. Is it possible to improve the search performance by using some sort of indexing on this field in SQL Server? Thanks CJ...more >>

Hi! I am using SQLDMO.SQLServer object to manage my database. There is BeginTransaction method. My question is: 1. In which database's context SQLServer.BeginTransaction starts transaction? 2. How can I know/change current context in which SQLServer object works?...more >>

We are experiencing the following deadlock error on a SQL Server 2000 system: "Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction." I've done some research to see how I can track where the dea...more >>

I am trying to create a Job that will run a stored procedure i have scripted and the email the the results to certain managers in my company for analysis purposes. The procedure is fine i was just wondering how i would get the results formatted and into an email. Do i use the SQL Mail?...more >>

Hi! I am using SQLServer DTS object to manage my database. There is BeginTransaction method. My question is: 1. In which database's context SQLServer.BeginTransaction starts transaction? 2. How can I know/change current context in which SQLServer object works?...more >>

I have 2 tables: CallLog and Assignee Both tables have a field named CallID. It is the primary key for the CallLog table. The Assignee table may contain multiple assignees for the CallID. The Assignee table has a field named ResolvedOrder that increments with each assignee that has the same ...more >>

I have a multithread component which call to a stored procesure to do some data processing. But the time for excuting the stored procedure increased dramastly with the thread number. If the stored procesure is very simple (only have insert statement ), it does not have this problem. Any reco...more >>

Hi all. i've written a portion of sql code with a dtetime parameter that run very very fast on a sql window, but when i create a function with the same code the execution time is extremely long! to recreate the same speed i found that i must declare a local variable inside the scope of t...more >>

Anyone know of a way of splitting a CamelCase sting into a spaced Camel Case string in T-SQL? This sounds as if it should be trivial to me, but I can't for the life of me think how to do it... Hmm... perhaps VBScript can do it easier... Thoughts? Dave ...more >>

Folks, I need to zip my log files (about 30) and send out the zipped file via email. There does not seem to be a out of the box functionality in Windows XP Pro. I am using SQL server 2000. But again, I might be wrong. The T-SQL stored procedure does all the processing and records the detai...more >>

Hi, I am populating an excel file using insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=c:\file.xls;', 'SELECT * FROM [Sheet1$]') select col1,col2 from table1 but every time I execute the command it append to existing records. How can I delete the records in the ...more >>

MS SQL Server 2000 let's say there is a table ORDERS with a clustered index on (order_date, some other column). Also there is a non-clustered index on shipment_date. Since most orders are shipped within 3 business days, the data is stored almost ordered by shipment_date. Most rows for the same ...more >>

how do i make 'select distinct field1, field2, field3 from mytable ' return only unique rows for field1? tia, mcnewsxp ...more >>

Can someone help me understand why this statement *does* return 1 row of column value NULL, rather than 0 rows? Policy_id 'XX9999' does NOT exist in this table. SELECT max(colname) FROM mytable WHERE policy_id = 'XX9999' Without the "max()" qualifier, SQL returns 0 rows. I don't get it...more >>

We have all done the following: SELECT COUNT(*) FROM TAB1 WHERE TAB1.COL1 LIKE '%mystring%' I would like to replace the mystring with another column in the same table. In this case the StreetName is both a separate column AND contained in the StreetAddress column. EX. StreetAddress = '1...more >>

Hi, I need to write a code to transfer data from one column in a table to another column in another table. e.g. Table A. Table B uniqueid_c (primary Key) uniqueid_c A1 B1...more >>

Evaluating a date field Hi I am having problems filtering data based on a date column. I want to find all records where the date to day is before the date in the EndDate field or where the date in EndDate is equal to the date 1970-01-01 The default value for the column is 1970-01-0...more >>

i'm using sql2k. can i do a bulk isnert operation to a table with a primary key (identity field) on it? i suspect the dts pacakge didn't utilize the bulk insert because pk is automatically a non-clustering index, and bulk insert can only work on table w/o any index. in this case, what should ...more >>

I have a VIEW from which my application has been been retrieving data. I recently updated the VIEW to call a user-defined function instead of the built-in DatePart function because of additional requirements. Performance testing over an 800,000 row table revealed a degradation in performance...more >>

Ok, I create my view like this (for now is exactly what I need): USE tsNess2 GO SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON GO CREATE VIEW V1 WITH SCHEMABINDING AS SELECT t1.MemberId, t1.Pe...more >>

I'm in the process of converting a Access Database to MS SQL and have = made the memo fields in the Access database text fields in MS SQL. = However, when I use a SELECT statement with the text fields the data is = not returned. The memo fields are all under 4000 characters.=20 Anyone know wha...more >>

Hello everyone. I've been searching for the last few hours for a solution to let me SELECT INTO a set of variables instead of a table (the SELECT statement will only return one row). Any suggestions? Right now I am SELECTing each field seperately and storing it into a variable. But I assume...more >>

I have a c# application that runs off a local database. This application is used by marketers to keep track of their leads and appointments. Currently two marketers are using this application on laptops. Obvisouly the two marketers will have different data on their laptops. They need to share ...more >>

i have a name field which contains "juan dela cruz" . i want a select statement that can return the first group of letter before the first space to be the firstname and the rest after the first space to be all members of the the lastname. in this scenario i should have a return Firstnae="juan"...more >>

Hi! I'm new to trans-SQL. I wounder how I can add an user to newly created database? I've created a window-application that is using a SQL-database for reading and storing data. When I now want to distribute my application I also need to distrubute my database. Is the best way of ...more >>

Im making this stored procedure to return the age of the user to the web control but the problem is that DATEPART(). I can only Specify one and i need the age to to the exact format of mm dd yy but i cant get it into the Query so it can execute. And when I use the yyyy to return the Age it ret...more >>

Ok, When I did this to add the index on the view, the view is no faster at all...Did I do something wrong? : USE tsNess GO SET NUMERIC_ROUNDABORT OFF GO SET ANSI_PADDING,ANSI_WARNINGS,CONCAT_NULL_YIELDS_NULL,ARITHABORT,QUOTED_IDENTIFIER,ANSI_NULLS ON GO CREATE VIEW V1 WITH SCHE...more >>

I have a table with the structure as below. I am running the query 'update DTH_StatementMaster_PREP set PrintIndicator = 1 where BatchID = 'BTCH00000000030'. There are only 25,000 records in the table and all of them qualify for the update. Since there is a clustered index on the predicate (...more >>

Hello All, BOL states that :- Before you create a view, consider these guidelines and one of the guideline is "The query defining the view cannot include the ORDER BY, COMPUTE, or COMPUTE BY clauses or the INTO keyword." But we can include Order By in A view if we use TOP like this crea...more >>

Hi, I've got a stored procedure defined like this : create procedure Proc1 as select Row1A,Row1B from Table1 select Row2A,Row2B,Row2C from Table2 go Could someone give me the syntax to call Proc1 from another stored procedure and to store the 2 resulting recordsets in 2 temporary tables ...more >>

Ok, This is so slow, and I've taken the advice of others to try to get it faster, but I think I'm in a "writers block". SELECT t1.MemberId, t1.PeriodID, SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount WHEN t2.amountTypeId = 23 THEN -t2.amount END) AS Purchase, SUM(CASE W...more >>

If I had a table of holidayItems (a holiday is made up of one or more holidayItems) that contains the columns, Id, HolidayID, StartDate EndDate, and I wanted to write a sproc that returned a table of clashes i.e the Holiday ID,the Id of the holiday it clashed with and the dates the holidays ...more >>

Ok, I have a web app that calls several very complex (at least to me) sql queries like this for example: SELECT t1.MemberId, t1.PeriodID, SUM(CASE WHEN t2.amountTypeId = 7 THEN t2.amount WHEN t2.amountTypeId = 23 THEN -t2.amount END) AS Purchase, SUM(CASE WHEN t2.amountTy...more >>

Is it possible to covert a smalldatetime in a select statement using a regular expression of some kind? My dates are stored as: 7/30/2005 6:58:00 PM I need to sort and group the dates by hour but I need to continue to store the minute. Can I envoke the use of a regular expression in a conve...more >>

I have a varchar column in a table that will be searched upon. (SQL 2000, standard edition, varchar(35)) Whats the best indexing options to use on this ? Cheers...more >>

I have a problem trying to work out the insert logic for an insert statement which inserts the rows from one table (Parcel1) into another table (Parcel2). Bare with me for this as I know this mightn't people might say why are you doing this but its just an example i've drew up. Basically...more >>

Hi, When i insert a value into my stored procedure sql server will sometimes change the number. for example i insert 43.34 into a column of type float the number stored is 43.34000015258789. But for other values like 43.78 this problem doesnt occur. I'm hoping theres a simply solution to this, ...more >>

Dear all, I would need to know which is the equivalence for this type of data in ADO library: Dim params as ADODB.parameters set params = new ADODB.parameters ... params.Append cmd.CreateParameter("@name_1", ??????, .... Thanks in advance and regards, ...more >>

What's the logic behind that? I have a large table that has a field called "Deleted" to indicate that a record has been deleted. For various reasons very few records will be marked Deleted. If I do a select where Deleted = 1 it will need to scan the entire table for those few deleted records w...more >>

Hi, I have an Employees table. I want to group by Dept_ID, but I would also like to retrieve the values of one more column(Location_name with comma separated for Dept_ID's) like Dept_ID Location_name ID 1 New York, New Jersey. 1,2 2 California, Philadelphia, .... 4,5 3 ...more >>

From one procedure I call another procedure: CREATE PROCEDURE s_procFirst BEGIN TRANSACTION rezTran exec s_procArt INSERT INTO test SELECT '1',@@error IF @@error=0 COMMIT TRANSACTION rezTran else ROLLBACK TRANSACTION rezTran Insert never happ...more >>

I not sure I posted in right newsgroup or wrong but i need your help, thanks~ I am using ASP to write a web application, and which will called a sql 2000 stored procedure through ADODB.Recordset I can store all provided information correctly if I am type English, other language such as Trad...more >>

I have SQL server and many people with the same account access to this server from our network. They access through the registration in their client enterprise manager. Is there any option to get the IP addresses and times for connections created to my server in last 3 months for example? ...more >>

Hi, My problem is procedure sp_xml_preparedocument. Local variables cannot be text type. How to pass text to stored procedure? I saw somewhere undocumented function which works with pointers in Sql Server, but I cannot find it now. Does anybody have link? I use SS2000, SP4, Win 2000 Advan...more >>