.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Post New Web Links

Running an XMLA script of a database on another database

Posted By:      Posted Date: October 20, 2010    Points: 0   Category :Sql Server


I used the "Script Database as -> Create to" option on an Analysis Services DB and now I'm trying to run the script on another DB server, but I get an error message that it already exists, and indeed it does, but then when I delete it and run the script again, I get a message that it doesn't exist(!) or the user doesn't have access to it (but it's actually defined as having server-wide permissions). Does anyone have an idea what the problem might be? What am I missing here?


View Complete Post

More Related Resource Links

Error when running an SQL script to restore a database on SQL Server 2008 R2 Express

Hi, At work, I use SQL Server 2008 Express running on Windows XP Professional Service Pack 3. At home, I also use SQL Server 2008 Express running on Windows XP Professional Service Pack 3. Whenever I do work on the database at work, I usually restore the database at home (or vice versa) I have been using the following SQL scripts to back up the database and to restore the same database (at work or at home). BackupRealEstate.sql USE Master BACKUP DATABASE [RealEstate] TO DISK = N'F:\My Documents\My Database\RealEstate.bak' WITH NOFORMAT, INIT, NAME = N'RealEstate-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO RestoreRealEstate.sql USE Master RESTORE DATABASE [RealEstate] FROM DISK = N'F:\My Documents\My Database\RealEstate.bak' WITH  FILE = 1, NOUNLOAD, REPLACE, STATS = 10 GO Everything worked fine until I replaced my 8yo home computer with a newer and faster one. My new home computer is running Windows 7 Home Premium and I use the latest SQL Server 2008 R2 Express. When I tried to run the above restore SQL script, I got the following error message: Msg 3101, Level 16, State 1, Line 2 Exclusive access could not be obtained because the database is in use. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. There was no other application/process using this database. Can someone please help me? Regards,

Generate Script for Database using WMI script

Hi All, I need to take "database script" or generate script for database by using scheduled job or by SSIS package.   Currently we are doing it manually by selecting "database" --> "Generate Script".  But i need to do it by scheduling a job say for every month and copy it in a particular folder. Is there any way to do it? if yes, what is the methodologies to accomplished it Thanks R Anand

Top 100 sql queries running on all database

hi This is sql server 2008, I am trying to find the top 100 queries on all database that has the high CPU usage. Trying to find the database, CPU usage and queries. I have something here, but does not show the query part. SELECT   [sql_handle]   ,[statement_start_offset]   ,[statement_end_offset]   ,[plan_generation_num]   ,[plan_handle]   ,[creation_time]   ,[last_execution_time]   ,[execution_count]   ,[snapshot_execution_count]   ,[total_worker_time]   ,[snapshot_worker_time]   ,[min_worker_time]   ,[max_worker_time]   ,[total_physical_reads]   ,[snapshot_physical_reads]   ,[min_physical_reads]   ,[max_physical_reads]   ,[total_logical_writes]   ,[snapshot_logical_writes]   ,[min_logical_writes]   ,[max_logical_writes]   ,[total_logical_reads]   ,[snapshot_logical_reads]   ,[min_logical_reads]   ,[max_logical_reads]   ,[total_clr_time]   ,[snapshot_clr_time]   ,[min_clr_time]   ,[max_clr_time]   ,[total_elapsed_time]   ,[snapshot_elapsed_time]   ,[min_elapsed_time]   ,[max_elapsed_time]   ,[collection_time]   ,[snapshot_id]   FROM [Data Collection].[snapshots].[query_stats]    

I need to create a script in SSIS which creates a data source that connects to an Access database.

I need to create a script in SSIS which creates a data source that connects to an Access database. The Access database file name needs to be set as a variable as it will change from month to month. I have no idea what I am doing can anybody give me some tips? Mr Shaw

Powerhsell script for MOSS 2007 to get content database size



Can you please provide me a script to get content database size in MOSS 2007.




Restore Database Backup using SQL Script


I need to backup a database using query.Please help me.

Database script generation


Dear All,

This post is basically a continuation of my earlier post titled "auto code generation" in this link http://forums.asp.net/t/1598255.aspx.

Now I want to know the possibilities of template based generation of database scripts in Oracle, Sql Server and postgres.

In other words what templates are available for generating PL/SQL procedures in Oracle, T-SQL procedures in SQL Server and PG-SQL/ OpenSQL functions in postgreSQL.

Thanks for your replies.

Deplying SQL Analysis service database using script problem with Impersonation


I have a problem with deploying SQL Analysis database from dev server to production , because I do not have an admin privileges , I cannot use Create script

The only action that I have , is to execute Alter Script against Any Empty database,


However I cannot use ImpersonateServiceAccount because it needs  Admin privileges

