Error inserting into table with identity column
It is always best practice to list out the columns of the table you are inserting into. This way you don’t have to worry about issues like this.

View Answer   |  May 22, 2009  9:18 PM
SQL 2000, SQL stored procedures, Stored Procedures

Making two records into one
How about something like this: <pre>SELECT empl_num, SUM((CASE WHEN pay_type = 1 THEN rate ELSE 0 END)) AS reg_Rate, SUM((CASE WHEN pay_type = 3 THEN rate ELSE 0 END)) AS ovr_Rate FROM tblPayrollFlashJobDetail WHERE unitnum = 8 AND salesdate BETWEEN ’01/01/09′ AND ’01/10/09′ AND empl_num = 257 GROUP BY empl_num ORDER BY empl_num</pre> But, why [...]

View Answer   |  May 22, 2009  8:49 PM
CASE statement, SQL Records

Retrieving Records based on Record Selection from another table
In the combo box for the item, put in the Row Source propery a query like this: Select item, description from items where category = forms![orderentry]![category] Also, in the Got Focus event of the item, do: me![item].requery This will cause the combo box to requery if the category has changed If you move the database [...]

View Answer   |  May 22, 2009  2:16 PM
Access Database, Microsoft Access, SQL Database

Removing data in a column after a hyphen
In SQL Server, you could use the CHARINDEX function. Something like this <pre>SELECT CASE CHARINDEX(‘-’,names) WHEN 0 THEN names ELSE SUBSTRING(names,1,(CHARINDEX(‘-’,names+’-')-2)) END FROM your_table</pre> If the space is not always present between the last name and the hyphen, you coud modify the statement to something like this: <pre>SELECT CASE CHARINDEX(‘-’,names) WHEN 0 THEN names ELSE [...]

View Answer   |  May 20, 2009  9:23 PM
SQL Database, SQL Database Formatting, SQL statement

Remote connection on SQL Server 2005 Express
The Windows account that you are using to access the remote doesn’t have access to the tables which is why you can’t see them. The person who’s computer you are accessing will need to give you access to the tables.

View Answer   |  May 20, 2009  12:41 AM
Remote connection, SQL Express 2005, SQL Server 2005 Express

Finding nth most recent action with SQL
In MySql, you could use the LIMIT clause to get something similar to the TOP clause. LIMIT’s example: SELECT * FROM yourTable LIMIT 10 You could also generate a row number, this way: select @rownum:=@rownum+1 ‘row_number’, y.* FROM YourTable y, (SELECT @rownum:=0) r LIMIT 10; ———————————————

View Answer   |  May 19, 2009  8:29 PM
COUNT statement, Database programming, SQL, SQL query optimization, SQL statements, SUM statement

Display Query for OS/400 SQL script
Hi, You can use the RTVQMQRY command to retrieve the SQL statement to a source file – you’ll need to specify ALWQRYDFN(*YES). Regards, Martin Gilbert.

View Answer   |  May 19, 2009  3:54 PM
OS/400, OS/400 Query, Query for OS/400, SQL scripts

Changing Database Collation
you may use simple alter statement: ALTER DATABASE MyDatabase COLLATE <desired collation> Try it after taking a backup

View Answer   |  May 19, 2009  9:39 AM
Collation, Database collation, SQL Server 2000

question: T-SQL stored procedure conversion to C# CLR assembly….
Ask and you <a href=”http://itknowledgeexchange.techtarget.com/sql-server/my-experience-with-putting-a-crl-procedure-into-sql/”>shall receive</a>. Example has a single in, and a single out parm.

View Answer   |  May 19, 2009  12:48 AM
C#, CLR, T-SQL, T-SQL Stored Procedures

Performance issues with a large table in SQL Server 2005
Adding 6000 records per day isn’t all that much. You are only looking at ~2.2M records per year. 1. When you add data the index isn’t rebuild. The new data is simply inserted into the correct data page of the index, and if needed SQL does a page split. When building your indexes pay attention [...]

View Answer   |  May 17, 2009  5:04 AM
SQL Server 2005 performance, SQL server 2005 performance tuning, SQL Server query optimization

SQL Log files
You can not delete the transaction log files. These files are required for the databases to function. There must be at least one per database. If you don’t require the ability to do point in time restores then you can change the database recovery model from FULL to SIMPLE so that log entries are not [...]

View Answer   |  May 16, 2009  3:59 AM
SQL, SQL Log Files

Fulltext Search – SQL Server 2005 SP2
SQL Server full text indexing doesn’t allow for indexing files outside of the database. They would need to be loading into a table within the database for the SQL Server to index. You can use the Windows 2003 Indexing service to index files on a file server, however it doesn’t support indexing files on an [...]

View Answer   |  May 16, 2009  3:46 AM
FTP Server, SQL Server 2005, SQL Server 2005 administration, SQL Server Full Text Search

Execute command in SQL
At first look, a comma is missing before ‘Amt’: set @cmd= N’insert into TmpDues ( PatNo, BillDate, ReceiptNo, PayMode<b>,</b>Amt) But I think it would be easier if you tell us what error message you are getting. Try printing the contents of @Cmd before executing it, so you can have a look at the actual SQL [...]

View Answer   |  May 16, 2009  3:39 AM
SQL, SQL error messages, SQL queries

Errors creating database with SQL Server 2008 I&M Training Manual
You’ll need to create the folder C:Test for this script to work.

View Answer   |  May 16, 2009  3:38 AM
CREATE DATABASE statement, SQL Server 2008, SQL Server 2008 error messages, SQL Server 2008 training, SQL Server administration

Changing SQL Server name and SQL Server group name
That’s all you should have to do. If you are adding in the group that you’ll use for the sysadmins to get access, add the new one first then remove the old one.

View Answer   |  May 16, 2009  1:20 AM
SQL Server group name, SQL Server name

stored procs in Master db, sql server 2005
Did you check <a href=”http://msdn.microsoft.com/en-us/library/ms176007.aspx”>Microsoft documentation</a>? I remember that there was a sp that listed all basic information on the stored procedure but can’t remind it right away. You can get a list of stored procedures by querying the sys.procedures catalog view. You can use the sp_helptext procedure to get the contents of each procedure.

View Answer   |  May 16, 2009  1:02 AM
SQL Server 2005, SQL Server stored procedures, T-SQL

SQL Server Agent Job Creation
It is not recommended to call a webpage from a SQL Agent job as the SQL Agent job is a behind the scenes process, so anything that it does isn’t visible to the user. You can find http_get.exe which is a command line app which can be used to get a web page. If you [...]

View Answer   |  May 15, 2009  9:08 PM
SQL Server 2000, SQL Server 2000 administration, SQL Server Agent, SQL Server Agent Jobs

How do you create stored procedures in SQL Server 2000?
Dont think much … Open Query Analise and press F1 Search for CREATE PROCEDURE Got it …. Njoy —- Take the SQL Statement that you want to make the stored procedure and put the below line above it. Then run the script. This will create a stored procedure based on the name you provided in [...]

View Answer   |  May 15, 2009  4:55 AM
SQL Server 2000, SQL Server stored procedures

Going from SQL Server 64-bit Enterprise to 32-bit Standard
Yes you can move a database from one edition to another, and from one build to another without issue.

View Answer   |  May 15, 2009  1:11 AM
SQL Server (32-bit), SQL Server (64-bit), SQL Server migration

SQL 2000 Upgrade
If you do an in place upgrade then all those passwords should remain. You’ll want to have copies of the passwords just in case though.

View Answer   |  May 15, 2009  12:51 AM
SQL Server 2000, SQL Server 2000 to SQL Server 2005 upgrade, SQL Server 2005, SQL Server upgrades, Upgrade from SQL Server 2000 to SQL Server 2005