Hi! I'm trying count the days that a person has been registered in my system given a specific time interval, i.e. 2007-06-01 to 2007-06-30. A person can be registered several times during a period. If a person starts at 2007-05-12 and ends 2007-06-10, and then starts 2007-06-15 and ends 200...more >>

Hi, I have an update query: UPDATE Pre_Appraisal_DevPlan SET DevPlan = 'Testing my capability. I am slogging...slogging...slogging to get this HRPro done.452', ModifyDate = '5/24/2007 4:16:50 PM', ModifyLawsonID = 'T3111' Where PreAppraisalGUID = '{C295F53D-727E-4E98-ABE5-1FE9828300A4...more >>

Dear All, My collegue Bj=F8rn D. Jensen and I have run into a problem with a database which we cannot solve. It's almost a test database, so we can drop the data in question, but not the entire database. But SQL Server will not let us drop the filegroup. Do you know how to get round this p...more >>

Hi If I want to modify the properties of the existing column of the table, what command should I use? The alter only allows add and drop of the column. Thanks ...more >>

Hello, after creating relationships between tables using T-SQL is it possible to visually see them in Management Studio? Thanks, Miguel ...more >>

tbl1 contains a unique ID column (not an Identity column) that is of datatype Int. The ID's range from 1 to 400,000, however, there are not necesarily 400,000 rows in the table. Each row has an ID. I want to select the ID numbers that are not contained in tbl1. Is there a tSql statement th...more >>

is there a performance hit when using a table expression rather than a derived table as in select * from x join (select * from y) y1 on .... vs. with y1 as (a, b, ...) ( select * from y ) select * from x join y1 on ... -- thanks (as always) some day i''m gona pay this forum b...more >>

Why is it when I script a view it gives me a bunch of red text lines that begin with "Begin DesignProperties ="? Do I need these? Also, what happened to the old DROPand CREATE scripts that used to be with SQL 2000 when I scripted a view or table? Thanks. David ...more >>

I need to determine dates from a given start date and a number of months, first going forward in time and second going backward in time. I got the going forward by using the year and month of the start date using the following code: SET @Future_Year = @Start_Year + ceiling(@No_of_Months/12) ...more >>

I have a simple insert statement that is taking much longer to run than anticipated. Below is a snippet. UPDATE MNT.dbo.MWB SET LZERO = DT.LZERO FROM ( SELECT L.ID AS ID, L.LZERO_FLAG AS LZERO FROM LZERO L ) DT WHERE MNT.dbo.MWB.ID = DT.ID GO I am updatin...more >>

I have this SP. When it does deletes the server returs -1 back to my apps. The deletes go thru Ok, but the value returned is -1. Any idea? set ANSI_NULLS ON set QUOTED_IDENTIFIER ON GO ALTER PROCEDURE [dbo].[usp_UpdateConsistPersonnel](@xmlstring TEXT) AS DECLARE @idoc int...more >>

can more than one common table expression be used in a single query? like : with x1 as (,......) (select * from x) -- with y1 as (,......) (select * from y) select * from y1 join x1 on ....... thanks (as always) some day i''m gona pay this forum back for all the help i''m getting ke...more >>

Hi, this statement: SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0', 'Data Source="\\server\excelfile.xls";Extended Properties="Excel 8.0;HDR=NO;IMEX=1"')...Sheet1$ works fine on our 2000 servers, but fails on the 2005 servers with an unknown OLE DB provider error, Message 7303. ...more >>

platform: sql2k5. i am looking for a way to limit the execution time of a query. i have litle control over the application is sending queries, so i need to find a way to do it on the server. is there a server and/or database setting for this? tia, gigel ...more >>

CREATE TABLE dbo.ProductDesc( ProdID nvarchar(16) NOT NULL, ProdType smallint NOT NULL, ProdDescription nvarchar(250) NULL) CONSTRAINT [PK_ProductDesc] PRIMARY KEY CLUSTERED ( ProdID ASC, ProdType ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] inser...more >>

Does anyone know where I can find these drivers for MSSQL Server? Thanks bunch!!! EggHeadCafe.com - .NET Developer Portal of Choice http://www.eggheadcafe.com...more >>

My SQL team was asked to test a new SQL 2005 instance of our production database. We have the management studio on our machines, but no other tools (ya SQL 2000 Ent Mgr and QA) but thats it. Can we debug SQL 2005 stored procedures without Visual Studio 2005 tools? ...more >>

Hi All: I did go through previous posts and could not either easily understand or thought it was a different problem that i am having at present : For example, a query of mine produces 3 rows. SELECT C.CustomerName, O.OfficerType, O.OfficerName FROM Table C INNER JOIN Table Officer O Whe...more >>

Help - Update Statement, I never really been able to understand it. Recommend me so reading. For example: Update Member_Login_Successful Set IPAddressId = I.IPAddressId From Member_Login_Successful M Join IPAddress I On M.IPAddress = I.IPAddress This worked but I can't understand ho...more >>

If an UPDATE statement is executed inside an INSERT + UPDATE trigger, will the INSERT + UPDATE trigger recursively fired? Any help appreciated. will f....more >>

