I wrote the following statements.. set @sqlstring = 'select * from employee' declare cursTemp Cursor for @sqlstring .... in sql server it is giving error.. what is wrong with this? How can I use string variable for declaring cursors? Thanks Chandra ...more >>

Where can I obtain the the same values that SQL Server 2000 Enterprise Manager display in the Space Allocated for Data: and Transaction log space: for the corresponding items Total, Used, and Free disk space? Please help me find the Total, Used and Free disk space from the EM GUI so I outp...more >>

Hey i am planning to use the BULK INSERT to copy the data into my Server. The data is being written to the file by external application. But i want the data that is being written should be deleted after the insert. Can BULK INSERT statement support such option to be specified. other wise...more >>

Hello, I hope this is an easier answer... I have a datetime column that I want to return and empty string ('') when the value in the row is NULL. I've been trying to use a CASE statement to make this happen, but it is not finding the NULL value. SELECT CASE WHEN LastLogon_Date = NULL T...more >>

I often founds 2 recommendations that sounds contradicting to me, regarding what types of columns should be chosen for a clustered index (in OLTP environment with lots of inserts): 1)avoid identity column, it will cause inserts to be slowed since they will compete the same disk area at the end o...more >>

guys, i've got a quick question on uploading a .txt or .csv file to a table in sql server 2000. i had been using a Bulk Insert to a dummy table where all columns are varchar. then selecting that table and running it thru a for each loop in an asp.net application and attempting the conversion ther...more >>

I am looking for anyone's opinions on the BEST SQL Server books out there. I want to invest in a book and I am posting out to the experts to hear what you people have to say. I appreciate your opinions. Thanks!...more >>

Hello, I'm trying to join results from two tables and still don't understand why the following statement doesn't work: SELECT rating FROM (SELECT DISTINCT TOP 10 bp.rating, bp2.Cnt FROM businesspartners bp INNER JOIN ...more >>

Hi, I have a table like this: Year OfficeID Revenue -------------------------- 2005 1 13 2005 2 14 2005 3 12 And about 100 0other rows. What SQL statements can give me the following result: Year Office1 Office2 Office3 .......... ------------------------------------------- 200...more >>

Hi, I have this SP that the User is selecting multiple items to send in as Parameter to a SP. I am having trouble sending the parameter to the SP This is what I am sending 'SAT','SRO','TRO','REG' @StrType Varchar(25) --Parameter SELECT Sh.ShiptoName AS [Cust Name], Req.RefN...more >>

How do I programmatically tell if my recordset is open? ...more >>

System is "MFG" Custom jobs. Tracking customer's last order and looking for changes they make in the job detail table as compared to order before. Table schema: OrderID, line#, Item, QtyReq I need to verify the last order and if it changed with the clients prior order. I have 6 line i...more >>

I've been struggling over this one and its kicking my booty. Given the following data i wanna select a row for each type: Type Specification More_Details 1 SPEC 1 DET 1 1 SPEC 2 DET 1 2 SPEC 3 DET 2 3 SPEC 4 DET 3 4 SPEC 5 DET 4 5 SPEC 6 DET 5 ...more >>

Would I be better served (ie faster execution & general database sexiness) using the dreaded, hated, and loathsome cursor or to attempt to write a series of stored procedures (6-12) to work with result sets of matching criteria? I am importing a large number of csv's to update existing informa...more >>

I'm working with a DBA that wants to have the PK for a personnel table be a uer's network ID. I asked him what happens when the network ID changes, and (even though there are tables that use it as a FK) he didn't seem to think it was going to be a problem Now the way I was raised as a DBA, ...more >>

