How can I initialize arrays in stored procedures?
SQL Server doesn’t support array’s within or outside of stored procedures. The closest thing that SQL Server has would be a table variable. It’s accessed just like a regular or temp table, but it’s done mostly in memory. <pre>DECLARE @TableVar TABLE (Col1 INT, Col2 INT, Col3 VARCHAR(10)) INSERT INTO @TableVar SELECT 1, 2, ‘test’ SELECT [...]
View Answer
| March 16, 2008 11:46 PM
SQL Server, Stored Procedures
SQL Server doesn’t support array’s within or outside of stored procedures. The closest thing that SQL Server has would be a table variable. It’s accessed just like a regular or temp table, but it’s done mostly in memory. <pre>DECLARE @TableVar TABLE (Col1 INT, Col2 INT, Col3 VARCHAR(10)) INSERT INTO @TableVar SELECT 1, 2, ‘test’ SELECT [...]
View Answer
| March 16, 2008 11:46 PM
SQL Server, Stored Procedures
How to transfer encrypted data between SQL Server 2005 and SQL Server 2000
SQL Server 2005 includes some native encryption procedures which you can use to encrypt the data. However decrypting the data on the SQL 2000 server or the Access database will be harder as they don’t include the same encryption procedures. (SQL 2000 doesn’t include any native encryption procedures at all.) If you have the option [...]
View Answer
| March 16, 2008 11:44 PM
SQL Server 2000, SQL Server 2005, SQL Server encryption, SQL Server security, SQL Server upgrades
SQL Server 2005 includes some native encryption procedures which you can use to encrypt the data. However decrypting the data on the SQL 2000 server or the Access database will be harder as they don’t include the same encryption procedures. (SQL 2000 doesn’t include any native encryption procedures at all.) If you have the option [...]
View Answer
| March 16, 2008 11:44 PM
SQL Server 2000, SQL Server 2005, SQL Server encryption, SQL Server security, SQL Server upgrades
Design a SQL Server trigger to calculate column averages and return them to another table
What you want is actually a stored procedure not a trigger. Triggers can not be fired off on schedule, while stored procedures can. First you need to write your select statement which simply returns the data that you need to load into the destination table. The query will look something like this. <pre>SELECT IdField, AVG(NumericField) [...]
View Answer
| March 16, 2008 11:33 PM
SQL Server development, SQL Server performance, Triggers
What you want is actually a stored procedure not a trigger. Triggers can not be fired off on schedule, while stored procedures can. First you need to write your select statement which simply returns the data that you need to load into the destination table. The query will look something like this. <pre>SELECT IdField, AVG(NumericField) [...]
View Answer
| March 16, 2008 11:33 PM
SQL Server development, SQL Server performance, Triggers
Decreasing CPU usage on SQL Server 2005
There is no sure fire step by step process to trouble shooting high CPU load on the SQL Server. A first step would be to use SQL Profiler to see what queries are taking the longest, and the most CPU time. Open SQL Profiler from Start > Programs > Microsoft SQL Server 2005 > Performance [...]
View Answer
| March 16, 2008 11:29 PM
SQL Server 2005, SQL Server performance tuning
There is no sure fire step by step process to trouble shooting high CPU load on the SQL Server. A first step would be to use SQL Profiler to see what queries are taking the longest, and the most CPU time. Open SQL Profiler from Start > Programs > Microsoft SQL Server 2005 > Performance [...]
View Answer
| March 16, 2008 11:29 PM
SQL Server 2005, SQL Server performance tuning
How to install a named instance in SQL Server 2005 without affecting the existing instance
You have to run through the installer a second time. During the install the installer will ask you what the name of the instance should be. A reboot will be required after the install, and then another reboot will be required after SP2 is installed. Your default instance will not be effected except for being [...]
View Answer
| March 16, 2008 11:11 PM
SQL Server 2005, SQL Server installation
You have to run through the installer a second time. During the install the installer will ask you what the name of the instance should be. A reboot will be required after the install, and then another reboot will be required after SP2 is installed. Your default instance will not be effected except for being [...]
View Answer
| March 16, 2008 11:11 PM
SQL Server 2005, SQL Server installation
maxdop hint
The MAXDOP is used like this. <pre>SELECT * FROM TABLE WHERE Col1 = ‘Value’ OPTION (MAXDOP 1)</pre> Basically it should be the last line of the SELECT statement.
View Answer
| March 14, 2008 8:08 PM
MAXDOP, SQL, SQL Server
The MAXDOP is used like this. <pre>SELECT * FROM TABLE WHERE Col1 = ‘Value’ OPTION (MAXDOP 1)</pre> Basically it should be the last line of the SELECT statement.
View Answer
| March 14, 2008 8:08 PM
MAXDOP, SQL, SQL Server
Unable to execute the saved SSIS package.
It sounds like there is a problem decoding the credentials when opening the saved package. When you setup the package what encryption level did you select?
View Answer
| March 14, 2008 5:22 PM
DTS, SQL Server 2005, SSIS
It sounds like there is a problem decoding the credentials when opening the saved package. When you setup the package what encryption level did you select?
View Answer
| March 14, 2008 5:22 PM
DTS, SQL Server 2005, SSIS
How SSIS works with cluster SQL and non cluster SSIS service?
Before I start, let me say that I’ve clustered SSIS and it works just fine. Assuming that you decide to continue to leave SSIS un-clustered what you’ll want to do is. Edit the xml config file for the SSIS service and add in nodes for each SQL instance so that SSIS can access each instances [...]
View Answer
| March 14, 2008 5:13 PM
SQL Server 2005, SSIS
Before I start, let me say that I’ve clustered SSIS and it works just fine. Assuming that you decide to continue to leave SSIS un-clustered what you’ll want to do is. Edit the xml config file for the SSIS service and add in nodes for each SQL instance so that SSIS can access each instances [...]
View Answer
| March 14, 2008 5:13 PM
SQL Server 2005, SSIS
sql server errors………..
1. SQL does not install the client tools by default. If you have to have the tools installed on your server you’ll need to run through the installer again and install the client tools. On the page with the list of services the last one on the list is the client tools. It is common [...]
View Answer
| March 12, 2008 7:00 PM
SQL Server, SQL Server 2005
1. SQL does not install the client tools by default. If you have to have the tools installed on your server you’ll need to run through the installer again and install the client tools. On the page with the list of services the last one on the list is the client tools. It is common [...]
View Answer
| March 12, 2008 7:00 PM
SQL Server, SQL Server 2005
Setting up database mirroring for disaster recovery in SQL Server
When you fail over to the mirror, once the primary server is back online SQL will automatically being feeding the transactions back. When you look at the databases in the UI it will say if it’s mirroring or synchronized. If it says that it is synchronized then there are no pending transactions to be sent. [...]
View Answer
| March 12, 2008 7:00 AM
Database mirroring, Disaster Recovery, High Availability, SQL Server 2000, SQL Server 2005
When you fail over to the mirror, once the primary server is back online SQL will automatically being feeding the transactions back. When you look at the databases in the UI it will say if it’s mirroring or synchronized. If it says that it is synchronized then there are no pending transactions to be sent. [...]
View Answer
| March 12, 2008 7:00 AM
Database mirroring, Disaster Recovery, High Availability, SQL Server 2000, SQL Server 2005
Do you have to break the replication after you install a service pack in SQL Server 2005?
No you do not have to remove the replication when installing the SQL Server service packs. You shouldn’t have had to do this in SQL 2000 either. It’s been a while since I’ve had to service pack a SQL 2000 machine with replication setup, but I don’t remember having to break the replication.
View Answer
| March 12, 2008 6:52 AM
SQL Server installation, SQL Server replication, SQL Server upgrades
No you do not have to remove the replication when installing the SQL Server service packs. You shouldn’t have had to do this in SQL 2000 either. It’s been a while since I’ve had to service pack a SQL 2000 machine with replication setup, but I don’t remember having to break the replication.
View Answer
| March 12, 2008 6:52 AM
SQL Server installation, SQL Server replication, SQL Server upgrades
Passing a database name as a stored procedure parameter
Yes you can do this, however it is not recommended. You have to use a technique called dynamic SQL. There are security risks on using dynamic SQL as the user who runs the procedure much have access to the table or tables listed in the dynamic SQL, and dynamic SQL is subject to a SQL [...]
View Answer
| March 12, 2008 6:51 AM
Stored Procedures, T-SQL
Yes you can do this, however it is not recommended. You have to use a technique called dynamic SQL. There are security risks on using dynamic SQL as the user who runs the procedure much have access to the table or tables listed in the dynamic SQL, and dynamic SQL is subject to a SQL [...]
View Answer
| March 12, 2008 6:51 AM
Stored Procedures, T-SQL
Linking SQL Server databases to create DTS packages
You’ve got a couple of options here. 1. On server1 create a linked server to server2 using the sp_addlinkedserver procedure. Then use a query from server1 to join the two tables together (using what ever columns are needed). <pre>SELECT T1.* FROM DB1.dbo.T1 T1 INNER JOIN SERVER2.DB2.dbo.T2 T2 ON T1.ID = T2.ID</pre> 2. Setup working tables [...]
View Answer
| March 12, 2008 6:47 AM
Business Intelligence, DTS, SQL Server
You’ve got a couple of options here. 1. On server1 create a linked server to server2 using the sp_addlinkedserver procedure. Then use a query from server1 to join the two tables together (using what ever columns are needed). <pre>SELECT T1.* FROM DB1.dbo.T1 T1 INNER JOIN SERVER2.DB2.dbo.T2 T2 ON T1.ID = T2.ID</pre> 2. Setup working tables [...]
View Answer
| March 12, 2008 6:47 AM
Business Intelligence, DTS, SQL Server
Merge Replication Identity Ranges
Basically you want to make sure that the ranges are large enough that you can’t run out of IDs between merges. If you do a merge every 1 hour and you add in 10000 new records per server per hour at peak you should have at least 12000 IDs in the range so that you [...]
View Answer
| March 11, 2008 7:34 PM
Merge replication, Replication, SQL Server 2005, SQL Server administration
Basically you want to make sure that the ranges are large enough that you can’t run out of IDs between merges. If you do a merge every 1 hour and you add in 10000 new records per server per hour at peak you should have at least 12000 IDs in the range so that you [...]
View Answer
| March 11, 2008 7:34 PM
Merge replication, Replication, SQL Server 2005, SQL Server administration
Connectiveity JAVA to Sql server 2000
Did you try to download SQL Server 2000 Driver for JDBC SP3 or SQL Server 2000 Driver for JDBC SP2? If not, you can find them here: http://support.microsoft.com/default.aspx/ph/2852?cid=C_48273 Thanks! Joelle
View Answer
| March 11, 2008 2:06 PM
JDBC, SQL Server 2000, Windows Server 2003
Did you try to download SQL Server 2000 Driver for JDBC SP3 or SQL Server 2000 Driver for JDBC SP2? If not, you can find them here: http://support.microsoft.com/default.aspx/ph/2852?cid=C_48273 Thanks! Joelle
View Answer
| March 11, 2008 2:06 PM
JDBC, SQL Server 2000, Windows Server 2003
Encrypt a Column in sql server 2005
This answer may help you <a href=”http://itknowledgeexchange.techtarget.com/itanswers/how-to-encrypt-a-column-in-sql-server-20002005/”>http://itknowledgeexchange.techtarget.com/itanswers/how-to-encrypt-a-column-in-sql-server-20002005/</a> If your C# application is using stored procedures to access the data then you can change the stored procedures to use the code in the above link. If you aren’t using stored procedures then you’ll need to get the source code to modify it. There is no way [...]
View Answer
| March 7, 2008 10:29 PM
SQL, SQL Server, SQL Server 2005, SQL Server security
This answer may help you <a href=”http://itknowledgeexchange.techtarget.com/itanswers/how-to-encrypt-a-column-in-sql-server-20002005/”>http://itknowledgeexchange.techtarget.com/itanswers/how-to-encrypt-a-column-in-sql-server-20002005/</a> If your C# application is using stored procedures to access the data then you can change the stored procedures to use the code in the above link. If you aren’t using stored procedures then you’ll need to get the source code to modify it. There is no way [...]
View Answer
| March 7, 2008 10:29 PM
SQL, SQL Server, SQL Server 2005, SQL Server security
Is it possible for named instances to share components in SQL Server 2005?
Typically not. What components did you have in mind?
View Answer
| March 7, 2008 2:21 AM
SQL Server 2005
Typically not. What components did you have in mind?
View Answer
| March 7, 2008 2:21 AM
SQL Server 2005
How can I bring a .dat database file into SQL Server 2005?
.DAT files aren’t usually SQL Server databases. Microsoft SQL Server database files are usually MDF and LDF files with the MDF files being the data file and the LDF being the log file. Microsoft SQL Server hasn’t used the .DAT file extension since SQL Server 6.5. Are you sure that this is a SQL Server [...]
View Answer
| March 7, 2008 2:19 AM
SQL Server 2000, SQL Server 2005
.DAT files aren’t usually SQL Server databases. Microsoft SQL Server database files are usually MDF and LDF files with the MDF files being the data file and the LDF being the log file. Microsoft SQL Server hasn’t used the .DAT file extension since SQL Server 6.5. Are you sure that this is a SQL Server [...]
View Answer
| March 7, 2008 2:19 AM
SQL Server 2000, SQL Server 2005
How can we reindex a SQL Server 2000 database?
This is a good article I have read on reindexing a sql 2000 DB. It has some basic steps you can take as well as some more in-depth steps that will work better on large databases. <a href=”http://www.sql-server-performance.com/articles/per/automatic_reindexing_sql2000_p1.aspx”>http://www.sql-server-performance.com/articles/per/automatic_reindexing_sql2000_p1.aspx</a> The easiest way is to setup a SQL Server maintenance plan. These plans can be configured to [...]
View Answer
| March 7, 2008 2:15 AM
SQL Server 2000, SQL Server database design and modeling
This is a good article I have read on reindexing a sql 2000 DB. It has some basic steps you can take as well as some more in-depth steps that will work better on large databases. <a href=”http://www.sql-server-performance.com/articles/per/automatic_reindexing_sql2000_p1.aspx”>http://www.sql-server-performance.com/articles/per/automatic_reindexing_sql2000_p1.aspx</a> The easiest way is to setup a SQL Server maintenance plan. These plans can be configured to [...]
View Answer
| March 7, 2008 2:15 AM
SQL Server 2000, SQL Server database design and modeling
Restoring a SQL Server 2005 IBM Rational RequisitePro database from SQL Server 2008, back to SQL Server 2005
You can not backup a database and restore it to a prior version of SQL Server. You’ll need to script out the objects and then copy the data from the SQL 2008 database to the SQL 2005 database.
View Answer
| March 7, 2008 2:11 AM
SQL Server 2005, SQL Server 2008, SQL Server database restore
You can not backup a database and restore it to a prior version of SQL Server. You’ll need to script out the objects and then copy the data from the SQL 2008 database to the SQL 2005 database.
View Answer
| March 7, 2008 2:11 AM
SQL Server 2005, SQL Server 2008, SQL Server database restore
