Hi all, Please suggest me a script to find out the identity column in a table TIA Satya ...more >>

Hello all. I have several machines participating in a small Workgroup. These are all Win2K/Win2KAS/XP/SQL Server 2000. A domain is overkill for my home office. I have successfully debugged a stored procedure on the server. However, when I attempt the same on a workstation, I am not able to...more >>

Can we restore the MSSQL Server 2000 database from the transaction log? Just D. ...more >>

Hi How Can I assign a value as a parameter that is dynamic and changes. For example I would like to pass a date parameter in the query that is always today's date. Regard TA...more >>

Hi, I write a SP that internally do some looping and calling nested SP in each loop to do some archiving and cleansing. I schedule this SP to run from SQL job scheduler. The SP run well when I test run via Query Analyser. However, the codes break halfway when I run from the SQL scheduled...more >>

I needed to work on the sproc in the following article http://www.sqlteam.com/item.asp?ItemID=2955 My only problem is the @select variable is maxed at 8000 chars and my dynamic sql is running over 8000 characters. Would appreciate if one could modify the sproc to include more than 8000 ...more >>

I just upgraded from access to SQL Server. In my access SQL statement logic I used IIF as a work around for a datareport. I needed to fill up a different field depending on if a keywork existed. Actually, it was to center it as a title. The datareport wouldn't let me bold,center it in the vi...more >>

SQL SVR 2K SP3 What are the possible causes of this error : Server: Msg 446, Level 16, State 9, Line 1 Cannot resolve collation conflict for equal to operation. I am working with a database for which all columns are defined to have collation = database default = SQL_Latin1_General_CP...more >>

I have inherited a DB that has no foreign keys and tasked with = identifying orphan records. =20 I believe I need to also accomplish the following: 1.. Identify Orphan records 2.. Identify FK's, and move into implementing them 3.. What else? I'm looking for suggestions on how bes...more >>

I need to retrieve a customer's last 15 distinct items (ItemIDs) that they have purchased from me. The problem is that they may have purchased the same item more than once at different prices and/or on different dates which poses a problem for a simple SELECT DISTINCT statement as I do not wis...more >>

Hi All, I have two sql tables say A and B with axact same structure. each has a primary key with IDENTITY(1,1). data already exists in both tables. Let say I have 1 record from table A (NOT exist in table B) and I want to copy the data over through the following sql script: INSERT ...more >>

Yes, I want to select a date column from a table and view it as a string. Example '1/1/2004' would be 'January- 04', '2/15/2004' would be 'February-2004'. I was going to write a UDF where I manually pick the Month and Year of my date and say Declare @m int Declare @y int Declare @m...more >>

I'm having a problem with a piece of VB code that has embedded SQL within it. Essentially our server has always had a specific user set up as Default Language 'English', as we are English it should actually be set to British like the rest of the users. However when we changed this setting in t...more >>

How do I set up an alert in the SQL agent to send me an email whenever there is a lock on the system...more >>

Is there a way to execute the sp_fulltext_enable stored proc automaticly after a server rebooted on all the databases in the server? maybe with a job or something? its getting a little annoying having to run it again on every database after we reboot our development server... thanks ...more >>

sql server enterprise manager right-click a table all tasks > import data source data source = text file target data source = microsoft ole db provider for sql server the dts import/export wizard will attempt to create the table -- triggerring an error if the table already exists. ...more >>

Hello, I am wondering if maybe someone has written a sproc which can, given a table with individual units of data, can return a non-aggregate crosstab table. (Actually aggregate would work if only there were a CONCAT aggregate or i could write one). By this, here is what i mean... Given...more >>

Hi, What's the best way to move packages from one server to another? tia, bob mcclellan ...more >>

Hi - I have used DTS/Export Data to transfer a copy of my remote database and data into a local version running on my local PC. On my remote server - the tables and procedures are owned by 'mtsqladmin' - my username on that server - so in EM, if I look at a list of the tables - it has mtsqladm...more >>

Can securityadmin roles change the sa user password and grant user to as an sa role using Query Analyzer? Thanks in advance ...more >>

CREATE TABLE LastNameSuffix ( LastNameSuffixKey INT NOT NULL IDENTITY(1, 1) CONSTRAINT LastNameSuffixPK PRIMARY KEY CLUSTERED, LastNameSuffix NVARCHAR(30) NOT NULL CONSTRAINT LastNameSuffixUC UNIQUE ) GO INSERT INTO LastNameSuffix (LastNameSuffix) VALUES ('JR') INSERT INT...more >>

