Hi, My db uses full recovery model. I used maintenance wizard to perform: 1) Index rebuild every night 2) Full backup once a week 3) Log backup every night 4) Integrity check every night The log file is 1GB now and because of regular index rebuild I expected this growth. DBCC LOGINFO displ...more >>

How could I get IP address of user (executing query on a machine other than server). I want to create a trigger on delete of a table which will insert data to a separate table storing the IP address of user. ...more >>

We have a worktran table WoNo Jobno Status 1 01 Fullfill 1 02 Pending 2 01 Fullfill 2 02 Fullfill We need to display those WoNo having all its rows status=fulfill In the example above, it should display only WoNo 2 because it contain all Fullfill status We tried - select dis...more >>

Hello, I was wondering if anyone can help me with a problem I have. I have a program (beyond my control) that constantly posts to a table the current value of a set of parameters identified by name. I need to query it and return for a given parameter and start\end times which values it had ...more >>

Hi, I am trying to get a list of subordinates from a nested table model. What is the meaning of the bold part in the SQL query below? SELECT P2.* FROM Personnel AS P1, Personnel AS P2 WHERE P1.lft BETWEEN P2.lft AND P3.rgt AND P2.emp = [b]:myEmployee;[/b] Thanks. -- -----------...more >>

What's wrong with this SP? ALTER PROCEDURE dbo.ViewRecords @UID int, @OID int AS DECLARE @Addr varchar(50), @Val int SET @Addr = (SELECT BAddr FROM Table1 WHERE UID = @UID AND OID = @OID) SELECT CASE WHEN @Addr IS NULL THEN @Val = 0 ELSE @Val = 1 END T...more >>

I think I know SQL pretty well but can't do this, maybe it is not possible. I have a table with Territory, Emp, Status Example Data: Terr1, EMPA, Open Terr1, EMPB, Closed Terr1, EMPC, Open TERR2, EMPA, OPEN TERR2, EMPB, OPEN TERR2, EMPC, OPEN I WANT MY QUERY TO Show: terr1, 2 op...more >>

hi, i've spent ages puzzling over this, and read similar situations on the net but it's usually a different problem of people using 'dd mm yyyy' format instead of 'dd mmm yyyy'. using sql 2005. i have a very straight forward stored procedure, with a datetime parameter set to default to Ge...more >>

I have a proc that returns 5 columns. I need to add a 6th column which is essentially a string of user_id's. My problem is that this string might be over 8k. I also need to build up this string based on values I have in the proc, but don't return at the end of the proc. The values are in a...more >>

I am encountering a problem when trying to delete duplicate records when one of the "key" columns contain null values. Thanks in advance for your replies. Jack Delete N1 >From Names N0 (nolock) Join Names N1 (nolock) on N0.item_code = N1.item_code and n0.description = n1.descript...more >>

