Hi Following advice to my previous post (thanks fellas, it works a treat), I've implemented a certificate in the master db with VIEW ANY DEFINITION permissions and used this to sign my stored procs so they can access Information_schema. However, my stored procs also make use of dynamic sql. Is it possible to create a certificate, gran...
I am completely confused by MS docs on Windows built-in accounts. SQL Server 2008 R2 online docs "Setting Up Windows Service Accounts" tells "Local System is a very high-privileged built-in account. It has extensive privileges on the local system and acts as the computer on the network. The actual name of the account is "...
If I place DISTINCT keyword i get an error other wise it is working fine. ERROR: Msg 145, Level 15, State 1, Procedure SP_Products_GetList, Line 15 ORDER BY items must appear in the select list if SELECT DISTINCT is specified. ALTER PROCEDURE [dbo].[SP_Products_GetList] @CatID int, @CatName int, @IsNew bit, @InActive bit, @SortBy ...
I'm working under refactoring of database (SQL Server 2008) scheme and gather arguments to change NCHAR(1) columns (which keep Y|N values) to BIT. Everybody understand this is necessary and don't know why does it take place but this change affects production database so weighty arguments are required. Table keeps address catalog (up to 1...
Apologies for the length of this question. I have a section of our database design which I am worried may begin to cause problems. It is not at that stage yet, but obviously don't want to wait until it is to resolve the issue. But before I start testing various scenarios, I would appreciate input from anyone who has experience with such...
I've outgrown the Sql Server custom actions available in WiX, so I'm taking the bold step of creating my own using Deployment Tools Foundation. I want to be a good citizen and make sure that mine support rollback. But what's the best way of doing it? I need to support SQL Server 2005 and later, all editions. The problem, as I see it, i...
I have data as Employee: id Name -------- 1 xyz 2 abc 3 qaz Employee_A: (Eid - employee table, title - title table) eid active type title ------------------------------ 1 1 1 1 1 1 2 2 1 1 4 3 2 0 3 4 2 1 2 2 2 0 ...
I have to write a delete script to delete rows form a database table. However the table has a lot of children tables (foreign keys) and those children tables have children tables too. There are foreign keys for all relationships and I'd like to use this info to get the list of tables where I'll have to deletes, in the correct order (le...
Hi, I am trying to set up the trigger in a way that when the administrator (not users) make any changes to the database, all the changed data with the administrator name and time gets saved in the audit table (already created) that has all the possible fields. I have created triggers on each table for any sort of updates in those table...
Hi, I'm working with VS2010 and asp.net and build a SQL Server Database with some int and char values. My problem is, that the char(50) automatically fills the unused chars with whitespaces like: "foo " What can i do to get only "foo"? ...
Background: I use stored procedures exclusively for an ASP.NET application. I am using a DataReader to load a dataset object. ...
I need to check 2 tables on similar databases on different servers, if they contain the same information (or size, hash, whichever is the best way to check if they're the same) then one of them should have its information deleted, on a scheduled job every day. Is this possible using only the Jobs interface from within SQL Server Managem...
I receive a denormalized text file that must be loaded into a normalized table. Denormalized table: CustomerID -- Category -- Category2 -- Category3 -- Category4 1 -- A -- B -- C -- D When this is normalized, it should look like: CustomerID -- Category 1 -- A 1 -- B 1 -- C 1 -- D What is the best way to write a T-SQL statement to ac...
If there are any DBAs out there, I'm making a fairly large piece of software and one of the biggest issues presently is where to put the business logic. While Stored Procedures would be easier to fix on the fly, the processing requirements would probably slow the DB down tremendously. I also don't want to have all of the business logic h...
Hi, I've got this strange problem which I'm sure is well known - When I insert a date like '20/08/2010' I mean it to be as 'dd/mm/yyyy' where MSSQL expects it to be 'dd/mm/yyyy'. How can it be changed for MSSQL to expect 'dd/mm/yyyy' as the field format. Thanks! ...
I want to have a single table that represents a person and have a number of other tables (such as Student/Teacher) use the Person table to store information related to a person. Unfortunately the entity framework doesn't seem to like it when I try to add an association between the Student or Teacher class and I don't understand why. Th...
I have a table that has the following columns Id ForeignKeyId AttributeName AttributeValue Created Some of the data may look like this: 1, 1, 'EmailPreference', 'Text', 1/1/2010 2, 1, 'EmailPreference', 'Html', 1/3/2010 3, 1, 'EmailPreference', 'Text', 1/10/2010 4, 2, 'EmailPreference', 'Text', 1/2/2010 5, 2, 'EmailPreference', 'Htm...
I wish to do the following: select * into tmptbl from sometable EXCEPT 'sometable' is a stored procedure that returns a result set AND editing the stored procedure to suit my goal is not an option. ALSO i may or may not know the columns and types of what the procedure returns. Basically i am looking for a proper way of doing this: ...
I am trying to create a case statment saying if 1 day or less has passed between my 2 date parameters then do this otherwise do this..... ...
Hi, I have 2 similar tables on different databases. I already linked the remote DB and set it's data access to 'True'. However I can't get the following query to work GO USE LOCALDB GO DELETE from [dbo].[TableA] WHERE [dbo].[TableA].[UniqueField] = (SELECT [UniqueField] FROM [REMOTESERVER].[REMOTEDB].[dbo].[TableA]) GO This query d...