Is there a simple way to achieve the equivalent of "drop table ... cascade constraints" (Oracle syntax) in SQL server 2000 - either SQL syntax or through an API The only solution I can see is to first call sp_depends and process the resultset from that before dropping the table Wondering if it c...more >>

Dear Friends, SQL Server 2000. I have observed an behaviour in my sql server 2000. I have an user 'UA' assigned as the 'db_owner' of the database DB_test. I logged into enterprise manager as user 'UA' and created a table via Enterprise Manager. I saw the owner(at enterprise manager)- dbo. ...more >>

I have two tables each containing records to the tune of 30,000 records... I need to create an optimized join on the two tables...Apart from creating necessary indexes is their a way to improve the join performance... The query is: SELECT DISTINCT Related.* FROM /* Ignoring duplicate rela...more >>

Hi I have a string (varchar(12)) with a date in in following formats DMMYYY or DDMMYYYY I want to convert this to a real datetime First of all I am stuck with the leading zero I am missing in my string and also the european format of the string If I put the string in a smalldattime field i r...more >>

Hello I have a new SQL Server called Accouting and I would like to migrate all of the databases and sp's and security from Accounting1 SQL Server to Accounting. What would be the best way to perform this task. Any Help would be greatly apprectiated. Accounting1 is a SQL 7.0 and Accounting is ...more >>

SELECT DISTINCT Related.* FROM /* Ignoring duplicate related records */ (SELECT b.RecordId,0 AS RecordSource,a.PayeeVendorNumber , a.GeneralizedInvoiceNumber, 1 AS Type,b.InvoiceAmountPaid FROM #TempTableForExceptionSet a INNER JOIN #TempBaseSet b ON a.PayeeVendorNumber = b.PayeeVe...more >>

for this kind of sql : select T1.*,T2.* from Table1 T1 left outer join Table2 T2 on T1.ForeignKey = T2.PrimaryKey where T1.ForeignKey = 'nnnn' is there syntax available to cause column aliases to be used in the resultset where the aliases are constructed as TableName_ColumnName .... o...more >>

For 2 days already Stored procedures on SQL Server don`t work. When debugging: "ODBC: Msg 0, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]Cannot load the DLL mssdi98.dll, or one of the DLLs it references. Reason: 126(The specified module could not be found.)." Other obj...more >>

Says I have 2 tables: tbTran & tbUser. Says the table has these fields: tbTran (SeqNo, UserID, AuthUserID, ...) tbUser(UserID, UserName, ...) Now, i need to list our the details of the transactions including the username. I have 2 options here: 1. Using Join table: "Select tbTran.SeqNo, tbTr...more >>

Dear All: I would like to know, how to get the total time in minute between 2 date. For example: from 30/03/2004 12:00 to 31/03/2004 20:00 Please advise Thanks ...more >>

For the benefit of whoever is in need of this type of data retrieval. I have a table whose structure is something like this: TableName: Recursion RootID ParentID TheData ------ -------- ------- 1 1 Level 0 - Group 1 2 1 Level 1 ...more >>

I have a SP which appears to be returning each row twice: Create Procedure Orders_ListAdviceLines @AdviceNoteID int, @LocationID int As Select s.StockMoveID, s.LineID, s.SerialNo, d.PartNo, p.CleanDesc, o.OrderID, o.CustOrderNo from StockMovements s inner join OrderDetail d ...more >>

Hi, I have the following code written in my sp. SET @DynamicQuery = 'SELECT @CommaListValues = ' + @fieldNames + ' FROM #TempBaseSet WITH (NOLOCK) where RecordId=' + CONVERT(NVARCHAR,@RecordId) { EXEC Sp_ExecuteSql @DynamicQuery , N'@CommaListValues NVARCHAR(200) OUTPUT',@CommaLi...more >>

Hello, I delete by mistake the account BUILTIN\Administrators How I put it back? Thanks, Andrew ...more >>

i have 2 tables test_oj(fld1 varchar(10) ,test_oj2(fld1 varchar(10) with test_oj value Fld1 ---------- test test test test test -------------------------------------------------------------- test_oj2 value Fld1 ---------- test1 test test test test test (6 row(s) affect...more >>

Hello, I delete by mistake the account BUILTIN\Administrators How I put it back? Thanks, Andrew ...more >>

Please help me with the select statements. Here is the result want show below. Thank you very much in advance. DROP TABLE #HomeProspect, #HomeQuoteProspect go CREATE TABLE #HomeProspect ( Person_id INT NULL ) GO CREATE TABLE #HomeQuoteProspect ( Person_id INT NULL, Quote_...more >>

I need to set up a routine to export some data to a linked server. The source table has among its many columns a three field key. I need to copy the records from this source table to a destination table. How do I run a select statement that will compare the three column key in the source table...more >>

Why is this allowed: create function dbo.fnCurs(@curs cursor) returns @result table ( data varchar(50) ) as begin declare @acctNo varchar(10), @acctName varchar(100) open @curs fetch next from @curs into @acctNo, @acctName while @@fetch_status = 0 begin insert @result s...more >>

Hi... I have a INSERT and a DELETE trigger defined on a table. So that when a row is inserted/deleted from it the appropriate trigger will either copy the inserted row in my personal table or delete the corresponding row from the personal table. However whenever I insert or delete rows to/from ...more >>

Have anyone used temporary procedures before? what is the advantage of this? ...more >>

Dear , all How can i select between SQL SERVER AND Mysql ? Thankyou ...more >>

Hi, Let's suppose I have this: Field1 ------ 4 7 6 7 8 I want to get the first available integer. I have found many solutions on the newsgroups about this. This one, for example: SELECT LowestInt = ISNULL (MIN (MyColumn + 1), 1) FROM MyTable WHERE MyColumn + 1 NOT IN (...more >>

Hello all. We have a stored procedure where, based on a parameter, we want to include the GROUP BY clause or not include it in the SELECT. Is this possible? Thanks :-) ...more >>

Wondering if/how this can be done *without* using a cursor: I have a table with columns named [sequence] and [name] (sequence holds in, and name holds varchar(50)) I want to update [sequence] so that [sequence] contains integers that increment by 1, starting with 1, for all rows in the tabl...more >>

Hi, I was reading another thread about copying SQL server databases and logins to a new server. I too am going to have to move a sql instalation to a new server. My question(s) is What about Jobs, DTS packages, Linked Servers, etc? Is this not a common thing that people do? Move everything to...more >>

I've got a web application where I want to show product sales by month, and want to create a stored procedure to display this. Pretty simple. The only catch is that I want to display this in a table, so I need to have a zero in the recordset for months that have no sales. What I'm doing now is...more >>

What is the SQL Server or Microsoft Newsgroup server name?...more >>

I have to get a count of rows on a table for a condition on various fields - like If something Select count(*) from tbl1 Where fld1 = 'Yes' If something Select count(*) from tbl1 Where fld2 = 'Yes' .... CREATE PROCEDURE sp1 --@fld nvarchar(50) @fld object --pseudocode As If ...more >>

Hi Freinds, SQL 2000 I need to update an ORACLE database table with PK in one of my SQL tables. I have the database as linked server too. I am using DTS to Transfer data between two servers. But can I use DTS to update destination server? Any other option I be able to update ORACEL tables? ...more >>

Hi Freinds, SQL 2000 I have a linked server for my ORACLE database . When I click on linked server I can see the server and tables. How can I access those table in a select statment? something like : select * from <linkedserver>.<tablename> I treid it but says cannot find table ! Tahn...more >>

Does the IF UPDATE(ColName) work in a multirow trigger ? Does it flag if one of the rows has tha ColName updates or if all rows have that column updated ?? Thx ...more >>

I am tasked with determining the best way to archive changes. That is when a column in a row changes (i.e. Person's Last Name is changed) I have to be able to show the person as the were before and after the change and when the change occurred. I have done this three different ways in the past and w...more >>

Hi, I opened up the debugger in Query Analyzer for my SP's. I got it to work once - maybe on an sp that did not take any arguments. But I am trying to debug an sp which takes arguments. I have placed breakpoints in the debug window and run the sp from the query analyzer window with the...more >>

Is there a program to fill random content, based on rules, on mssql2k? Thanks ...more >>

Hi all, I would like to ask is there any method for getting definite number of records? For example, I have a field for date in the table and I would like to get 50 records before a date. Is there any sql statement for getting the records? Thanks a lot, Ray ...more >>

Hi, Is there a way to count number of columns for specific table from Information_Schema.Columns? Example I have table News and I need count of columns named News"number"Text NewsID NewsDescirption News1Text News2Text News3Text News4Text News5Text News6Text...more >>

Hi, Is there an alternate way to run a SQL statement stored in a varchar then to use EXEC. Example: =================================================== DECLARE @SOME_STRING VARCHAR(500) DELCARE @USERID INT SET @USERID = 1 SET @SOMESTRING = 'SELECT * FROM SECURITY WHERE USERID = ' + ...more >>

Hi: I am starting to rip my hair out. I pretty much tried everything. I want to insert following values: sqlString = "INSERT into myTable (ProductID, Name, Title, CategoryID, Organization, Address, City, State, Zip, Country, Phone, Fax, Email, Heard, Referral, Computer, Implement, Descr...more >>

Hi, I'm writing a trigger that will modify some fields in a record to reflect some facts like the last user that modified the record and the computer on which he was. I currently use SUSER_SNAME() to find the user name, how can I find the Computer Name? Thanks ThunderMusic ...more >>

Hi, In the SQL Server Enterprise Manager when I create a stored procedure by default the "CREATE PROCEDURE [OWNER].[PROCEDURE NAME] AS" text exists. In which file this text is located? Thank you Ruslan ...more >>

Hi all, i have problem to update a table with following query, which fail = both in query analyzer or by VB app. have error "[Microsoft][ODBC SQL = Server Driver]Syntax error or access violation" the query : UPDATE Email SET EmailDT=3D'31 Mar 2004' WHERE = Idx=3D{BDF51DBD-9E4F-4990-A751-5...more >>

I want to be able to pass an optional dynamic and to the where clause. I want the entire assembled query to be parsed and if it fails to parse I'll return a user defined parse error code to the calling application. Basically in the proc, I want to be able to parse the assembled query prior to...more >>

Hello: I am involved in the the creation of a .NET application that makes use SQL Server 2000 and was hoping for some insight into the following question. Our database has a Customer table with a MedicareNumber field. It is a business requirement that the Medicare field be unique for all ac...more >>

i'm trying to create a backup of databaseA and then restore this database to databaseB. however, i do not want to overwrite the databaseA files. i'm thinking i need to use the 'With Move' command, but im not sure how to do this. i want databaseB to exist on the same drive as databaseA, but i...more >>

Hii Can anyone tell me the difference between normal stored procedures and extended stored procedures? TIA Ramesh :) ...more >>

Can I do this?? have two tables.. [table1] addressnumber (ID) referencenumber guestnumber [table2] addressnumber (ID) adresstype addresstext Now I need to create a Uniqe index on: table1.referencenumber, table1.guestnumber, table2.addresstype Can this be done???? Med v...more >>

Hello All I am trying to execute the following sql statement - what am I doing wrong as I am getting an incorrect syntax error Declare @typ char declare @rate decimal select GroupID, Contrnum, Secid, SN, Lastname, Firstname, MI, Fund1COntr = Case WHEN Fund1Fringe =1 SET @TYP = (S...more >>

Is there a way to disable triggers only for a particular DB User or a Group of users? There is a user in our db called 'sysmgr' and he runs updates and refreshes db on monthly bases. Every table in our db has an audit log administered by a trigger, when 'sysmgr' does any modif. to the db we do...more >>

Hi all, I have the query below and wonder if you could show me a different way to write the query and get the same result. I have thousand of rows and I am try to avoid using GROUP BY clause. Any help would greatly appreciate. Please look at the result want below. Thanks. -- Creating...more >>

Hi, I'm looking to create some wizards that given a table will create insert, update, delete sp's, something similar to the data adapter wizard in .net, but I want to extend it to include Audit control, standard headers etc.. Any ideas if theres anything out there already that does ...more >>

Is there any way to insert a new column into an existing table with SQL i.e Enterprise manager allows columns to be inserted into tables between existing cols, but as far as i know SQL lik ALTER TABLE [tabx ADD [cola] [int] will always append the new column to the table TIA Steve...more >>

Hi - I run the following query in sql analyzer and the data passed is converted to binary. Update table1 set FieldA = convert(varbinary(256), 'UDJJRKSJWKK=') where field1 = 'xxxxx' FieldA id defined as a varbinary on table1. I write a stored procedure to recieve 2 parameters - the ...more >>

My Business Object is calling a stored procedure and passing in some parameters. Not all parameter values are being passed in (such as FileStatusFlag). For those with- out a value being passed, the SP assigns default values. However, I am finding that my SP is ignoring the default values...more >>

Using STS 1.0 with SQL Server 2000 in the back end. One of the user groups is requesting a numeric field that uses multiple decimal values. Much like chapters in an old school book: 1.0 1.1 1.1.01 1.1.02 1.1.03 1.2 1.2.01 And so on. What's the best Data Type value I can select i...more >>

Hi all, Trying to build a sproc that groups record filing years together (easy so far) but groups any filing years up to and including 1995 into 1995. So the only rows we could get back are 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 1995 may be quite large as its all up to that point U...more >>

Hi, Is it possible to have an a temp table OUTPUT from a SPROC? TIA, Eric...more >>

Hi, Could anyone tell me what to use for textfields in my database tables varchar & nvarchar ? What is better for performance ? Thanks. Regards, Daniel Rakojevic...more >>

hi all, does anyone have some code to track what user has updated a table in SQL server? i am trying to program a SQL database to record the username of a user who has updated data in a table. any ideas. cheers, lee....more >>

Any idea why the following doesn't work for me? It throws "ORDER BY items must appear in the select list if the statment contains a UNION operator"- SELECT Product_id, 0 AS Product_level,(SELECT TOP 1 Label FROM Labels WHERE Label_id=Product_label) AS Product_title FROM Products WHERE Product...more >>

Trying to figure out a way to pass a value stored in dbo.table1.Sample_Size into a SELECT TOP "X" statement in another query CREATE TABLE [dbo].[Table1] [ID] [char] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL [Sample_Size] [int] NULL ) ON [PRIMARY G CREATE TABLE [dbo].[Table2] [ID]...more >>

Hi there, I noticed the "new" defaults feature on mssql2k. Is it possible to use a default I created (@@SPID) and put it in DECLARE variables from within stored procedures (not as column default). Thanks! ...more >>

Im trying to find out a 'processing' date from any given 'Movement' dat Ie if the movement date is '21/01/2004' then the processing date would be '20/01/2004' , how ever its not that simple. Processing dates can not be Saturday or Sundays or one of the dates in the table below. for instance ...more >>