So, I always use ImpersonateCurrentUser as you know, I have to supply my credential.


are there any way that I can Deploy the db to the production  with the same impersonation type ImpersonateServiceAccount

as it was before?


thank you

SQL 2008 SP2 succefull but instance start fails - Script level upgrade for database 'master' faile


after installing SP2 instance starts and stops with error:

Script level upgrade for database 'master' failed because upgrade step 'sqlagent100_msdb_upgrade.sql' encountered error 5831, state 1, severity 16. This is a serious error condition which might interfere with regular operation and the database will be taken offline. If the error happened during upgrade of the 'master' database, it will prevent the entire SQL Server instance from starting. Examine the previous errorlog entries for errors, take the appropriate corrective actions and re-start the database so that the script upgrade steps run to completion.

Already tried without success:
- enabled SA account (was disabled) http://support.microsoft.com/kb/960781/en-us
- uninstalled SP2 -> other error, but same problems with running the script



script to check the database size



I work with Microsoft SQL Server Workgroup Edition 2008 and I would like to know if there is a script to check the database size.

I need something like ‘give databasename’ and return the database size.
Can I do this via a vbscript?

Background info:
Different departments one SQL Server.
Every department has one or more databases on the SQL Server.
Department managers are billed per GB in use.
Department managers require a script to check the database size without using Microsoft SQL Server Management Studio or any other console. Just a shortcut on their desktop.

Let me know if you need more information.
Thank you,

Script SQLServer - 2000 database


Hi Gurus,

I need your advice, how to go about the following task which i need to do

1) Create SQLSERVER 2000 database creation script. This will have script for all objects in database.

2) Update the header of each sql files based on the template provided.

for task 1 i can create the sql scripts using enterpirse console for sqlserver. Database have lot of indexes and table. how to find the logical sequence the sql scripts(with out hit and trial) ?

for task 2 do you know any documentaion utility  which can apply header template to each database object sql script ?




If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Rajender Saini Aditi Enterprise Solutions - Partnering Innovation | www.aditi.com

"Create failed for Database" when running under administrator account without built-in account.


- I am having an application which can install several instances of sql 2008 express on my pc, in built-in administrator windows account, everthing worked well, then i created more an account and grant administrator permission on this account,  but when i logged into this account, after logged on SQL management by Windows authtication mode, then attempt to create any database in SQL management studio, i got an exception as below:

TITLE: Microsoft SQL Server Management Studio

Create failed for Database 'test'.  (Microsoft.SqlServer.Smo)

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1447.4+((KJ_RTM).100213-0103+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Create+Database&LinkId=20476


An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)


SQL Server 2008 database script



I created a database in sqlserver2008 and then i created a script which comprised of Schema and Data both ,now i run that script in sql server 2005 but two tables are not getting create on 2005,although there is not anything new in both the tables which is not in 2005...Pls help 

Script Database Mail Setup


I have an application that relies on Database Mail for sending out alerts and notifications.

I have it working wonderfully... as long as I manually set up Database Mail.

How can I provide a script to take care of the details of setting up Database Mail?

Most of SQL Server Management Studio has a "script" button which will accept input, then generate a script to take the actions requested.  This doesn't exist for the "Configure Database Mail" Wizard.

Any pointers?

I was able to find "Database Mail Simple Configuration Template" in the template explorer.  My main question is how to set one of the profiles as the default profile

Simple Database connection check to seee if the database server is up and running using linq


I am using Linq

Before i start executing my query, want to check if the database connection is being established, i.e if the database server is up and running.

Incase database is down, display appropriate error message?

How can i do that?


Is there any script for daily how much my database growth rate increasing




s there any script for daily how much my database growth rate increasing


Plz tel me any advice hw can we trac growth rate of DB for daily basis

Restore of database backup done by script failed



In my C++ application (with MFC) developed in Visual Studio 2010 the user has an option to take a backup of the current database. This is done by executing a Stored Procedure which takes the database name and backup file name as parameters:

CREATE PROCEDURE sp2_backup_database @DATABASE nvarchar(100), @FILENAME nvarchar(260)
  Exec ('Backup Database [' + @DATABASE + '] TO  DISK = ''' + @FILENAME + '''
      '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10')
   Return @@ERROR

All this works fine, the backup is done properly by the SP.
What is bothering me is that when trying to make a restore of the database from the backup file the restore fails with a message like (freely translated from Swedish):

"Restore failed for [the SQL Server]. (Microsoft.SqlServer.SmoExtended)
Additional information:
System.Data.SqlClien.SqlError: RESTORE can not process the database [Database-name] since it's in use by this process. It's recommended to use master database during this task."

The only way I can

ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend