sql 2005 db security for public
Your users are still a member of the public role. The UI simply doesn’t show the public role as you can not remove users from the public role. Are users getting errors when logging in via Windows Authentication or SQL Authentication? What error are they getting? What is shown in the ERRORLOG? After you restored [...]
View Answer
| March 6, 2008 1:13 AM
SQL Server 2000, SQL Server 2005, SQL Server security
Your users are still a member of the public role. The UI simply doesn’t show the public role as you can not remove users from the public role. Are users getting errors when logging in via Windows Authentication or SQL Authentication? What error are they getting? What is shown in the ERRORLOG? After you restored [...]
View Answer
| March 6, 2008 1:13 AM
SQL Server 2000, SQL Server 2005, SQL Server security
VMware ESX 2.5 or 3.0 Virtualizing SQL (2000 or 2005)
Good morning. I guess I have to ask “the” question: What do you hope to gain by virtualizing? Are you looking to consolidate SQL servers in order to reduce the number of physical servers? If that’s the case, then a little leg work may be in order. You may want to do some performance trending [...]
View Answer
| March 5, 2008 9:18 PM
SQL Server 2005, SQL Server Virtualization, VMware, VMware ESX, VMware ESX 2.5
Good morning. I guess I have to ask “the” question: What do you hope to gain by virtualizing? Are you looking to consolidate SQL servers in order to reduce the number of physical servers? If that’s the case, then a little leg work may be in order. You may want to do some performance trending [...]
View Answer
| March 5, 2008 9:18 PM
SQL Server 2005, SQL Server Virtualization, VMware, VMware ESX, VMware ESX 2.5
Installing Terminal server to existing domain
You can leave the server components on the existing server. The only thing which you need to install on the new server is the client component of the tool. This would be what ever software is normally installed on the users workstation. If it’s a web based application then all that the users will need [...]
View Answer
| March 5, 2008 7:49 PM
Exchange, SQL Express, Terminal Server, Windows Server 2003
You can leave the server components on the existing server. The only thing which you need to install on the new server is the client component of the tool. This would be what ever software is normally installed on the users workstation. If it’s a web based application then all that the users will need [...]
View Answer
| March 5, 2008 7:49 PM
Exchange, SQL Express, Terminal Server, Windows Server 2003
SQL Right Trim Nulls
In order to not view values which are null you would need to simply not select those columns. You could also write a script (or possibly a stored procedure or DTS package) that did this for you programatically. Can you provide an example or the data that you are dealing with?
View Answer
| March 5, 2008 7:32 PM
Nulls, SQL, TRIM function
In order to not view values which are null you would need to simply not select those columns. You could also write a script (or possibly a stored procedure or DTS package) that did this for you programatically. Can you provide an example or the data that you are dealing with?
View Answer
| March 5, 2008 7:32 PM
Nulls, SQL, TRIM function
Equivolent SQL Server function for Access DoCmd.TransferText
You can use the BULK IMPORT command to load a file into the SQL Server. BUL IMPORT probably won’t be able to do much with an HTML file. If you craft the BULK IMPORT parameters correctly you might be able to get it to work. You might be better served to use DTS or SSIS [...]
View Answer
| March 5, 2008 6:58 AM
DoCmd.Transfer, Microsoft Access, SQL Server, Stored Procedures
You can use the BULK IMPORT command to load a file into the SQL Server. BUL IMPORT probably won’t be able to do much with an HTML file. If you craft the BULK IMPORT parameters correctly you might be able to get it to work. You might be better served to use DTS or SSIS [...]
View Answer
| March 5, 2008 6:58 AM
DoCmd.Transfer, Microsoft Access, SQL Server, Stored Procedures
Can one instance of SQL Server 2005 support two databases on one server running Windows 2003?
Yes, absolutely! Providing you have enough storage space to hold the data and there is enough horsepower in the server to handle the transactions the two databases’ transactions. Regardless, two databases should not be a problem at all. Most places use one server with multiple databases on them– it just makes sense with the cost [...]
View Answer
| March 5, 2008 5:31 AM
SQL Server 2005, Windows Server 2003
Yes, absolutely! Providing you have enough storage space to hold the data and there is enough horsepower in the server to handle the transactions the two databases’ transactions. Regardless, two databases should not be a problem at all. Most places use one server with multiple databases on them– it just makes sense with the cost [...]
View Answer
| March 5, 2008 5:31 AM
SQL Server 2005, Windows Server 2003
In SQL Server 2005, how do I display all permissions a user/login has? Is there a stored procedure to do this?
SQL Server includes a procedure which I’m not fond of. It’s called sp_helprotect. However I’ve got some code which I’ve thrown together which will display this data nicely and in a single call instead of the mess of code within the procedure. <pre>select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type, sys.database_permissions.permission_name, sys.database_permissions.state permission_state, sys.database_permissions.state_desc, [...]
View Answer
| April 19, 2013 5:25 AM
SQL Server 2005, SQL Server administration
SQL Server includes a procedure which I’m not fond of. It’s called sp_helprotect. However I’ve got some code which I’ve thrown together which will display this data nicely and in a single call instead of the mess of code within the procedure. <pre>select sys.schemas.name 'Schema', sys.objects.name Object, sys.database_principals.name username, sys.database_permissions.type permissions_type, sys.database_permissions.permission_name, sys.database_permissions.state permission_state, sys.database_permissions.state_desc, [...]
View Answer
| April 19, 2013 5:25 AM
SQL Server 2005, SQL Server administration
How can I write a stored procedure to copy data from one database to another database on the same SQL Server, every weekend?
This is kind of a two part question. The first part being how to access data in another database, and the second part being how to schedule the procedure to run each week. Part 1. In order to access data in another database you have to use the three part name of the object. With [...]
View Answer
| March 5, 2008 5:23 AM
SQL Server, Stored Procedures
This is kind of a two part question. The first part being how to access data in another database, and the second part being how to schedule the procedure to run each week. Part 1. In order to access data in another database you have to use the three part name of the object. With [...]
View Answer
| March 5, 2008 5:23 AM
SQL Server, Stored Procedures
execution plan is same even though it takes 10 times longer to run
It shouldn’t be the same query plan. Can you post both of the execution plans? Also what database platform is this.
View Answer
| March 3, 2008 6:15 AM
Execution Plan, Indexes, Performance/Tuning
It shouldn’t be the same query plan. Can you post both of the execution plans? Also what database platform is this.
View Answer
| March 3, 2008 6:15 AM
Execution Plan, Indexes, Performance/Tuning
Do replica dbs need to be updagraded when uprading from SQL 2000 to SQL 2005
No they don’t need to. You can upgrade each server independently of each other. However after you upgrade your publisher or distributor you may need to resetup the replication.
View Answer
| March 2, 2008 9:43 PM
SQL Server 2000, SQL Server 2005, SQL Server replication
No they don’t need to. You can upgrade each server independently of each other. However after you upgrade your publisher or distributor you may need to resetup the replication.
View Answer
| March 2, 2008 9:43 PM
SQL Server 2000, SQL Server 2005, SQL Server replication
Upgrading from SQL 2000 TO SQL 2005 Will all the sites (Multisites) need to be upgraded
Each SQL Server needs to be upgraded separately. You’ll need to upgrade each server one at a time.
View Answer
| March 2, 2008 9:40 PM
SQL Server 2000, SQL Server 2005, SQL Server installation
Each SQL Server needs to be upgraded separately. You’ll need to upgrade each server one at a time.
View Answer
| March 2, 2008 9:40 PM
SQL Server 2000, SQL Server 2005, SQL Server installation
Msg 102, level 15, State 1, Line 102
Are you running the code using Management Studio (SQL 2005) or Query Analyzer (SQL 2000)? Sometimes the “compiler” cannot ubicate the exact place where the error appears. You can create the SP in phases: each time place the “go” in a different place and see what are the parts that run successfully without returning the [...]
View Answer
| March 2, 2008 9:34 PM
SQL Server, SQL Server errors, Stored Procedures
Are you running the code using Management Studio (SQL 2005) or Query Analyzer (SQL 2000)? Sometimes the “compiler” cannot ubicate the exact place where the error appears. You can create the SP in phases: each time place the “go” in a different place and see what are the parts that run successfully without returning the [...]
View Answer
| March 2, 2008 9:34 PM
SQL Server, SQL Server errors, Stored Procedures
Settng Memory Sql Server 2005 SP2 64Bit
There are a couple of numbers you can try. A lot of people like setting SQL Server to use 1 Gig less than the physical memory, so in your case 11 Gigs. This leaves 1 Gig for Windows and anything else running on the server. If you have a lot of very complex SSIS packages [...]
View Answer
| February 29, 2008 9:42 AM
SQL Server, SQL Server 2005, SQL Server performance tuning
There are a couple of numbers you can try. A lot of people like setting SQL Server to use 1 Gig less than the physical memory, so in your case 11 Gigs. This leaves 1 Gig for Windows and anything else running on the server. If you have a lot of very complex SSIS packages [...]
View Answer
| February 29, 2008 9:42 AM
SQL Server, SQL Server 2005, SQL Server performance tuning
29 Feb incorrect in DateAdd and GetDate command
That would be because in 543 years there is no leap year. Leap year is in 544 years. I ran this to find the leap years. (I’m on the west coast so it’s still the 28th. <pre>select GETDATE()+1 select DateAdd(yyyy,541,GetDate()+1 ) select DateAdd(yyyy,542,GetDate()+1 ) select DateAdd(yyyy,543,GetDate()+1 ) select DateAdd(yyyy,544,GetDate()+1 ) select DateAdd(yyyy,545,GetDate()+1 ) select DateAdd(yyyy,546,GetDate()+1 [...]
View Answer
| June 11, 2013 7:46 AM
SQL Server 2000, SQL Server development
That would be because in 543 years there is no leap year. Leap year is in 544 years. I ran this to find the leap years. (I’m on the west coast so it’s still the 28th. <pre>select GETDATE()+1 select DateAdd(yyyy,541,GetDate()+1 ) select DateAdd(yyyy,542,GetDate()+1 ) select DateAdd(yyyy,543,GetDate()+1 ) select DateAdd(yyyy,544,GetDate()+1 ) select DateAdd(yyyy,545,GetDate()+1 ) select DateAdd(yyyy,546,GetDate()+1 [...]
View Answer
| June 11, 2013 7:46 AM
SQL Server 2000, SQL Server development
Stored Procedure for dropping indexes
What database platform are you using? 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. It requires a little more maintenance when ever indexes are added to the [...]
View Answer
| February 29, 2008 1:41 AM
DROP, Indexes, Restore, Stored Procedures
What database platform are you using? 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. It requires a little more maintenance when ever indexes are added to the [...]
View Answer
| February 29, 2008 1:41 AM
DROP, Indexes, Restore, Stored Procedures
Using SELECT statements
You can use a combination of oracle functions within one select statement to get your answer. The following code seems to work: select (next_day(last_day(sysdate),’SAT’) – next_day(last_day(add_months(sysdate,-1)),’SAT’)) / 7 from dual; Sysdate gives the current date; but you could replace sysdate with any date The concept is to get the number of days between the first [...]
View Answer
| February 28, 2008 5:06 PM
Oracle, Oracle development, SELECT statement
You can use a combination of oracle functions within one select statement to get your answer. The following code seems to work: select (next_day(last_day(sysdate),’SAT’) – next_day(last_day(add_months(sysdate,-1)),’SAT’)) / 7 from dual; Sysdate gives the current date; but you could replace sysdate with any date The concept is to get the number of days between the first [...]
View Answer
| February 28, 2008 5:06 PM
Oracle, Oracle development, SELECT statement
SQL MERGE
Hi, If I understand the problem, you want to produce one row per day and employee for every day from the employee’s first transaction to the employee’s last transaction. (For example, John had transactions from Jan. 24 to Jan. 27, so you want a row for each day in that range, even days like Jan. [...]
View Answer
| February 27, 2008 4:47 PM
SQL, SQL MERGE
Hi, If I understand the problem, you want to produce one row per day and employee for every day from the employee’s first transaction to the employee’s last transaction. (For example, John had transactions from Jan. 24 to Jan. 27, so you want a row for each day in that range, even days like Jan. [...]
View Answer
| February 27, 2008 4:47 PM
SQL, SQL MERGE
Microsoft SSAS with Cognos Front End
We have been using Cognos front-end with SQL 2005 back-end for about 1.5 yrs – using SSIS for copying/transforming data from Cognos to our accounting system, but not using SSAS with it (and no plans to that I’m aware of). Just have the normal DBA jobs to dynamically back up any user database nightly ( [...]
View Answer
| February 27, 2008 1:15 PM
Cognos, SQL Server, SSAS, SSIS
We have been using Cognos front-end with SQL 2005 back-end for about 1.5 yrs – using SSIS for copying/transforming data from Cognos to our accounting system, but not using SSAS with it (and no plans to that I’m aware of). Just have the normal DBA jobs to dynamically back up any user database nightly ( [...]
View Answer
| February 27, 2008 1:15 PM
Cognos, SQL Server, SSAS, SSIS
Performance Tracking of Stored Procedures
Not a general tool, but if you own the stored procedure and related subprograms then you might be interested in the <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/apis/perfmgmt_pex.htm”>Performance Explorer (PEX) APIs</a>. These APIs allow you to capture application related performance data at points in the application that you choose and, as a “free” side benefit, also system tracked performance data [...]
View Answer
| February 27, 2008 11:50 AM
.NET applications, COBOL, COBOL stored procedures, CPU usage, MIPS, Performance management, Performance/Tuning, Stored Procedures
Not a general tool, but if you own the stored procedure and related subprograms then you might be interested in the <a href=”http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/apis/perfmgmt_pex.htm”>Performance Explorer (PEX) APIs</a>. These APIs allow you to capture application related performance data at points in the application that you choose and, as a “free” side benefit, also system tracked performance data [...]
View Answer
| February 27, 2008 11:50 AM
.NET applications, COBOL, COBOL stored procedures, CPU usage, MIPS, Performance management, Performance/Tuning, Stored Procedures
Fixing a corrupted file
After talking to my Cisco guru, I have an answer for you. It may not be the answer you were looking for, though. If you are wanting to do a backup of your running config, but are unable to save it, the simple way would be to do the following: 1. Open HyperTerminal (Accessories->Communication->Hyperterminal) 2. [...]
View Answer
| February 26, 2008 2:46 PM
Config, File, Networking, Routers
After talking to my Cisco guru, I have an answer for you. It may not be the answer you were looking for, though. If you are wanting to do a backup of your running config, but are unable to save it, the simple way would be to do the following: 1. Open HyperTerminal (Accessories->Communication->Hyperterminal) 2. [...]
View Answer
| February 26, 2008 2:46 PM
Config, File, Networking, Routers
