Dynamically Switching Databases In A Script

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


ADVERTISEMENT

Switching Databases In ADO.NET

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

How To Design Databases Dynamically?

Jul 14, 2007

Hi,
We are developing products to target thousands of clients.
Front-End : C#.Net 2005
Back-End : SQL-Server 2005

We started an Inventry control project (Product) with 10% percent of data as we didn't meet any clients (User requirements). I mean, I could not get complete user requirements. version by version, I need to change the database dynamically, and it should support backward compatiblity. While altering the database the existing primary keys are affecting. It seems I need to complete reconstruction of data Layer, Business Layer and Presentation Layer. How to avoid reconstructions in such scenerios? How database designers designing databases for a product like this? Or should I need to create "Table_Name_Serial_Number" (identity data type) column for each table and column to be primary key of that table?. Please help me!

Thanks in advance,
R. Pandiarajan.

View 2 Replies View Related

How To Dynamically Update Existing 50 Databases' Sp?

May 21, 2002

I have 50 MSDE SQL2k servers, each server has around 10 customer databases.
There are 5 stored procecures need to update to 50*10 = 500 databases.
These 5 stored procedures each has many 'Go' keywords and 4 of 5 with more than 8000 characters.
What might be the best way to loop execute them automatically, instead of
isql/w to each database connection to run the script?

I had bumpped by 'Go' keywords error and limitation of max varchar of 8000 error.
thanks for the help
David

View 4 Replies View Related

Help Switching To SQL Authentication

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

Xp_startmail And Switching Profiles

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

Switching Off Replication For A Restored DB

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

Switching Signs + And - In Matrix

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

Switching From Per Seat To Per Processor License

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

Switching In And Out Of Bulk Logged Mode

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

Switching Compatability Mode From 6.5 To 2000

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

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 View Related

Wrong Connection When Switching Computer

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

SSRS Configure Error While Switching Panels

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

Switching 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,

View 1 Replies View Related

Problem With Context Switching Feature In SQL 2005

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€™, I€™m 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

SQL 2005, OPENQUERY And Security Context Switching

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

Switching Excel Worksheets Based On Date

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

Switching Database's And Retrieving Table, Etc Info

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

Multiple Tables - Loop Through Query Switching Table Name?

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

Transact SQL :: Check Constraint On Switching In Table Not Working?

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

Switching Schema Ownership Of Views, Stored Procedures, Tables

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

Transact SQL :: Fast Data Loading With Partition Switching Strategy

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

SQL Server 2012 :: Fast Data Loading With Partition Switching Strategy

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

Recovery :: AlwaysOn Availability Groups Server Logins Fails When Switching Between Nodes

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

Switching SQL 2005 Authentication Mode From Windows To SQL Authentication

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

Log Shipping - Switching Recovery Model In Log Shipping

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

Linking Tables From Different Databases Or Querying From Multiple Databases

Dec 10, 2007

Dear 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. 
 

View 3 Replies View Related

Switching From SQL Authentication To Integrated Authentication?

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

Need Help Switching From MS Access Database To SQL Database

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

Service Broker Not Working For Restored Databases (SQL 2000 Databases Restored On 2005)

Jan 24, 2006

I 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

View 3 Replies View Related

Copying Databases Between Databases

Feb 8, 2000

hi 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

View 1 Replies View Related

How To Put Table Name At Dynamically ???

Oct 14, 2006

Hi,I have 30 Table with same structure but it will accept different value at RunTime according to AirCraftType. I wand to create a stored prosedure that will accept TableName, FlightDate, FlightNo. whenever i wnat to execute this code in the database I received error :Must declare the variable '@TableName'create proc InsertDataForAirBus330 @FlightNo varchar(5),@TableName varchr(5),@FlightDate datetime
as
insert into @TableName values(@JourneyDate,@FlightNo,18,42,280,3,7,35)plz suggest me how to solve this pboblem...Jasim...
 

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved