Hi, This is the scenario: SELECT * FROM tblTest (This is just a portion of records, actual list goes more than 1000 records) id partno filename ----------- --------------- 1 part1 file1 2 part2 file2 3 part3 file3 4 part3 file1 I want to retrieve the ...more >>

Hello, If i have a copy of the .ldf and .mdf from a database is that the same as a backup of all the data? Does these files contain all the data up until the time of te intital copy?...more >>

Hi, I have a rather complex procedure (sp_x), returning one resultset, which I need to call from anthoer procedure (sp_y). Only I do not want sp_y to return the resultset from sp_x, but only use the values found in sp_x, and then return a different resultset based on the values from sp_x. But...more >>

Dear all, I have a problem while creating SQL statement. My situation is as below: Column: Number Reference ------ --------- Record: 1 A 1 B 1 C 2 A 3 B 3 C ...more >>

Is it possible to read/view the transaction log of SQL Server, and which table store the tran log.? ...more >>

Hello, I want to set a custom host name when opening Query Analyzer so that the HOST_NAME() function returns the specified name and not the workstation name for the processes initiated from QA. I know that merge agent allows you to specify a custom host name by using the parameter -Hostname. ...more >>

Hi to all wizards out there, Can anybody tell me how to identify if a user has logged in twice or more than once in SQL Server? For instance, if i log-in to my computer and log again from another computer using the same account i used in my computer, i will be prompted that i was already logg...more >>

I have encrypted a sp and don't have a script. Any body has any idea how to decrypt it. Thanks. ...more >>

Hi I want to insert huge count of rows into table. Is it possible to disable index to do it more fast? ...more >>

Hi all, I have recently joined a company, where I noticed one of developer keep issuing some weird commands. How can I trap all the commands executed by him in a day from query Analyzer or otherwise. Any help is highly appreciated. ...more >>

I have table meetings and I should check for current week and for each hour if there is some meeting. If there is, then return meeting name else 0 or null. Something like that: hour monday tuesday wednesday thursday friday saturday sunday 01 0 0 ...more >>

Hi, Further to my question yesterday about constructing a trigger, if possible I'd like someone's opinion on this: I have my trigger which will update a field in the table that the trigger applies to. ************************* Alter TRIGGER trigCustomerID ON STOCK FOR INSERT, UPDATE ...more >>

Triggers would be fine for add, update or delete but can you create an audit trail of who saw what, except in code in the calling application? Is there a way to have the SQL server database engine itself do that? I don't think the database logs are useable to perform audits on, are they? Basic...more >>

Hi, I am attempting to adjust a bit field in a table to not allow nulls, with a default value of 0 & to populate any currently null rows with this default value. However, my attempt to use the ALTER TABLE results in a Syntactical error I can't fathom. I could really do with achieving this in TS...more >>

Hi, Do rows containing NULL's occupy the same space as an equivalent row in the same table which does not contain NULL's. I am trying to estimate the possible size of my database and have been adding up the size of each of the columns datatypes to come up with a "size per row" figure. I...more >>

I'm afraid my brain is melting on this one... I've done this before, but cant remember it now... I have three tables: Location, Hotels, PrefHotels Locations: A list of locations that people in my company travel to. Hotels: A list of hotel chains, inc URL etc that we may use PrefHotels: Iden...more >>

Hi there, One of our user had (and still has) the right to post DELETE commands to one of our main tables in the Database. I usually hate when managers has this kind of rights. Generate a lot of trouble. The scene is as following: he deleted some records from a very important table in...more >>

Hi! As My database applications will be hosted in other company, I don't want the administrators in hosting company to see anything in my database. Is there any way to protect my intellectual property? 1: compile SPs? and how to? 2: anything for tables, views and indexes? Thank you ...more >>

Hi! I would like to calculate a datediff on days, but from midnight to midnight. Onfortunately the fields are populated with time in them as well. Any hints? Jochen -- Jochen Daum - CANS Ltd. PHP DB Edit Toolkit -- PHP scripts for building database editing interfaces. http://sourcef...more >>

The following query produces... Server: Msg 8624, Level 16, State 23, Line 1 Internal SQL Server error. on SQL Server 2000 SP3a. We need to produce this type of query (actually much more complex) and need to refer to an alias in the final "from" clause subquery from the inner subquer...more >>

Hi guyz, I'm lost, i need some help... I've been given the task to take any DB and execute scripts that would convert any columns that are varchar or char to nvarchar and nchar. To do so, i thought of doing 3 scripts: 1- the first one will remove any relation between table such as constraints, ...more >>

Hi, I'm trying to select fileds that are in the results of a SP. So I have the table "tblItem" itemID int Identity Key, itemName varchar (100), itemDate smalldatetime I have a SP "p_getItemID" which is lets say SELECT TOP 10 itemID FROM tblItem Now what I want to do is: SELEC...more >>

Hi, we've been working with SQL server since version 7.0, and we upgraded our database to sql 2000, since that, we found a huge performance degradation, we noticed that every single part of our program, that had an average time, increased dramatically. After that, we encountered a w...more >>

Hi, I have a mysql dump with a table with this field: "hdstart" integer which contains integer like 1042758000 1028152800 etc etc How can I convert it to a SQL Server DateTime field? Thank in advance and sorry for my bad english... Ste ...more >>

I came across a scenario today where I needed to create a temp table twice, with slightly different columns in the 2nd table. Logically however, the name "Sums" described each table. But this doesn't work: =================================================================== create table #s...more >>

