In SQL Server 2005, I have an order details table with an order id and a product id. I want to write a sql statement that finds all orders that have all the items within a particular order. So, if order 5 has items 1, 2, and 3, I would want all other orders that also have 1, 2, and 3. Also, if order 5 had 2 twice and 3 once, I'd want ...
I would like to use TDE, but I cannot use it, so I have chosen to use the EncryptByCert and DecryptByCert functions. However, I was also considering encrypting/decrypting data in c# as shown here. My question is are EncryptByCert and DecryptByCert unsecure because the certificate is also stored in the database? How do people get around ...
I'm using DBDeploy.NET for change control management in my T-SQL codebase. DBDeploy works by the developer maintaining a numbered set of change scripts. When a deploy is triggered (via NAnt), DBDeploy looks at a changelog table and determines which patches need to be executed to bring the instance up to date. The issue I have is with ...
my company wants to sponsor me for some online sql server DBA classes. can someone please recommend a beginner's class to me? ...
I have a query that returns a result set of one column, but I want it to be converted into one long string for use as a subquery. I.E. I want to be able to do: SELECT user.*, (SELECT group_name FROM user_groups WHERE userID = user.ID) AS Groups FROM user However, that will fail because a user can belong to more than one group. For exa...
How can I write a command on dataset files with transaction either, how can I do it with tableadapters ...
I have two columns in a SQL table, fooId (int) and fooName (varchar). Is there a way to select them both as one column with a space between them? select fooId + ' ' + fooName as fooEntity from mytable They're different types so I'm getting an error. This field will be databound directly in a control in the web app. SQL Server 200...
this question is based on answer got from another SO question, can be found here. I have managed to write a query myself based answer provided there Select s.pName, s.ProductCode, min(s.Price) as MinPrice, sum(s.Quantity) as SalesQty, sum(s.Price * s.Quanti...
I would like to make a copy of what's on my remote server at xxx.safesecureweb.com. How do I connect to it from my local SQL Server? Is the syntax something like: sp_addlinkedserver @server='PRODUCTION', @provider='xxx.safesecureweb.com', @Username='myUsername', @Password='myPassword' What I'm thinking of doing is writing a bunch ...
Say I have the following table: id|myId|Name ------------- 1 | 3 |Bob 2 | 3 |Chet 3 | 3 |Dave 4 | 4 |Jim 5 | 4 |Jose ------------- Is it possible to use a recursive CTE to generate the following output: 3 | Bob, Chet, Date 4 | Jim, Jose I've played around with it a bit but haven't been able to get it working. Would I be bett...
I am new to writing SQL fucntions and trying to write a scalar function in T-SQL. The function should insert leading zeros into an input field of numbers the length of the input can vary in length, i,e,. 123, 1234567, 9876543210 and so forth. The input field is defined as varchar(13) here is the code that I have written for the function...
I have a stored procedure with a username parameter. I want to use one query to grab a userid, then use this variable in further queries. Here's what I have so far. It compiles OK, but on execution I get an error "Error converting data type varchar to uniqueidentifier." ALTER PROCEDURE [dbo].[sp_User_delete] @username uniqueidentif...
Is it possible to assign a DB role to a DB user in Sql Server 2008 using t-sql? If so, how? ...
First, I am NOT trying to spur yet another debate about LINQ vs. stored procedures. Assume for this question (for right or wrong) that I'm going to use SQL server stored procedures and will access those stored procedures via LINQ. I am using stored procedures (again, for right or wrong) because I want to enforce security at the stored ...
I have several files (they are XML but that's not important) that need to be inserted into an existing SQL table (i.e. I didn't design it.) The table looks like this. ReportType ID (int) <- identity Name (varchar(32)) TransformXSLT (nvarchar(max)) Normally I would do: INSERT INTO ReportType (Name, TransformXSLT) VALUES ('templa...
I am having to interface some C# code with SQL Server and I want to have exactly as much precision when storing a value in the database as I do with my C# code. I use one of .NET's decimal type for a value. What datatype/precision would I use for this value in SQL Server? I am aware that the SQL Server decimal type is the type that mo...
In Microsoft SQL Server, I have a schema XXX. Q: How do I create a user XXX such that issuing the following command: SELECT * FROM Table is the same as SELECT * FROM XXX.Table Here's what I have so far: CREATE SCHEMA XXX authorization dbo -- I think CREATE LOGIN XXX WITH PASSWORD = '123'; CREATE USER ItDontMatter FOR LOGIN XXX WIT...
I've installed SSRS on my development machine and everything works as expected - except anyone else who is logged on to my domain can view all of the reports by browsing to http://mymachine/reports. I find it a bit strange that the default behaviour is for everyone on the domain to have access to the reports. Am I doing anything wrong...
Hey, I am in the following situation: I have been requested to write an application for managing where we have customers - this must flag up when we try to add a customer too close to another one, so must be able to calculate distances based on post codes. I have chosen to use ASP.Net VB because I am fairly good at that and I like tha...
Why do i have to drop all constraints (keys in general) before i can drop a table in sql server. I dont understand why this would be necessary if I have permissions to drop the table and the know how to do it why not drop all constraints for me? Is there some sort of technical or database design reason for this? ...