I have been trying to figure out permissions and how they work on my Sql Server. I have gotten quite a bit of help and think I understand most of it pretty well, but am having trouble understanding why this is happening. I have a Domain - Travac. My Sql Server 2000 is on Raptor - which is par...more >>

Hi All, I have a this query which works fine, SELECT Hours * (SELECT Rate FROM tbRate WHERE ID = (SELECT MAX(ID) FROM tbRate WHERE Project=T.Project AND Employee=T.Employee)) as Amt FROM EmployeeTime T The output is like this: Amt ------------- 200.0 300.0 If I want to sum the...more >>

I would like to import Table A's Primary Key Values into a New Table B as a new record. Using SQL, how would i do that? Example: Table A MemberID [Primary Key] Name ------------------------------- ------------------- 1 Bob 2 Mary 5 John 7 Billl Table B Bil...more >>

I need help with a SQL problem. This is "problem" as in like math problem. (And no, this isn't for class, it's a contract that I've lost a lot of time on by spending it pulling my hair out.) I have a database with one catch-all table, something like as follows: string Username string Key s...more >>

I am trying to write a stored precedure which will retrieve and analyse a load of telephone call data from an electronic phone bill. For each phone call, I have a dtStartTime and a dtFinishTime, both DateTime fields. I know that we get charged 3 pence per minute between 8am and 6pm (peak), ...more >>

The following set of commands do not seem to work. Any idea how to dynamically set the current database in T-SQL? DECLARE @Publisher_Database_Name varchar(8000); SET @Publisher_Database_Name = N'Northwind'; use @Publisher_Database_Name; GO Error message: Server: Msg 170, Level 15, State...more >>

Hi, I have 2 tables: Table1 and Table2. Table1 and Table2 have the same columns Date Name Value Table1 contain original data and has the primary key is column Date and Name. Table2 is designed so that it will contain any fixed data from Table1. The View1 will select ALL data in Table1 ...more >>

Hello, If you run the sql statement below, you will see that the default index created for the primary key is clustered. From what I understand, clustered index is useful for columns that are mapped to many others; for instance OwnerId will own many objects so it will be a good candidate. So ...more >>

How can i retrieve the nth row from a table using join Aneesh R....more >>

I'm using the script below to copy data from an old database into a new one and am wondering if there might be a more efficient way of doing this. I would also like to do this in a way that won't fill up the transaction log if its possible. thanks, Aaron BEGIN TRANSACTION DECLARE ...more >>

If the query joins 2 tables from a linked server, the performance seems very bad: EXEC sp_addlinkedserver 'SEATTLESales', N'SQL Server' GO select * from SEATTLESales.myDB.dbo.myTable1 T1 join SEATTLESales.myDB.dbo.myTable2 T2 on T1.c1 = T2.c2 Because the select statement acces...more >>

I was surprised when I hit this issue today. It seems that when I create an indexed view on a table with a computed column, even though the CC is not in the index, that all connections to the base table of the index require the same SET options. As you will see below if you run the 2 scripts in...more >>

Hi, I am trying to create an index in a view, and after I select the columns, the unique clustered, and hit ok, it returns: --------------------------- Create New Index - localhost --------------------------- Server: Msg 1943, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][SQL Server]I...more >>

Hello Is there anyway to convert a GUID to a 10-12 character value, using some sort of hash function or lookup val or something? Want to let customers type in a 10-12 digit code and from that reference an entity guid. The app is distributed so don't want to assign it in one place. than...more >>

i want to execute my string in stored procedure like this declare @query varchar(50) if @surname<>"" set @query="select * from urunler where 1=1 and surname="+@surname exec @query is it possible? or how can i do it? i have some parameters. as depend on value of this parameters i want c...more >>

Hi all, Ok - I've just drawn out what I need to achieve in my .net web application and I'm not 100% sure that I can achieve the sql side of it - so I thought I'd ask here :o) I have the following stored procedure structure sproc1 sproc2 sproc3 sp...more >>

does anyone know of a way to write this procedure without building up the sql string first?? CREATE PROCEDURE spLookupValue ( @table_name as varchar(50), @field_name as varchar(50), @field_value as varchar(50), @user_id as int) AS declare @sql as varchar(1000) declare @err_code as integer...more >>

I know how to create tables in another database via a stored procedure (i.e., the stored procedure resides in databaseA and a table is created in databaseB from the stored proc executed in databaseA)... however, how does one create a view in a different database using a stored procedure. To cr...more >>

