I am using Sql Server 2008 and would like to create a user login that can only have a readonly view of the data, create backups and restore the database. The first two I can do fine, using db_datareader, db_datadenywriter and db_backupoperator. I have problems with the restore however. From what I have read, you have to make the user ...
I need to perform update/insert simultaneously changing structure of incoming data. Think about Shops that have defined work time for each day of the week. Hopefully, this might explain better what I'm trying to achieve: worktimeOrigin table: columns: shop_id day val data: shop_id | day | v...
Given that I have a table that holds vehicle information and one of those pieces of information is VehicleType (usually 6-20 characters), what are the technical reasons why it is better to design the tables like this: Vehicles VehicleID VehicleTypeID (INT) (relates to an INT in the VehicleTypes table) versus this: Vehicles Veh...
I have the following query that runs in my Oracle database and I want to have the equivalent for a SQL Server 2008 database: SELECT TRUNC( /* Midnight Sunday */ NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL) ) AS week_start, TRUNC( /* 23:59:59 Saturday */ NEXT_DAY(NEXT_DAY(SYSDATE, 'SUN') - (7*LEVEL), 'SAT') + 1 ...
Input @StartDate = '01/25/2010' @EndDate = '02/06/2010' I have 2 CTEs in a stored procedure as follows: with CTE_A as ( [gives output A..Shown below] ), with CTE_B as ( Here, I want to check if @StartDate is NOT in output A then replace it with the last known date. In this case, since @startdate is less than any date in output A...
Is this equivalent to a LEFT JOIN? SELECT DISTINCT a.name, b.name FROM tableA a, (SELECT DISTINCT name FROM tableB) as b It seems as though there is no link between the two tables. Is there an easier / more efficient way to write this? ...
The problem: we have one application that has a portion which is used by a very small subset of the total users, and that part of the application is running off of a separate database as well. In a perfect world, the schemas of the two databases would be synced up, but such is not the case. Some migrations have been run on the smaller ...
hi. I have ten or more(i don't know) tables that have a column named foo with same datatype. how can i tell sql that values in all the tables should be unique. I mean If(i have value "1" in table1) I should NOT be able to have value "1" in table2 ...
I am using SQL server 2008 on windows 7 Machine and when i try to logon using local user i get error message login failed cannont login, i am administrator on my machine. Any Ideas? ...
I have a User table that has all of their avatars saved in an image field. I'd like to just take that out of the database and store it as a regular file on disk. I looked around and saw some code for textcopy, but that doesn't seem to be on my machine for some reason. Here is the code I wrote up anyway. Anyone know a way to get this ...
I have 2 databases on the same server. Lets call them db1 and db2. Now I have a user defined table variable that is called u1 which I created on both the databases. I have a stored procedure (p1) on db2 that takes u1 as a parameter which I want to call from db1. So I call db2..p1 @u1 = @u1 and then I get the following error: Operand ty...
Hi, I am relatively new to databases,so please forgive me if my query sounds trivial. I need to send bulk email to all the contacts listed in a particular table. The body of the email is stored in a .doc file which begins as Dear Mr. _. The SQL server will read this file, append the last name of the contact in the blank space and send it...
Background: Professional tools developer. SQL/DB amateur. Setup: .Net 3.5 winforms app talking to MS SQL Server 2008. Scenario: I am populating a database with information extracted from a large quantity of files. This amounts to about 60M records, each of which has an arbitrarily sized message associated with it. My original plan w...
I have a table with three fields, User, City and Country, where exactly one of the fields must be non-NULL at all times. Can I use an SQL constraint for this or should I rethink what I'm doing? The scenario is that my database should contain documents that can be attached to users, cities or countries. So a row in this table contains ex...
On table "A" depend about 30 other tables via FK to "A.Id". For integration testing I have to drop the table and recreate it to create a defined state. Because of the dependent objects their seem to be no way to delete and recreate the table. The error message is: Could not drop object 'dbo.A' because it is referenced by a FOREI...
We are using the IsVersion property on the ColumnAttribute on a property in a LINQ to SQL class for optimistic concurrency checks. What should the column definition be in the database? Currently we are using version_number int NOT NULL IDENTITY (1, 1) Do we need Identity? Can we get LINQ to SQL to update the version number for us? T...
I'd like to add the "msdb.dbo.sp_help_job" system stored procedure to a LINQ to SQL object, but I can't figure out how to specify it. If I create a new Data Connection in Server Explorer and specify the "msdb" database of the server I want, and navigate to "Stored Procedures", that procedure is not listed. Am I looking in the wrong place...
Hi All, This is probably a easy one, but for the life of me I can't seem to figure it out. Here is my table: Date User Amount ---------- ----- ------ 01/01/2010 User1 2 01/01/2010 User2 2 01/01/2010 User1 4 01/01/2010 User2 1 01/02/2010 User2 2 01/02/2010 User1 2 01/02/2010 User2 4 01/02/2010...
I need to import sheets which look like the following: March Orders ***Empty Row Week Order # Date Cust # 3.1 271356 3/3/10 010572 3.1 280353 3/5/10 022114 3.1 290822 3/5/10 010275 3.1 291436 3/2/10 010155 3.1 291627 3/5/10 011840 The column headers are actually row 3. I can use an Excel Sourch to...
Hi I am faced with an interesting problem and I am not even sure if it is possible: I need to create a Stored Procedure in SQL Server 2008 that when executed does the following: Executes a Batch file - located on the SQL Server (i.e. C:\Mybatchfile.bat) - This Batch file will output a single text file to a directory on the SQL Server...