We are experiencing some problems with deadlocks in our system. We call Sql server 2k from a .Net applicationlayer. The flow is Create transaction object in .Net FOR each table in a list (e.g. 11 tables in total) DELETE FROM Table WHERE SomeID IN (x,y) // SomeID is foreign key (done i...more >>

Was there a beta version of the new 2005 "QueryAnalyer" that came with Intellisense??? Am I just crazy? I thought that there was a beta with this feature....more >>

Hello, I need some assistance with some T-SQL programming. I have a column called "Message" and the following data is stored in this column. (See Below) Logon Failure: Reason: Account locked out User Name: adubey Domain: TSI Logon Type: 3 Logon Process: NtLmSsp Authentic...more >>

Hi, I am looking for a public domain version control system for my SQL server project. The public domain price is right, but if it works well with either Enterprise Manager or Query Analyzer, that would be nice too. Anyone got any recommendations or links? JD ...more >>

I have a staging table with the following fields: LineNumber,StudentId,EnteranceYear,Status and some other fields. There are some duplicates based on two fields (studentID and EnteranceYear) for which I'm trying to update the older record to be active (status=1) and all the other duplicate...more >>

Hi All, I have a huge text file to be imported in a table. I do not know how the text file was created and there should be carraige return after each line. However, when I used DTS I see that two separate records form one records. It has one column only, from there I will parse into different...more >>

I have created an Alias field in a View using a Case statement and next I want to reference that Alias field in another Alias field with a Case statement. I get an error stating the first field is not valid. Example of what I'm trying to do Column ...more >>

Does anyone know how to program a "business hours" datetime subtraction? I have a table with business hours and want to determine the # of business hours from now to a starting date. Below is what I am using now, but need to only add in business hours between WorkDayStarts and WorkDayEnds. ...more >>

Hi All, I have a schedule job for backups. How can I get the name dynamic of each schedule job inside of the steps (tsql script) ? Tks JFB ...more >>

How can I tell how many and what type of SQL lincenses are installed on my SQL 7.0 Server? Not SQL 2000, I already know about the SERVERPROPERTY function. Thanks, Tony...more >>

I am getting the following error messages when trying to compile this sp: Server: Msg 156, Level 15, State 1, Procedure RecordUnitStatDelta, Line 36 Incorrect syntax near the keyword 'NULL'. Server: Msg 156, Level 15, State 1, Procedure RecordUnitStatDelta, Line 49 Incorrect syntax near the...more >>

I'm wondering if anyone installed the 180 day fully functional trial SQL 2005 (enterprise edition) and used the client tool (from a workstation) to connect to it? I installed the server/client components (on the server) and client tool on my workstation. I can connect to server using the sql ma...more >>

I have an SQL2000 database that I am running a query on. The query is looking for computers that have not been restarted in the past 24 hours(LANDesk). The query is matching up subnet mask to email address. What is need to do is use the email address that is associated with the subnet mask an...more >>

I searched but did not find the answer to my specific question... I have a table where I need to return all columns, however, I need only distinct rows for one of the columns. The problem is that the data types are uniqueidentifiers. The DISTINCT keyword works on the entire row so I cannot ...more >>

SQL 7 or 2000 I need to insert large volumes of CSV records to a table with unique indexing that won't allow duplicates. Using DTS or BCP, the import fails because there are duplicate records. Is there a way to import the records and have the duplicates skipped or ignored and to insert th...more >>

I have about 250 databases that I would like to take and compare the database structure with a Template Databases. I need to make all of the 250 databases the same structure as the Template Databases. All of the databases have live data in them. Can anyone suggest any tools for this? ...more >>

All, I have a UDF for SQL that will padd a varchar value to a specific width and align left or right. I found this code online somewhere, but the problem is that it accpets a value up to Varchar(8000) and returns a varchar(8000). I am writing a query to upload to a mainframe and need fixe...more >>