I have some proccess in transaction. Than I call some procedure, which works also on linked server. This part I would like to exclude from transaction. Is it possible? (and I can't have distributed transaction) begin tran ..... ..... exec procedure1 outside transaction .... .... ...more >>

Hi, I have troubles with a threaded application on W2003 server. It seems to leave open connections behind time to time, it sums to hundreds over a day (the application make thousands). It is using the SQLOLEDB provider, MDAC 2. 82.1830.0, SQL Server 8.00.2039 (SP4), Windows 5.2 (3790). I...more >>

Is this something you can only do with additional developer tools. as there does not seem to be a facility in the express edition. of visual web developer -- Goofy ...more >>

I found some of my developers (on 2000) are overnormalizing, i.e. they gave me a 13 join query to optimize. This query pulled 21 columns from 14 tables and used, as I mentioned, 13 joins. They are at the beginnings of a design that could grow big and so it is very important that this scala...more >>

Hi, I have created my linked server to another SQL Server, now I need to know how to connect to it before running the select statement. How do I do this ? -- Goofy ...more >>

I've got a user defined data type in C#....when using the context connection to issue the query below, I get this error: {"This statement has attempted to access data whose access is restricted by the assembly."} I have this attribute line before the method: [Microsoft.SqlServer.Server.S...more >>

I am a newbie to Sql Server programming, and I was hoping that someone might be able to point me in the proper direction. I am trying to write a C function that takes an array of strings and insert them into either a temporary table or a table variable. This table will just be a one column ta...more >>

I have a simple stored procedure that inserts rows into two separate tables: client table and address table. The pseudo code is as follows: -- Client info @Name @AccountNumber .... -- Address info @AddressLine1 @AddressLine2 ..... begin transaction insert a record to the address table...more >>

Hi, I've got this query: SELECT lubaszdb_user.Galleries.ID, lubaszdb_user.Galleries.Name, lubaszdb_user.Galleries.Comments, lubaszdb_user.Images.ID AS ImageID FROM lubaszdb_user.Galleries INNER JOIN lubaszdb_user.Images ON lubaszdb_user.Galleries....more >>

With SQL Server 2005, this is producing a syntax error- ALTER TABLE mytable ALTER COLUMN mycolumn SET DEFAULT 'something'; Yet google finds references that claim that syntax is supported. What is the correct syntax? ...more >>

Yesterday 6 items were accidently deleted from admin on our websites sql server database. I have a local backup of the database and did a select query to view the items we need back in our live database. I think the first column is an identity field and when I save the selected data to a .CSV f...more >>

Hi, I used a small example of Joe Celko regarding SQL Trees. The code is: IF Exists(Select * From Information_Schema.Tables Where Table_Name = 'Tree' and TABLE_TYPE ='BASE TABLE') Begin drop table [dbo].[Tree] End GO create table tree (emp Char(10) Not Null, Boss Char(10)) ...more >>

I want to create a copy table from another table. What is the simplest way to do this with a sql statement. Ive tried Create Table MyNewTable Select * from ExistingTable but this fails. -- Goofy ...more >>

I recently had to reinstall SQL 2k on my development system. I don't use the c:\Program Files\... folders for my data files but instead C:\Data\MSSQL... Somehow I managed to end up with my databases in c:\Data\MSSQL\Data\MSSQL... this time. I detached, moved and reattached the db's I'm developing...more >>

Hi, I am facing a very strange error, wonder if anyone can kindly help. Say I have a table like this InvoiceID ItemID Quantity LineValue 182786 317067 0 12.00 182786 317068 1 67.56 182786 317069 -1 0.0...more >>

I have a stored proc on Server A that is executing a stored proc on Server B. The stored proc call on Server A looks something like this: [ServerB\SQLServer2005].TargetDatabase.dbo.TargetStoredProc 'SomeData' Can anyone tell me what port is being used on Server A to execute the stored proc...more >>

Hi! I'm trying to determine the best/easiest way to query the SqlServer for the presence of a specified index. I'm using two methods. The first is to use sp_helpindex, but this is abit cumbersome for my needs so I was hoping method 2 would work better. The problem with method 2 is that ...more >>

Hi again, In my database's indexed table, there's a column called "Filename", that unsurprisingly contains an indexed list of filenames. There are no spaces in the filenames, but spaces are replaced with either hyphens "-" or underscores "_". But there's a problem, SQL Server's full-text...more >>

Hello I'm having problems with searching 2 tables using the CONTAINSTABLE function. This query works great with just one table, but I need to adjust it so it also searches Companygroups. No matter how I left or right outer join it seems to either return nothing or everything. Anyone know howto...more >>

Hi I have been trying to find a query method in sql. Google engines search phrases and find the best suitable result . for instance if you write (play station) it can find all suitable result in "playstation" or "play station" but I could not do it in sql(2000) even in fulltext search I tri...more >>

Hi, We have the following scenario. the application works in a multi user environment. we have a screen opened by user1, which shows data from several tables. it fetches and populates the data as it is opened. Now, there is action done by user2, which inserts or updates the records in ...more >>

Hi, We have a stored procedure, that returns the set of rows of companies... Following is the query in it. Select PMCompanyID AS [COMPANYID] , TC.NAME AS [COMPANYNAME] , TC.ShortName AS [SHORTNAME] From PM_Company AS PMC INNER JOIN T_Company TC ON PMC.NOMSCompanyID = TC.CompanyID...more >>