Hello and thank you for helping. I have written a SQL Server database front end for a mixed win98/winXP/win2K Office97/2K environment. The error only happens on some machines of varying office software and OS. I fixed one win98/office97 machine with the MDAC_TYP.EXE file when I saw there was...more >>

is there a way to select the name of a column in a table? for example, i want to select the column name in tableA that exists in the 3rd column ...more >>

I have a sql script that I would have sworn worked 10 minutes ago, but after continuing to play with it returns the error: Server: Msg 8154, Level 16, State 1, Line 1 The table 'drUser' is ambiguous. The TSQL follows: update drUser set drUser.managerid = mgr.userid from drUser U INNER ...more >>

Hi, I'm wondering if there is a better way to select a 'bit' indicating = existence of a record. For example, I am currently using the following = statement: SELECT [other columns from e], -- this is what I'm questioning: (SELECT top 1 CAST(COUNT(*) AS BIT) FROM dbo.EventNotes en WHERE = ...more >>

Using ROUND to truncate (using a non-zero third parameter) is not giving me a truncated result. Anyone know why? Example: create table test (f float) insert into test values(2.9999999999999999E-2) declare @a float, @b float SELECT @a=ROUND(f,6,1), @b=f FROM test if @a=@b print 'Truncated...more >>

Hi, I'm using VB6 + ADO + SQL Server 2000. I specify a timeout of 20 seconds. I know my query lasts about 10 seconds from the Query Analyser. Still, from my VB, the treatment never ends, and never times out either ! When I look at the active jobs, it indeed shows my query running. I ...more >>

I have a stored proc that runs this command "Update SVC00300 set svc00300.ITEMNMBR=rtrimsvc00300.ITEMNMBR)+'-U' from SVC00300 where svc00300.itemnmbr not like '%-U' and svc00300.custnmbr not in ('SUPPLY', 'WAREHOUSE')" The command works perfectly in query analyzer. We run sql 2000 (Sql...more >>

I have the following in a stored procedure. This procedure executes several times a minute as clients visits this Web Service. ============================ DELETE FROM TransGivebackSummary WHERE CardID = @138 AND GivebackID = 1 SELECT @LifetimeGiven = SUM (dbo.TransDetailsGivebacks.Quan...more >>

I'm currently trying to optimize a particular SQL QUERY to run as fast as possible. The database is enormous and the amount of data returned from the QUERY is monstrous so any tuning tips would be greatly appreciated. Put simply - the database has 2 main tables: Projects and Assignments...more >>

I'm looking at a web application that has user definable fields in a great many tables, and I wanted to see if there was a consensus on the best practice for achieving this. Currently the web-based administration allows admin users to name certain fields in a table, determine their type, and a...more >>

Subject says it all... I have an INSERT/UPDATE trigger on a table that deletes the row being inserted or upated if the amount = 0. CREATE TRIGGER [RemoveCleared] ON [dbo].[Table] FOR INSERT, UPDATE AS DELETE Table FROM Table t1 INNER JOIN inserted i ON t1.Id = i.Id WHERE i.Amount =...more >>

Hi All, This is a twist on an earlier post, I'm trying to figure out why a query that I would assume to receive great assistance from an index would produce awful performance results. Here's the background... I'm runing SQL2K (no SP's installed, yet) Developer Ed., on a P3/256MB w/ about 1...more >>

HI ALL GURUS I HAVE INSTALLED SQL SERVER AND IF I WANT TO TAKE COMPLETE DUMP OF SQL SERVER , IS ANY COMMAND LINE UTILITY FOR IT. THANKS AND REGARDS ...more >>

I would like to know about the security features about user,roles,permissions,database ownership related info Is there any good document on this.. Thanks in Advance PVR ...more >>

Hi, We would like to distribute a ready SQL server to our customers, with our installation. We create the msi installation file with WiX and can also do it with VS.NET (though it's really the same ...). We cant use Installshield, nor Wise and any other setup programs. Could you recommen...more >>

where to find instructions on how to backup database remotely to local machine ...more >>

SQL 2K My SQL job , executes 4 stored procedures and recently I added SELECT hint READPAST in my select statements. Occasionally my job fails with error message Executed as user: dbo. Transaction (Process ID 164) was deadlocked on lock resources with another process and has been chosen as t...more >>

I am using the following statement as part of a cursor to calculate medians. Select @MedianValue=Price from @tmpTable where PK=@MedianRow The odd thing is that it runs fine the first time and is then ignored on every iteration besides the first. The value returned is the same as that of the...more >>

Ok. I have a SQL statement like the following: SELECT * FROM (SELECT * FROM CATEGORY_LINK WHERE GROUP_ID = 2) RIGHT JOIN CATEGORIES ON CATEGORIES.ID_CATEGORY = CATEGORY_LINK.CATEGORY_ID (NOTE: I know that the above statement will not work...it's just there to illustrate what I want to do.) ...more >>

background: sql2k on nt5. i have a table with two columns: Email and EmailHash is there a function or way to update whatever the values are in Email and generate a hash value for it in the EamilHash column? thank you!! ...more >>

Hi, I am trying to alter a view to contain with Schemabinding, and I get this error: Cannot schema bind view PAY_TOTAL' because name INVOICED_WRITEOFF' is invalid for schema binding. Names must be in two-part format and an object cannot reference. What is this 2 part format means? How can ...more >>

Hi, I have a table with email addresses, name@domain.com. I need to create a new field for domain name, the value should be the same as the email but only everything after the '@'. How can I select everything after the @ and insert it in field 'domain' (which is in the same table). The field ...more >>

Hello everyone, I would like to know if it is possible in Access to save the username of sql server in a field. Greetings, Sam ...more >>

I have name column in Partner table, type of For example, I have name "Peter Pan" ID(int) NAME(nvarchar) ------------------------------- 1 Peter Pan .... If I try: SELECT CHARINDEX(' ', name))as indeks from Partner I get 0 - but I have blank space inside the wo...more >>

I would like to remove s and blank from names on the start. For example: I have table Partner with ID(int) and name(nvarchar(200)). ID name ----------------- 1 s Mary 2 Mark 3 s Bill 4 s Irene 5 Kevin 6 Peter ....more >>

In a certain stored procedure, I have the following: DECLARE @priv_level INT SELECT @priv_level=priv_level FROM sometable WHERE foocol = 1 IF( @@ROWCOUNT = 0 ) -- error IF( @privlevel > 16 ) -- permission denied ELSE -- ... The sole purpose of priv_level is to check whethe...more >>

I've changed into a table the column type of a field from money to numeric. After that some query using thos field didn't work anymore 'cause the system seen thois field as varchar (and so rise an error for aggregation function and operation like field1 -field2). I had to go into design view cha...more >>

I have a SQL job that runs as part of a nightly load of data tables. Part of the job is a DTS job (loading a table from a text file), and other parts of it are either sql code, or calling of a stored procedure. We have 2 production servers, both (I thought) set up identically. SQL 2000, most...more >>

I have a few fields that have defaults set. I decided to change the names of a couple of these fields and noticed after scripting the db that the constraint names didnt' change. I looked around for a place to change the names but found that there is no such place (that I could find) in EM. So I...more >>

Does anyone know where to find or how to write a quick user defined fucntion that will return a table object when passed the string name of the table object. The reason why I want dynamicallly set the table name in a stored procudue WITHOUT using concatination and exec a SQL String. Hence ...more >>

Hi All MS SQL 2k - Dev ed. I got the basis of the following stored Proc from somewhere (can't remember, think it was aspfaq.com) and have incresed its speed slightly. I was wondering if any of you gurus could change it and increase it even more. This is basically to return @intRecordsPerPag...more >>

Hi, I have a table that I am trying to insert into. It has a primary key made up of 2 fields, a datetime field and an integer field. I am using DTS to insert data from another table into this new table with the primary key. When I try to insert 2 records with the same date but different ...more >>

I am trying to execute the following in QA SELECT * FROM OPENQUERY(5DL, 'Select Account,accountNo,campaignid from results where entrydate =Date()-1') and keep receiving this error: Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '5'. This is foxpro linked s...more >>

I did the followings, and would like to get the rowcounts at the end. I always got @@rowcount = 1. Can anyone help me? Thanks in advance. - Joe --------------------------------------------------------- declare @tbl varchar(35), @cmd varchar(128), @rc int select @tbl = 'categories' selec...more >>

Can we use dynamic quries in User defined functions?...more >>

Hi, I need to write a custom interface to create/restore a backup of a database (without using enterprise manager but my custom C# interface) in SQL Server 2000, is it possible? any ideas, info will be greatly appreciated. Cheers JT....more >>

Can anyone help me on writing a trigger that disallows insert into the table based on certain condition. for example if the value that is being inserted in Employee Name column has text 'Unknow' then just ignore Insert. ...more >>