i know that the tempdb gets cleared upon restarting of sql server, but is there a script that will do this? i'd like to add this to our nightly maintenance plan, but not sure of a way to programmatically do this. any suggestions?? ...more >>

Hello, I have a lot of SP which valid the quality of some data and also modify other in consequence. They start with BEGIN TRAN and if all conditions are met the COMMIT else ROLLBACK. The problem that I have is that if a SP execute a TSQL command (let's say INSERT) which generate a key violation...more >>

Hi, I am trying to parse a formula from a table with information from another table. table A account amount ------- ------ x 10 y 20 z 30 table B formula ------- x+y-z I wonder if anyone has any thought on the approach on how to get ...more >>

I'm taking a look at replacing embedded SQL with parameterized stored procedures, and have a syntactical question from an example I'm working with. In the following line: SET objParam = objConn.CreateParameter("@strLogin",200, 1, 50) What are the 200, 1, and 50 doing? I have a similar line...more >>

Any system sotred procedure in SQL2000 to generate a data dictionary for all tables in a database (fld name, table name, len, type etc)? Thank you mike *** Sent via Developersdex http://www.developersdex.com *** Don't just participate in USENET...get rewarded for it!...more >>

Is there anyway of getting the list of time zone available in Windows and save it into a text file? ...more >>

Can anyone give me the syntax for selecting rows from a table that where created yesterday regardless of the time. I have a col called entryDate which is a datetime col, if I use: select * from myTable where entryDate = getdate()-1 I dont get reliable results, I think it's bec...more >>

Hi Sql Gurus, Problem: Updating highest max data with second max data & deleting the second max record Example: Create table #table1 ( seqnum int, partnum int, createddt datetime ) insert into #table1 values (1,10,getdate()) insert into #table1 values (2,10,getdate() ...more >>

How do you find the gaps in a field containing sequential data. For example, I have a field with the following data: x001 x002 x004 x005 x007 I want to return the missing records. For example, the results returned should be: x003 x006 Thanks!!!!...more >>

Hi, I have a table consisting of C_ID StartDt EndDt 100 1/01/03 1/10/03 100 1/11/03 1/31/03 100 3/02/03 3/05/03 100 3/03/03 4/10/03 I need to create a row containing the min...more >>

what is better for performance? INSERT INTO tTable(colA, colB, created_date) VALUES (@colA, @colB, getDate()) or just set the column created_date to have a default value of getDate() in the table definition and don't insert a value INSERT INTO tTable(colA, colB) VALUES (@colA, @colB) ...more >>

I have a table TABLE with columns A, B, C, and D. I want to select the rows with DISTINCT A, B but I want all columns returned. Is there any way to do this besides making the two selects as: SELECT A, B, C, D FROM TABLE WHERE A IN (SELECT DISTINCT A, B FROM TABLE). I've dummied down the exa...more >>

Why not specify the widest possible column size when defining a column that must hold a variable length string? Is there a downside to doing so? TIA ...more >>

Very new to SQL Server, and I've been assigned the task of exporting the following stuff out of a database our software creates: Table Structures Stored Procedures Maintenance Tasks Mainly, we want to know what the differences (ALL) are between our clients SQL server PRE our product and PO...more >>

Hello Experts, Is there any way to encrypt my sql scripts? If not, can any utility (not third party tool) encrypt the script when execute it? Thanks in advance....more >>

Hi everybody, I would like to restore a database in NORECOVERY state and only after restoration, to set it in RECOVERY mode. To restore it I use the TSQL requests below, don't mind about virtual device, it's only a way transfert data : RESTORE DATABASE pubs FROM VIRTUAL_DEVICE = 'my_virtu...more >>

Is there any way to determine the machine name of the client computer that has executed some SQL? I.e. without altering the client software I want to be able to have a trigger on a table capture the name of the client machine that has fired the trigger. The reason for this is that we have seve...more >>

Hi All, We are using indexed views in our application for performance benefit and better utilisation of resources on MS SQL Server 2000 Enterprise Edition... We are facing a strange problem... Before explaining the problem let me explain how we are making use of indexed views in our applica...more >>

Hi all, I have a table with ~1.5 million rows. I added an instead on a couple of fields - 2 ints and a bit field. I used the command CREATE INDEX to build the indexes. The indexes were not picked up until by the Query Processor until after I reindexed the table using DBREINDEX. Is thi...more >>