1- How can rename a field name after creating it and inserting data in it? I used: EXEC sp_rename 'Students.DOB', 'StDOB', 'COLUMN'; But when I want to add a constraint for this column, the column is not identified. Are there ways to change the Column name without affecting Constraits? 2...more >>

Thank you to all your genuises out there who helped me with getting an unique SQL server ID. I now have another problem. I must also get the database ID. (Something about a/the GUID?) That must also be some or other unique ID (numbers and\or digits)to identify the current database you are workin...more >>

Hello, First let me apologize for this long posting. Also, this may be a database design question more than a SQL query question. Ok, here we go... I have three tables; tblreps, tbltrans and tblreporting. Here is a simplistic schema of the tables: tblreps ------------ rep_id - primar...more >>

i have a problem with MSSQL, my datatype is smalldatetime. when i open enterprise manager i can see the date display is 12/8/2003 = 6:14:00 PM. But when i want to retrieve that time cannot, it only = allowed me to retreived if my sql statement is select * from tbluserid = where TDateTime =3D '12...more >>

Hi All, Is there a way to change all SQL key words to upper case automatically and globally for hundreds of SQL files. I inheritted hundreds of SQL files with every thing in lower case and no indentation. I want to change all SQL key words such as SELECT, INSERT, DECLARE, i.e. to upper ca...more >>

