For a database that is shipped with a remotely deployed product, are there any drawbacks to encrypting all code objects (procs, functions, views) via the WITH ENCRYPTION clause in MS SQL Server 2005 and 2008? The advantages are a deterrent to "custom" changes in the field that solve one need while creating many more. The goal is not to ...
Our database is about to reach 1000 stored procedures. Although we were wise and created a good scheme for naming the stored procedures, hunting for the stored procedure you need can be a bit frustrating as you scroll and scroll and scroll. If I know the exact name of the stored procedure I'm looking for, it would be great to identify ...
I have a table Persons with personaldata and so on. There are lots of columns but the once of interest here are: addressindex, lastname and firstname where addressindex is a unique address drilled down to the door of the apartment. So if I have 'like below' two persons with the lastname and one the firstnames are the same they are most l...
Hey friends I need to implement Full text search across tables. what are the different options. ...
My current understanding is that Microsoft does not offer a viable tool for creating a DB ERD model and then forward-engineering the DDL to create the database. I've heard that Visio Enterprise Edition 2003 had this feature, but that it has been removed in subsequent editions, and that the old edition does not operate well when installe...
I'm loading an XML in SQL using OpenXML while declaring the variable the max i can go up to is 8000 chars : DECLARE @xml_text varchar(8000) Since text, ntext is not allowed to be used with openXML what other alternatives do i have to load the entire XML (over 20000 chars) in SQL ? ...
In an application that I am writing, I am getting Write Conflicts when I use VBA code to change the value of a checkbox on a form. I have tried inserting the following code into the events that have VBA changing the values of any object on the form: If Me.Dirty Then Me.Dirty = False End If But the problem persists - as if the rec...
The sp_password sproc in sql server 2000 only required membership in the db_accessadmin db_securityadmin database roles and membership in the securityadmin server role to allow a user to change passwords w/o knowing the old password. However in sql server 2005/2008 the BOL says a user now needs the "CONTROL SERVER" permission to do this...
can anyone share their experience with SQL Server DTS? ...
I'll describe what I am trying to achieve: I am passing down to a SP an xml with name value pairs that I put into a table variable, let's say @nameValuePairs. I need to retrieve a list of IDs for expressions (a table) with those exact match of name-value pairs (attributes, another table) associated. This is my schema: Expressions ta...
I have a SSIS package that is stored in a SQL Server 2005 DB. I am trying to execute this package from a stored procedure on the same server. Is there a better way than exec master..xp_cmdshell 'dtexec /SQL... I am running into (I think) file system permission issues with xp_cmdshell that is preventing execution ...
What's the best way to truncate a datetime value (as to remove hours minutes and seconds) in SQL Server 2008? For example: declare @SomeDate datetime = '2009-05-28 16:30:22' select trunc_date(@SomeDate) ----------------------- 2009-05-28 00:00:00.000 Thanks ...
Hey all, I have restored a database from a backup onto a new server, and I cannot delete the old schema from the database. I have created a NEW user for this database. The NEW user is named differently (to make things clearer for other developers), so I have no use for this schema. I DID manage to delete the USER finally, but the sche...
From my old Access days, there was a First() function that allowed you to get the first row as an aggregate function. Is there any equivalent in SQL Server? SELECT c.ID , p.ID , FIRST(p.ProductName) , SUM(fee.Amount) from Fee as f INNER JOIN Product as p ON p.ID = f.ProductID INNER JOIN Customer as c ON c.ID = p.CustomerID GROUP BY c....
I'm writing a database view to sum up a bunch of records where the value in a date column is within the last 7 days. It looks something like this: CREATE VIEW RecentRecordSum AS SELECT t.ID, SUM(t.SomeValue) AS ValueSum FROM SomeTable t WHERE t.RecordDate >= DATEADD(d,-7,GETDATE()) GROUP BY t.ID Is ...
Details: I am using Reporting Services 2005 in a C# Application with Visual Studio 2008 to generate reports based on a SQL Server 2005 database. The application views the report locally using the .net report viewer and no report server is used. There is a page break in the report after each person. I am currently using just one rep...
I am working on a SSIS package that extracts some data from DB to a file then uploads it to a FTP server. I need to execute several FTP commands but seems like the SSIS FTP task only allows me to specify source & destination locations. Does any one know how to make SSIS execute a FTP script without having to create a Script Task and wri...
I have an ODBC Database (some third party DB) where I have a bunch of tables. I am able to use 'GetSchema' to get a list of tables in there. I am also abe to utilize SQLBulkCopy to copy Data from these tables to SQL Server tables (only when I have created a Destination table with the same structure in SQL Server). But here is the scenar...
If you have to create an application like - let's say a blog application, creating the database schema is relatively simple. You have to create some tables, tblPosts, tblAttachments, tblCommets, tblBlaBla… and that's it (ok, i know, that's a bit simplified but you understand what i mean). What if you have an application where you want ...
I have a routine which is updating my business entity. The update involves about 6 different tables. All the commands are being executed within a transaction. Recently, I needed to add some code into the routine which accesses a lookup table from the database. The lookup code already existed in another business object so I used that ...