I want to programatically connect to MSDE but on install it forces me to 'trusted connection' which is -E when using osql. How would I code a 'trusted connection' in my connection string (c#, if it makes any difference). TIA Kevin...more >>

Here is my task I have SQL 2000 tabl id identit field1 varcha field2 varcha there are some duplicate reocrds regarding the fileds field1 and filed I want to see which ones are with the same field1 and different field2, and also to list the Thanks...more >>

hi everybody I've just installed Sql server 2000 enterprise trial version. I'm having a difficult time issuing a simple "Select" statement from the Query Analyser. I'm trying to visualize the content of the "authors" table in the "pubs" database. I issued the following: "select * from author...more >>

Hi I am experiencing a problem with the statement RESTORE DATABASE [NEWTEST3] FROM VIRTUAL_DEVICE='xxx' WITH SNAPSHOT, MOVE 'NEWTEST1_Data' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL$ANDERSON\\data\\NEWTEST3_Data.MDF', MOVE 'NEWTEST1_Log' TO 'C:\\Program Files\\Microsoft SQL Server\\MSSQL...more >>

Hi All, How Do I Know what is the Phisical PATH for the .MDF File for a Database "Test"? (Suppose the File Name is: "Test_Data.MDF") I know that that we can find from Database Property. But I want a Script to Find that. Thanks Prabhat ...more >>

Hello everyone I am trying to write a query to get a list of unique constraints in my database. I want the constraint name, table name, and column names. I get everything fine except for the column names. sysconstraints' colid column has zeros instead of values. How can I get a list of the col...more >>

Hi, I've used the method outlined in the link below to use a variable in an ORDER BY clause. http://www.aspfaq.com/show.asp?id=2501 I'm having trouble with the CASE statement and a column alias. The SELECT statement is shown below. I want to ORDER BY CountOfPreview and MaxOfDate in my st...more >>

How could I convert a character like '20040604' to a datetime? -- .~. Might, Courage, Vision. In Linux We Trust. / v \ http://www.linux-sxs.org /( _ )\ Linux 2.4.26 ^ ^ 8:36am up 12:27 0 users 1.13 1.14...more >>

Okay, I need to do dynamic SQL (Don't shoot me, please) and I want to make sure that the text that is being entering the SP from the parameter doesn't contain anything other then these characters ',brdhos<space> Is there some way that I can check and make sure that these 9 characters are the...more >>

I have the following query (both gets the same results) and the problem is it just doing nested loop iteration using CONTACT table which has alias "A" in the query. it has about 78,089 rows, so CODENAME table has to be scanned for 78,089 * 6 times (for each self join) so the performance is poor. ...more >>

Hi, What is the correct SQL statement to append data from an existing table into another existing table? For example: Table1 has data in it. Table2 does not. Table1 gets new entries into it everyday. I want to put only the new data from Table1 into Table2. I tried this statement but it...more >>

I am trying to create a procedure which will calculate the total tuitio This process involves 3 tables Contract table has tuition information which is all $100 (setted price) Discount table has discount type and discount percentage (ex. 0.3) on each discount type ContractDiscount table have c...more >>

If I have a while loop that inside starts a transaction and commits the transaction, will each loop being and end a new transaction? Is there a better way to do this? Here's some pseudo code... -- begin pseudo code set @accno = '' set @res = 0 while @accno is not null begin --lo...more >>

Hi, use pubs declare @a varchar(8000) set @a = '' select @a = @a + au_lname from authors select @a I can't use varchar(8000), b'coz my result will contain more characters. I can't use text, b'coz value can't be assigned. Is there any alternate way to get "text" data type values using T...more >>

Is there SQL Server functions that are same as Excel MOD, EVEN functions? If not are there easy SQL statements that will do the same. Thank You, Dean ...more >>

Hello ! I have a problem: i'm trying to compare two int variables (i'm using SQL server 2000 and query analyzer), but the syntax: SELECT (1<>2) SELECT (1=2) give me back an error. Please can someone explaine me why it happens ? Thank you very much, and sorry for my english ;-) ...more >>

How do i return a list of system roles for a database that have been created by a user/admin i was looking at using sp_helprole but this returns the SQL roles such as db_owner db_accessadmin db_securityadmin db_ddladmin which i dont want to be returning i guess the easiest way woul...more >>

Is there are way to query the DB to return a listing of tables that have a rowcount of 0 (no rows)? TIA, Jordan...more >>

I have a sp where in ened to select all the data from a specific table and then use the fields from that record as the basis to manipulate other rows. Do I have to enter Select @field1=field1,@field2 ... or and open and fetch into ? Or is there some simple way I can select all the fiel...more >>

SQL2K backend Access 97 MDB frontend. I have a ComboBox whose Recordsource is a SQL Passthrough Query. One of the returned fields is a Boolean value - bit field in SQL Server. But I want it to display in the Access combo box as either Yes or No. Currently Access displays 0 or -1. Fine for me, but...more >>

Hi All, Is there any Limit for the Number of Values that we can Pass in the IN Operater of the SQL Statement. Thanks Prabhat ...more >>

Dear all, I do need your help! I have a program written by VB6, in which calling a stored procedure (by passing a xml string) which is quite huge and takes around 3 to 5 second to run. At beginning, I excepted that it will run faster besides first time since the sql server will use the exi...more >>

Hello. I am trying to figure out how to calculate the first day of the quarter last year. I have figured out the code to display the first day of the current quarter, but I am struggling to incorporate the code to find this date last year. Here's what I have so far and the corresponding ou...more >>

