How do I move all stored procedures to a new SQL server?
When you moved the database to the new server all objects (tables, procedures, views, functions, etc) would have moved from the old server to the new server.
View Answer
| March 22, 2008 12:16 AM
SQL Server 2000, SQL Server 2005, Stored Procedures
When you moved the database to the new server all objects (tables, procedures, views, functions, etc) would have moved from the old server to the new server.
View Answer
| March 22, 2008 12:16 AM
SQL Server 2000, SQL Server 2005, Stored Procedures
Can SQL Server 2003 provide CPU usage information by application?
There is no such thing at SQL Server 2003. There is SQL Server 2000 and SQL Server 2005. Windows 2003 has the same Task Manager which Windows XP has. Right click on the task bar and select Task Manager. You can also see this information in Performance Monitor. Under the Process performance object select the [...]
View Answer
| March 22, 2008 12:13 AM
CPU usage, SQL Server 2003, SQL Server performance
There is no such thing at SQL Server 2003. There is SQL Server 2000 and SQL Server 2005. Windows 2003 has the same Task Manager which Windows XP has. Right click on the task bar and select Task Manager. You can also see this information in Performance Monitor. Under the Process performance object select the [...]
View Answer
| March 22, 2008 12:13 AM
CPU usage, SQL Server 2003, SQL Server performance
Is firewall preventing SQL Server from showing up in the EM?
It’s possible that it’s a firewall problem, but not very likely. Go to register a new server again, but this time disable the wizard and just type in the server name. This should then allow you to register the server with EM.
View Answer
| March 22, 2008 12:07 AM
MMC, SQL Server, SQL Server 2000, SQL Server Enterprise Manager, SQL Server security
It’s possible that it’s a firewall problem, but not very likely. Go to register a new server again, but this time disable the wizard and just type in the server name. This should then allow you to register the server with EM.
View Answer
| March 22, 2008 12:07 AM
MMC, SQL Server, SQL Server 2000, SQL Server Enterprise Manager, SQL Server security
How do I import a file to a SQL Server table with dates before 1970?
The easiest way to do this would be to load all the data into a staging table, then delete any data which is before 1970 and then move the data into the production table.
View Answer
| March 22, 2008 12:04 AM
SQL Server 2005, SSIS
The easiest way to do this would be to load all the data into a staging table, then delete any data which is before 1970 and then move the data into the production table.
View Answer
| March 22, 2008 12:04 AM
SQL Server 2005, SSIS
Stored Procedure Problem
In order to have a dynamic order by you have two options. 1. Use dynamic SQL. 2. Use a branching storage procedure. Option 1: <pre>CREATE PROCEDURE MyProc @OrderBy NVARCHAR(30) AS DECLARE @SQL NVARCHAR(4000) SET @SQL = N“Select * From Tbl_Name Where Name LIKE ‘%xyz%’ ” + @OrderBy exec (@SQL) GO</pre> Option 2: <pre>CREATE PROCEDURE MyProc [...]
View Answer
| March 21, 2008 12:35 AM
SQL, Stored Procedure variables, Stored Procedures
In order to have a dynamic order by you have two options. 1. Use dynamic SQL. 2. Use a branching storage procedure. Option 1: <pre>CREATE PROCEDURE MyProc @OrderBy NVARCHAR(30) AS DECLARE @SQL NVARCHAR(4000) SET @SQL = N“Select * From Tbl_Name Where Name LIKE ‘%xyz%’ ” + @OrderBy exec (@SQL) GO</pre> Option 2: <pre>CREATE PROCEDURE MyProc [...]
View Answer
| March 21, 2008 12:35 AM
SQL, Stored Procedure variables, Stored Procedures
Learning SQL 2005/2008
The best way to learn is to just grab the new product and go for it. If you have an MSDN account you can download SQL 2005 from the MSDN site. If not you can buy the Developer edition for about $50 US. SQL 2008 is still in beta so you can download from the [...]
View Answer
| March 19, 2008 9:33 PM
SQL Server 2005, SQL Server 2008, SQL Server career advice
The best way to learn is to just grab the new product and go for it. If you have an MSDN account you can download SQL 2005 from the MSDN site. If not you can buy the Developer edition for about $50 US. SQL 2008 is still in beta so you can download from the [...]
View Answer
| March 19, 2008 9:33 PM
SQL Server 2005, SQL Server 2008, SQL Server career advice
SQL Server 2005 multi-instance cluster
In a 2 node cluster you can use either Standard Edition or Enterprise edition. I pretty much always recommend Enterprise Edition for people who are clustering the database as they can not afford any down time as Enterprise Edition gives you the ability to do online index rebuilds while Standard Edition locks the table while [...]
View Answer
| March 19, 2008 9:30 PM
SQL Server 2005, SQL Server administration, SQL Server clustering
In a 2 node cluster you can use either Standard Edition or Enterprise edition. I pretty much always recommend Enterprise Edition for people who are clustering the database as they can not afford any down time as Enterprise Edition gives you the ability to do online index rebuilds while Standard Edition locks the table while [...]
View Answer
| March 19, 2008 9:30 PM
SQL Server 2005, SQL Server administration, SQL Server clustering
How can I set the database password by the SQL Server Enterprise Manager?
SQL Server doesn’t have a way to set a password on a database. If you are referring to changes a SQL Login’s password Under security navigate to Logins and double click on the login in question and simply type in the new password in both fields.
View Answer
| March 19, 2008 9:28 PM
SQL Server Enterprise Manager, SQL Server security
SQL Server doesn’t have a way to set a password on a database. If you are referring to changes a SQL Login’s password Under security navigate to Logins and double click on the login in question and simply type in the new password in both fields.
View Answer
| March 19, 2008 9:28 PM
SQL Server Enterprise Manager, SQL Server security
How to create a maintenance plan in SQL Server Management Studio Express
SQL Server Express edition doesn’t support maintenance plans as using the maintenance plans requires have Integration Services installed. As Express edition doesn’t include Integration Services you can not create a maintenance plan. To rebuild the indexes you will need to write scripts to handle the work, then run then from a scheduler. As the Express [...]
View Answer
| March 19, 2008 9:27 PM
Microsoft SQL Server Management Studio, MSSMSE, SQL Server 2005, SQL Server Express Edition
SQL Server Express edition doesn’t support maintenance plans as using the maintenance plans requires have Integration Services installed. As Express edition doesn’t include Integration Services you can not create a maintenance plan. To rebuild the indexes you will need to write scripts to handle the work, then run then from a scheduler. As the Express [...]
View Answer
| March 19, 2008 9:27 PM
Microsoft SQL Server Management Studio, MSSMSE, SQL Server 2005, SQL Server Express Edition
SUB-QUERIES
sample uncorrelated subquery (team which has the most wins) – SELECT teamname FROM teams WHERE wins = ( SELECT MAX(wins) FROM teams ) sample correlated subquery ( players who scored most goals on their teams) – SELECT playername FROM players AS p WHERE goals = ( SELECT MAX(goals) FROM players WHERE teamid = p.teamid )
View Answer
| March 19, 2008 1:57 PM
SQL Server, Subquery
sample uncorrelated subquery (team which has the most wins) – SELECT teamname FROM teams WHERE wins = ( SELECT MAX(wins) FROM teams ) sample correlated subquery ( players who scored most goals on their teams) – SELECT playername FROM players AS p WHERE goals = ( SELECT MAX(goals) FROM players WHERE teamid = p.teamid )
View Answer
| March 19, 2008 1:57 PM
SQL Server, Subquery
How can I save a picture to the SQL Server database and retrieve it using VB.Net 2005?
You need to save your picture as a BLOB. Here’s a <a href=”http://www.codeproject.com/KB/aspnet/Store_and_manipulat_BLOBs.aspx”>tutorial </a>that shows you how to do it. Have a good one! Joelle If you decide that you wish to store blob data within the database the information that Joelle provided is a excellent resource. However it is usually recommended to not store [...]
View Answer
| March 18, 2008 5:53 PM
SQL Server, VB.NET, VB.NET 2005, Visual Basic 2005
You need to save your picture as a BLOB. Here’s a <a href=”http://www.codeproject.com/KB/aspnet/Store_and_manipulat_BLOBs.aspx”>tutorial </a>that shows you how to do it. Have a good one! Joelle If you decide that you wish to store blob data within the database the information that Joelle provided is a excellent resource. However it is usually recommended to not store [...]
View Answer
| March 18, 2008 5:53 PM
SQL Server, VB.NET, VB.NET 2005, Visual Basic 2005
I am using SQL Server 2005. How can I retrieve data from live server through the Internet using VB.Net 2005?
You can create a website with VB.Net and then pull information from your database by specifying your DB server in the connection string. You will find an example of how to do this <a href=”http://www.connectionstrings.com/?carrier=sqlserver2005″>here</a>. Hope this helps, Joelle In addition you will also need to have the firewall opened to allow connections to the [...]
View Answer
| March 18, 2008 5:48 PM
SQL Server 2005, VB.NET 2005, Visual Basic 2005
You can create a website with VB.Net and then pull information from your database by specifying your DB server in the connection string. You will find an example of how to do this <a href=”http://www.connectionstrings.com/?carrier=sqlserver2005″>here</a>. Hope this helps, Joelle In addition you will also need to have the firewall opened to allow connections to the [...]
View Answer
| March 18, 2008 5:48 PM
SQL Server 2005, VB.NET 2005, Visual Basic 2005
SQL Server – I would like a stored procedure that will take the name of a table as input.
I’ll post the same answer here as when Dangagne asked the question on the forum back on Feb 28. While this can be done, it is more efficient and much safer to hard code the list of indexes which need to be dropped into a stored procedure, and the same with the create index commands. [...]
View Answer
| March 18, 2008 5:45 PM
SQL Server, SQL Server performance, Stored Procedures
I’ll post the same answer here as when Dangagne asked the question on the forum back on Feb 28. While this can be done, it is more efficient and much safer to hard code the list of indexes which need to be dropped into a stored procedure, and the same with the create index commands. [...]
View Answer
| March 18, 2008 5:45 PM
SQL Server, SQL Server performance, Stored Procedures
Create Information_Schema view
Not that I know of. Everytime I’ve tried to create an object in the sys schema I get an error. What’s the end result of the view you are trying to create? SQL 2005 has tons of data exposed via the DMVs.
View Answer
| March 17, 2008 6:36 PM
INFORMATION_SCHEMA views, SQL Server 2000, SQL Server 2005
Not that I know of. Everytime I’ve tried to create an object in the sys schema I get an error. What’s the end result of the view you are trying to create? SQL 2005 has tons of data exposed via the DMVs.
View Answer
| March 17, 2008 6:36 PM
INFORMATION_SCHEMA views, SQL Server 2000, SQL Server 2005
Hosting SSRS on Windows XP – user limitation.
Yes, but not because off SSRS. The IIS componant of Windows XP only allows a small number of users to connect to the website at once. As SSRS runs via IIS this would prevent more than a few people from using SSRS at once. Also the only edition of SSRS which will install on Windows [...]
View Answer
| March 17, 2008 6:34 PM
SQL Server reporting, SQL Server Reporting Services, SSRS
Yes, but not because off SSRS. The IIS componant of Windows XP only allows a small number of users to connect to the website at once. As SSRS runs via IIS this would prevent more than a few people from using SSRS at once. Also the only edition of SSRS which will install on Windows [...]
View Answer
| March 17, 2008 6:34 PM
SQL Server reporting, SQL Server Reporting Services, SSRS
Restoring Databases
You will want to restore your most recent backup of the database to another database name or to another server, then script out the database and then copy the data from the restored database to the production database which is missing the table. If your database is in full recovery mode or bulk logged recovery [...]
View Answer
| March 17, 2008 6:31 PM
Restore, SQL Server, SQL Server database restore
You will want to restore your most recent backup of the database to another database name or to another server, then script out the database and then copy the data from the restored database to the production database which is missing the table. If your database is in full recovery mode or bulk logged recovery [...]
View Answer
| March 17, 2008 6:31 PM
Restore, SQL Server, SQL Server database restore
SQL Server – Is there a formula for determining what identity range to set?
The easiest formula is how many records will each subscriber add into the database between syncs. Take that number and multiply by 5. That should give you plenty of identity values for a decent amount of time if the systems don’t replicate for a couple of cycles. Of course if you replicate every hour you [...]
View Answer
| March 17, 2008 6:25 PM
SQL Server, SQL Server errors
The easiest formula is how many records will each subscriber add into the database between syncs. Take that number and multiply by 5. That should give you plenty of identity values for a decent amount of time if the systems don’t replicate for a couple of cycles. Of course if you replicate every hour you [...]
View Answer
| March 17, 2008 6:25 PM
SQL Server, SQL Server errors
How can I restore my old data into the new design in SQL Server 2000
There is no way to directly restore data from one database design to another. Restoring data typically refers to taking a database backup and loading that backup into the SQL Server so that the database looks like it did at a prior point in time. In order to load for data from the existing schema [...]
View Answer
| March 17, 2008 6:19 PM
SQL Database, SQL Server, SQL Server 2000
There is no way to directly restore data from one database design to another. Restoring data typically refers to taking a database backup and loading that backup into the SQL Server so that the database looks like it did at a prior point in time. In order to load for data from the existing schema [...]
View Answer
| March 17, 2008 6:19 PM
SQL Database, SQL Server, SQL Server 2000
How can I structure this in SSIS?
You’ll want to setup an SSIS variable with the scope of Package. Then add in a SQL Query task to return the statement that you want. Then using the record set which is returned drop than into the SSIS variable. Then use the SSIS variable to in the select statement as part of the data [...]
View Answer
| March 17, 2008 6:13 PM
SQL Server 2005, SSIS
You’ll want to setup an SSIS variable with the scope of Package. Then add in a SQL Query task to return the statement that you want. Then using the record set which is returned drop than into the SSIS variable. Then use the SSIS variable to in the select statement as part of the data [...]
View Answer
| March 17, 2008 6:13 PM
SQL Server 2005, SSIS
Using Case Statement Inside SQL Join
You will have to build the whole SQL statement into a variable in one go then execute it. The INNER JOIN is implied e.g. select x.a from x, y where x.rowid=y.rowid This is an inner join and will only return rows where there is a reference in both tables
View Answer
| March 17, 2008 4:54 PM
CASE statement, JOIN statement, SQL, T-SQL
You will have to build the whole SQL statement into a variable in one go then execute it. The INNER JOIN is implied e.g. select x.a from x, y where x.rowid=y.rowid This is an inner join and will only return rows where there is a reference in both tables
View Answer
| March 17, 2008 4:54 PM
CASE statement, JOIN statement, SQL, T-SQL
