I want to get Date part and Time part of a DateTime variable by using T-SQL in SQL Server. how to implement it? Peter ...more >>

Hi, We people are using alias name for tables in our T-SQL queries. One of my friend told that alias name will decrease the performance of the query. Is it right? I have used aliasing in so many queries. It looks silly, but I need your help. ...more >>

Hi, If a table has clustered or non-clustered index, then how does the index size increase or decrease if Truncate command is issued on the table. Also would like to know if the behaviour of Delete command is different from Truncate command. Thanks, Ajay ...more >>

I'm using an autonumber to create an ID for tables. I'm currently using BigInt because it seemed best suited for the job; however, I'm not sure that I should be, instead, using Numeric. Please let me know the advantages and disadvantages of one over the other. Thanks. ...more >>

Imagine I have a data base with information about customers and the products they have bought. I have these three tables: Customer: id_customer, customer_name Product: id_product, product_name, product_price CustomerProduct: id_customer, id_product. I need to know how much a customer has...more >>

Please help me modify the procedure to change the #fraglist temporary table to a permanent table. Write the dbcc showcontig data to the permanent table before and after the reindexing and defragment. Please help me with this task. Thanks, Mike CREATE PROCEDURE INDEXDEFRAG ...more >>

hi, I have a view that 'union all' bunch of partitioned tables of over 30 million rows each. these tables have over 500 million rows in total. each table has clustered index and a combined index (of two columns) on them. When you search each individual tables, it's really fast. Ye...more >>

A sql book I am reading claims that when an after insert trigger fires the inserted table may contain 1 or more rows. However, this seems incorrect. Instead it would seem that the inserted table should contain exactly 1 row since every insert statement creates one row in the inserted table. How ...more >>

I am trying to execute this SP via a web service: CREATE PROCEDURE AdminRemoveBook (@BK_ISBN [nvarchar]) AS DELETE [shopDb].[dbo].[Books] WHERE ( [BK_ISBN] = @BK_ISBN) GO The "Books" table has several fields, and the BK_ISBN primary key is nVarChar(15). The xml returns "true" and...more >>

Hi, This is driving me crazy, even tough I am using left join, the query only brings the results where the field is not NULL (Form Emp.Id). It should display ALL cases even thogh there was no EmpId, correct ? What am I doing wrong ? Query: SELECT a.Id , a.CaseId , a.EmpId, a.AlienId, b...more >>

I am trying to create a trriger. My DB has two admin users. Only user who has a admin right can have right to either delete or insert a record on contract table. when they delete or insert a record on contract table, it wll generate the modification information which is user name , contractNum, da...more >>

