How to check which locks are held on a table
How can we check which database locks are applied on which rows against a query batch? Any tool that highlights table row level locking in real time? DB: SQL Server 2005 ...
sql-server
How should I estimate hardware requirements for SQL Server 2005 database?
We're being asked to spec out production database hardware for an ASP.NET web application that hasn't been built yet. The specs we need to determine are: Database CPU Database I/O Database RAM Here are the metrics I'm currently looking at: Estimated number of future hits to website - based on current IIS logs. Estimated worst-case...
Creating table names that are reserved words/keywords in MS SQL Server
Is it ok to name my database tables that are already keywords? For my case, I am trying to name the table that will hold my users. I've named it User but it is showing up as pink in SQL Server Management Studio so I am assuming its an existing System Table or Keyword. Thanks for your advice. ...
Manipulate VARCHAR variable larger than 8000 characters
I have a stored procedure on an SQL Server 2005 database that is creating the body of an e-mail. The result of this procedure must be placed in a table that has a TEXT field for the message body for later processing. The problem I've encountered is that in some scenarios the e-mail to be generated is larger than 8000 characters. So I...
Change Login Name
i have a problem when i restore DB_1 database backup into DB_2 database. but Login Name for user dbo is missing (). how i can re-create the Login Name?? i use SQL 2000 ...
SQL column edit issue
When i am going to change the data type of column name i am getting these error,,Can you tell me the solution Saving changes is not permitted.The changes you have made require the following .You have either made changes to a table that can't be created or enabled the option Prevent saving changes that require the table to be re-created ...
SQL Server Two-Tier replication - transactional/merge - rows not coming down
I have a SQL 2000 instance that has database with a transactional publication - tables only. It is subscribed to by a SQL 2005 instance - no problems. I need to publish some of the tables from this transactional subscription in a merge publication. Right now, I'm using another database in the SQL 2005 instance as the merge subscriber. ...
How do I use select with date condition?
Hello, In sqlserver, how do I compare dates? For example: Select * from Users where RegistrationDate >= '1/20/2009' (RegistrationDate is datetime type) Thanks ...
Getting common rows from two different queries in SQL Server
Is there any way that I can get the common rows in two different queries? Say one query returns 30 rows, and other one returns 100. But there are only 17 rows that have the same IDs... there are two columns, say QuestionID and Answer .. I nbeed to get the same questions answered by two different users. I would appreciate if I can do this...
How do I guarantee data is the same after upgrading SQL Server?
I have to upgrade a database from SQL Server 7 and SQL Server 2000 to SQL Server 2008. Additionally I have to be able to prove in a court of law that the data pre-migration is exactly the same post-migration. Any ideas? ...
sql server 2005 command line install error ADD_LOCAL property already installed
Hi, I have a silent installation of SQL Server 2005 that works great when installing SQL Server on a machine that does not have it already installed. I use the following parameters when I perform the installation: #define SQL_SILENT "/passive /qb" #define SQL_USERNAME "username=MyUserName" #define S...
How do I join (merge?) several tables?
I've got three tables AUTHOR_TABLE ------------- AUTHOR_ID (PK) AUTHOR_NAME 1 me 2 you ARTICLE_AUTHOR_TABLE ------------- AUTHOR_ID ARTICLE_ID 1 100 2 101 EVENT_AUTHOR_TABLE ------------------------------------------ AUTHOR_ID EVENT_ID 1 200 1 201 All ...
Sql server 2005 torn page,error 424
Hi all, My Dell pc failed;it was blue screen.I fixed that problem by formatting and reinstalling OS and other software that i have been using.Then I recoved my db designed using sqlserver 2005 and other files using recovery tools ;Easy Recovery 6.0. The problem is : When I try to attach the recovered file(lpdb.mdf),It can not attached.T...
Conditionally branching in SQL based on the type of a variable.
Hi everyone. I'm selecting a value out of a table that can either be an integer or a nvarchar. It's stored as nvarchar. I want to conditionally call a function that will convert this value if it is an integer (that is, if it can be converted into an integer), otherwise I want to select the nvarchar with no conversion. This is hitti...
simple search engine using web developer 2008
I'm working on a simple e-commerce website right now, nothing real just practicing the problem that I'm having is how to make a simple search in the website? i.e. if I want to search for the word "iphone" in the database of the website. how can I do that? and how can I specify a column to search in it instead of searching the whole ta...
Loop through columns SQL
Hello everyone, I'm looking for a way to loop through the columns of a table to generate an output as described below. The table looks like that: ID Name OPTION1 OPTION2 OPTION3 OPTION4 OPTION5 1 MyName1 1 0 1 1 0 2 MyName2 0 0 1 0 0 And the output looks like that: MyName...
How do you remember/manage your SQL examples ?
I currently have a SQLTIPS.sql file, which lives on my hard disk, at work. I'll add any new or nice examples, of writing good SQL, to this file. Now it's becoming hard to manage, and I have to search for specifics, in my own file! I am looking for something that can be loaded in SQL Enterprise Manager quickly, something similar to V...
Split Function equivalent in tsql?
I'm looking to split '1,2,3,4,5,6,7,8,9,10,11,12,13,14,15...' (comma delimited) into a Table or Table Variable. Does anyone have a Function that returns each one in a row? I am using SQL Server 2008. Thanks. ...
Formatting an SSRS report to not look terrible in Firefox
Hi all. I've got an SSRS report that I'm dynamically writing to an HTML page. The report looks great in IE, but it keeps none of its formatting in Firefox. I've done a bit of research on this, and found that I can insert rectangles inside of all of my matrix data fields, which will keep the formatting. This seems to me like a lot o...
Stored Procedure with optional "WHERE" parameters
Hi to all. I have a form where users can specify various parameters to dig some data (status, date ecc). I can producea query that is: SELECT * FROM table WHERE: status_id = 3 date = other_parameter = etc. Each WHERE is optional (i can select all the rows with status =3, or all the rows with date = 10/10/1980, or all the rows with st...