SQL 2012 :: Switching From One Database To Another Within The Same Script?
Jan 28, 2015
I would like to know how I can switch from one database to another within the same script. I have a script that reads the header information from a SQL BAK file and loads the information into a Test database. Once the information is in the temp table (Test database) I run the following script to get the database name.
This part works fine.
INSERT INTO @HeaderInfo EXEC('RESTORE HEADERONLY
FROM DISK = N''I:TESTdatabase.bak''
WITH NOUNLOAD')
DECLARE @databasename varchar(128);
SET @databasename = (SELECT DatabaseName FROM @HeaderInfo);
The problem is when I try to run the following script nothing happens. The new database is never selected and the script is still on the test database.
EXEC ('USE '+ @databasename)
The goal is switch to the new database (USE NewDatabase) so that the other part of my script (DBCC CHECKDB) can run. This script checks the integrity of the database and saves the results to a temp table.
What am I doing wrong?
View 2 Replies
ADVERTISEMENT
Jul 28, 2015
I’m looking for clearity on partition switching. The idea is to use many BULK INSERT statements into table dbo.X_n in parallel and when BULK INSERT for table dbo.X_n is completed, switch dbo.X_n into dbo.bigdaddy. I think this is the fastest way to upload a couple hundred GB of data.
In learning about partition switching (in part) from The Data Loading Performance Guide under Partition SWITCH, I hear the instructions to say copy the main table exactly to become a target. But in that same step (#1), I read that we need to change the default file group of the target (dbo.X_n) from the default file group. Then it says I need to match indexes and lists the filegroup as something we need to match with the main table.
As an overview of the partition switching strategy, I think the whole point of BULK INSERT with partitioning is to have seperate files (in same group) to enable concurrent uploading where each table has its own file. Once the upload is completed to a table (dbo.X_n) then we do the partition switch into the main table (dbo.bigdaddy). The data we just uploaded doesn’t actually move, just the metadata for it.
“Don’t have the same filegroup on your target as the main table. You must have the same filegroup on your target as the main table.”
View 1 Replies
View Related
Jul 8, 2007
I have a query that gets the tables, columns, indexes, index_columns for all tables in a data base.
But, I need to be able to select any data base on the server
and then drill down into the tables, columns, etc.
I looked at sys.databases but can't see how to relate that to the
sys.tables to extend my query.
Is there a way?
I've looked at the information_schema area and catalog views
but I don't see a solution yet.
View 7 Replies
View Related
Sep 16, 2006
I recently created a form to MS access database web application and it worked fine on my local cpu. When I published the same web app to my hosting server the program wouldn't work but I couldn't understand. Well, after researching the matter I found out my hosting server doesn't allow ASP.NET applications that use the aspcompat page directive because of security trust levels. So, I decided to switch to a SQL database but I can't even test the web application on my local cpu because I get this error "An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)". So, once again I researched this error on the internet and found these two websites with incite to what is causing the error. http://support.microsoft.com/default.aspx?scid=kb;EN-US;914277http://www.cryer.co.uk/brian/sqlserver/hresult_0x2%20npp_error_establishing_connection.htmI tried all the remedies suggested except for the cmd procedure in the second lind because I don't understand how to run that command in sqlcmd from start>run. Anyway, I can't get my web application to work and I wanted to know if someone can assist me. I use the following programs:MS Visual Web Developer 2005 Express EditionMS SQL Server Express 2005 Here is my code:sqlForm.aspx<%@ Page Language="VB" Debug="true"%><%@ Import Namespace="System.Data" %><%@ Import Namespace="System.Data.SqlClient" %><!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><script runat="server">Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim connContacts As SqlConnectionDim strInsert As StringDim cmdInsert As SqlCommandconnContacts = New SqlConnection("Server=MSSQL$SQLEXPRESS;database=contacts")strInsert = "Insert users ( name, email, comments ) Values ( @name, @email, @comments )"cmdInsert = New SqlCommand(strInsert, connContacts)cmdInsert.Parameters.AddWithValue("@name", txtName.Text)cmdInsert.Parameters.AddWithValue("@email", txtEmail.Text) cmdInsert.Parameters.AddWithValue("@comments", txtComments.Text)connContacts.Open()cmdInsert.ExecuteNonQuery() Response.Write("The form information was inserted successfully.") connContacts.Close() txtName.Text = ""txtEmail.Text = ""txtComments.Text = ""End Sub</script><html xmlns="http://www.w3.org/1999/xhtml" ><head runat="server"><title>Untitled Page</title><style type="text/css">body {background-color:aqua}</style></head><body><form id="form1" runat="server"><div><asp:Label ID="Label1" runat="server" Text="Name:" Width="120px" ForeColor="#FFFFFF"></asp:Label><asp:TextBox ID="txtName" runat="server" Width="200px"></asp:TextBox><br /><asp:Label ID="Label2" runat="server" Text="Email:" Width="120px" ForeColor="#FFFFFF"></asp:Label><asp:TextBox ID="txtEmail" runat="server" Width="200px"></asp:TextBox><br /><asp:Label ID="Label3" runat="server" Text="Comments:" Width="120px" ForeColor="#FFFFFF"></asp:Label><asp:TextBox ID="txtComments" runat="server" TextMode="MultiLine" Width="200px"></asp:TextBox><br /><asp:Button ID="Button1" runat="server" Text="Submit" Width="110px" OnClick="Button1_Click" /></div></form></body></html>Please help! Note: I'm trying to test on my local cpu first with the intention of publishing to my hosting server.
View 6 Replies
View Related
Apr 1, 2006
At the moment my asp.net app is working ok and I can connect to the database using windows authentication, however i'm trying to use sql authentication on my local computer.i'm using sql server studio at the moment to manage the database. And tried numerious combinations of things to try and get it working and allow me to connect to the database using SQL authentication but still no luck :(can anyone give me some rough step by step instructions to setting up the sql username and password for forms authentication and activating it for a certain database?thanks
View 1 Replies
View Related
Oct 9, 2006
Hi
If I have a connection open which is in database master, can I switch to another database within that connection without having to close the connection and recreating it?
Thanks
Steve
View 1 Replies
View Related
May 13, 2002
Guys!
I'm using SQL Mail to send out Emails to Customers re: outstanding Invoices. I also use SQL Mail on the same box to send out emails to our Sales Reps with summaries of outstanding invoices. Now, I've set up multiple outlook profiles on the server. I tried using
xp_startmail @user = 'CreditControlDept'
xp_sendmail .... (to customers)
xp_stopmail @user = 'CreditControlDept'
xp_startmail @user = 'InvoiceTracker'
xp_sendmail...(to internal sales reps)
xp_stopmail @user = 'InvoiceTracker'
DOES NOT WORK!!! the profile does'nt switch at all. SQL Mail sends out all the emails using the profile that it started with. How can I use xp_startmail and xp_stopmail to switch between different user profiles.
Regards
Uday
View 1 Replies
View Related
Oct 19, 2000
Hi
If I dump a replicated Db and restore it onto another DB, how do I switch off replication from the restored DB. I believe there were some scripts developed for this in 6.5.
Any suggestions will be appreciated.
Rich
View 1 Replies
View Related
Apr 10, 2008
Could anyone help me to find out how to switch signs of "+" and "-" in matrix drilldown?
In columns drilldown i have wright (that means "+" for drilldown and "-" for drillup)
In rows I have it conversely, don't know why and don't where I can change this.
Thank you!
View 2 Replies
View Related
Aug 6, 2004
We have an active database that sits on SQL Server 2000 that currently has per seat license...but now want to switch to per processor.
If I go to ControlSQL Server 2000 License Set up...it doe not give me the option to choose per processor.
Do I need to un-install/re-install SQL Server 2000 and choose the per processor choice and then re-attach our database ?
It seems like a lot of work..please advise.
Thank you
View 7 Replies
View Related
Jul 22, 2004
I sometimes have problems when running index rebuilds due to the tran log growing too big. MS suggest switching the db from full recovery mode to bulk-logged mode fur the duration of the rebuild. Question is, when you switch a db from full recovery to bulk logged, does that invalidate the tran log backups up to that point? When the db is switched back into full-recovery mode, are the following tran log backups still in sequence?
Clive
View 2 Replies
View Related
Jan 9, 2006
Do you know of any resources in terms of Transact SQL in order to migrate from 6.5 to 2000?
View 1 Replies
View Related
Jan 5, 2007
Hi
My C# application has a DataGridView which is bound to a DataSet. I used Visual Studio to add/configure the data source. Everything works fine on my computer. When I try to run the application on another computer I can't connect to the database, because it uses a wrong connection string.
How can I make a specific connection/connection string permanent for a specific application?
Thanks.
View 6 Replies
View Related
May 2, 2006
I've got a situation where I need to execute portions of a script against every database on a given instance. I don't know the name of all the databases beforehand so I need to scroll through them all and call the "use" command appropriately.
I need the correct syntax, the following won't work:
DECLARE DBS CURSOR FOR
SELECT dbname
FROM #helpdb
ORDER BY dbname
OPEN DBS
FETCH NEXT
FROM DBS
INTO
@dbname
WHILE @@FETCH_STATUS = 0
BEGIN
USE @dbname
The last line - the "USE" statement - is invalid. The following for example works:
USE master
But when supplied a declared variable a syntax error results for the use command because it expects an identifier.
So .. what is the correct syntax to pass a declared parameter to "USE", or is there another way to meet this requirement?
Thanks for your time.
View 6 Replies
View Related
Nov 6, 2013
i want to upload reports in ssrs , when i trying to configure reporting service configuration manager, getting error" there was an error while switching panels. The most likely cause is an error retrieving wmi properties. the exception details are access is denied . You must have an administrator on the report server computer to use this tool".
View 2 Replies
View Related
Apr 29, 2008
It is simple question, just slipped out of my mind at this time...... how do we change Security Context for 2005 version from network to local system.
thanks,
View 1 Replies
View Related
Jan 26, 2007
Hi,
One of our client application launches a execuatable on server by calling master..xp_cmdshell. We are migrating to SQL 2005 and I would like to use the Context Switching feature
We have sql login client? which is used by clinet application and has very least privelge and have only required access to requried database and tables.
xp_cmdshell is disabled by default on our server and I would like to enable xp_cmdshell, launch the executable on the server and disable xp_cmdshell.I have created another SQL Login Bob? and is member of sysadmin. The below SP is created by logging in as Bob?. My objective is to grant execute access on this SP and grant impersonate access to sql login client? . So that client application connecting to SQL server can launch the executable by calling the SP
create proc Bob.[Bob_enable_xp_cmdshell]
with execute as 'Bob'
as
EXEC sp_configure N'show advanced options', N'1' RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'xp_cmdshell', N'1'
RECONFIGURE WITH OVERRIDE
EXEC sp_configure N'show advanced options', N'0' RECONFIGURE WITH OVERRIDE
-- launch the application using xp_cmdshell and disable the xp_command shell
When I try to execute (exec [Bob].[jtr_enable_xp_cmdshell]
) the above SP by logging in as Bob, Im getting the below error
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 4
You do not have permission to run the RECONFIGURE statement.
Msg 15123, Level 16, State 1, Procedure sp_configure, Line 51
The configuration option 'xp_cmdshell' does not exist, or it may be an advanced option.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 6
You do not have permission to run the RECONFIGURE statement.
Msg 15247, Level 16, State 1, Procedure sp_configure, Line 94
User does not have permission to perform this action.
Msg 5812, Level 14, State 1, Procedure jtr_enable_xp_cmdshell, Line 7
You do not have permission to run the RECONFIGURE statement.
Please note the SQL Login Bob? is a member of sysadmin fixed role. Thank in advance for you help.
With regards
GK
View 10 Replies
View Related
Oct 4, 2007
Hi,
I need to allow only a specific group of users to use a linked server. Rather than granting each user access in the linked server I create and give access to a local SQL login "link_user" and then grant the group impersonate to "link_user". The folllowing statements then work fine:
EXECUTE AS LOGIN = 'link_user'
SELECT * FROM OPENQUERY(linked_server, 'SELECT * FROM ...)
But when I use the exact same statements in a stored procedure I get the folllowing error:
Msg 7416, Level 16, State 1, Procedure linktest2, Line 5
Access to the remote server is denied because no login-mapping exists.
I need to be able to work with the result set which is why I use OPENQUERY rather than EXECUTE... AS... AT which works fine also in stored procedures!?
Is there another workaround then to create all users in the linked server security?
Rgds
Bertrand
View 1 Replies
View Related
Feb 25, 2007
Hello,
I need to pull data from an Excel Workbook (DTS Excel Source) where the data is stored in worksheets that are each named one of the twelve months of the year. The package is fired by a job that runs once a month. Currently, I have to go into the select statement of the Excel source before the process runs and change the name of the Worksheet that the data is pulled from.
How can I set up the sql statement for the Excel source so that I can pull from the Worksheet that contains the name of the current month?
Thank you for your help!
CSDunn
View 2 Replies
View Related
Mar 8, 2012
I would like to run the same query on multiple tables. So say I have a list of tables
@tableList = a|b|c|d
And then I have my query looping through the tables
for (@table in tableList)
{
update from @table
set = ''
}
Is there a simple way to do this in an mssql query, if so how do I get to loop through the query switching the table name?
View 4 Replies
View Related
Jul 7, 2015
I have a table which has been partitioned on BIGINT column.
Partitioned_Table (ID BIGINT, Name VARCHAR(10), Gender VARCHAR(2))
I have a left range partition function on ID column.
CREATE PARTITION FUNCTION Partition_Function ( BIGINT )
AS RANGE LEFT
FOR VALUES ( '20150601000', '20150602000', '20150603000' );
That means the first partition is ID >= 20150601000 to ID < 20150602000.
I have to switch in a table into an empty partition.
Switching_In_Table(ID BIGINT, Name VARCHAR(10), Gender VARCHAR(2))
Before the switch in, I am creating a CHECK constraint on Switching_In_Table CHECK(ID LIKE '20150625%') Can I use like clause in this scenario?
View 6 Replies
View Related
Feb 20, 2007
Hi all--I used the following type of script to migrate tables, views and stored procedures from 'dbo' to 'hqmsuser':
alter authorization on dbo.PK_T_PQ_MASTER to HQMSUSER
alter schema HQMSUSER transfer dbo.PK_T_PQ_MASTER
go
This was per recommendations from the BOL to use instead of the 'sp_changeobjectowner' procedure. I returned the following error:
Msg 15346, Level 16, State 1, Line 3
Cannot change owner for an object that is owned by a parent object. Change the owner of the parent object instead.
Msg 15347, Level 16, State 1, Line 4
Cannot transfer an object that is owned by a parent object.
How do I find and change the parent object on this from 'dbo' to a non-dbo schema ownership?
View 1 Replies
View Related
Jul 28, 2015
I’m looking for clearity on partition switching. The idea is to use many BULK INSERT statements into table dbo.X_n in parallel and when BULK INSERT for table dbo.X_n is completed, switch dbo.X_n into dbo.bigdaddy. I think this is the fastest way to upload a couple hundred GB of data.
In learning about partition switching (in part) from The Data Loading Performance Guide under Partition SWITCH, I hear the instructions to say copy the main table exactly to become a target. But in that same step (#1), I read that we need to change the default file group of the target (dbo.X_n) from the default file group. Then it says I need to match indexes and lists the filegroup as something we need to match with the main table.
As an overview of the partition switching strategy, I think the whole point of BULK INSERT with partitioning is to have seperate files (in same group) to enable concurrent uploading where each table has its own file. Once the upload is completed to a table (dbo.X_n) then we do the partition switch into the main table (dbo.bigdaddy). The data we just uploaded doesn’t actually move, just the metadata for it.
When I read the instructions linked above, I hear “Don’t have the same filegroup on your target as the main table. You must have the same filegroup on your target as the main table.”
Where am I disconnected?
View 5 Replies
View Related
Feb 13, 2015
I have several users with an unusual problem with SSMS 2012. When they attempt to connect to a database using the "Connect to Server" dialog box, the connection just hangs. Sometimes after about 15 minutes the connection will be successful. Other attempts simply spin seemingly endlessly. Users experiencing this issue are both running SSMS 2012 on Win 7 Pro (64 bit). The following troubleshooting steps have been tried:
1. When the user runs SSMS "As Administrator" the connections work almost instantly. (Elevating privileges is not a solution in our environment)
2. Wireshark shows that SSMS does not try to hit the SQL server when the user experiencing this issue clicks connect.
3. I can create a new virgin user on the PC and that login experiences the same problem.
4. A complete rip and re-install of SSMS 2012 does not resolve the issue.
View 3 Replies
View Related
Jul 9, 2015
I am setting up a 3 node cluster as part of an availability group.Initially I tested failover between nodes using SQL Management Studio and everything failed over successfully when I stopped a node, and I was still able to write queries. I started to test with an application which connects using an SQL user and whenever I would switch nodes, I would get login failed. I believe the cause of this issue is because the server logins SID's which are tied to the database are different than the server logins on the other nodes which resulted in login failed.how can I ensure that the server logins SID's are the same between nodes? Is there a way of copying this over or how is this supposed to be done? I read a little about contained databases where I could just set the login on the database itself vs. creating a server login but I would rather not go down that route.
View 4 Replies
View Related
Dec 2, 2014
I have just finished configuring my first test mirrored environment (High safety mode). I setup the database engine service accounts on each of the servers with domainuser. I inherited a production mirrored environment set up by someone else. On the production servers the database engine service account is NT Authorityuser a local account. I am trying to practice installing Windows updates within a mirrored environment and I not sure how to proceed when the service account is NT Authority user account. should I change the service account to a domainuser?
View 2 Replies
View Related
Apr 18, 2007
Hi there,I have installed MS SQL Server 2005 on my machine with windows authentication. But now I want to switch the authentication mode to SQL Authentication. I am unable to switch, I can’t find the proper way to do so here in 2005.Could any one help me in doing this?Thank you,-Ahsan
View 1 Replies
View Related
Jun 11, 2014
I would like to do partitioning one of my client, but that primary database is participating in log shipping.
If I do partitioning on primary, automatically apply those changes on secondary db? if not, what is the way?
Note: I am adding new filegroups and ndf file in different drives on primary database.
View 2 Replies
View Related
Jul 9, 2014
Last week I backed up my SQL Server by using BE 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.what causes this?
View 7 Replies
View Related
May 13, 2007
Hi
I could not able to find Forums in regards to 'Log Shipping' thats why posting this question in here. Appriciate if someone can provide me answers depends on their experience.
Can we switch database recovery model when log shipping is turned on ?
We want to switch from Full Recovery to Bulk Logged Recovery to make sure Bulk Insert operations during the after hours load process will have some performance gain.
Is there any possibility of loosing data ?
Thanks
View 1 Replies
View Related
Jul 29, 2014
Sometime during the night last night some user account permissions were "lost". Am I right to think that restoring the master database would be the way to go? We have a 2 node 2012 cluster and I stop the cluster resource and start the db in single user mode from the active node. Somehow the sharepoint farm is still trying to connect so I can't get logged in single user. What method could I use to stop users from connecting when I don't have access to the sharepoint farm.
View 8 Replies
View Related
Aug 25, 2006
Hi,I'm using SQL Server 2005. My Connection String looks like that at the moment: <add name="LocalSqlServer" connectionString="Data Source=xx;Initial Catalog=xx;Persist Security Info=True;User ID=xx;Password=xx" providerName="System.Data.SqlClient"/> Now I'd like to change this kind of authentication to Integrated Windows AuthenticationI added the WorkerProcess IIS_WPG to the permitted Users but it didn't help.Changed the Connection String to this:connectionString="Server=xx;Database=xx;Trusted_Connection=True;"All I'm getting is that my NetworkService is not permitted to access DB when I try to connect to the DB in ASP.NET.How can I properly configure that? Thanks!
View 4 Replies
View Related
Apr 3, 2015
Our auditors are trying to enforce a requirement that all users be disconnected from any application or database after 15 minutes of inactivity. Is there any way to force a logoff within SQL Server? For example, someone is in Management Studio connected to a database. They should be logged off after 15 minutes of no activity.
View 1 Replies
View Related