HI: If when joining parent and child tables, a query returns multiple entries for a given parent, how can I limit query to showing only first child? Kind of like grouping on one field in result set. Thanks, Charlie ...more >>

Hi i have created a view that does a sum of values in another view. However if there isn't anything to add up then it has a value of null, i need it to 0 if there hasn't been anything added up. I need to use this value in another view for a reporting system i am creating plus it needs to be use...more >>

I have this store proc that ends at the update command. No matter where I move it to, the whole thing seems to quit there. CREATE PROCEDURE sp_LPModUser @ModFName NVarChar(100), @ModLName NVarChar(100), @ModLogon NVarChar(100), @ModPassword NVarChar(100), @ModLPUserID Int, @ModPageAccess ...more >>

What sp can I use to find all groups and roles and users in those roles?...more >>

I'm trying allow a user to change track listings on songs. On the ASP page, the user is presented with a textbox for each song where they can issue a sortkey number for each song. When done, they submit it to the SP below. There's multiple rows anywhere from 1, 23, to 50 or whatever coming int...more >>

I want to encrypt my bak/trn backup files. Should I just use EFS? Can anyone recommend a better way to do this w/perhaps a third party tool? What about encrypting the mdf/ldf files? I know that performance will take a hit but wanted some input. ...more >>

What is the max number of FK constraints I can define on a referenced table in SQL Server 2000? If in the wrong newsgroup, please point me to the right one. TIA ...more >>

Hi, What is the pros and cons of using Default instance and Named instance for Production Server? I have been working with Default and I had no need for named instances. We have a physical/vm servers for all environments. Production always have a physical server. So I have not needed a na...more >>

Here is my query: --------------------------------------------------------------------------------------------------------------------------------- SELECT M.Master_ID, M.Case_No, M.Case_Year, M.Case_Date, M.Office_Year, M.Office_Sufix, M.Page_No, M.Master_Text FROM Master AS M Where M.Master_ID ...more >>

Hi there, I have a table that has a sNoteText column type as a text 16 this column contain also a null values. I want to create a view that will show the value and empty if the column is null so I used ISNULL(sNoteText,' ' ) but I'm getting an error message: "The text ntext and image data t...more >>

hi there, i am trying to find the following date formula: if today is 15th november, i want to find the number of days 3 months back at start of month, eg. from 1st september (like Last 3 Months) i tried something like DATEADD(d,DATEADD(m,-3,getdate()),getdate()) ......bot no luck... ...more >>

