recovery when using sql tables
SQL tables are generally journalled. Use DSPFD to make certain this is the case and the journal names. Recovery is no different than other AS/400 files. Commitment control level is set in SQL At the end of process an SQL commit or Rollback should be sent. Phil
View Answer
| July 7, 2009 8:38 PM
AS/400, AS/400 SQL, SQL Server tables, SQL tables
SQL tables are generally journalled. Use DSPFD to make certain this is the case and the journal names. Recovery is no different than other AS/400 files. Commitment control level is set in SQL At the end of process an SQL commit or Rollback should be sent. Phil
View Answer
| July 7, 2009 8:38 PM
AS/400, AS/400 SQL, SQL Server tables, SQL tables
T-shirt giveaway: Have you uploaded your profile pic yet?
added my photo. dave
View Answer
| July 7, 2009 2:30 PM
AS/400, Development, Exchange, ITKE, Lotus Domino, Microsoft Windows, Networking, Security, SQL Server, Storage, Virtualization
added my photo. dave
View Answer
| July 7, 2009 2:30 PM
AS/400, Development, Exchange, ITKE, Lotus Domino, Microsoft Windows, Networking, Security, SQL Server, Storage, Virtualization
SQL embedded in RPGLE
You wll want to create SQL indexes on each file based on the where field(s) I believe that you can run these interactively in Navigator and capture the necessary indexes. With the correct indexes your SQL commands will find the record to update very much like a chain would. Phil
View Answer
| July 7, 2009 11:50 AM
DB2, iSeries, performance, RPGLE, SQL
You wll want to create SQL indexes on each file based on the where field(s) I believe that you can run these interactively in Navigator and capture the necessary indexes. With the correct indexes your SQL commands will find the record to update very much like a chain would. Phil
View Answer
| July 7, 2009 11:50 AM
DB2, iSeries, performance, RPGLE, SQL
Shrinking a SQL Server database
The only way to shrink a database is to use the DBCC SHRINKDB or DBCC SHRINKFILE statements. However databases should not be shrunk on a regular basis as it causes a large amount of index fragmentation and will hurt your database performance.
View Answer
| July 6, 2009 9:55 PM
SQL Server database, SQL Server Database Consolidation, SQL Server databases
The only way to shrink a database is to use the DBCC SHRINKDB or DBCC SHRINKFILE statements. However databases should not be shrunk on a regular basis as it causes a large amount of index fragmentation and will hurt your database performance.
View Answer
| July 6, 2009 9:55 PM
SQL Server database, SQL Server Database Consolidation, SQL Server databases
Deadlock issue in SQL Server 2005
Using NOLOCK wouldn’t cause you to miss data because it’s a NTEXT field. It could cause you to get bad data back because of the dirty read. What’s the transaction isolation level of your transactions. With the default locking that SQL server does you shouldn’t get a deadlock when those two queries are run at [...]
View Answer
| July 6, 2009 9:53 PM
SQL Server 2005, SQL Server 2005 error messages, SQL Server errors, SQL Troubleshooting
Using NOLOCK wouldn’t cause you to miss data because it’s a NTEXT field. It could cause you to get bad data back because of the dirty read. What’s the transaction isolation level of your transactions. With the default locking that SQL server does you shouldn’t get a deadlock when those two queries are run at [...]
View Answer
| July 6, 2009 9:53 PM
SQL Server 2005, SQL Server 2005 error messages, SQL Server errors, SQL Troubleshooting
RPGLE Stored Proc to Reporting Services
Hello I’m getting the same error that you in both Java procedures and SQL Triggers that try to catch data from the same record and field. Have you found some solution to this error? PS: i5 / 6.1
View Answer
| July 3, 2009 7:25 PM
Reporting Services 2005, RPGLE, SQL stored procedures, SQLRPGLE
Hello I’m getting the same error that you in both Java procedures and SQL Triggers that try to catch data from the same record and field. Have you found some solution to this error? PS: i5 / 6.1
View Answer
| July 3, 2009 7:25 PM
Reporting Services 2005, RPGLE, SQL stored procedures, SQLRPGLE
Increasing Processors of SQL Server 2005 in VM
It should be fine. Add the second vCPU. You’ll probably need to reboot the guest a few times to get Windows working with both vCPUs. Once Windows is working with both of them correctly SQL will work with them both as well. Nothing will need to be done to make SQL use both CPUs.
View Answer
| July 2, 2009 6:40 AM
SQL Server 2005, Virtual Machines
It should be fine. Add the second vCPU. You’ll probably need to reboot the guest a few times to get Windows working with both vCPUs. Once Windows is working with both of them correctly SQL will work with them both as well. Nothing will need to be done to make SQL use both CPUs.
View Answer
| July 2, 2009 6:40 AM
SQL Server 2005, Virtual Machines
sql server remote server connection
You can use SQL Server Management server and/or a linked server to connect. However the firewall on the remote side will need to open the firewall and allow network access to the SQL Server.
View Answer
| July 2, 2009 6:11 AM
Linked SQL Servers, SQL Server Remote Connections
You can use SQL Server Management server and/or a linked server to connect. However the firewall on the remote side will need to open the firewall and allow network access to the SQL Server.
View Answer
| July 2, 2009 6:11 AM
Linked SQL Servers, SQL Server Remote Connections
How do I get the value of a lookup field stored in nvarchar field
I have actually found the answer to my own question. In Sharepoint I have a list where one of the values is a multi-value lookup field. I wanted to be able to create a query to return those values, and didn’t know how to. Here is the answer: select a.tp_id, –this is the company id [...]
View Answer
| July 1, 2009 7:45 PM
NVARCHAR, SharePoint, SQL Server, SQL Server Query
I have actually found the answer to my own question. In Sharepoint I have a list where one of the values is a multi-value lookup field. I wanted to be able to create a query to return those values, and didn’t know how to. Here is the answer: select a.tp_id, –this is the company id [...]
View Answer
| July 1, 2009 7:45 PM
NVARCHAR, SharePoint, SQL Server, SQL Server Query
Convert column from Number(9) to varchar2(3) in SQL
In Oracle, I would use the bulk collect capability, as follows: 1. Create a copy table of the source (empty with no data), as in: create table MyClone as select * from OldTable where 0=1; 2. Alter the table to change the data type of the column from Number(9) to Varchar2(3) alter table MyClone modify [...]
View Answer
| July 1, 2009 3:50 PM
SQL, SQL tables, VARCHAR
In Oracle, I would use the bulk collect capability, as follows: 1. Create a copy table of the source (empty with no data), as in: create table MyClone as select * from OldTable where 0=1; 2. Alter the table to change the data type of the column from Number(9) to Varchar2(3) alter table MyClone modify [...]
View Answer
| July 1, 2009 3:50 PM
SQL, SQL tables, VARCHAR
DTS: Export to Excel
Hello You can try using the transformation between SQL server to MS Excel. If this doesnt work than use ActiveX script to generate the MS Excel file which will able to handle dynamic column names. This will involve writing macro which will select the required columns from table in SQL and put the same in [...]
View Answer
| July 1, 2009 3:25 PM
ActiveX, DTS, DTS Import/Export Wizard, Microsoft Excel
Hello You can try using the transformation between SQL server to MS Excel. If this doesnt work than use ActiveX script to generate the MS Excel file which will able to handle dynamic column names. This will involve writing macro which will select the required columns from table in SQL and put the same in [...]
View Answer
| July 1, 2009 3:25 PM
ActiveX, DTS, DTS Import/Export Wizard, Microsoft Excel
Using SQL Connect Statement in SQLRPGLE
Use the WRKRDBDIRE (Work with Relational Database Directory Entries) command to define the data source. /EXEC SQL + CONNECT TO :remotedb USER :userprf USING :passwrd + SET CONNECTION remotedb *** your select statements should work now /END-EX
View Answer
| June 30, 2009 6:03 PM
CLP, RPGLE, SQL, SQL Connect Statement, SQL statements
Use the WRKRDBDIRE (Work with Relational Database Directory Entries) command to define the data source. /EXEC SQL + CONNECT TO :remotedb USER :userprf USING :passwrd + SET CONNECTION remotedb *** your select statements should work now /END-EX
View Answer
| June 30, 2009 6:03 PM
CLP, RPGLE, SQL, SQL Connect Statement, SQL statements
Code Impact Analysis Tool
Have you checked out the tools that <a href=”http://www.klocwork.com/”>Klocwork </a>offers? They may have a solution.
View Answer
| June 30, 2009 2:18 PM
.NET, Impact analysis tools, Java, Performance and availability, Stored Procedures
Have you checked out the tools that <a href=”http://www.klocwork.com/”>Klocwork </a>offers? They may have a solution.
View Answer
| June 30, 2009 2:18 PM
.NET, Impact analysis tools, Java, Performance and availability, Stored Procedures
Which version of SQL will work best?
You can use SQL Express, however if your database grows beyond 2 Gigs you’ll need to upgrade to SQL Workgroup Edition.
View Answer
| June 29, 2009 12:41 AM
SQL 2005, SQL Express 2005, SQL Server 2005, SQL Server Express
You can use SQL Express, however if your database grows beyond 2 Gigs you’ll need to upgrade to SQL Workgroup Edition.
View Answer
| June 29, 2009 12:41 AM
SQL 2005, SQL Express 2005, SQL Server 2005, SQL Server Express
Software to automate backups for SQL Server 2008 databases
Let me clarify a bit on what I’ve said above. I realize a maintance plan can be created and I have tried this however upon execution of that plan I am getting the following error: TITLE: Execute Maintenance Plan —————————— Execution failed. See the maintenance plan and SQL Server Agent job history logs for details. [...]
View Answer
| June 26, 2009 6:53 PM
Automated backup, Backup & recovery, Backup software, SQL managment Studio, SQL Server 2008, SQL Server backup, SQL Server backup and restore, SQL Server Management Studio
Let me clarify a bit on what I’ve said above. I realize a maintance plan can be created and I have tried this however upon execution of that plan I am getting the following error: TITLE: Execute Maintenance Plan —————————— Execution failed. See the maintenance plan and SQL Server Agent job history logs for details. [...]
View Answer
| June 26, 2009 6:53 PM
Automated backup, Backup & recovery, Backup software, SQL managment Studio, SQL Server 2008, SQL Server backup, SQL Server backup and restore, SQL Server Management Studio
dynamic temporary table variable usage
What’s the error message that you are getting? There’s no limit in SQL to the number of temp tables you can use in a single batch. However I don’t see a temp table (or table variable) in your select statement.
View Answer
| June 25, 2009 8:43 PM
Dynamic SQL, INNER JOIN, SQL tables, SQL variables
What’s the error message that you are getting? There’s no limit in SQL to the number of temp tables you can use in a single batch. However I don’t see a temp table (or table variable) in your select statement.
View Answer
| June 25, 2009 8:43 PM
Dynamic SQL, INNER JOIN, SQL tables, SQL variables
Deleting Nightly Database Backups (dumps)
There’s a cleanup task which you can add to the maintenance plan which will delete the older backups from the folder.
View Answer
| June 25, 2009 5:03 PM
Database Backup, Database backup and recovery, Database maintenance, SQL 2005
There’s a cleanup task which you can add to the maintenance plan which will delete the older backups from the folder.
View Answer
| June 25, 2009 5:03 PM
Database Backup, Database backup and recovery, Database maintenance, SQL 2005
Failover issues in a SQL Server 2005 mirrored database
What driver are you using for the DSN? If memory serves you can’t create a DSN using the SQL 2005 driver and give it the failover server’s information. The SQL 2008 driver includes this ability. Try downloading the SQL 2008 driver from the Microsoft site and installing that, and changing the DSN to use this [...]
View Answer
| June 25, 2009 5:02 PM
Database mirroring, DSN, Failover, SQL Server 2005, SQL Server 2005 connectivity, SQL Server Database Mirroring
What driver are you using for the DSN? If memory serves you can’t create a DSN using the SQL 2005 driver and give it the failover server’s information. The SQL 2008 driver includes this ability. Try downloading the SQL 2008 driver from the Microsoft site and installing that, and changing the DSN to use this [...]
View Answer
| June 25, 2009 5:02 PM
Database mirroring, DSN, Failover, SQL Server 2005, SQL Server 2005 connectivity, SQL Server Database Mirroring
Migrate User & Permissions SQL 2000 to SQL XXXX
Google for sp_help_revlogin. This script from Microsoft will export the users and passwords and allow you to create them on the new server. You can then attach the database from the SQL 2000 server to the new server and the logins will all match the users in the databases.
View Answer
| June 25, 2009 4:57 PM
SQL Server 2000, SQL Server 2000 migration, SQL Server 2000 to SQL Server 2005 migration, SQL Server 2005, SQL Server migration, SQL Server permissions, SQL Server User Accounts, SQL Server user permissions, Upgrade from SQL Server 2000 to SQL Server 2005
Google for sp_help_revlogin. This script from Microsoft will export the users and passwords and allow you to create them on the new server. You can then attach the database from the SQL 2000 server to the new server and the logins will all match the users in the databases.
View Answer
| June 25, 2009 4:57 PM
SQL Server 2000, SQL Server 2000 migration, SQL Server 2000 to SQL Server 2005 migration, SQL Server 2005, SQL Server migration, SQL Server permissions, SQL Server User Accounts, SQL Server user permissions, Upgrade from SQL Server 2000 to SQL Server 2005
Adding processor to SQL Server 2005
No, there shouldn’t be any problems. Just don’t forget to get a new CPU license if needed.
View Answer
| June 25, 2009 5:03 AM
CPU, Processors, SQL Server 2005
No, there shouldn’t be any problems. Just don’t forget to get a new CPU license if needed.
View Answer
| June 25, 2009 5:03 AM
CPU, Processors, SQL Server 2005
