I have pulled a small amount of data from CHEOPS into an sql db and would like to display the data over the intranet using IE. I have been doing research into this and am becomming confused as to what would be the best procedure to follow - XML, XSL, ASP, SOAP, WDSL,TSQL XMLSQL, HELP ME! It is only...more >>

How can I generate the stored procedures, views or triggers' script automatically in batch mode? Thanks...more >>

For another program I need a SQLce database on my pocket pc, since MS doesn't support CDB database anymore in VB.NET 2003. So I wrote a program to make a XML file from a access database (the database info is supplied in access MDB format) and I'm now trying to write a little program to convert...more >>

Hi, I was running the below given query which has got 3 joins. But it was not returning any result. If I run from query analyzer it just says conmmand executed successfully without returning any result. The same works on different Sql servers with the same data. Is there any problem with any...more >>

hello all, I have a column of 4,5 and 6 digit decimal numbers I need to convvert to octal any ideas? thanks, sherpa ...more >>

Hi, I have 3 database backups created from sql7. I have to restore them in sql2000. Then I have to create a sql login and attach it to the databases as users with dbo role. For restoring the dbs I used "restore database with move" statement and executed using ADO. Then to create the sql login...more >>

New to SQL and need help. I like to be able to create a table with customer name and 12 monthly columns from a customer table with customer name, amount and invoice month. 1. Read a record from customer table, select if amount is > 1000 and < 5000 and then add 1 to the month column(New table)...more >>

We have a user at work at enjoys stopping SQL Server 7 and 2000 machines and copying the .mdf and .ldf files from one server to another. He then simply restarts the production server and re-attaches the database to the new server. I have asked this employee to either backp the DB to disk and c...more >>

I have a tables populated with data every night conataining information about other tables. The data is appended so we can analyse the row/size growth over time, but at the same time and on a daily basis I need to see a snap shot of the most recent table size and number of rows ordered by table ...more >>

Hello - I'm trying to run Exec sp_change_Users_login 'report' From a few databases on our production server. When I try to execute, I'm receiving the following error message: Server: Msg 15289, Level 16, State 1, Procedure sp_change_users_login, Line 27 Terminating this procedure. C...more >>

Incident # 1013 Take a look at: http://www.elevatesoft.com/scripts/viewincident?incident=1013 It says the query: select name,SUM(number1) As Tot1, SUM(0) As Tot2 from table1 union select name,0,number2 from table2 GROUP BY name gave the correct (the web page said "correct", not me)...more >>

I have the following records(probably a few hundred) ReportName ReportVariable JimRpt ~[reportdate]^stamp...runjob~ BillRpt ~[reportdate]^stamp2...runjob~ My question: I want to replace, in the field 'Report Variable' ,the text called [reportdate] w...more >>

My coworker has a SQL table he uses for locking some of his processes so that more than one user doesn't run the same process (A process here has nothing to do with SQL directly). He is using a table with a name and value field. Neither field is a primary key and neither field has a unique co...more >>

Hello All & TIA, I find myself in need of some knowledgeable SQL Server DBAs. I was recently thrown a copy of SQL Server for Dummies and dubbed Company DBA for a large project. I have to load over 550 tables in 289 MS Access databases into 1 SQL Server database. I have most of it working ...more >>

select * from sysforeignkeys where rkeyid = object_id('tablename') How can I convert the following columns (constid fkeyid, fkey, rkey keyno , key ids) to table names, column names etc? Thanks. ...more >>

some of our stored procs which in turn call other stored procs give the following error: Maximum number of databases used for each query has been exceeded. The maximum allowed is 8. The number of databases involved are only 3 to 4. Why do i get this error? What might be a good solution? ...more >>

Considering that it is possible to disable a trigger with the comand below, how do I discover if a trigger is currently enabled? alter table TestTable disable trigger all Thanks, Philip ...more >>

I am attempting to write a Transact-SQL script to change a column's data type from smallint to real. This works OK unless the column has a default specified, in which case I get an error. Is there a simple way to do this. Thank you in advance. ...more >>

Hi Freinds, SQL 2000 I have a SP like : --------------------------------------------------- Create procedure mysprocedure @Tmp1 int ,@tmp2 int ,@tmp3 chr(10) as select mycolumn from mytable where myf1 = @tmp1 and myf2 = @tmp2 and myf3 = @tmp3 go ------------------------------------------...more >>

Hi I am running a SQL query and printing the output to a file, as well as showing it in the Grid. The Grid has column headings, my file does not. Is there a way of getting my column headings into the output file? To create the output file I am using the following; EXEC master..xp_cmdshell '...more >>

Hello all. We know of the "sp_changeobjectowner"; is there a way to put this in a loop and change the owners of all the stored procedures in the current database to "dbo"? Thanks :) ...more >>

Just curious is you can use a carriage return in a select statement. table A Table B column1 column1 column2 column2 and have the columns from table B show up a line below table b. ie a.column1 a.column2 b.column1 b.column2 any thoughts would help thanks warren...more >>