I'm finding the SQLContext.Pipe won't send an OracleDataReader, like it will a SQLDataReader. How do I return a result set for a query to Oracle? code snippet: public static void BPLinkedQuery() { using (OracleConnection connection = new OracleConnection("provider=MSDAORA.1;...more >>

(this is a repost, the original post was mistakenly posted to the odbc group) Hello all, I have a nightly job which runs under the SQLServerAgent service on my SQLServer2000 database. Someone mentioned that the jobs did not run last night, so I logged in to the server to see why, and I noti...more >>

Every night we get a big dump of data from one of our outside financial companies. In the file, one of the columns is a date, with the following format: YYYYMMDD When we migrate this data into our database, SQL Server will automaically cast this 8-character "varchar" field to a smalldateti...more >>

Hi I have an update trigger on a table with 50 columns. I want to perform a specific action in the trigger if only COLUMNA is updated and nothing else. Any quick way to do this without having to check each column? Thanks ...more >>

My default port was of course 1433. I recently changed to to 5 digit number but had some problems with it in Delphi. So I changed it back. Now I can't get EM to connect. I looked at the db registration settings but nothing in there about port #. How do I get EM to work again? Also, now it seems t...more >>

How do I get Modulo on: select 200501 / 4 The sum = 50125.25 I want the .25 I tried: select 200501 / 4 select CAST((200501 / 4) as float) select CAST((200501 / 4) as real) select CAST((200501 / 4) as numeric(16,3)) Also how can one use Modulo(%) with 200501 / 4 ? ...more >>

hi all, I have an employees table and phonelogins table and i want to update employees date_terminated with now when all phonelogin_active fields are false -- thanks, ------------------------------------ Jose de Jesus Jr. Mcp,Mcdba Data Architect Sykes Asia (Manila philippines)...more >>

I know this function isn't in SQL7 but is there a way to get it in there so I can use it? Thanks, Tony...more >>

Hi Everyone: I have a datetime column (col7) where the output is in the format mm/yyyy. When I execute the following sql statement, I do get the result set as 8/2005 or 10/2004. In all double-digit month numbers I do get the right output. However, in case of single-digit month numbers I n...more >>

I have created scripts to create approximately 200 tables. I have all of these .sql files saved in a folder on a server. Is there a way to execute or run all of the files at once in this folder instead of running them one at a time? Thanks...more >>

Hi there, right now i'm working with a VB6 project that connects to a SQLSERVER 2000. I discovered a problem that, i hope you can help me. The problem is that, when i try to save a record with a date field - the date saved in database is 2 days more that the typed date. ALWAYS 2 DAYS!!! ...more >>

I am trying to update a field called NodeName from [Table A] to add the characters "?BadLink" to the end of the NodeName based on NodeID being equal to [Table B].NodeName. How can I do this? Example TableA.NodeID=12345 and TableB.NodeID=12345 update the nodename "NameoftheNode" in table ...more >>

Hi: I have conttructed the following query to delete all records from a table if a Reservation date is older than the current date: Delete From ConfRoomSchedule Where DateReserved < GetDate The syntax checks out fine but when I run it, I get an error stating that GetDate is not a valid...more >>

hi all, I want to stage an employee into the warehouse so i decided that the best script is this select empno,datehire,centerid, lastname, firstname from employees where (empno,datehire,centerid,) --< i need this one. no concat please not in select empno,datehired,centerid from dw_employ...more >>

I am trying to update a field called NodeName from [Table A] to add the characters "?BadLink" to the end of the NodeName based on NodeID being equal to [Table B].NodeName. How can I do this? ...more >>

hello, is it possible to export the content of all stored procedures from a database in a textfile thanks Xavier...more >>

How does one write INSERT queries for tables that have LEFT OUTER JOINS? I have 5 tables (Company_Name_and_CIK, Date_Filed, Form_Type, SEC_Web_Address, Form_XML) that all have left outer joins to one other table (_Company_Info_01) The first 3 tables above (Company_Name_and_CIK, Date_File...more >>

Hi all, We have a requirement in our project where we need to audit log any query (SELECT queries inclusive) that are fired on a specific set of objects (Table and views) in our database. We need to capture information like Who fired the query, When and the actual query itself. The appro...more >>

I would like to rename a database progrmatically. if any users are connected, this causes an error. is there a way by which i can forcefully close all connection and restore the database. Thanks GS...more >>

I have a master table. This table has relationships with multiple child table. If the list of child tables and the foreign key relationships are unknown how do I find the same? Is there any stored procedure that I can run to get this? Thanks in Advance ...more >>

Hi there, I am having real problems finding the optimum way of retrieving data from a Sql server and marshalling that into my c# business objects. I have a fairly complex object hierarchy which I want to populate all in a single shot (rather than relying on lazy loading which would cause m...more >>

Hi, can anayone help me with the following. Suppose next table Id Val1 Val2 nText Val3 1 10 11 house 12 2 20 21 street 22 3 30 31 car 32 4 40 41 man 42 Now a need a sql UPDATE st...more >>

Hi, I have a table tScores with 3 fields: user, score and stage. What I want is to select the 2 best scores (highest) of each user. Ex. tScores --------- A, 20, 1 A, 10, 2 A, 30, 3 A, 40, 4 A, 10, 5 B, 20, 1 B, 30, 2 B, 10, 3 C, 10, 1 The result would be: A, 40, 4 A,...more >>

I am trying to take a series of rows containing a date range and merge a quantity column for rows with over lapping date ranges. For example Date From Date To Quantity 5/1/2005 20/1/2005 1 15/1/2005 25/1/2005 1 1/1/2005 6/1/2005 1 7/1/2005 ...more >>

Hi NG, I have a problem with a query. I have three columns with relations from A to B and the number of eg. Orders: C1 | C2 | Number A B 17 A C 4 A E 23 B A 22 B G 19 B J 21 What I...more >>

Hi All, I received a copy of SQL Server 2005 Standard at a conference. To say the least I am beyond blown away!! Anyway, I went out and purchased Visual Studio 2005 Pro. When installing it also installed SQL Server Express (I thought it would skip it since standard was already there. I w...more >>

I'm trying to find ANY examples of using the Microsoft.SqlServer.Management.Trace namespace. What I'm looking for is an example of being able to create and initialise a new trace, haing this processed on the Server, and then initializes some sort of traceReader against this. e.g the equi...more >>

Hi, I am trying to construct a SQL statement that will allow me to get the latest date for a particular row (client). i.e. First Name Second Name Date Ordered Bob Smith 10/11/2005 Bob Smith 05/11/2005 Bob Smith ...more >>

Hi, I would like to add a column in an existing table with this code. ALTER TABLE tCheckImport ADD COLUMN bStdOpt BOOL But it doesn' t work, what' s wrong with it ? Regards ...more >>

What newsgroup should I use to ask MDAC questions? I couldn't find one that is just for MDAC or ADO. ...more >>

Does anyone know how to configure SQL 2005 BOL to make it more user friendly? Notably I would like to know "How To" ... 1- Get rid of the Fly Over ScreenTips in the search result window 2- Resize the columns on the search results window 3- Get rid of tab (Local Help (500)) in the search r...more >>

Hi All i Have Var Type nvarchar in my TRIGGER The maximum allowed (4000) i need (8000) or more How can i do this Thanks ...more >>

I've been troubleshooting a problem I've had for a couple of hours now and am getting nowhere and hope someone here can help. I have a car website under development that allows registered users to enter search criteria when looking for cars. I want to allow users to save their search and th...more >>

Hello, Long time I am puzzled over the following task. This problem should be similar to many systems, and it would be interesting to know what are best practices to solve this. The setup: Table, that contains log of events. Typical fields would be: 1) uniqueID; 2) evtTime; 3)evtType. T...more >>

Hi All I know that if SQL 6.5 was on a server and you then install SQL 2000 on it you get the wizard option to upgrade an SQL 6.5 DB to 2000, but I don't have the luxury of this. I have an SQL 6.5 DB, which I want to convert, but only SQL 2000 on my server. Do you know what options I hav...more >>

Hi! When i install a layered SPI, the SQL will report supersocket failed while bind to port 1433 ? when i uninstall the layered SPI, it is OK. but i disable the SPI by move the SPI down, it also report failed? who can tell me the reason and how to avoid it? Thanks! Xiaorun huang ...more >>

Is there a way I can find out what things were changed on a table, like new PK were created or dropped as well as constraints added/deleted? TIA ...more >>

Is it possible to find out the default path SQL Server creates databases in depending on what instance you are connected to. E.g. My default instance default data path is C:\Program Files\Microsoft SQL Server\MSSQL\Data. My named instance path I also have is C:\Program Files\Microsoft SQL ...more >>

Hello, I've been converting/normalizing data and inserting it to other tables. What i would like to do is to create 2 records for a particular value if it occurs in the denormalized table. Any idea how i should do this?...more >>

Hi All, We have various processes running symultaniously but no two processes will access the same record and each process is in a seperate transaction. The problem is with SQL Server Database Locks. i.e. if we access any query in two different transactions then one is getting success and t...more >>