Hi All, I have heard that if you create a clustered index on identity column(PK also) it would decrease the page splits. But how? Thanks, Pradeep ...more >>

hello, i have a basic question about triggers. are they created the same way you create stored procedures? right click on the stored procedure and pick new stored procedure? i created a new stored procedure and inserted this text: CREATE TRIGGER trig_sequence_number ON acd_document_s FOR IN...more >>

We're trying to upsize from MS Jet (Access) db to SQL Server and am having a major problem. Because we've not seen this error with the MS Jet db, I suspect it's related to the user configuration in SQL Server. The servername is "sqlbox" and the database name is "TCR". We use WinNT authent...more >>

What are the typical things you look for to decide whether to use a lookup table? I typically have Country lookup tables, State lookup tables, Airline lookup tables, etc. We also have a field that is a company type which would be one of 6 values: Corporation ...more >>

Hi, Here is my SP I need to run this as this ConFirmOrders_SP 'SVR,UCC' this is giving me Server: Msg 207, Level 16, State 3, Line 1 Invalid column name 'UCC'. Server: Msg 207, Level 16, State 1, Line 1 Invalid column name 'SVR'. ALTER Procedure ConfirmOrders_sp ( @StrTy...more >>

It just occurred to the suits recently that the 240 GB is not enough, so what did they do? Change the RAID configuration?!? NO! Get more drives for the RAID 10 stack?!? NO! They bought a SATA drive and wired it to the SQL Server with a USB connector. And now there's an email storm go...more >>

Has anyone create a GoalSeek function similar to that of Excel? I've been researching this and trying to script one out myself without much sucess. If someone could tell me that its impossible, that would be helpful too. TIA. ...more >>

I am using a cursor to take information from a temp table and either insert or update another table. I am using a while loop to fetch all the vaules from the cursor and then I close and deallocate the cursor. Everything runs fine the first time but when I run the procedure again it updates t...more >>

CREATE PROCEDURE dbo.msl_UpdateMSLJobTitle ( @description NVARCHAR(70), @jobTitleKey INT ) AS UPDATE msl_JobTitle SET Description = @description WHERE JobTitleKey = @jobTitleKey GO Then I have some VB.NET code that runs it: Private Sub fixJobTitles() Dim cnstr As String = ...more >>

I am looking for a specific Datetime column and I don't know what table it resides in. How can I query the systables looking for all those columns with Datetime type??? Thank You!...more >>

How would I execute a stored procedure and turn off the transaction. We do not care on our end if the data makes it over. So we dont need transaciton. Any Idea? Thanks...more >>

hi, what diff: select * from mytable where intfieldname between 1 and 5 and select * from mytable where intfieldname between 5 and 1? Thanks. ...more >>

Does it make sense to have a stored proc which selects from one of a number of tables depending on a variable, as below. Or should I have a stored proc for each table. Thanks Soc. CREATE PROCEDURE [user].[usp_tables] ( @pg_id int, @pagesource varchar (30) ) AS Select * from @pagesou...more >>

Sirs / Madams I am new to MSSQL and was wondering about data conflicts with Views in a Multi-User System. By that I mean if I have five users execute the same report against a database at the same time but with different criteria how does MSSQL prevent data corruption between them. ...more >>

I have to calculate the average age in a group. What is the best way to calculate the average age where I have the individual's birth date??? Thanks in advance!...more >>

In the continuing saga of dealing with blobs . . . READTEXT is a fine function, but useless by itself. I need to return the results of READTEXT into a varchar column but have been unable to do so or even find a similar example. Displaying the results in Query Analyzer is all the example MS...more >>

Hi everyone I have a silly question about SQL, I wonder if it is possible using existing technique to accomplish it. I have a binary field(e.g. Image) in SQL, I need to store image file(scanned from original document) in that field. I don't think it is possible but my boss want me to give h...more >>

I am having trouble shrinking a database. The database is 14 gig, with 6 gig free space. I have tried to shrink the db with ERM, and I have tried from command line with dbcc shrinkdatabase and dbcc shrinkfile with no success. Anybody have a clue as to what I am doing wrong. Thanks for the ...more >>

I have the unfortunate task of dealing with an ntext column. I have to update part of the contents but first I was just trying to display the contents in Query Analyzer using a script from page 61-62 of the Guru's transact sql book. Well, the script does print out a few characters, skip a few...more >>

Hi, all. What's the syntax for an insert from one database's table into another database's table on the same server? Here's what I've got and it keeps blowing up in SQL Query Analyzer, while connected to db1 which has Table1. I need to insert into db2's Table2. It keeps giving an "Invalid ob...more >>

I need some ideas on this query. I have a table with entries similar to the following with columns name, id, and timestamp. kmyoung 345 2005-08-22 07:29:00.000 kmyoung 345 2005-08-29 07:29:15.000 mphillips 360 2005-08-27 14:48:18.000 rbeheler 360 2005-08-22 09:29:11.000 rbeheler 3...more >>

Hi, Is there anyway I can capture the name of the trigger that is fired? I tried SQL:StmtCompleted and SQL:BatchCompleted but both did not show the name of trigger that was fired during the Insert/Update/Delete... Thanks Ed...more >>

Hello all, I'm trying to write a very basic stored procedure that inserts a row into an indexed table with computed columns. I originally created it with this code : CREATE PROCEDURE insert_test AS SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT, QUOTED_IDENTIFIE...more >>

Discuss amoung yourselves; meanwhile I'm heading for the beach! Have a nice weekend folks! ...more >>

Hi vs2005/sql server2005. I have created a simple winform app by dragging a table on a winform. I have used stored procedures for data access. I have the following questions; 1. Using the default code generated by vs2005 for data access, how can I trap record insertion to set some field val...more >>

What database/table stores information on the recovery models used for all database on an SQL server? I want to change all to FUll. Thanks...more >>

sql server 2k I am aware of SELECT * FROM INFORMATION_SCHEMA.TABLES ad sp_help, but in each case I also get a table called dtproperties and, in neither case, is there a logical way to tell one apart. I am also adverse to using undocumented system tables seeing as sql server 2005 is just ar...more >>

Hi, I would like to use Northwind as an example. I am trying to use the Orders table and why the following query only returns one record for each customer if the outer query pass info into the inner query since each customer has more than one records in the Orders table. e.g. customerid...more >>

Dear All, I am trying to write some SQL that will give a list of all tables and Primary Key/ Foreign Key constraints in a database. The code below goes part of the way but not what I would like. It gives me: Parent Table: Activities Child Table: ActivitiesLocation ForeignKey: 1 Primar...more >>

I need a suggestion. I have a table with Columns (Rec_ID, Amt_Recieved, Amt_given, Rec_Status, Rec_Frequency , Rec_date) here Rec_ID and Rec_date are primary key I need a resultset out of this table which has columns Rec_ID, Amt_Recieved, Amt_given from the day previous to Rec_...more >>

Is there a problem with the case and order by when you attempt to order by a column that has been labeled? example (with assist from user MySqlServer, thanks you) CREATE TABLE tenbeat ( tenbeatcol1 int, tenbeatcol2 int ) INSERT INTO tenbeat VALUES(3,4) INSERT INTO tenbeat VALUES(1,2...more >>

Hi I have a table with an identity where I need to insert a set amount of rows. However I need to inser a full block with no gaps in the id's. If this sql was run by 2 people simulataneously then I could not get back a full block. I was thinking that a table lock would help here but do not kn...more >>

Hi! I wounder how i can find a speciffic digit numberin a nvarchar string in my table. Case: I have a costumer table. In there I have a column for IdentityNumber (nvarchar 255) - storing a 11 digit number. I want to make a select statement based on this column where the digit number ...more >>

Which one is faster (regarding compilation time and execution time): SELECT @var1=(some expression), @var2=(other expression), [...] @var6=(last expression) or: SET @var1=(some expression) SET @var2=(other expression) [...] SET @var6=(last expression) All expressions are arithmetic...more >>

It seems that remote insert takes much more time than "local" insert. My table MyServer.MyDb.dbo.MyTable has hudreds of milions of rows and hudreds of GB. It has 3 indexes: clustered (not unique), nonclustered unique and nonclustered (not unique). An insert of tens or hundreds is imediate ...more >>

Dear All, I am looking over the design of another person's Database and I find that nearly all tables have Primary Keys and also a column called: rowguid - uniqueidentifier - with a (newid()) default value Why would the designer need a primary key and a GUID in every table. I am confuse...more >>

I'm using BCP to export data. When I look at the data with Notepad, some empty fields have this "ÿ" character, but this character does not appear in the database table. This is apparently causing problems with the company we're exchange data with. They are asking for the field to be empty, n...more >>

Here's a user defined function I'm working on: CREATE FUNCTION dbo.CreateList (@Table varchar(50), @ListField varchar(50), @Query varchar(500)) RETURNS nvarchar(1000) AS BEGIN DECLARE @List nvarchar(1000) SELECT @List = COALESCE(@List + '', '', '''') + @ListField + ' FROM ' + @Table + ' ...more >>