can anyone give me the correct syntax of quotations to make this code work. I can't seem to get the correct combination of quotation marks around the @vFieldDelimiter I am passing in a "|". error message is : Incorrect syntax near '|'. I tried using double quotes on the outside of the wh...more >>

I have two temp table name #table1 and #table2 when I run the following update, I get an error " Cannot use the column prefix 't'. This must match the object in the UPDATE clause '#test'. " update #test1 set t.name=tt.name from #test1 t, #test2 tt where t.last=tt.last This in SQL 200...more >>

Hi, I'm having trouble with a query I'm writing, and I don't know why it's not returning correct results. I've tried BOL, and it's not helping too much. Here's what I'm trying to do: The following query returns 10,326 rows: select * from tarinvoice This query returns 8869 rows: selec...more >>

I thought "text in row" is a very good feature, so I used it a lot, but recently I found I can not create index on this kind of field, this limits the usage of the feature quite a lot, what is your opinion? ...more >>

hi guys, I need to capture a recodeset (returning from a select statement) in side a sp? any ideas how to do it ? -Aruna ...more >>

OK, first add an IDENTITY field to your table that has duplicates I'll call it MyDupTable and I'll call the IDENTITY field MYPK, also the field which is your basis of believing they are duplicates I have called MyDupCol Then run this code until it returns 0 (you may have to run this sever...more >>

Instead of having one big table and having multiple Select queries, is it better to break that in two object oriented table and JOIN them and query it? Is it true that JOIN will effect the performance, this is an internet website project. thanks...more >>

This is what I would like to do.. SELECT SUM(o.ChargeAmount) As ChargeAmount, o.OrderDate As OrderDate (SELECT SUM(le.Fee) FROM LinkedEmployees le WHERE le.OrderID = o.OrderID) FROM Orders o GROUP BY o.OrderDate === of course "WHERE le.OrderID = o.OrderID" is the problem are...more >>

Hi, I have a SQL table that has duplicate numbers in a column that is not primary key. These duplicates are not part of an exact row duplicate and some of the columns of these rows do differ. I have been trying to find a way to get rid of the rows that contain the duplicate entries. One th...more >>

Hi, How can I display multiple SELECTs in one SP? For example, I have the following: SELECT 'Total Contracts' AS Descripton, COUNT(contractName) AS TotalContracts FROM dbo.contracts and SELECT 'Total Companies' AS Descripton, COUNT(pk_companyID) AS TotalCompanies FRO...more >>

I'm having a bit of a problem writing the correct syntax to create a SQL Server 2000 database with a two-word name. For example, I can create the following: SQL = "if db_id('oneword') is not null DROP DATABASE oneword" MyConnection.Execute SQL But I can't figure a way around the followi...more >>

Hello, I am currently using MDAC 2.6 in VB COM Object for our Web Application. Now, we are consider to change it to MDAC 2.8, coz Microsoft said that = it have fixed many problem. Now here is the question in my mind: - Should I install MDAC 2.6 SP2 or MDAC 2.8? What is the difference = bet...more >>

Hi, If I run an insert Sql statement to a table,which level lock that the database will give to the statement,table-level or row-level?how about update statement and delete statement. Thanks Jack ...more >>

I want to do a series of changes to a database that are required to be done all at once or not at all. These include the creation of 2 stored procedures. I was wondering if anyone could tell me if this is possible as I am currently having a problem. I receive the error "Server: Msg 111,...more >>

Hello This must be a quick one. Simply I would like to trim off left and right White spaces in a string . In Oracle, we use LTRIM or RTRIM to do that. What's the equivalent in SQL SERVER Best Regards....more >>

Is there a way to query a table with an image data type field and save it to a hard drive as .bmp file to a specified location? I need to do it in stored procedure. If it can be done can you explain how? or maybe post some article for me toread. Thank you! ...more >>

Give a column with values like 1,2,3,a4,100,110,f6 How would I sort. Number in ASC and String ASC and display the results together.as in sortNumber then sortString? Thanks in Advance. ...more >>

I am working on an advanced sql query from within an ASP.NET application that is not being parsed correctly. Here is the sql query: ---------------------------------------- SELECT SNO, ACAD_SESS_CODE, SUM(TMPTOTAL) AS TOTAL, SUM(TMPOUTSTANDING) AS OUTSTANDING FROM (SELECT S...more >>

Does anybody has any idea when we do alter table mytable enable trigger all This flag gets updated in which system table.field ...more >>

I want to use the SET LOCK_TIMEOUT command with a value passed in from another procedure/function. But this code doesn't work, i.e. it leaves the LOCK_TIMEOUT value as the default. What's the secret to this, so I don't have to hard code a value into my stored procedures? thanx DECLA...more >>

Hi, I would like to know which of these queries will give a good performance if both the tables have huge data (say rows > 300000). -- Query 1 select A.A from TableA A where not exists (select B.B from TableB B where B.B = A.B) -- Query 2 select A.A from B right outer join A on A.B ...more >>

Hello, When trying to run: EXECUTE master.dbo.xp_sqlmaint N'-PlanID ....' -Rpt "F:\mydb" -chDBRepair , I'm receiving: (29 row(s) affected) Server: Msg 22029, Level 16, State 1, Line 0 sqlmaint.exe failed I run a db check and it seemed fine. Did anyone experience a similiar issue? ...more >>

Suppose I want to go through each user table in my DB , and alter it so that the column 'ID' is an AUTO IDENTITY COLUMN. What would be the simplest SQL statement ot do that? You can assume every user table has a column named ID and that they are all regular INT's for now. SEED and INCREMENT ca...more >>

Using the Northwind database I type in a very simple trigger to send an email on update: CREATE trigger [thesend] on employees for update as exec master.dbo.xp_sendmail @recipients = 'dm13@txstate.edu', @subject = 'Your customer request', @message = 'Your customer request was processed.'; ...more >>

Is there a way to set up a job to zip a backup that is auto-generated...more >>

i'm using delphi 7 and have a query in which i'm trying to find names that have an apostrophe in them, i.e. "o'mally". my problem is that when i write my select statement i can't get the quotes right. i get all types of errors no matter what i try. i get "missing right quote", "invalid token" et...more >>

HI ALL I got SQL Server 2000 SQL1 and in linked servers i make server SQL2 witch looking at itseft (SQL1)...looks like loopback I need this for debuging. In real system i got 2 servers When i start transaction in server SQL01 and do some work with linked server SQL2 i got message "The operation ...more >>

Hi All, I'm trying to convert a "stardate" integer value ("ms excel date" something as 37973) into the string YYYY-MM-DD (i.e.: "2003-12-18"). The only thing I manage to obtain is: SELECT MYINTEGERVALUE , ...more >>

Hi, I have 2 tables with a 1:n relation. Now I want to select entries from the left table that are related to more than one entry within the right table. This works fine with OR like SELECT * FROM T1 INNER JOIN T2 ON T1.X = T2.Y WHERE T2.A = B OR T2.A = C BUT: if I want to select ...more >>

Will it still be possible to use the new Yukon Server in the way as I use now SQL Server 2000 with ADO 2.6 (adhoc queries, stored procs, ....)? Or could I forget VB6? Any opinions? Patrick Simons, MCP...more >>

Hi there. I have a table that should contain distinct rows but which actually contains a number of duplicate records (Unfortunately, there are no constraints on the table). Is there any way I can delete all duplicate records (leaving one record of each that has been duplicated)? I hav...more >>

Source Server: Win2000 SP4, SQL2000 SP2 Target Server: Win2000 SP4, SQL2000 SP2 Configuration: Data is written to a table on the source server and a trigger is fired. The trigger calls a stored procedure on the target server which creates a SQLAgent job and runs and .exe. Problem: Th...more >>

Hi All My Backup Strategy is like this: Complete Database backup every 2 days Transaction Log Backup every 3 hours 1) Is this sufficient to ensure a disaster recovery/point in time recovery 2) Can I truncate the Transaction Log (Backup Log Database WITH TRUNCATE_ONLY) before taking t...more >>