Hi, I can use scalar UDF from dll as extended stored procedure ? How ? Please. ...more >>

I am performing an Insert INTO ... Select .. from command. The command includes convertion of fields from character to date formats. Problem is that some of the input rows may have invalid data. This results in the whole input not working. Is there any way to tell it to put a null value when t...more >>

Dear All, I have a VB6.COM program using a VB6.COM DLL, which in turn uses ADODB that performs various bits of SQL. I have re-written the VB6.COM DLL in C#.NET (with a VB6 shell), which uses the ADODB.Interop library supplied with .NET. The VB6.COM program above calls this C#.NET library i...more >>

Hi, I need to remove all indexes from any DB... (dynamic script). I used pointers to go through the sysobjects table (to get the name and id of user-created tables) and then the sysindexes table (to get the indexes related to the user-created tables). I then execute this: EXEC ( 'DROP INDEX ' ...more >>

Hello Can someone confirm the following limitation, or am I doing something wrong? I am writing some INSTEAD OF triggers to keep an audit trail of changes to text columns (and potentially ntext and image). I want to be able to capture the contents of a text column which may contain up to...more >>

Is there a way for me to add a prompt into a script? Basically I would like to write an update statement that would have 2 variables that would be entered my the person running the script... update dbo.table1 set field1 = %variable% where field2 = %variable% Any help or ideas would be ...more >>

I am trying to run the following command but hitting with too much usage of transaction logs. I have tried both BULK_LOGGED and SIMPLE recovery models and they both behave the same for this particular query. INSERT INTO [dbo].[temp_Audit] ([Price], [SQLCreated]) SELECT convert(int, Price) as ...more >>

Hello, I have a problem with the "sortby function". The following statement works fine: CREATE PROCEDURE [...] @sortby varchar(20) AS SELECT ... FROM ... ORDER BY CASE @sortby WHEN 'RezNr' THEN RezNr END, CASE @sortby WHEN 'RezBez' THEN RezBez END, -- CASE @sortby WHEN 'Freig...more >>

Hello, Someone knows the correct way to restore a file *.mdf in SQL Server 7 ??? My disk fail and I lost my *.ldf, only I can recovery *.mdf file. When I try to do: EXEC sp_attach_db @dbname = N'TCC', @filename1 = N'E:\mssql7\data\TCC_Data.mdf' -- Out Error -- Server: Msg 8...more >>

I'd like to be able to display the bottom records in a bulk inserted db much like you can see the top 1000 or so. I've been haveing errors importing the data and would like to see how the last imported records faired with my own eyes. Rick...more >>

I would like to retrieve 5 records (of products) at a time from an SQL Server database. The end user will be able to select which order the list of items will be displayed in according to different criteria (model number, price etc.). My main problem is, what is the most efficient way to ...more >>

Hi, I working on an application and I have inserted some data into a table within a sql database, and the date has obviously been entered in the wrong format, the problem is I can't amend or delete the record from the table, everytime I click away from the record I get the following error: ...more >>

Please Advice I need to track changes on tables in a database. Things like column Update,delete and Add. Also table drop and add activities. Checking the base-schema version in sysobjects would tell an object has change. How can I achive tracking to the column level? Thanks for your kind effort...more >>

Hi I have two tables in seperate databases on SQL Server 2000 One table contains Sales Order When I insert update or delete a row in the Sales Order I want a row to be inserted, updated or deleted with the same Sales Order Numbe The Column name is OrdLineN Can you have diagrams that refer ...more >>

Hi The code pasted gives me an overview of the names Foreign Keys of a table, the column which has got the FK constraint, the table and column that the FK references. Furthermore 1 is shown in CASC UPD and CASC DEL output columns if such cascade contraints exist. The variable @m_tablen...more >>

enterprise manager , right-click a stored procedure, all tasks, generate sql script ... it's my understanding that the t-sql generated (if run) would re-create the sp *exactly* as it currently exists ... in other words, a sp "knows" what options (ansi_nulls) were in effect at the time it wa...more >>

Hello All, Can we make a table readonly like we can do it in Oracle? Thanks in advance Anand...more >>

Hi, I have this one backup job keeps fails with the following error message in application event log: '18204 : BackupDiskFile::CreateMedia: Backup device 'F:\Microsoft SQL Server\Backup\Database\databaseFile3.bak' failed to create. Operating system error = 32(The process cannot access...more >>

I have this Stored proc in SQL server, which works perfectly when being run via Query Analyzer. The Stored proc is simple, just runs on a table and updates some fields in it. When calling the same Stored proc, with the same data, the Stored proc seems to run partly and then stop for no reason. T...more >>

I am using the routine: SELECT ID FROM TABLE where ID=(SELECT MAX(ID) FROM TABLE) to obtain the maximum ID in the table, then ID = ID + 1 to insert a new record with new ID. However, after creating a record with ID = 9, it continued giving the same number. Therefore new number is always...more >>

Hello, I will explain my question about how to perform deleting duplicate rows with the best performance. CREATE table tbTest ( ID int, CHK bit) INSERT INTO tbTest ( 1, 1) INSERT INTO tbTest ( 1, 0) INSERT INTO tbTest ( 1, 0) INSERT INTO tbTest ( 2, 0) INSERT INTO tbTest ( 2, 0) Res...more >>

how to tell whether a server machine is running msde or mssql without looking at the enterprise manager?? ...more >>