We have an ssis package that imports a 2GB flat file. Sometimes it works and sometimes we get this error: Error: 2007-05-23 16:19:07.88 Code: 0xC020200E Source: Data Flow Task US File In [73] Description: Cannot open the datafile "\\computerName\data$\sharefile\LongPath\Load_Filel\FlatFile_...more >>

All, There should be a simple answer to this, but I haven't been able to find one. Maybe you can help... I have written a CLR stored proc which iterates through a small set of data and then inserts it into the db. to do this, I'm creating a dynamic insert statement string and executing it u...more >>

pyrahna View profile More options May 23, 2:46 pm Newsgroups: microsoft.public.sqlserver.programming From: pyrahna <pltayl...@gmail.com> Date: 23 May 2007 11:46:03 -0700 Local: Wed, May 23 2007 2:46 pm Subject: Can't quite get the right grouping w/ a view Reply | Reply to author | Forw...more >>

Hi All, I have been reading the wealth of information on groups for ages but this is my first post and i'm hoping someone can give me a hand. I have built a query in access to correct a postcode field which includes padding zeroes if the postcode doesn't conform to a certain amount of chara...more >>

I have a process that uses xp_cmdshell to do a directory listing that I put in a Sql table. I parse the filenames and use that in a compare statement against another tbl to run a process that does a matching process. My problem is the speed of the xp_cmdshell dir command. Should I use somethin...more >>

What's the functional difference between: SELECT <whatever> FROM A INNER JOIN B ON A.Field = B.Field and SELECT <whatever> FROM A, B WHERE A.Field = B.Field Is the 2nd one an "implied join" or is it called something similar? Do...more >>

Hi All How Can I Convert This Code Dim cimv2, PInfo, PItem ' no idea what to declare these as Dim PubStrComputer As String PubStrComputer = "." Set cimv2 = GetObject("winmgmts:\\" & PubStrComputer & "\root\cimv2") Set PInfo = cimv2.ExecQuery("Select * From Win32_Processor"...more >>

i'm working on a database(sql server 2005) which collect a lot of data everyday and process this data everynight (so that it won't affect daily querys). recently i wrote a stored procedure and add it into the nightly batch processing to get a summary of some data, and another procedure to que...more >>

Hi all. As I understand in 2005 we have got a new posibility to include columns to cover the query when making indexes. If I include a column will it make the DB work on the index when one of the included columns are changed? In other words, will including columns decrease inserts/updates ...more >>

tbl_1 ----------------------- dt corp pro 3/5 c1 p1 3/9 c2 p3 4/7 c5 p4 4/30 c2 p3 ======================== tbl_2 ------------------- dt2 corp2 pro2 unit_pr 3/2 c2 p3 100 3/8 c2 p3 200 3/30 c2 p3 300 4/6 ...more >>

Is there a command, a sp, something to know when a table reindex is needed? Yesterday while trying to insert into a table, ad a certain point the insert started to go slowly and on the same table just to delete 2488 records it gets more than 5 minutes. Since the tests has been done along t...more >>

I have a database which contains metadata for photos. I want to be able to query it for things like the caption containing some (user-entered) words, and query it for combinations of keywords. I made two tables, one containing the metadata which has one value, and another which contains the me...more >>

Hi all, I would like to know if there is a way to format a currency column on the SQL Server 2000 side of things (i.e. not formatted back on the client). For example, SELECT 'Your total is: ' + CurrencyFormat(ytd_totals) FROM ytd_figures Any such CurrencyFormat type of formatting exist? ...more >>

I found that in a query, this is done with SET DATEFIRST 1 That is OK, but not when I need to run a select-query to create a VIEW. Then it's forbidden to include commands like that. Inside Sql Srv Mgmt Studio, it seems like the database "remembers" this, but if I do a query from Excel to tha...more >>

Hey, please help! 1 select 2 count (dbo.SALESPICKINGLISTJOURNALTABLE.salesid) as TT 3 from 4 (select 5 count (dbo.SALESPICKINGLISTJOURNALLINE.salesid) as LL 6 from dbo.SALESPICKINGLISTJOURNALLINE) 7 8 dbo.SALESPICKINGLISTJOURNALTABLE Subquery does not work S...more >>

I have 2 procedures that return results based on search parameters provided... Example. Search for Don it will search both tables and anyone that has DON as the first name it will return Results: -------------- Don B Don G Don T Don Y and so on Now these results are combined t...more >>

I have 3 tables.. One is Brands One is Models One is Products Not all my products have a model. Some of my product have models some not. All my products have a Brand. All models have a brand.. What is the best way i should follow ? ...more >>

Hello, The scenario is; update a ntext field from an Access linked server memo field; UPDATE T1.Commentarios = T.PER_Comentarios From Operarios as T1 INNER JOIN (Select CAST(P1.PER_Matricula AS int) as Matricula, PER_Comentarios From OPERARIOS...Personal as P1 INNER JOIN ...more >>

Hi all, Does anyone have any recommendations (or good links) to implement data encryption at the column level in SQL Server 2000? For example, we might have a table like: ID, FirstName, LastName, Zip We would like the FirstName, LastName, and Zip columns to be encrypted, so that if pry...more >>