Hi all, I have this simple query: /------------------------------------------- select cc.contactcategory_id, a.alpha + '|' + cc.contactcategory_name as TreeInfo, count(jct.contact_id) as Total from dbo.tblContactCategories as cc join dbo.Alphabet as a on left(cc.contactcate...more >>

Hello In the program "Client Network utility" in SQl 2000 it is possible to delete "Named pipes" in the "Alias" tab and also to make a new Tcp/ip Network Library configuration. My question is if it is possible to do this automatically, e.g in a Transact SQl Script ? \Anders ...more >>

I an trying to import into sql server a very large file (52 million rows). I have got various errors which i seem unable to get rid of even splitting the file into 5. One message says that the log file for the database is now full, back up the transaction log for the database to free up some log...more >>

I am working on an existing system, that has a series of reports. We have a requirement to change the data coming out of the database for some of the reports but we don't have the option of changing the report structure, so I need to make the new data fit the old model. The old query used 'ORD...more >>

Hello, I have a stored procedure that I want to use for an underlying recordset = for=20 an .adp form. Can anyone tell me the correct syntax to set this property passing an=20 input param? Basically, when a user logs in, the form sees the associated branch for = this user. I want to disp...more >>

Hi we want to set auto start properties in sql server->properties- >general in enterprise manager. but they are greyed out, i am logged in as administrator why is this? The same with SQL server Agent start up properties are greyed out why is this..? The server i think is a virtual ser...more >>

I have a cursor that is created on temp table with unknown number of columns or column names I need to find a way to loop through this cursor and insert record by record into another temp table Something like that: fetch next from cur_Temp into #TempTable Is anything like that possible ...more >>

Hey all, Having a slight bit of trouble with a Free Text Query here. I think I see what is happening but not sure how to fix it...yet I may not be correct in that either. Anyway, the procedure being used is this: CREATE PROCEDURE spGrantorList @SearchFor varchar(32), @DocType1 varchar(...more >>

If I have table with columns of start and end date, how can I get the first free time for the defined date and hour? Lets say the date is 2003-11-24 and hour is 23: startDate endDate 2003-11-24 23:00:00 2003-11-24 23:10:00 2003-11-24 23:15:00 2003-11-24 23:20...more >>

I want to replace null with something else for a column in a SELECT column, but cannot get the function name. Please help. Thanks....more >>

I am supporting an app written by someone else, and this error is being logged: [SQL-DMO]The passed ordinal is out of range of the specified collection What situations could cause this particular SQL-DMO error? I could not locate it in SQL help. thx! ...more >>

I have the following statements in query analyser Begin Transactio Select * from table update table2 set f1 = "test" where ... insert into table3 ... commit transactio If the update statement or the insert statment fails, I would still be committing the transaction, right? I just wanted to ...more >>

Is there a query i can run to find out whether a given column has the identity attribute? ...more >>

Hi Folks. Is there a (generic) way to make inserted database values be null? for example when inserting an int with a value of zero I want it to be inserted as NULL into the database. The same goes for empty strings. Any generic way to handle this using sprocs or .Net code? --Roy Osherove www....more >>

If I do such a query: select name from Customers where city='New York' I get back 50 names, I only want to return 1 by random from that resultset, is it possible? ...more >>

Hi all, I am developing an apps utilizing SQL Server as the backend database and I need to reset the database frequently (i.e delete all data and reset all identity field such that they start again from 1). The DELETE FROM command does delete the data, but it doesn't reset the identity field. ...more >>

can any one help me with the correct syntax of the following select * from openquery(server_name,'exec pubs..sp_MStablekeys ''sales''') here is the error i get. Server: Msg 7357, Level 16, State 2, Line 1 Could not process object 'exec pubs..sp_MStablekeys 'sales''. The OLE DB pro...more >>

Hi, I have a varchar field with the following data. Table: TB1 Select * From TB1 Result: Column1: Matt 189 Tim Mary 932 I would like to do a select to show only Column1 starting with a Char (A...Z) and nothing numeric. this is the result I would like to show. Column1: M...more >>

Hi, I have a performance issue with following query. SELECT DVA1283_2003_1.* FROM DVA1283_2003_1 LEFT JOIN tempSub ON substring(DVA1283_2003_1.CSYNADJID,1,36) = tempSub.synid WHERE tempSub.synid IS NULL order by DVA1283_2003_1.CSYNADJID ASC This query takes around 6 minute...more >>

Hi there! I'm currently working on a DB that has a weird problem.... When i script the tables, i get this: -- Lot of code -- CREATE TABLE [dbo].[tblCustomer] ( [CustomerID] [int] IDENTITY (0, 1) NOT NULL , [strCustomerID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [E...more >>

I am trying to unload a table of 51 columns to a text file. The Transform Data task won't define columns in the text file. I hit populate from source button and execute button but no fields appear in the destination window. This only happens on some tables....more >>

Hi, I have the following value in a float datatype variable DECLARE @tempval float set @tempval = 123.4233653422342342 Is there a way in SQL Server to truncate upto 4 decimal places and return the value 123.4233 ? Note:- I do not want to use the round() which would return me 123.42...more >>

I need to create reports that are subtotalled by month and year. I've done this for a million different reports in the past, so I decided I needed a function that I could reuse. Given a date parameter, the function should return month and year concatentated together like... select monthYear(ge...more >>

Is there a way to redirect the output of following query to a set of tables ? which return more than one recordset like the following. Use pubs go sp_helpconstraint 'SALES'...more >>

Hi, Gurus, I have a query similar to: select X, Y, Z from Table_A, Table_B where EXISTS(select id from Table_A and Table_B where Table_A.id = Table_B.id) and Table_A.id = Table_B.id. I have two questions on this query: first, why use the EXISTS operator. It seems to me it is not necessar...more >>

Is there a way i can execute the following? declare @column_name varchar(10) set @column_name='CustID' select * from customers where @column_name=1 I can't use set @sql='select * from customers where ' + @column_name + ' =1' exec(@sql) Because i need to further process values returned b...more >>

Hello all. We're writing a stored procedure that needs to pull everything from a table, tblPDSMaster, and then match the positionID from that table to another table, tblPositionType, which contains the different position types. However, some positions are NULL in tblPDSMaster, and trying to run t...more >>

I want to create an average but throw away the top and bottom numbers from the average. Something like the below (which doesn't work) Select avg (select hour_count from tbl_test where ID not in (select top 1 ID from tbl_test order by hour_count desc) and ID not in (select top 1 from tbl_tes...more >>

There has been alot of discusion on this topic. Everybody is trying to say a different frm others. Here is my practical questions: I have imported few data from excel sheet and have many duplicate values. Now I inserted them into SQL Server, but I can not define any unique constraint or any p...more >>

I store them as xml into a ntext field, but the performance is so bad, I am looking for your experiences, thanks! ...more >>

Hi, I can beliebe my eyes when I see a Stored P. running with SQL Server Database compatiblity 7.0, and taking just 30 secs to finish, and as soon as I change the Database compatibility up to 8.0 (SQL Server 2000), the same SP, with no changes takes 30 MINUTES, and even more!!!. The probl...more >>

Is it possible to create a "where" type clause within a check constraint? The script below contains a few FK references, so you won't be able to cut and paste, but what I am trying to accomplish is the check constraint CK_PHYS_TAGS_DATE_COUNT in the second table without having to resort to a tri...more >>

Hello All, I am in the process of writing a SQL query to filter out a selected rowset, and was hoping for a little guidance on the best way to structure the query. One way that I've read that might be possible is using derived tables (found in the Advanced Queries chapter of the Wrox SQL Serv...more >>

Hey I have a table with UPDLocks, I need to know the ID of witch process is locking for the SQL I just executed an got a Lock time out on... Have made this litle script, but the problem is that the blocked ID is gone... Any soluction??? To see source script look below.. Regards Kla...more >>

After executing a command which caused a catastropic error on a sql 7 box (caused by connecting to a sybase 11 database using merant driver http://support.microsoft.com/default.aspx?scid=kb;en-us;245500) the spid cannot be killed. I have tried the kill command but the spid is still active and ca...more >>

Hi All This is a belter that my little brain can't handle. Basically I have 1 SQL table that contains the following fields: Stock Code Stock Desc Reference Transaction Date Qty Cost Price Basically this table stores all the transaction lines of when a user books stock items into st...more >>

Do I need a script in SQL SERVER that imports a certain table of Access (the one where the use of DTS is not necessary). that is possible? My idea is the following: I have two applications, one with VB and database in SQL Server, and another fact everything in Access. I want to create a ...more >>

I need a way to test if a type is Double and I am doing the following, but I have heard that the try, catch has a lot of overhead and slows the system down. Is this true, and is there a better way to accomplish this? double Val = System.Double.NegativeInfinity; string strValue = null; //...more >>

Hi all, I have a script that deletes all stored procedures in a specific DB. Here it is: declare @str_proc_name sysname declare cur_procedures cursor local fast_forward read_only for select name from sysobjects where xtype = 'p' open cur_procedures fetch next from cur_procedures into @st...more >>

Hi, I wanted to know how to monitor a sql server database. How to monitor any change in the database tables and notify interested people of the changes. Thank you in advance. Ashwin ...more >>

Hello, I have made a clone of the "sp_sproc_columns" and made some adjustments to it. I samed this proc in a "utility" database as I want to execute this procedure within multiple databases. I think my problem is related to how I am calling this stored procedure and I am not sure the prope...more >>

Hi A large complex of stored procedures in a customers application uses temporary tables, some of the sprocs are nested (or recursive) and several of the sprocs uses the same temporary table created in a higher level stored procedure. A complete rewrite of the stored procedures cannot (...more >>

Hi, all. In a sp I declare a table variable that I would like to add a identity column to. Have tried these two ways but none of them work. So how should I proceed? Test 1. DECLARE @tbl table ( a_id int, a_pName varchar(30), a_company varchar(30), a_seqno int IDENTITY(1,1) ) ...more >>

I have a table set up with a primary key identity column. Most of the time this works fine, but there are occasions where I need to update a record and set the primary key to a number that is not in the identity sequence. For instance the identity value is 160001 and I need to manually update a ...more >>

Hi, I am running perfomance monitor and I noticed that my network bandwidth is off the chart. How can I write a SQL query to find out who and which database is using all the bandwidth on the SQL Server? Thanks in advance. ...more >>

Hello, I am trying to rotate a report with the table and query and report format shown below. I have the following inspection table: CREATE TABLE [dbo].[tblInspection] ( [Inspec_ID] [int] IDENTITY (1, 1) NOT NULL , [InspReq_ID] [int] NULL , [Prop_ID] [int] NOT NULL , [Bldg_ID] [int...more >>

Hi! I need help in writing a sql query. please see the dd below. create table #t (id int, acct varchar(12), type varchar(10)) insert into #t values (100, '123','cr') insert into #t values (100, '345','dr') insert into #t values (100, '12-423','cr') insert into #t values (102, '898...more >>

I am migrating an app from straight Access to a SQL back- end, and several of my SQL statements in VBA contain nested IIF statements as calculated fields, such as 'Qty' = IIF(x = 1, 2, 3), when either INSERTing into a table or UPDATEing a table. I run these using the DoCmd.RunSQL xxx, with...more >>

I need to know whether features introduced with SQL Server 2000 can be used in a database running in compatibility level 7. Specifically, the table data type variable. I did a quick check using a variable of table data type which is a SQL server 2000 feature in a procedure. The stored pr...more >>

I crate a dll with vb. and call it from sqlserver via sp_OACreate.but don't work.What's wrong?How must i make dll or use in t-sql, thanks,...more >>

Hi, I need to compare rows of 2 identical tables and find out in which coulms data is different. I need column name of table 1, value of this column from table 1 and value from table 2 and the primary keys of this row...more >>

We have a situation where we are log shipping from the prod server to a report server every 15 minutes. However, as the logs are being applied to the report server DB, the DB is not available for a brief period of time. Most of the time it is just a few seconds, but it has taken up to 2 mi...more >>

I am not quite sure how to go about creating procedures which are used across multiple databases. I took the sp_spproc_Columns and modified it a bit and then save it with my unique name. I place the proc in the "Master" database. My problem is how do I now use it. I am under the impressi...more >>

In SQL Server is there a way to create a table as a select from another table? For example: create table x as select * from y thanks Pete -- ...more >>

Hi there, I am asked to come up with a report on actions per day over the last month, say november. Each record has a field CREATIONDATE, which I can use within a cursor to get the results I want. I don't like cursors and wonder, if this can be done with a regular sqlstatement? I am working on ...more >>

Hello, I have a stored procedure, that work fine from the Query Analyser. I used it from C code by ODBC api - not all of the rows that supposed to be changed, were changed. so i added SET NOCOUNT ON in the stored procedure, then it worked. After few days i tried it on another computer and i...more >>

Can I have a DSN for one database that is Windows Authentication only and have another DSN for another database that is SQL authentication? Please bare with me a moment, I do not remember all the secuirty option in Enterprise. But suffice it to say that everything in Enterprise is set ofr Wind...more >>

To create stored procedures, I use the SQL create statement. Then I add the access permissions manually using the management console. This manual step I would like to include in the create statement. How can I do this? Thank you very much for hints....more >>

Hi I have inherited a web app with the following table structure, and need to produce a table without any duplicates. Email seems like the best unique identifier - so only one of each e-mail address should be in the table. Following xxx I have been able to get a duplicate count working: ...more >>