Hi all Is it possible to create a trigger on SYSOBJECT ? I am logged on as dbo, and trying to create a trigger that executes when a table with a specific name is created. 1) Check if you are DBO: select user Result: dbo 2) Create a trigger CREATE TRIGGER [BBP_SO_AI] ON [dbo].[sysob...more >>

Hi I have a table create table temp_cname ( cname char (3), id int (4), sym04 char (10), c_num char (10), s_num char (6) ) go which has a few sym04 values of 123425KS, 584461KS etc. I am trying to insert a a period betw...more >>

I know this is probably a stupid easy question.... but i'm having a major brain fart today....its monday! i have 2 tables with the extact same structure.... i need to create a query to grab records from both tables that meet certain criteria.... i can't think how to structure the query to do ...more >>

We use software written by a vendor and I like to check all new modifications to see if they work right. On a new mod we received, the program is leaving a transaction open (I guess) because while using it the program will throw error windows at me saying a timeout has occured. When I loo at s...more >>

I'm making and application in .NET (C#) and I need to store objets in a SQL Server 2000 tables. I magane three objects: - Object "Customer" with simple attributes (int, string, float) - Collection "Customers" that stores Customer objects - Object "Subscribers" that has simple attributes (data...more >>

Hi Sql Gurus, select * from tab1 (nolock) where col1 = 344424 and CreatedDtTm between isnull(null,CreatedDtTm) and isnull(null,CreatedDtTm) OR select * from tab1 (nolock) where col1 = 344424 and CreatedDtTm >= isnull(null,CreatedDtTm) and CreatedDtTm <= isnull(null,CreatedDtTm) ...more >>

I've seen articles on passing an xml node into a SP as a text or varchar param and then some how using it as a table in the SP to perform various tasks such as an update or inserting multiple rows. However, I cant remember where I say these articles. Can anyone show me some sample code on how ...more >>

Very rusty on on my TSQL and would appreciate some guidance. I have 1000 records that need to insert from one table that only has the ID column populated into another table 3 times but with the same 3 value types each time. i.e ID type 1 31 1 32 1 33 13 31 13 32 1...more >>

Hi all, I have a function on MSSQL server, but I don't want to seen it by DBA. When I use stored procedure and while used with encryption it's OK for strored procedure. I want to do same think to function and hide my method. I can't use stored procedure directly because I am using function in ...more >>

Hi All why cant we create triggers on Views??? Ramesh ...more >>

How do I filter the result of an SP, for example, sp_who2, to only show the result for a certain database? Thanks ...more >>

how do i compare two records and find out which value is different and get the name of that column? more then one value and be different i.e. col1 col2 col3 col4 col5 20 20 hi 20 20 20 town 0 ...more >>

I need to know if there is any way to have a profiler running and save data in a table.. If I use profiler, the api is hearvy, is there not at "light" version for this.. I will have the trace data saved in at table named "tracerun" Please hlp me //Spider ...more >>

I have the following: SELECT FirstName, LastName, Age, CAST(NULL As smallint) As RecordNumber INTO #TMP_TABLE FROM MyTable ORDER BY FirstName, LastName, Age In the statement that follows, I want to fill the field RecordNumber in table #TMP_TABLE with 1, 2, 3, .... in sequential order. Usin...more >>

Hi Is it possible to have something like this SQL Server? CREATE TABLE [Subs_ConfigAdmin] ( [ID] [int] NOT NULL , [DBVersion] [int] NOT NULL DEFAULT (MyConstant) CONSTRAINT [PK_Subs_ConfigAdmin] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] ) ON [PRIMARY] GO MyConstant is a g...more >>

does anyone know how to remove the ("nnn" rows affected) text from the bottom of the output file of a query executed via the osql command line utility?? ...more >>

...more >>

i have a field in my db of type varchar(50). somehow, this field is storing the small box character as the value. this value only appears when i go to the record through enterprise manager and actually select the fields, otherwise it just appears blank. how can i do a search to fix all record...more >>

Hi, Why the following code returns 4 instead of 3 ? Thanks, Alan declare @a smalldatetime set @a = '2004-03-31 23:59:59' print month(@a) ...more >>

how do i compare two records and find out which value is different and get the name of that column? more then one value and be different i.e. col1 col2 col3 col4 col5 20 20 30 20 20 20 10 10 ...more >>

We are completing the restructuring of our schema. Currently we have one table that stores all the values for 250 lookup fields. The biggest problem we have is lack of referential integrity between the lookup table and all the tables that lookup values against the lookup table. If we didn’t...more >>

I have a series of SQL batch scripts that I'm using for application testing. I need to be able to pass command line arguments into these scripts to avoid having to rebuild them every time there is a small change in testing conditions. I can't create the scripts as stored procedures because...more >>

Here is my question. All of my data that I need to insert is on two tables. I need to select all of the data from one table, then have two other select stmt's that will fetch the remaining data based on two columns on the previous splat (SELECT *). This will be done on multiple rows returned ...more >>

Hello Sql Gurus, There is already an existing sp i need to improve the performance. I got stuck with the following queries Can any one of you help me out in improving the following Queries by making it a single query or atleast two queries. UPDATE it SET SellerEMail = dcl.Cont...more >>

Hello, Did anyone have some experience with the following: 1) Need to track high volume of impressions - 20,000,000+ per day 2) Backend is SQL Server 2000 3) Webfarm of IIS with ASP.NET 4) Need to track the data as follows: DATE, TIME (resolution should be 1 hour), ID, NUMBER OF IMPRESSION...more >>

