Manjot's questions

Why do we need to run dbcc updateusage(0) after restoring a SQL Server database on a different server? What are the implications of not running it?

We have a Microsoft Access database + application (on Server A) which connects to a remote SQL server (Server B) using System DSN ODBC connection (on Server A) to the SQL database server.

The users are open this Access database remotely as it is on a shared location on the server A. They still have to create a local ODBC connection on their computers to connect to Server B.

Is there anyway that they can access the Access database and not have to create a local ODBC connection?

thanks in advance

I am setting up biztalk logshipping for Biztalk 2009 database. Following http://msdn.microsoft.com/en-us/library/aa560961.aspx article, I am doing the following to setup biztalk logshipping on destination server:

Enable Ad-hoc queries by:

sp_configure 'show advanced options',1
go
reconfigure
go
sp_configure 'Ad Hoc Distributed Queries',1
go
reconfigure
go
sp_configure 'show advanced options',0
go
reconfigure
go

1. Execute LogShipping_Destination_Schema & LogShipping_Destination_Logic in master on destinations server

2. Run:

   exec bts_ConfigureBizTalkLogShipping @nvcDescription = '', @nvcMgmtDatabaseName = '', @nvcMgmtServerName = '', @SourceServerName = null, -- null indicates that this destination server restores all databases @fLinkServers = 1 -- 1 automatically links the server to the management database

When I run this I am receiving the following error:

        Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'.

After some research I found some info :

Usually this error means that the SQL Server Service Principal Name (SPN) was not configured, and NTLM was not being used as an authentication mechanism.

SQl services are runing under different domain accounts. So, I asked the domain admin to create SPNs for the servers, SQL service accounts for beoth source and destination using name and FQDN. enabled computer name and service accounts for delegation. When I run the following:

select * from sys.dm_exec_connections

I can see all TCP connection being made via Kerberos but still same error:

Login failed for user 'NT AUTHORITY\ANONYMOUS LOGON'

Any help please?

I am a database administrator and I need to move SQL databases from one server to another for enterprise project management, Project Server 2007 installed on WSS 3.0 (both at patch level SP2 + Aug09 CU).

I know how to move databases but I don't know how to re-point the application to the new server.

Can anyone please help me?

-

Thanks!

I have migrated SQL server 2005 database to sql server 2008 using side by side upgrade. I have copied Reportserver and reportservertempdb database across and configured SQl server Reporting services 2008 to use this new database. I am not able to restore SSRS key over to new SSRS 2008 server. The key restore reports:

Microsoft.ReportingServices.WmiProvider.WMIProviderException: An error occurred when attempting to connect to the report server remote procedure call (RPC) end point. Verify that the Report Server Windows service is running, and then retry the operation.

 ---> System.Runtime.InteropServices.COMException (0x800706B3): The RPC server is not listening. (Exception from HRESULT: 0x800706B3)
   --- End of inner exception stack trace ---
   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.ThrowOnError(ManagementBaseObject mo)
   at Microsoft.ReportingServices.WmiProvider.RSWmiAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, String password)
   at ReportServicesConfigUI.WMIProvider.RSReportServerAdmin.RestoreEncryptionKey(Byte[] encryptedBytes, String password)

I can open SSRS website but just not the encrypted content because of failure in restoring SSRS key.

Can anyone help please?

For multiple instances of SQL server 2000 servers on the same server, how can I set up to use static ports? I tried to search it on the Internet and i came across This article. If I use this article, how will assign port number for seperate instances?

Thanks in advance.

If the temp db drive is unavailable (others system database files are available), how can I trick SQL server 2000 to create new temp db on C: and start up?

When trying to start up using sqlservr -c -f -T3608 -T4022 from command prompt, it just displays the following and thats it:

Thanks in advance

I created an SSIS package to transfer logins from one server to another in SQL 2005 SSIS project using BIDS.

I want to transfer all packages but skip 3. So I changed the maximum number of error count to 3. But it didn't help.

can anyone please help me in achieving this?

Thanks in advance

Can anyone tell me what is the Equivalent of SQL 2005's SQLAgentOperatorRole (msdb ) in SQL 2000? I want to grant a login ability to disable a job in SQL 2000.

Thanks in advance....

How can I know which edition of SSRS, SSAS and SSIS service is installed on a server? Finding database edition is easy but how to find edition for other SQL services?

Any help please?

Thanks in advance.