Hi, I would like to use Northwind as an example In order to calculate the percentage of Freight Charge for each customer, I have the following SQL ----------------------------- select CustomerID, sum(Freight)/(select sum(Freight) from orders where orderdate > '7/5/1996' and customerid > ...more >>

Hi, Is there an equivalent to the TRIM() function in SQL Server to remove any leading or trailing blank spaces in a string variable? Please advise. Thanks, -Joe ...more >>

I would create a query to output the percentage of tempdb data file used space and tempdb log file used space. Please help me with this issue. ...more >>

I want to create a function which can convert text to a table... let's said @text='1,2,3,a,b' select * from ConvertTextToTable(@text) it will return key -------------- 1 2 3 a b Note: parameter must be text, not varchar. ...more >>

Hi, I have been using the SQLXMLBulkLoad object without any problems as follows: Dim objBL As New SQLXMLBulkLoad objBL.ConnectionString = "provider=SQLOLEDB.1;data source=SOURCE;database=DBNAME;uid=XXX;pwd=XXX;" objBL.ErrorLogFile = "SQLBulkLoad_Error.log" objBL.Execute "xxx_mapping.xml",...more >>

We upgraded to SP4 last night. Prior to that upgrade, a specific function worked fine (since 2003 as a matter of fact). Now, if I create a view using Enterprise Manager and call this function, I get an erroneous error message about needing to convert a data type. If I write the same query i...more >>

Hi, My programm (24/7 - VB6) is connected to a MSSQL Database with only one table where data is stored with a time_stamp field as index. Automatically the software updates each 2 minutes the table and with the same frequency querys and display data for the last 3 days. I have also one functio...more >>

I know I can split up data and indexes onto different file groups, but can I then move the index file group to another server? if so, what are the pros & cons? thanks in advance!!...more >>

We have an application that uses Access databases and archives its data every year. Then there is the current year's data is an Access database. I have imported the prior years data into their own table in a SQL database. I've set up a DTS job to run every day that will import the current Ac...more >>

Hello, I have a backup plan as below: A. Everyday midnight Step 1. USE myDB BACKUP LOG myDB WITH TRUNCATE_ONLY DBCC SHRINKFILE(myDB_Log, 1) Step 2. USE myDB SET @db_backup_path = @db_backup_path + 'myDB'+ '.bak' BACKUP DATABASE myDB TO DISK = @db_backup_path WITH INIT B...more >>

I am doing a compare last history query that I'm seeing only a cursor as a way to do. Data to return: custID, CustRegion, # orders, Total volume, #monthsExpectedUse 1) Look at all orders by customer within a date range. ( I get col 1) Greates cursor 2) Establish prior order per custome...more >>

What is the best practice for using default parameters in a stored proc? Is it using dynamic sql based on the value of the parameters passed in? Thanks, Jason ...more >>

How do I modify the statement listed below to give me the date with the time of 12:00 AM. declare @Yester_day smalldatetime set @Yester_day = (select getdate()-1) print @Yester_day ' Output 2005-08-30 10:48:12.127 ...more >>

My problem can best be described using the Northwind db as an example. I would like to select all Customers that have never ordered a particular Product. Thank you. ...more >>

Ok here is a good one, Why would you get a not a valid user for a certain databsase if trying to use QA, it is even not valid for the sa account. If you try to connect to another database in the server it connects no problem, but for this one there is ability to write sql code as all come ba...more >>

Hi All, I have just gotten a crazy request and am unsure if it is possible. Let's say I have 20 columns, one being [PersonsName]. I need to know how many NULL values, per column, each Person has. e.g.... [PersonsName] COL1, COL2, COL3, ect.. John 6 ...more >>

hello, Is there a way to create static tables, and have the data in them dependant on the user connected to the server? Basically, i know i have a few queries where the best option would be to create new tables from these queries with specific data. the table needs to be persistant for th...more >>

If i would update a field with a value, that longer is as the field length, it comes the error 8152 "Character sequence or binary-coded data is cut off.". In this case how can i avoid, that this error comes? ...more >>

I am trying to write a stored procedure that will only return the data from the DB that is not null based on an item that the user chooses. For example, if the user chooses item A I may want to return only column1, column2, and column5. All other columns will be either NULL or 0 (zero) for t...more >>

I have an application that processes approximately 300 transactions/sec on a SQL Server 2000 database. Periodically I have a process that uses a large amount of TempDB database used disk space. I have an alert set if the TempDB database used disk space reaches 80% then send an alert. ...more >>

Dear All, Firstly since the first time posting on this group, I apologise if this is posted on the wrong newsgroup but could someone please help me with the question below. I have received a database designed by a colleague who has since left the organisation and I was wondering if there...more >>

Hi... I have a major problem with a web application I am about to launch and just can't find out what the problem is... I believe it might be a Buffer Overflow problem but can't pin point the source of the error! Basically what happens is when I launch the application on my test server i...more >>

A little question to make sure I get it right. You cannot update records using Enterprise manager! Or can you?...more >>

HI, Can I send the output of sp_columns stored procedure into a temporary table?? or is there any query which works the same as sp_columns?? thanks Chandra...more >>

I am now writing a stored procedure that will dynamically generate a trigger base on a specific table structure. The generated trigger script will have variable lenght, depends on how many columns are defined inside the specific table. I plan to generate the CREATE TIRGGER script on-the-fly and...more >>

Hi I have set up a windows group for our Crystal reporters as they sometimes have code that runs for way to long is there a cmd I can issue that will cut of any queries going on longer then 5 minutes just for this particular group. Not sure if possible Thanks for any help Sammy...more >>

Hello all, I have a query i’d like to sort based on a field derived from other fields from that same query. I’d like to know if my field would then be calculated twice (Once in the SELECT clause, another one in the ORDER BY clause). In this case it would probably be better to use a sub...more >>

Hi all, I have a very weird problem: A table in a SQL database has about 1.000.000 records in it. There's 1 PK. Nothing to worry about (you should think). When I open this table in the Enterprise Manager (return all rows) the resultset is returned immediately. When I use the Query Analyse...more >>

in sql2000 "select * from tbl where tbl.date between date1 and date2" - results with nothing "select * from tbl where tbl.date >= date1 " - gives result "select * from tbl where tbl.date >= date1 and tbl.date<=date2" - results with nothing how to select between 2 dates? thanks ...more >>

If a user of my application is connecting to their sql server using integrated security doesn't this allow them to fire up something like QA and do things to the database that they shouldn't. I know I should have everything secure even if the user is connected that way but assuming at least ...more >>

Dear Sir, I need one Table Design information. My requirement is: I have some products (say 10 products) in my master table. Each product has Product ID (PK). ID Code Desc --------------------- 1 A1 AAAA 2 B1 BBBBBB etc. etc.... I hav...more >>

Hi people, new here, any help on this one appreciated. I have a SQL statement, it's cut down version looks like.... SELECT PeriodStartDate = CASE WHEN CI.PeriodStartDate >= BH.StartDate THEN CI.PeriodStartDate ELSE BH.StartDate END FROM History AS BH, Interest AS CI, Rates AS RS WHERE ...more >>