Hi I would like to export the date from SQL Server like the following: 2004 Jan 01. I have almost got it except there is no leading zero on the month so right now I am getting 2004 Jan 1. Code snippet below: CONVERT(CHAR(4),DATEPART(yyyy,dbo.Schedule.StartDate)) + ' ' + CONVERT(VARCHAR(3...more >>

I have a data witch is designed wrongly, it is not possible to "Just" rewrite the source using the database :( I have a table <table1> witch i join with <table2> as I again join with <table3>.. very simple.. Table design: Table1: Cust_No INT, Cust_Data TEXT --Fictive Table2: ...more >>

Hi all, I'm trying to get ADO and cancelling of statements working. I'm using Delphi and its ADO components. I'm executing the statement (a SELECT in this case) with the asyncexecute and asyncfetch parameters. In a background thread, I'm waiting while it executes and in the main thread, ...more >>

Hi, I'm trying to debug a Stored Proc that uses a smalldatetime value, but I keep getting the error '[Microsoft][ODBC SQL Server Driver]Invalid character value for cast specification' I've tried passing in 5/7/2005, 05/07/2005, 05-07-2005, 05-07-2005 00:00:00, 20050705. Also tried using s...more >>

I have the following statement: ==================================================== SELECT IDENTITY(smallint, 1, 1) As SrNo, [Date], AccountID, Symbol, Qty, Price, TradeDate INTO #PrevPositions FROM Positions WHERE [Date] = @ProcessDate AND AccountID = @AccountID ORDER BY AccountID, Symb...more >>

I am porting an app from Access to SQL Server 2000. In the Old App, a table is copied to another name (e.g. BillingTable -> BillingTable062104). I have found sp_rename but, of course, this destroys the original table. Can I programatically copy a table to a new name (i.e. cp BillingTabl...more >>

I have an application that uses COM to do simple INSERTS into SQL. The data is received from about 4,000 client passed over the Internet. When I start my app, everthing works well but as the database grows, my clients start to time out because the information takes longer to write to the DB. Is ...more >>

I am trying to put the result of a select statement into a new table. The statement I am trying to use is below - it fails with a complaint about the parens. I can't seem to figure out the correct way to write this ========================================== select * into Test1 from...more >>

Hi I was told to check the version of sql server to see if it needs an update. I done the above command and it says the below but I read on the Microsoft site that the most up to date service pack is 3 but below it says service pack 4??? Microsoft SQL Server 2000 - 8.00.760 (Intel X86) ...more >>

Is there a way to know when a index was created? I see crdate in sysobjects but nothing like that in sysindexes. SQL Server 2000 EE sp3. Any information is appreciated. Thanks, Kirk...more >>

Hi, Is it possible to create a trigger (or something similar) on a table so that when a row in that table is deleted (or just before), a stored procedure using a value from the row to be deleted as a parameter is executed. Thanks, Paul...more >>

I post my discussions on Microsoft's site but find it difficult to track my post or responses I make to other post. Does anyone know of or use a tool to help track their discussions? I get emailed when someone responds to my post which is great but I am curious if there is a more adv...more >>

Using the SQL syntax ALTER TABLE {tablename} ALTER COLUMN {field} {type} in Visual Basic, in Access 2000, what should I use for {type} to set the data type to Yes/No? Thanks Caroline...more >>

Hello Gurus, I got the following Table with 3 records in it. Select 'Something1' Something, '01' Code Into #MyTable Union Select 'Something2' Something, '02' Union Select 'Something3' Something, '03' Select * from #MyTable What I need, is to Select the Record with Code = '02' and ...more >>

Hi, I have a table (tbl1) containing four columns: colA (id) colB (varchar) colC (varchar) colD (datetime) I have data in another table (tbl2) where I want to update colB, colC and put the latest time and date in colD, that match with colA in both tables. I know I can use triggers for...more >>

Hi I have two Master tables. Table Name Cols ---------- ----- MstParent parentId, col1 MstChild ChildId. ParentId, col2 MstChild is the child of the MstParent. and i have a transaction table TranDaily in that table i have the following cols. Table Name Cols ---------- -...more >>

Hi Everyone, Is there any way (without using convert function) so that Datediff in Year format gives me output based on Year difference on the entire date and not on only Year portion of the date ? For example, Datediff(yy,'31 Dec 2003','1 Jan 2004') if giving me result 1 whereas I require it as 0...more >>

When you supply scripts to customers, where you believe their collation default may be different from yours (e.g. UK <-> US), do you --- in order to avoid collation conflicts --- go through (manually) and remove all the "COLLATE" clauses that scripts from EM etc. seem to generate in their database &...more >>

How can i know the number of fields in my tables ? Thanks ...more >>