I have a server/client application that has multiple users with different access rights to the data in a database. Access is granted on a column level. First I was planning on writing my own autorization/ authentication layer. Then I was told that the right way to do this is by creating views th...more >>

Do you know of any applications such as RedGate's SQL Compare that are less expensive or free? <%= Clinton Gallagher NET csgallagher AT metromilwaukee.com URL http://clintongallagher.metromilwaukee.com/ MAP http://wikimapia.org/#y=43038073&x=-88043838&z=17&l=0&m=...more >>

Hi all, I have a SQL table which needs to act as a "queue". Multiple threads/connections will be requesting the next record from the table. What is the best way to structure the table so that unique records dequeued to users (i.e. 2 users never have access to the same record). Would run...more >>

I am having a conversion problem. See below: Code snippet from encryption routine: case @tvByteUnencrypted when 'a' then char(157) when 'A' then char(144) Code snippet from decryption routine: case @tvByteUnencrypted when char(157) then 'a' when char(144) then 'A' When executing ...more >>

I have a table that keps tracks of employees punch ins and outs. [code] CREATE TABLE [EmployeePunch] ( [scalldate] [varchar] (20) NULL , [sEmployeeId] [varchar] (20) NULL , [TotalHr] [numeric] (10,4) NULL , [sLogin] [varchar] (20) NULL , [sLogout] [varchar] (20) NULL ) INSERT [Em...more >>

Hi all, I'm trying to construct a sql statement in my stored procedure. My application calls the stored procedure and will pass it a concatenated string containing guids.... These should 'not' be returned in the results of the query, the problem I'm having is the conversion between the ...more >>

I have a sophos (Antivirus) database installed by the Sophos software. The databases are flagged as suspect I do not seem to be able to run a "dbcc checkdb" .... the database cannot be accessed because it is suspect so I can't fix the corruption ??? It must be a 2000 MSDE install I guess...more >>

we recently migrated the database. we have a huge tables rows and some of them have many duplicate records Is there any query to identify duplicate rows? thanks...more >>

I have SQL 2000 and use Enterprise Manager - no problem! I just gained access to SQL 2005 for testing. Cannot connect to SQL 2005 With EM. I installed SQL Server Management Studio Express, connected, but I don't see where I can create DTS packages. I don't see any of the Wizards that were...more >>

Hi, I have a table with two non-clustered indexes. by default, the query use the first index. How do I dictate the query to use the other index? Thanks ...more >>

Hello, I have a class in my ASP.NET project. I made this class Serializable. I now need to Serialize it and sent it to an SQL 2005 database table. Does anyone knows such examples? And what data type should I use in SQL 2005 to hold this? Image? Thanks, Miguel ...more >>

Hi. I need to parse/replace/manipulate strings > 8000 characters. I understand you really can't accomplish this w/o breaking up the string into smaller pieces, but all the references i found in this group are for MSSQL 2000 or older; are there any new ways to deal with this in 2005? ...more >>

Hello, I am having a problem with a user procedure in an SQL Server 2000 DB. The procedure should update some columns in a table for the record that matches the WHERE statement. When I run the proc I just get the message '0 records affected'. The proc is run from an Access 2000 DB by using ...more >>

After upgrading from sql server 2000, I read that i need to update statistics and rebuild indexes. Are there commands that do this for all tables? Are there anything that I need to do after the upgrade that I am forgetting? Thanks ...more >>

I have searched BOL & must not be using the correct verbage.. How can I search a database for all indexes in a database? I am confident it is a system sp or some kind of call to the system tables.. Thanks in advance, Claudia....more >>

Is it possible to write sql that is able to work on MS SQL 2005, Postgres and MySQL without translation or modification? ...more >>

Hello, I'm testing service broker on two machines in the same domain. I'm using windows authentication. When I begin a dialog on serverA the message is immediately put into the sys.transmission_queue with a blank transmission_status, and is_conversation_error = 0. Also, I specified the br...more >>

I'm trying to create an end-user style data dictionary. In the Table Designer you can enter a description for a column. How can you get it back? I can get extended properties for tables but can you do the same for columns, (I don't think columns have extended properties, but I've been wrong...more >>

Here is code that takes a name as input and puts it in proper case. -- LOWER() and UPPER() Functions DECLARE @LastName VarChar(25), @SpaceIndex TinyInt SET @LastName = 'shara gibbons' -- Find space in name: SET @SpaceIndex = CHARINDEX(' ', @LastName) IF @SpaceIndex > 0 ...more >>

We have a three tier web application that have implemented into a two server environment. The logical layers are the WebServer, Application Server (Business Layer) and Data Layer (SQL Server). I would like to know what is considered "better" in terms of security or any other relevant factors abo...more >>

Hi, Unfortunately in the project i participate right now we don't have DBA. we have to deply SQL server in production environement. Although i know how to basically install sql server i'm sure there are several deployment considerations i'm not aware of. can u please list some deployment conc...more >>

ITEM_NO decimal(12,0) not null ITEM_COST decimal(13,2) not null ITEM_QTY decimal(12,0) not null ITEM_START smalldatetime not null ITEM_END smalldatetime not null ITEM_NO ITEM_COST ITEM_QTY ITEM_START ITEM_END 5630 1.23 46 2006-04-01 2006-04-30 5630 ...more >>

This sounds stupid, but can you use a local variable or parameter in a CASE statement? I am trying to set a local variable to a value based on the value of an input parameter. However, I always receive a syntax error....more >>

Hi all, I'm using this code but I can't get the error code of sp_executesql when I have an error : Declaration ..... Begin Tran SET @DELETEFROM = 'DELETE ' SELECT @SQL = @DELETEFROM + @FROMWHERE exec sp_executesql @SQL IF @@ERROR = 0 BEGIN .... END ELSE BEGIN .... END Thanks i...more >>

Is it possible to create a script to export data from SQL Server, particularily to CSV files? Thanks. ...more >>

Hi everyone, I've created a table that stores the scheduling start date/time and end date/time. What I would like to do is to call several specific stored procedures when the system time is the same as the scheduling one. The StartDT and EndDT are also editable by the person in charge. So her...more >>

The following query works: SELECT sdb.name, sdb.status, MAX(bs.backup_finish_date) last_backup FROM master.dbo.sysdatabases sdb LEFT OUTER JOIN msdb.dbo.backupset bs ON sdb.name = bs.database_name WHERE (bs.type = 'D' OR bs.type IS NULL OR bs.backup_finish_date IS NULL) AN...more >>

GETDATE returned "12/30/1999 9:10" when it should have returned "02/21/2007 9:10." It was a one time thing, the production before and after (20 minute gap) had the correct date. The event and SQL Server logs don't indicate any problem. We're running SQL Server 2000 Standard SP4 on Windows 2...more >>

I'm pretty sure the answer is no, but ... I'm looking for a way to do arrays, hashes, or dynamic variables in T-SQL. Var[1], Var[2], ... (array) Var{One}, Var{Two} (Perl like hash) @DynamicVar='Var1' EXEC [something] PRINT @Var1 ...more >>

I would like a script to kill all current users on a database (and the entire instance) if possible and block users from conencting to the database while I am performing database upgrades, installing SP2 for instance. So I would be the only person who can connect to the while database instanc...more >>

Hi... Last week, one of my coworkers wanted to get a dev environment of our service broker setup, so he took a backup of the main db and restored it into a new db in the same Sql Server instance. So the new db has all the same message types, contracts, queues and services as the old one. ...more >>

Hello, I'm trying to work around the 4000 character limit w/ sp_executesql in SQL 2000 by breaking apart my query into two strings and passing them as parameters. To test this, I'm trying to make it work in a very simple case, using Query Analyzer. I have run into the following problem that I c...more >>

Hi, We recently upgraded one of our servers from Sql server 2000 to 2005. We still have a mix of 2000 and 2005 servers in the organization. On the newly upgraded 2005 server, I have a job whose final step is to spawn another job on a 2000 server using the sp_start_job command. This work...more >>

Hi All, Here is the DDL: --drop table #LeaveRequest --drop table #LeaveRequestUnit CREATE TABLE #LeaveRequest ( [LeaveRequestID] [int] IDENTITY (1, 1) NOT NULL , [EmployeeID] [int] NOT NULL , [LeaveTypeID] [int] NOT NULL , [LeaveStartDT] [datetime] NOT NULL , [LeaveEndDT] [datetim...more >>

Assuming there is a stored procedure with multiple OPENXML queries... Is it necessary to have: EXEC sp_xml_preparedocument @idoc OUTPUT, @XMLdoc select ... from openxml... EXEC sp_xml_removedocument @idoc for each openxml or is it ok to have one of each in the whole of Stored proc? Thanks ...more >>

I am trying to delete the records in a table when joining to data in xml using openxml. The error is: Incorrect syntax near the keyword 'as'. Could you see how to solve this error please? Here is the sql code: delete from tblMarketValues as mv inner join FROM OPENXML (@idoc, '/marketD...more >>

Is there a way that update rows without changing the original value of timestamp field? Thanks. ...more >>

Is there any way that updates rows with changing the value of timestamp field? TIA ...more >>

Hi, I have following problem. I have one table with location and quantity on that location. I wont to find minimum locations where I can pick desired quantity. From one location I can only pick whole quantity. Lets say I have this table create table TEMP_TABLE (id int identity primary key, Q...more >>

Hi All, I'm having a bit of a problem with an SQL statement, and I'm wondering if someone can give me some ideas? I have a table that contains information on trailer contents. I need to write an SQL statement to look at the contents of a trailer and determine if it contains parts for both...more >>

Hello Im having a small problem with finding numbers in a sequence depending on a "flag". Not sure howto explain it, but I'm trying to find the lowest missing number in sequence, IF the flag is 1, else find the next value where flag = 1. Note that I only need to find 1 value, not an entire ...more >>