sql-server

unable to restore the backup in sql server

Hi, I am using SQL Server 2008. When I tried to restore backup from SQL Server Management Studio (or) using query, I got the access denied error. Cannot open backup device 'C:\Backup\101015.bak'. Operating system error 5(Access is denied.). I have tried both "sa" account and windows authentication. I am the administrator for the mach...

local database synchronization between sql server database and local sql ce database

Hi, Im implementing application in which there is local database which uses SQL CE. Each time app starts there has to be synchronization between local database and server database (to have new values in dictionary tables). Problem is that mappings are different in sql ce and sql server 2008 when using entity framework. Is it common pr...

Help with database structure

I am trying to create a system which allows local network users to collaborate content together. Requirements: The user should be able to: create content, edit content (content created by anyone), records should be kept on who edited what and when, should easily be able to go back and forth between edits, add comments to pages, sho...

Change the roles of multiple security accounts

I have many security accounts on the sql database and i want to remove/add roles to them based on a simple string comparison. Basically i want to list all accounts Filter out accounts that DON'T start with "MyDomain\" Remove role A. Add role B. What i found out by now is that i use sp_helprolemember to list all the accounts and sp_ad...

Error handling in DTS Package execution using .NET

Hi, I am executing a SQL Server 2000 DTS package using C# by following the code from this article http://support.microsoft.com/kb/319985. once the package is executed I am looping through each step to find out if any step has failed and get info about error if it has failed. I also use this information to find out if the package has succ...

SQL Server, C#: Timeout exception on Transaction Rollback

Hey guys, I've got a strange problem. I have a .NET program and my process logic needs a long-running transaction (~20min) on a SQL Server 2005 database. That's ok, since nobody accesses the database in parallel. When something goes wrong, the transaction should be rolled back. Infrequently and without any visible pattern the Rollback(...

Actions log table structure

Hi, I have to write a service which will implement some kind of a black/white list manager. Short description : It is very similar to messaging in social networks. imagine by default anyone can send you a message. And you have the possibility to block it for everyone, so no one can send you a message. Once it is blocked you can have a ...

What's the best way to connect to SQL Server from Ruby on OS X?

I need to let my Rails app connect to a MS SQL Server database and do a simple query to do a lookup. The main DB for the app is MySQL. It just needs to do this SQL Server thing on the side. What's the best way to do that? I could write an entirely separate app in Java that connects to the DB and dumps some XML data to the filesystem fo...

update table with random values

I have two tables, lets say Table1(Col1,Col2,Col3,Col4) and Table2(Col1). I want to update some cols in Table1. For each row in Table1 where Col1 ends with '001' (assume all values are at least length 4) I want to: 1.place a random number from the set (10,20,30,40,50,60,70,80,90) in Col2. 2.place a random 9 digit number in Col3. ...

Lookup Filter dropdown incomplete in programmatically altered SQL backend Access database

In this thread a user had problems with the lookup filter being missing in an acess database that had a SQL server backend. The problem was easily solved simply by checking on an option in the current database settings that allowed ODBC fields to also provide lookup filter dropdowns. For those confused, the lookup filter is the excel l...

SQL SELECT Query for a Total and parts building the total

Hello! I need a SQL Server Query for the following scenario: The Tables: CREATE TABLE [dbo].[JobStatus] ( [OID] [int] IDENTITY(1, 1) NOT NULL, [Name] [varchar](100) NOT NULL, [Code] [varchar](5) NOT NULL, [PictureID] [int] NOT NULL, [LastModifiedAt] [timestamp] NOT NULL, CONSTRAINT [PK_JobStatuses]...

How could i get the content of a stored procedure using sql querry

I need to get the content(statements) of a stored procedure on databaseA and in serverA, i am using sql server ...

How to kill or rollback active transaction?

Hi all, How can i kill or rollback, uncommitted transaction? I am listing my active transactions with the following sql: SELECT * FROM sys.dm_tran_session_transactions My result is: session_id transaction_id transaction_descriptor enlist_count is_user_transaction is_local is_enlisted is_bound -------------------------------...

SQL Server T-SQL concat aggregate

hi there Imagine I have this table BirthDay |Name 1-10-2010 | 'Joe' 2-10-2010 | 'Bob' 2-10-2010 | 'Alice' How can I get a result like this BirthDay |Name 1-10-2010 | 'Joe' 2-10-2010 | 'Bob', 'Alice tks ...

Converting data types in SSIS

I get an input CSV file that I have to upload to my oracle database. Here is some sample data ContractId, Date, HourEnding, ReconciledAmount 13860,"01-mar-2010",1,-.003 13860,"01-mar-2010",2,.923 13860,"01-mar-2010",3,2.542 I have to convert the incoming column to DB_TIMESTAMP (to match the structure in the destination table). But w...

Constraint is key is index is constraint?

in SQL Server Management Studio (SSMS) 2008 R2 Dev on the table (because I cannot ask without it) --SET ANSI_NULL_DFLT_ON ON create table B (Id int) I create unique constraint ALTER TABLE B ADD CONSTRAINT IX_B UNIQUE (ID) WITH (IGNORE_DUP_KEY = ON) SSMS shows that I do not have any constraint but have a key + index instead w...

Why insert-select to variable table from XML variable so slow?

Hi I'm trying to insert some data from a XML document into a variable table. What blows my mind is that the same select-into (bulk) runs in no time while insert-select takes ages and holds SQL server process accountable for 100% CPU usage while the query executes. I took a look at the execution plan and INDEED there's a difference. The...

What is the State of the Art for deploying database updates to production databases?

Every shop at which I've worked has had their own cobbled-together, haphazard, poorly understood and poorly maintained method for updating production databases. I've never seen a consistent method for doing this. So, in the most recent versions of SQL Server, what is the best practice for updating schema changes and migrating data from...

Problems with checking particular columns of uploaded data against the existing database

I have got an upload button on my webportal through which the customers can upload a csv file. After uploading the .csv file the csv data will be displayed on datagrid view on the screen, After uploading, the customer is suppose to verify data by clicking on verify button which basically checks the uploaded data have got the right data i...

PHP & SQL: Best way to protect this query from SQL injections NOT using PDO

Hi, I have a query which is run against a mssql database and I'm not using PDO drivers. Is there something like prepared statement i can use? Here is the query: $tsql = "INSERT INTO cplinktable (liferayid, bmsid, autotaskid, waspdb, cpid) VALUES ($liferayid, $bmsid, $autotaskid, '$waspdb', $cpid)"; thanks, Jonesy ...