Switching Databases In ADO.NET
Oct 9, 2006Hi
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
ADVERTISEMENT
Dynamically Switching Databases In A Script
May 2, 2006I'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.
Help Switching To SQL Authentication
Apr 1, 2006At 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 RelatedXp_startmail And Switching Profiles
May 13, 2002Guys!
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
Switching Off Replication For A Restored DB
Oct 19, 2000Hi
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
Switching Signs + And - In Matrix
Apr 10, 2008Could 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!
Switching From Per Seat To Per Processor License
Aug 6, 2004We 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
Switching In And Out Of Bulk Logged Mode
Jul 22, 2004I 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
Switching Compatability Mode From 6.5 To 2000
Jan 9, 2006Do you know of any resources in terms of Transact SQL in order to migrate from 6.5 to 2000?
View 1 Replies View RelatedSQL 2012 :: Switching From One Database To Another Within The Same Script?
Jan 28, 2015I 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?
Wrong Connection When Switching Computer
Jan 5, 2007Hi
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.
SSRS Configure Error While Switching Panels
Nov 6, 2013i 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 RelatedSwitching Between Network Service And Local System....!
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,
Problem With Context Switching Feature In SQL 2005
Jan 26, 2007Hi,
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
SQL 2005, OPENQUERY And Security Context Switching
Oct 4, 2007Hi,
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
Switching Excel Worksheets Based On Date
Feb 25, 2007Hello,
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
Switching Database's And Retrieving Table, Etc Info
Jul 8, 2007I 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.
Multiple Tables - Loop Through Query Switching Table Name?
Mar 8, 2012I 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?
Transact SQL :: Check Constraint On Switching In Table Not Working?
Jul 7, 2015I 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?
Switching Schema Ownership Of Views, Stored Procedures, Tables
Feb 20, 2007Hi 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?
Transact SQL :: Fast Data Loading With Partition Switching Strategy
Jul 28, 2015Iâ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?
SQL Server 2012 :: Fast Data Loading With Partition Switching Strategy
Jul 28, 2015Iâ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.â
Recovery :: AlwaysOn Availability Groups Server Logins Fails When Switching Between Nodes
Jul 9, 2015I 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 RelatedSwitching SQL 2005 Authentication Mode From Windows To SQL Authentication
Apr 18, 2007Hi 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 RelatedLog Shipping - Switching Recovery Model In Log Shipping
May 13, 2007Hi
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
Linking Tables From Different Databases Or Querying From Multiple Databases
Dec 10, 2007Dear Readers,Is it possible, like in Access, to link to tables in other SQL databases that are on the same server? I have a query that I originally had in Access that queered from multiply databases. It did this by having those other tables in the other databases linked to the database that had the query.
Switching From SQL Authentication To Integrated Authentication?
Aug 25, 2006Hi,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 RelatedNeed Help Switching From MS Access Database To SQL Database
Sep 16, 2006I 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 RelatedService Broker Not Working For Restored Databases (SQL 2000 Databases Restored On 2005)
Jan 24, 2006I just restored my SQL server 2000 database on the SQL server 2005. after this i ran the Service broker sample ("Hello World") on this database by changing the AdventureWorks name to the new database name. The "setup.sql" runs fine. When i run the "SendMessage.sql" i was not getting any rows in the output (The message was not getting inserted into the queue). I checked the Service broker is enabled on this databased using the query "select is_broker_enabled from sys.databases where name = 'newdbname' " It was 1. I even tried the ALTER DATABASE SET ENABLE_BROKER. but it didnt work.
When i tried the sample on a newly created database it worked fine.
Is there any solution to make the restored database to work for service broker.
Thanks
Prashanth
Copying Databases Between Databases
Feb 8, 2000hi from France !!!
i would like how to duplicate a database to another server with all datas, constraints, keys, indexes...
should i use sp_attach_db, dts, backup/restore, sql scripts... ???
thanks to all, nico
Connect From Databases In 1st Instance To Databases In 2nd Instance
Mar 13, 2007This is the scenario. Is it possible to create views or something (like Oracle DB Links) in a database in Instance 1 which can show data from another database in 2nd Instance ?
I want to do this to create reports.
Two Databases
Sep 24, 2006I have created a site which now has two databases. Firstly is the ASPNETDB.MDF which I beleives get set to manage the security, logon/Roles that I have on my site. The second dataabase "MyDatabase" is one that I have created for all my data withing the site. I would like to combine this to one Database for simplicity and cost. Now I can't see the ASPNETDB.MDF when I use SQL Manager 2005 and I can't see how to change the set up of ASP.Net Configuration if I was to just use mydatabase. Any suggestions
View 1 Replies View RelatedTwo Databases?
Sep 25, 2006Hi, I have my site set up, but have had difficulty using the data gathered by the aspnet.mdf. I wanted to use the email and username columns from the membership table to send newsletters automatically from the database(but that's a whole other post)I also have my own databse, which will be populated by information gathered from the website, so is currently empty. I'd like to take this opportunity to say that i don't think there's enough information given on how to populate a blank database from a website application. Most tuorials etc are concentrated on retrieving and displaying data, which is all well and good, once you actually have data to play with!I don't need two databases, so can I add tables to the aspnet.mdf ? But then what about relationships etc? I won't damage the current relationships by adding new ones? On the other hand can i copy the aspnet.mdf into my own database, and then assign the relevant permissions to that db? Any help would be greatly appreciated-in language for newbies please! I am relatively new to .asp.net/Visual Basic/ and SQL. It's a steep learning curve!Also....I have used create user wizard, login, password recovery etc within my site, the data is inserted into the correct db tables-without using an sql datasource for some reason...? is that right?I have also created a wizard to collect some more data (because i couldn't work out how to insert the data from the extra steps in create user wizard, into my own database) but when i click submit the data is not inserting into the specified table - (yes the connection string is fine, insert and update has been selected from the advance tab in connections wizard). Would this/could this be because i have assigned a 'finishbutton url'? should i use a Response.Redirect("blahblah.aspx") instead?
View 4 Replies View Related