Decreasing the unused space of a SQL Server table
In all OLTP environments, virtually all indexes will become fragmented over time. Nearly all UPDATE, INSERT or DELETE activity will cause your indexes to become less well organized than they were when they were first created. There will be more page splits, there will be a greater number of pages with less data on them, [...]
View Answer
| January 15, 2009 4:10 PM
SQL Server index strategy, SQL Server tables
In all OLTP environments, virtually all indexes will become fragmented over time. Nearly all UPDATE, INSERT or DELETE activity will cause your indexes to become less well organized than they were when they were first created. There will be more page splits, there will be a greater number of pages with less data on them, [...]
View Answer
| January 15, 2009 4:10 PM
SQL Server index strategy, SQL Server tables
Import SQL Server 2000 DTS package to new box
In SQL Server 2000, if you have a requirement to copy/transfer multiple DTS packages from one instance to the other, doing it manually becomes cumbersome. There are utilities in the market that do that for you but what if you have to make this import a part of your install? If you have such a [...]
View Answer
| January 15, 2009 4:05 PM
Data Transformation Services, DTS packages, Import DTS packages, SQL Server 2000, SQL Server 2000 import/export, SQL Server 2000 migration, Windows Server 2003
In SQL Server 2000, if you have a requirement to copy/transfer multiple DTS packages from one instance to the other, doing it manually becomes cumbersome. There are utilities in the market that do that for you but what if you have to make this import a part of your install? If you have such a [...]
View Answer
| January 15, 2009 4:05 PM
Data Transformation Services, DTS packages, Import DTS packages, SQL Server 2000, SQL Server 2000 import/export, SQL Server 2000 migration, Windows Server 2003
Can embedded SQL in a SQLRPGLE program be used as a stored procedure?
Yes it should work. If I remember correctly, don’t close the cursor on result sets that you intend to return. Phil
View Answer
| January 15, 2009 1:50 PM
Embedded SQL, iSeries development, iSeries RPG programming, RPG, RPGLE, SQL, SQLRPGLE, Stored Procedures
Yes it should work. If I remember correctly, don’t close the cursor on result sets that you intend to return. Phil
View Answer
| January 15, 2009 1:50 PM
Embedded SQL, iSeries development, iSeries RPG programming, RPG, RPGLE, SQL, SQLRPGLE, Stored Procedures
Need SQL query
You want a query like this then. <pre>SELECT Detail.ProjectDetail FROM Detail JOIN User on Detail.ProjectId = User.ProjectId AND User.UserId = TT15</pre> As I understood, Minuvinu needs a comma-separated list. Here the sketch of solution (MS SQL 2005): <pre>select userid, (select cast(projectid as varchar)+’, ‘ as ‘data()’ from [user] u1 where u1.userid=u2.userid for xml path(”)) list [...]
View Answer
| January 14, 2009 10:21 PM
SELECT statement, SQL, SQL Query
You want a query like this then. <pre>SELECT Detail.ProjectDetail FROM Detail JOIN User on Detail.ProjectId = User.ProjectId AND User.UserId = TT15</pre> As I understood, Minuvinu needs a comma-separated list. Here the sketch of solution (MS SQL 2005): <pre>select userid, (select cast(projectid as varchar)+’, ‘ as ‘data()’ from [user] u1 where u1.userid=u2.userid for xml path(”)) list [...]
View Answer
| January 14, 2009 10:21 PM
SELECT statement, SQL, SQL Query
Duplicating records in a table with a SQL query
You could do it this way: <pre>INSERT INTO YourTable SELECT * FROM YourTable GO</pre>
View Answer
| January 14, 2009 7:01 PM
Duplicate records, SQL queries, SQL tables
You could do it this way: <pre>INSERT INTO YourTable SELECT * FROM YourTable GO</pre>
View Answer
| January 14, 2009 7:01 PM
Duplicate records, SQL queries, SQL tables
Adding an index on fields in a SQL Server table
Yes, you can. You should consider creating indexes on columns that are frequently used in the WHERE ( and ORDER BY, and GROUP BY ) clause of queries. Fields with low selectivity (such as gender) are not good candidates for indexing.
View Answer
| January 14, 2009 6:40 PM
Primary keys, SQL Server index strategy, SQL Server tables
Yes, you can. You should consider creating indexes on columns that are frequently used in the WHERE ( and ORDER BY, and GROUP BY ) clause of queries. Fields with low selectivity (such as gender) are not good candidates for indexing.
View Answer
| January 14, 2009 6:40 PM
Primary keys, SQL Server index strategy, SQL Server tables
COPY data between to files with SQL in iSeries
Hi, You can use CPYF with the record format field mapping options to do this :- CPYF FROMFILE(Yourlib/Oldfile) TOFILE(Yourlib/Newfile) MBROPT(*REPLACE) FMTOPT(*MAP *DROP) Regards, Martin Gilbert.
View Answer
| January 14, 2009 2:33 PM
iSeries SQL, SQL, SQL Copy Record, SQL Record
Hi, You can use CPYF with the record format field mapping options to do this :- CPYF FROMFILE(Yourlib/Oldfile) TOFILE(Yourlib/Newfile) MBROPT(*REPLACE) FMTOPT(*MAP *DROP) Regards, Martin Gilbert.
View Answer
| January 14, 2009 2:33 PM
iSeries SQL, SQL, SQL Copy Record, SQL Record
Differences between SQL Server 2005 and SQL Server Express
When buying SQL Server there are a couple of different Licensing modes that you have to link into. They are CPU based licensing and CAL (Client Access License) based licensing. With CPU based licensing you buy one license for each CPU in the SQL Server. For CAL based licensing you buy a server license and [...]
View Answer
| January 13, 2009 1:20 AM
SQL Server 2005, SQL Server Express Edition
When buying SQL Server there are a couple of different Licensing modes that you have to link into. They are CPU based licensing and CAL (Client Access License) based licensing. With CPU based licensing you buy one license for each CPU in the SQL Server. For CAL based licensing you buy a server license and [...]
View Answer
| January 13, 2009 1:20 AM
SQL Server 2005, SQL Server Express Edition
Adding a password to a SQL Server 2005 database
Here is a <a href=”http://www.sqlservercentral.com/articles/Administering/beginningsqlserverloginsandusers/1460/”>good article.</a>. You will have to register to read it, but registration is free. ——- Each login which is setup within the SQL Server has its own password. The login is what allows the user to see the databases. Logins are mapped to users within the database and rights within the [...]
View Answer
| January 13, 2009 1:10 AM
Access Database, Microsoft Access, Password management, SQL Server 2005, SQL Server databases
Here is a <a href=”http://www.sqlservercentral.com/articles/Administering/beginningsqlserverloginsandusers/1460/”>good article.</a>. You will have to register to read it, but registration is free. ——- Each login which is setup within the SQL Server has its own password. The login is what allows the user to see the databases. Logins are mapped to users within the database and rights within the [...]
View Answer
| January 13, 2009 1:10 AM
Access Database, Microsoft Access, Password management, SQL Server 2005, SQL Server databases
How to check the existence of a Column in SQL Server 2000?
Try this: <pre>select sysobjects.name from syscolumns left join sysobjects on sysobjects.id = syscolumns.id where syscolumns.name = ‘FieldName’ and sysobjects.type = ‘U’ order by 1</pre> Or this <pre>SELECT name FROM syscolumns WHERE name = ‘YourColumnName’ and id = object_id(‘YourTable’)</pre> A standard way: <pre>select table_name, column_name from information_schema.columns where column_name=’YourColumnName’</pre>
View Answer
| January 12, 2009 9:35 PM
SQL queries, SQL Server 2000, SQL Server 2000 queries
Try this: <pre>select sysobjects.name from syscolumns left join sysobjects on sysobjects.id = syscolumns.id where syscolumns.name = ‘FieldName’ and sysobjects.type = ‘U’ order by 1</pre> Or this <pre>SELECT name FROM syscolumns WHERE name = ‘YourColumnName’ and id = object_id(‘YourTable’)</pre> A standard way: <pre>select table_name, column_name from information_schema.columns where column_name=’YourColumnName’</pre>
View Answer
| January 12, 2009 9:35 PM
SQL queries, SQL Server 2000, SQL Server 2000 queries
Creating a username and password on a SQL Server database
Here is a <a href=”http://www.sqlservercentral.com/articles/Administering/beginningsqlserverloginsandusers/1460/”>good article</a>. It requires you register to read it, but registration is free.
View Answer
| January 12, 2009 5:00 PM
Password management, SQL Server databases, SQL Server security
Here is a <a href=”http://www.sqlservercentral.com/articles/Administering/beginningsqlserverloginsandusers/1460/”>good article</a>. It requires you register to read it, but registration is free.
View Answer
| January 12, 2009 5:00 PM
Password management, SQL Server databases, SQL Server security
Upgrading to SQL Server 2005
Upgrading from SQL 2000 to SQL 2005 in a clustered environment is very similar to going through the upgrade process in a non-clustered environment. After verifying that your code is ready for backup, stop your clients from connecting, and backup the database (or backup the last log). If the upgrade fails badly enough you’ll need [...]
View Answer
| January 11, 2009 10:46 AM
SQL Server 2000, SQL Server 2005, SQL Server upgrades
Upgrading from SQL 2000 to SQL 2005 in a clustered environment is very similar to going through the upgrade process in a non-clustered environment. After verifying that your code is ready for backup, stop your clients from connecting, and backup the database (or backup the last log). If the upgrade fails badly enough you’ll need [...]
View Answer
| January 11, 2009 10:46 AM
SQL Server 2000, SQL Server 2005, SQL Server upgrades
Changing Compatability Level from 65 to 80
If the database is attached to a SQL 2000 instance then the database has already been upgraded to SQL 2000. Setting the compatibility level from 65 to 80 simply changes the way some commands respond from the way they responded in SQL 6.5 to the way they respond in SQL 2000. For the most part [...]
View Answer
| January 11, 2009 10:35 AM
SQL Server 2000, SQL Server 2000 SP4, SQL Server Compatibility Level, SQL Server database
If the database is attached to a SQL 2000 instance then the database has already been upgraded to SQL 2000. Setting the compatibility level from 65 to 80 simply changes the way some commands respond from the way they responded in SQL 6.5 to the way they respond in SQL 2000. For the most part [...]
View Answer
| January 11, 2009 10:35 AM
SQL Server 2000, SQL Server 2000 SP4, SQL Server Compatibility Level, SQL Server database
Find Next SQL AutoNumber
The IDENT_CURRENT function will give you the most recently used identity. You can then use the IDENT_INCR to get the number by increment by (usually 1, but not always). Add them together and you’ll get the next value to be used. However it is better to insert the new record then get the value of [...]
View Answer
| January 11, 2009 10:32 AM
SQL, SQL AutoNumber, SQL tables
The IDENT_CURRENT function will give you the most recently used identity. You can then use the IDENT_INCR to get the number by increment by (usually 1, but not always). Add them together and you’ll get the next value to be used. However it is better to insert the new record then get the value of [...]
View Answer
| January 11, 2009 10:32 AM
SQL, SQL AutoNumber, SQL tables
Creating tables on a SQL Server
Hi, Do you think you need many tables as many business categories exists ? No, you don’t. You need one table to save all 4816 business categories. If you need to save more or detailed information for each business category, then you can create another table with the resting data, and create a relation between [...]
View Answer
| January 11, 2009 12:33 AM
SQL Server, SQL Server Scripts, SQL Server stored procedures, SQL Server tables
Hi, Do you think you need many tables as many business categories exists ? No, you don’t. You need one table to save all 4816 business categories. If you need to save more or detailed information for each business category, then you can create another table with the resting data, and create a relation between [...]
View Answer
| January 11, 2009 12:33 AM
SQL Server, SQL Server Scripts, SQL Server stored procedures, SQL Server tables
DB2 SQL Confusion
To be able to help, I think we would need to know your table structures, and see what results are you getting and what results are you expecting with some example data. When joining tables, if you do not write the appropriate join conditions, you could be duplicating rows.
View Answer
| January 9, 2009 9:45 PM
DB2, DB2 SQL, SQL
To be able to help, I think we would need to know your table structures, and see what results are you getting and what results are you expecting with some example data. When joining tables, if you do not write the appropriate join conditions, you could be duplicating rows.
View Answer
| January 9, 2009 9:45 PM
DB2, DB2 SQL, SQL
Changing databases at run time in SQL Server 2005
You may try using Exec SP_ExecuteSQL in SQL Server, or ADO.NET and the System.Data.SQLClient Namespace. – Parameters for Stored procedure @DataBase_Name nVarchar(20) – set variables in SQL Declare @sql_All nVarchar(1024), @sql_Select nVarchar(1024), @sql_From nVarchar(1024) /* @sql_Where nVarchar(1024), @sql_Order nVarchar(1024) */ set @sql_Select = ‘ Select field1, field2′ + CHAR(13) set @sql_From = ‘ From ‘ [...]
View Answer
| January 9, 2009 8:57 PM
Crystal Reports 8.5, SQL Server 2005, Visual Basic 6
You may try using Exec SP_ExecuteSQL in SQL Server, or ADO.NET and the System.Data.SQLClient Namespace. – Parameters for Stored procedure @DataBase_Name nVarchar(20) – set variables in SQL Declare @sql_All nVarchar(1024), @sql_Select nVarchar(1024), @sql_From nVarchar(1024) /* @sql_Where nVarchar(1024), @sql_Order nVarchar(1024) */ set @sql_Select = ‘ Select field1, field2′ + CHAR(13) set @sql_From = ‘ From ‘ [...]
View Answer
| January 9, 2009 8:57 PM
Crystal Reports 8.5, SQL Server 2005, Visual Basic 6
sql server 2005 mirroring
You can certainly run backups of the Primary node of the mirror and do them online just like a server that is not being mirrored. As far as the backups are concerned they do not care if you are mirroring. I would recommend backing your DBs up and do not rely on the mirror as [...]
View Answer
| January 9, 2009 7:05 PM
Server mirroring, SQL Server 2005, SQL Server backup and restore, SQL Server Mirroring
You can certainly run backups of the Primary node of the mirror and do them online just like a server that is not being mirrored. As far as the backups are concerned they do not care if you are mirroring. I would recommend backing your DBs up and do not rely on the mirror as [...]
View Answer
| January 9, 2009 7:05 PM
Server mirroring, SQL Server 2005, SQL Server backup and restore, SQL Server Mirroring
What should our next contest be on ITKnowledgeExchange.com?
<b>I believe the contest should be quantitative and measurable in regards to sales? For instance as an expert replies to a question, and it leads directly to a Solution – and preferrably a SALE. That will show real value, and results too!</b> Bob VL but how to measure it – objectively (not subjectively)? Jaideep Sales [...]
View Answer
| January 9, 2009 6:05 PM
AS/400, CRM, Database, DataCenter, Development, Exchange, Linux, Lotus Domino, Mobile, Networking, Oracle, SAP, SQL Server, Storage, Virtualization, VoIP
<b>I believe the contest should be quantitative and measurable in regards to sales? For instance as an expert replies to a question, and it leads directly to a Solution – and preferrably a SALE. That will show real value, and results too!</b> Bob VL but how to measure it – objectively (not subjectively)? Jaideep Sales [...]
View Answer
| January 9, 2009 6:05 PM
AS/400, CRM, Database, DataCenter, Development, Exchange, Linux, Lotus Domino, Mobile, Networking, Oracle, SAP, SQL Server, Storage, Virtualization, VoIP
How to backup a SQL Server database
Typically you set up a maintenance plan in SQL to do a backup “dump” to disk then you tell the Veritas software to back up that backup folder on the SQL server. That way work is not interrupted while SQL does its backup and then when veritas backs up that folder. Here is <a href=”http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx”>one [...]
View Answer
| January 9, 2009 2:45 PM
SQL Server 2005, SQL Server 2005 backup, Veritas Backup Exec 9.1
Typically you set up a maintenance plan in SQL to do a backup “dump” to disk then you tell the Veritas software to back up that backup folder on the SQL server. That way work is not interrupted while SQL does its backup and then when veritas backs up that folder. Here is <a href=”http://www.sql-server-performance.com/articles/dba/creating_backup_jobs_p1.aspx”>one [...]
View Answer
| January 9, 2009 2:45 PM
SQL Server 2005, SQL Server 2005 backup, Veritas Backup Exec 9.1
