Good afternoon all. I'm going to post the stored procedure in it's entire glory. Feel free to rip it to shreds. The author won't mind. DECLARE @itemTypeID INT SELECT @itemTypeID=ItemTypeID FROM dbo.ItemTypes WHERE ItemTypeName = 'Advert' BEGIN SELECT a.Active, a.ParentClass, a.Classification, a.Variant, FV."Full Views", ...
I have recently moved a database from Sql Server 2000 to Sql Server 2005. In the table designer, it insists on putting square brackets around a column named "Content" I don't see Content on the list of reserved words for Sql Server, so I don't understand why it is doing this. Is there a way I can prevent it? ...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'This a Lock ID 3', @LockMode = 'Exclusive', @LockOwner = 'Transaction', @LockTimeout = 60000, @DbPrincipal = 'public' if @res < 0 begin declare @errorMessage nvarchar(200) set @errorMessage = case @r...
Sorry about the last message. I did a paste over my question. Long question short, when using sp_GetAppLock inside of a try / catch block, should I call sp_ReleaseAppLock when an exception is caught? SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED BEGIN TRAN DECLARE @res INT EXEC @res = sp_getapplock @Resource = 'This ...
I have a user Sql Authentication account that has remote access to my database server. The user has a default database, and is only able to access or modify that DB. So far so good. However, I'd like to prevent the user from viewing the other databases that he doesn't have access to anyway. Inside server permissions, I can deny the u...
How could i create a trigger that at any insertion on my table [users] will change automatically the content of its [password] field to its MD5 hash? Ps: I do not want this being done at client side. ...
I can deny a specific user DB viewing by doing something like this: DENY VIEW ANY DATABASE TO Myuser But, is there a way to also deny the "Security", "Server Objects", "Replication" and "Management" from being viewed by a remote user when logging in through SSMS? Thanks. ...
I have the following hierarchical table: Table Category: CategoryId, ParentCategoryId, CategoryName 1, null, SomeRoot 2, 1, SomeChild 3, 2, SomeGrandchild 4, 3, SomeGreatGrandchild (note this sample data doesn't include a leaf on an earlier node than level 4, but that is possible). The data will never go deeper than level 4, if that i...
For the life of me I can't remember where/how to add ips to sqlserver's whitelist for remote connections. Thanks. ...
I have a constraint (named INVS_ITEM_LOCATIONS_PK) in my SQL Server 2005 table. How can I disable it? ...
Given a table of logical resource identifiers (one per row), what is the best way for an arbitrary number of database clients to perform the following operations: Claim access to a specific resource, if it is not already claimed SELECT the next available resource and claim it (similar to above) Release a previously-claimed resource (...
What datatype should i choose for storing an Ip Address in a SQL Server? By selecting the right datatype would it be easy enough to filter by IP address then? ...
Hi all, I am a developer-having-to-play-admin and wish to connect to a remote sql server from my development machine using a sql server user ("op_web"). When I try to connect from vs2008 Server Explorer, I can connect to the server, but no databases are listed. If I connect using the server admin user, all databases are listed as expect...
As part of the holiday processing some of the jobs are disabled in sql server using script. They are not disabled manually. Now when we see the jobs there is a check mark for enable entity. And when we right click on the job the instead of enable thing being highlighted disable entity is highlighted. Now we are getting doubt whether job...
Has somebody measured performance differences between "real" SQL Server database and a SQL Server Express user instances (attached .mdf database file)? What was the conclusion? Was the .mdf database file fast enough for your application? ...
A table I have no control of the schema for, contains a column defined as varchar(50) which stores uniqueidentifiers in the format 'a89b1acd95016ae6b9c8aabb07da2010' (no hyphens) I want to convert these to uniqueidentifiers in SQL for passing to a .Net Guid. However, the following query lines don't work for me: select cast('a89b1acd950...
Hi, I'm having to return ~70,000 rows of 4 columns of INTs in a specific order and can only use very shallow caching as the data involved is highly volatile and has to be up to date. One property of the data is that it is often highly repetitive when it is in order. I've started to look at various methods of reducing the row count in o...
Does anyone have any suggestions about how to schedule the creation of reports and how to allow users to view them in a SQL Server 2005 Work Group environment where scheduling and subscriptions do not appear to be supported. SQL Server is installed on a separate machine to that running IIS to support multiple production websites. ...
This SQL only returns the first Activity element. How do I select them all? If I remove the [1] in the query I get an error that "value() requires a singleton". DECLARE @myDoc xml SET @myDoc = '<Root> <Activities> <Activity>This is activity one</Activity> <Activity>This is activity two</Activ...
I want to run a SELECT statement and I want execute a DELETE statement for the same row and the read result be respond by the SQL Server. ...