Hi all Does it make sense to use 'With (nolock)' after each table name in select statement? If yes, why?If no, why Thanks Ray...more >>

Thanks vyas for the sp_generate_inserts. He noted that there was a limitation with some datatypes such as text and image. I assume many have already tried it with TEXTPTR(),WRITETEXT and such, to work around with that limitation. Anyone out there who knows can share with us about the outcome o...more >>

I have a stored procedure that can take a list of years (the SQL says 'WHERE year in (@years)'). The problem I am having is passing in that value. I've tried all of the following with results ranging from errors to no records coming back: execute GetData "'2002','2003','2004'" (no data coming ...more >>

I have a problem that I can't quite get started on solving. I have a table of asset statuses. Each time the asset status changes, a new row is inserted into the table. CREATE TABLE dbo.Tbl_EMStatusHistory ( EMStatusID int IDENTITY (1, 1) NOT NULL , EMSessionID int NULL , AssetID int NO...more >>

we created a vfp-8 application wich creates a file fith fcrate(). When an other user (diffrent account on xp) can not write on this file. How can I create a file wich can be accessed (read and write) from all users ? Thank you Peter ...more >>

The scenario: I have an application that is live. When I do later phases of the app in my dev environment, I often make modifications to my dev db that I will need to later update the live db when I move the dev phase to production. My questions are: How should I track the changes I make to t...more >>

Environment: PowerBuilder 6.5, MS SQL Server 2000, Windows 2000. I have to pass an integer array from my Powerbuilder program to a stored proc so that my stored proc can work on it. But looking at the help i find out that array as an argument for a stored proc is not supported. Is it true? If ...more >>

Hi, How do I get the following reordset SELECT * FROM Photographer_Specialty Specialty_ID Specialty_Desc ------------ ---------- 1 Photojournalism 2 Portrait 3 Sports 4 Nature 5 Wedding ...more >>

I have a situation at a customer where the age old debate around "to use or not use stored procedures" has been raging. The good news is that stored procedures won. The bad news is that a question was raised by a member of the group, which is particular to their business: They have a situation...more >>

How can I tell, via TSQL code only, if the current server has MSDTC running? I am on MSSQL 2K. Thanks. ...more >>

I build a #temp table in code. Then I do some dynamic SQL and Exec it. How can I make the Exec'd code output into another #temp table so I can join on each #temp for a final result set? Thanks. ...more >>

I am in the US and all dates in my database are in US format (MM-DD-YYYY). Everyday, we receive one CSV file that I import to a table using a DTS package. This file has a date field which is in European format (DD-MM-YYYY). What is the best way to convert this date to US format so that it is s...more >>

hi. i found some online message board discussions on this but have had a hard trouble figuring them out. i have a udf that i'm trying to run in a view (all records) but i'm getting an error that says: sql subquery returned more than one value. this is not permitted when the subquery follo...more >>

Hello, I'm using the above command to connect two SS2K servers. Something like: EXEC sp_addlinkedserver 'remoteserver','','SQLOLEDB',null,null,'SERVER=remoteserver;UID=remotelogin; PWD=remotepassword;' But then, when I try this simple query: select * from remoteserver.remote...more >>

I have three columns in a table: name_full, name_first, and name_last. I would like to return a recordset where name_last is not like name_full. the data in the table appears like: name_last: Benage name_first: Aaron name_full: Benage,Aaron I am trying to find the records where the fu...more >>

Hello, I have the case statement snippet below: AND CASE WHEN @address = '' then @address Else tblOwner.OwnerAddress1 End LIKE '%' + rtrim(@address) + '%' I want it to do and OR statement for three fields ownerAddress1, ownerAddress2, ownerAdderss3 I've tried all sort of versions o...more >>

I want to do an bulk insert into an partitioned view . Can i do it ?? In usual case we need to drop the indexes from the tables and then do the bulk insert . But if we want to do an bulk insert on the partitioned view , where from we drop the index ? If we drop the indexes from the underlying table ...more >>

Hi, Thanks in advance to any help / insight you can provide. I am stuck on the following changes, that need to be migrated from pl/sql to t-sql. 1. How do I handle code that calls/uses dbms_lock? 2. How about handling object types? 3. FOR i IN 1 .. no_of_ids loops? 4. Will probably come up ...more >>

Does anyone know from where I can get a list of the SQL 2K System stored procs and what they do? I realize that one can figure out what they do, but I'm looking for a general list. One of the sp procs I'm hoping is around, would be one that lists all of the objects a user can access (ex: views...more >>

I have database that has 2000 tables and numerous indexes which is approximately 60GB in size. I am presently using the DBCC REINDEX twice a week to reindex. The job takes approximately 7 hours to complete. Is there a SQL script that will reorder the indexes that will complete in sever...more >>

Hi, I have a problem I'd like some advice on. Here is the situation. I have an sp which queries data using a linked server. Running the sp works fine. Now I've read about the loopback and transaction issue but I can't explain this For whatever reason one of our developers has to run a number of s...more >>

Can osql be run from any PC, regardless of wether or not PC's have SQL Server, or any SQL Server Elements, installed? If possible, I am wanting to use osql to re-build indexes from MS Access (call relevent bat file) when temp data is recreated. TIA Tony C...more >>

Hi All I have a Parent-Child-Grandchild table as defined by the DDL below. What is the most efficient way in order to update the records as illustrated below: I am trying to update the parent at level 1 and then update the child at level 2 and the grandchild at level 3. I am also try...more >>