When restoring a backup, how to disconnect all active connections?
My SQL Server 2005 doesn't restore a backup because of active connections. How to force it? ...
sql-server-2005
SELECT non-duplicate records joined to table with duplicate date records, by last date
The title probably doesn't make much sense, so I'll try to be descriptive here in the subject. Consider 2 tables in MSSQL2005: Cases table: id int, caseNo string Events table: id int, caseID int, eventDate date/time I need a select statement for a view which will return single rows of: cases.caseNo, events.eventDate (date part on...
Scheduling backup in sqlserver 2005 (not able to Script Action To Job)
Hi, I'm trying to specify backup, the scripting to a new job as described in http://support.microsoft.com/default.aspx/kb/930615 However, the option Script Action to Job is not available to me (disabled). Could it be insufficient rights for the logged on user? Which rights must I grant the user? Thanks, Anders, Denmark ...
SQL Server roles, schemas, users
I've been trying to figure out why SQL Server has db_owner schema and db_owner role? This is very confusing. I've been searching for answers and so far this is how my understanding goes: All tables and objects (such as constraints etc) belong to a schema. DBO being the default schema. A user may be given permission to edit each obje...
SQL server: Compare rows
Hi SQL gurus, I need a query which can do the following operation. I have a table with 2 columns ID Values 1 1 1 2 1 3 1 4 2 2 2 5 2 6 if you see for ID 1 I have 1,2,3 and 4 as values and for ID 2 I have 2, 5 and 6. I want to write a query which return the following 1(-) 4(-) 5(+) 6(+) mean 1 and 4 are deleted and 5...
SP: Get 5 random records plus 1
Hi, I need to get 5 random records from a table plus a further record based on data from the users preferences as stored in a second table. Here are the two statements i have created so far: Gets favourite charity based on key set in TBL_MEMBERS: SELECT DISTINCT TBL_CHARITIES.* FROM TBL_CHARITIES JOIN TBL_MEMBERS ON TBL_CHARITIES.cha...
Handling database record deletions with several levels of offspring
We've got a database that generally has this structure: Master Record Table id (pk) MasterRecordId <-- constrained to be unique Children/Siblings (2nd Generation, if you will): Table1 ( table1ID (pk), MasterRecordID (fk)) Table2 ( Table2ID(pk), MasterRecordID (fk)) Grandkids (3rd Generation): Table3 ( Table3ID (pk) Table1I...
SP: Random Records, Fave Record, Plus Known Record, NO repetition.
Hi, Thanks to help from a lot of you guys ive been given the following code which works great. However ive realised ive missed an important bit of info out of the question and so have reposted here (with updated code) to clarify. The following code gets 5 random records from a table plus a further single record based on the users favou...
SSRS 2005 - Capturing RAISERROR message
I am checking whether a user has a permission to view the report through dataset stored procedure and if the user doesn't, raiserror is called. Is there a way to display a different message from SQL Server Reports 2005 when a stored procedure (that populates report dataset) raises error (through RAISERROR) instead of returning data? He...
How can I select the entire row when using max
I have a table with the following columns: SignatureID PatientID PatientVisitID TreatAuthDate HIPAADate DrugTestDate Now I have the following Select statement: SELECT * FROM tblSignature WHERE PatientID = 12345 This select statement returns 8 rows. What I need to accomplish is getting the MAX TreatAuthDate - and with that MAX Trea...
SQL query to count and list the different counties per zip code
I have a sql server 2005 table called ZipCode, which has all the US ZIPs in it. For each entry, it lists the zip, county, city, state, and several other details. Some zipcodes span multiple cities and some even span multiple counties. As a result, some zipcodes appear many times in the table. I am trying to query the table to see which...
Need some help with a SQL query (SQL Server 2005) to transform this data
Here's a simplistic look at the table I have Category CategoryId CategoryName ParentCategoryId As you can see, the catalog table is a self referencing table, so recursing it is possible by starting at 0, which is the fake "root" category (could just as well be null, but it's not). Sample Data: 1, Apples, 0 5, Yummy, 1 10, Really Y...
how to add foreign key constraint, SQL Server
hi guys, I have a registration table with primary key RegId. I have another table named Users, also contain RegId as Foreign key. When I delete one RegId from registration, I have to delete RegId from Users. Can anybody help? ...
Strange problem in SQL job and stored procedure
I have a stored procedure which will pick MQ files from receive location, process it and drop it in save location. Currently, this stored procedure is not working when I execute it manually. But files are picked up and saved in the save location automatically at a particular time of day daily. I don't know which stored procedure is cal...
Sql server 2005 date function
I would like to know how to get no. of days using getdate() & existing dates in other table, where that table contains multiple records, ie i would like to know the jobs posted 5 days back or 10 days back or 15 days back. I have written the query like declare @q1 datetime; select cdate from jobposting where cdate like (select cdate) se...
How to retrieve machine IP adress via SQL Stored Procedure
Hi, I wanted to know if we can retrieve IPAddresses of any machine (can be local or remote) via Microsoft SQL Stored Procedure WITHOUT using INSERT EXECUTE. ...
SQL Error Locating Server/Instance Specified
Having a problem moving a site from Win2003 IIS6 w/ SQL2005 Express to a Win2008 IIS7 w/Fill SQL 2005? I keep getting this error Desc : SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF]. Can anyone point me in the right direction ...
How do I programmatically add an apostrophe before and after a string and add a comma inbetween?
I get lists in Outlook, Word or Excel of hundreds of strings like so: 7864750A 7888801BZ 5189748C 48982572E 6936001F 7096235FG 3218833H etc. Currently I run a stored procedure on each of these, in one-by-one fashion. How could I programmatically add an apostrophe before and after a string and add a comma inbetween, like so: ('7864750A...
Need generic function(s) for connecting to DB and retrieve data
Hi guys. I'm making a DAL class which I can use to connect to DB and retrieve data. I'm using SQL Server 2005 Express (and Visual Web Developer 2008 Express Edition). I found several examples on the web for connecting an retrieving data. But none where made inn to a class object. This is kind of a pseudocode I've put together. Can any...
Apply upgrades (application related) to database.
Guys, Since I've not done this before I am not sure if the way I am planning to do this is okay or is there a better way. Like using Windows Installer or Install Shield or Windows Installer XML (WiX) toolset. Any help would be great, as I have no clue. We have a product and we ship new version every few months. So far we've only been r...