For Sql Server 2000: I've noticed that every time I've tried to write a SELECT with ORs in the ON clause, the optimizer resorts to a table scan or clustered index scan. I end up having to break the SELECT into 2 or more UNIONed SELECTs, which I really don't like doing especially if there are o...more >>

Hi, I have table that has a datetime field and this field accepts NULL values, if a user doesn't enter a value I capture this in the stored procedure like so: @END_DATE DATETIME= NULL This seems to work fine, but if I try to delete or update the record I then receive the error: Micr...more >>

Hi I have a table with a column of datatype BIT. I have a stored procedure that includes a sql statement of the form: ....WHERE bitcol = @val so if I pass @val = 1 or @val = 0, I can retrieve rows that have the BIT column set as true or false. Is there a way I could use the same SQL w...more >>

I know how to list sql servers on network using osql. Is there a way we can list the running instances. thanks...more >>

Hi All, I have configured the backup scripts inside a stored procedure and this stored procedure is called periodically inside a job. After a backup I am doing a Restore VERIFYONLY .. to check backup validity. Is it possible to send an alert when this backup verification retur...more >>

Hi, I'd like to know, on what stored procede depends. (especially on others SP) Is there simple way? For example: SP A calls SP B when I have script: ALTER A.... ALTER B.... the result shown using Ent.Man (and sp_depends) is that the A depends on nothing. I don't like text sear...more >>

Hello, Firstly, I must say that I do not like the way in which the database was built but I must live with it. There is a table which contains this information NoInfo NoPeriode NoGroup Data 1 1 1 10 2 1 1 ...more >>

I have a stored procedure: CREATE PROCEDURE spCountBidsOver500 AS DECLARE @CNT INT SELECT @CNT = COUNT(*) FROM tblBids WHERE MaxBid > 500; RETURN @CNT; GO From VB6, I want to execute the stored procedure, but don't know how to receive the returned INT value: ...more >>

Does anyone know if there's a hard limit to the number of columns you can import with BULK INSERT? Maybe 255? Any change between SQL 7 and current? Trying for 335, I get an error with what I believe is a correct fmt file; says "invalid column number", but doesn't say which. Thanks, D ...more >>