We have a central management server on which we collect Baselines for remote servers. Baseline includes perfmon counters like logical disk, SQL server buffer manager, physical disk, network interface etc. We save it to a SQL server database.

The problem is, it is not collecting data for logical disk for 1 server. To check that there is no problem with counters on this server, I created a server specific baseline to collect only the logical disk counters for this server remotely. It collected counter data just fine. this server specific baseline is running under same credentials as the full baseline.

Server specifications: Windows server 2003 R2 standard (64 bit) edition Management Server specifications: Windows server 2003 enterprise edition

Any help why it is not working for the full baseline? I have dropped and added the counters manually but it doesn't help.

Thanks in advance

Are there any general rules/tips which we can use to run Sql Server Reporting Services 2005 reports faster?

I have been tuning the Tsql behind the reports but does there exist any SSRS specific performance tuning?

regards

Manjot

In SQL 2005 and above, If i dont want to give someone sys admin rights how can the person see Activity monitor and sql server logs?

what is the minimum level of rights to see Activity monitor and sql server logs in SQL server?

any help?

regards

Manjot

I have migrated some DTS packages to SSIS 2005 using "Migration" wizard. When I tried to run it, it fails saying you need a higher version of SSIS even though the destination SSIS server is on 9.0.4211 level.

then I digged in the package using business intelligence studio and saw that one of the package subtasks is "Transform data task" (the dts version) and the package fails to run that. The storage location for this dts task is set to "Embedded in Task". I didn't touch it.

why didn't it convert this task to an SSIS data flow task?

any help please?

Thansk in advance

On a SQL server, why do we need a weekly update stats job (with full scan) when we have AUTO_UPDATE_STATISTICS=ON for all databases?

Doesn't this option update statistics all the time?

Regards

I can backup and restore databases on Microsoft SQL server Analysis Service 2008 using GUI as from Backup SSAS

I want to schedule backup and restore it to another server every night. so what i did is : I scripted out the backup and restore process from the GUI. Created a new SQL server agent job in database engine and added a "Run SSAS query" step. Copied the scripts to this step. But it fails. the scripts that the GUI copied out look like:

<Backup xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <Object>
    <DatabaseID>DB</DatabaseID>
  </Object>
  <File>C:\Backup\DB.abf</File>
  <AllowOverwrite>true</AllowOverwrite>
</Backup>

<Restore xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
  <File>\\server\C$\Backup\DB.abf</File>
  <DatabaseName>DB</DatabaseName>
  <AllowOverwrite>true</AllowOverwrite>
</Restore>

Any help please?

I want to do log shipping for biztalk databases on SQL server 2008 standard edition (server A) to another SQL server 2008 standard edition (server B).

I was told that for biztalk, logshipping is not like standard logshipping. I was able to find 2 links: http://msdn.microsoft.com/en-us/library/cc296836%28v=BTS.10%29.aspx

http://msdn.microsoft.com/en-us/library/cc296741%28v=BTS.10%29.aspx

but they are not talking about SQL 2008 servers.

Can anyone please help in this?

Thanks in advance

On my SQL server 2008, i have a SQL agent job to restore a database on nightly basis. Procedure:

1. find latest backup on other server
2. Kill all conenction to the destination database
3. Restore destination database with replace, recovery

It failed last weekend because the database was being used by a system process (spid 11 checkpoint). since I couldnt kill the system process, I fixed this by restarting sql server. It failed this weekend as well with same error (checkpint process in this database as from sp_who) and when I run:

SELECT session_id,request_id,command,status,start_time
FROM sys.dm_exec_requests 
WHERE session_id = 11

It shows:

11  0   CHECKPOINT  background  2010-04-06 10:17:49.103

I cant restart the server every time it fails.
Can anyone please help me in fixing this?

Thanks in advance

Manjot

I have installed 11G Oracle OLE db provider on Windows Server 2008 Release 2. I can find it in the registry but I can't see it in "create a new ODBC connections" wizard.

Any help please?

Regards

I am using SQL server 2008 Standard edition. I am using Policy based management with policies which come with SQL server during installation.

I want the policies to only look at events that happened in last 24 hours. For example for "Windows Event Log System Failure Error" policy if system restarted unexpectedly 5 days ago, i don't want to be alerted daily.

Is there any way by which I can restrict a policy to look at events which happened in last 24 hours not older?

Any help please?

Thanks in advance.