I have successfully configured a principal and mirror with SQL Server 2008 and Windows Server 2008 R2 64bit. However, when I attempt to add a witness (SQL Server 2005 and Windows Server 2003 32bit) to the mix, I get the following errors in the event log of the primary: EventID 1456 "The ALTER DATABASE command could not be sent to the rem...
Let's say I've got a SQL Server database table with X (> 1,000,000) records in it that need to be processed (get data, perform external action, update status in db) one-by-one by some worker processes (either console apps, windows service, Azure worker roles, etc). I need to guarantee each row is only processed once. Ideally exclusivity ...
We have been using several nested queries for reporting purposes since the days of SQL Server 2000. select * from (select * from t1) t1 inner join (select * from t2) t2 on........ We recently upgraded our database to SQL Server 2008 Standard from SQL Server 2000 Enterprise. We are noticing some of those old queries are now runn...
I'm wondering if is it possible to copy one table definition from .mdf file to create it in SQL Server and could it be done in Visual Studio 2010? It doesn't matter if I'll do it by some kind of wizard or retrieve a SQL query with a command like "Create Table...". I just want to have one table which I created in mdf file in database on ...
I have Following structure Col1 Col2 Col3 --------------- F P R1 F P R2 F P R3 F P R4 Col3 values can be any thing Now I want in following format only top 3 Col1 Col2 Res1 Res2 Res3 ------------------------------ F P R1 R2 R3 ...
Hi, I was wondering if there is any way to check the execution logs of SQL server. I am not talking about the proprietary transactions logs or the error logs. But just the recently executed logs in a verbose fashion for debugging. For example, I have a web application that calls stored procedures, passes in parameters and populates gri...
Is it a way to retrieve the view's column description from the base table (fn_listextendedproperty)? The following syntax only list down the column name from the view SELECT * FROM sys.columns c JOIN sys.views v on c.OBJECT_ID = V.OBJECT_ID JOIN sys.schemas s ON s.schema_id = v.schema_id and V.NAME = 'v_P...
I would like to know which format SQL Server saves datetimes, GMT or UMT? ...
Hi Everyone, I have created SSIS packages to move data from AS400 to SQL Server which are scheduled daily.some of the packages in sql agent are taking longer duration more than 9 hours to complete.IF I run same package in Business intelligence studio manually, it is completing in less than 4 hours.Due to this problem my schedule packages...
Hello, I have a table that contains a list of tasks; TableName: Tasks. Fields: (ID Int, Description nvarchar) The tasks are completed daily and are logged in a table like follows; TableName TasksDone. Fields: (TaskID Int, TaskDate DateTime) I need to have a query that runs for a date range and shows the tasks that were NOT done (do ...
Hi, I have created a tables named Media in a database and I want it to accepts only image files. It has two column : ImageID and Image. Data type of the image column is varbinary. Now I want to create a check constraint to check the files extension to make the column to accept correct and special type of files. For example I want the ima...
I have a script that gives an error when being executed: Msg 1505, Level 16, State 1, Server CBR07I300FVA1, Line 1 CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 17. Most significant primary key is '44'. The statement has been terminated. The script contains thousands of lines of queries so I have no idea...
I want to randomly choose a value from a small range of integers (less than 200). As an alternative to SELECT RAND() I'm trying to use CAST(CAST(CRYPT_GEN_RANDOM(2) AS INTEGER) AS FLOAT) / 65535 but I'm getting some strange effects. For example: WITH Numbers (num) AS ( SELECT num FROM ( VAL...
Hi, Recently a very friendly user on stackoverflow helped me with this SQL: SELECT (SELECT TOP 1 a.id FROM vAnalysesHistory AS a WHERE a.companyid = n.stockid ORDER BY a.chosendatetime DESC) AS id, n.name, (SELECT TOP 1 a.chosendatetime FROM v...
What's the easiest way to export data to excel from SQL Server 2000. I want to do this from commands I can type into query analyzer. I want the column names to appear in row 1. ...
I've inherited a piece of code at work and I've just noticed a fairly trivial but bizarre little quirk with it. So maybe someone can save me setting up clean .NET 2.0 Environment to test it out. There is a SQL2K5 table containing a column called IsEnabled BIT NOT NULL There is a sproc selecting from that table There is a piece of C# c...
With SQL Server 2008, how can I detect if a record is locked? EDIT: I need to know this, so I can notify the user that the record is not accessible because the record is blocked. ...
Dear readers, I have a stored procedure named insert2Newsletter with parameters (@sex nvarchar(10), @f_name nvarchar(50), @l_name nvarchar(70), @email nvarchar(75), @ip_address nvarchar(50), @hotelID int, @maArt nchar(2)) I want call this stored procedure in an insert trigger. How do I retrieve the corresponding fields from inserted...
Hi, I renamed the table by sp_RENAME (SQL SERVER 2008) sp_RENAME 'dbname.scname.oldtblname' 'dbname.scname.newtblnam' The resulting message (it was in black color - so I get it as just a warning or successful message) was something like "Caution: Changing any part of an object name could break scripts and stored procedures." So afte...
I consider myself rather proficient with T-SQL and I'm usually able to optimize a query pretty good without loosing readability. In short: I like my SQL short, descriptive, declarative and elegant. While the following code works, i have two problems with it: I am using cursors and I can't shake the feeling I have in the back of my hea...