SQL 2012 :: Transfer All Jobs From One Instance To Another By Using Data Tools - SMO Connection?
Feb 12, 2014
I am trying to transfer all jobs from one instance to another by using data tools. However, once i tried to make smo connection i am getting this error;
A network-related or instance-specific error occurred while establishing a connection to SQL Server.
In order to solve this issue i have tried these solutions;
1. SQL Server should be up and running. (OK)
2. Enable TCP/IP in SQL Server Configuration (OK)
3. Open Port in Windows Firewall (FW ACCEPTS ALL LOCAL PORTS)
4. Enable Remote Connection (CHECK OUT THE sp_configure SETTINGS, i even right-click instance then see from properties )
5. Enable SQL Server Browser Service (sql server browser has been restarted)
6. Create exception of sqlbrowser.exe in Firewall (FW ACCEPTS ALL PROGRAMS)
7. I tried windows and sql authentication which has sysadmin role
8. INSTANCE name is also chekced millions of times
View 0 Replies
ADVERTISEMENT
May 26, 2015
SQL Server 2012 Data Tools was working fine for me but something must've changed, now every time I try to create a new SSIS project I get:
The server threw an exception. (Exception from HRESULT: 0x80010105 (RPC_E_SERVERFAULT)).
When I try to open an existing project I get:
exception has been thrown by the target of an invocation
external component has thrown an exception (SSISUpgrade)
The issue seems to only arise with SSIS projects.I have already uninstalled SQL Server 2012 and reinstalled it and that didn't work.I tried to install Visual Studio 2012 Data Tools with BI and that also crashes when I try to create an SSIS project.Output of SQL Server SELECT @@VERSION is:
Microsoft SQL Server 2012 - 11.0.2100.60 (X64)
Feb 10 2012 19:39:15
Copyright (c) Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: ) (Hypervisor)
SQL Data Tools page info:
Microsoft SQL Server Integration Services Designer
Version 11.0.2100.60
Microsoft Visual Studio 2010
Version 10.0.40219.1 SP1Rel
Microsoft .NET Framework
Version 4.5.51641 SP1Rel
View 5 Replies
View Related
Mar 25, 2014
Am customizing SQL server MGMT tools 2012 for Mass deployment.Client had asked to remove Customer Feedback option from help menu.how to disable that.
View 6 Replies
View Related
Jan 20, 2015
I have a bunch of packages in msdb. Is there any easier way to move those packages from msdb to DataTools/BIDS, instead of adding one package at a time using ADD option.
View 9 Replies
View Related
Mar 19, 2014
In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?
I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?
I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?
View 6 Replies
View Related
Sep 1, 2014
I am a complete newbie to SSIS. I can create a simple package to transfer data between SQL instances and thats about it.
I have tableA (source data) and tableB (Destination data). TableA has 4 column and tableB has 5. I want to transfer all of the columns from tableA into TableB, but the 5th column in tableB needs to be populated with the ServerInstance name of the server TableA sits on. Do I need to have multiple data sources to achieve this? I have tried but no matter how I set it up, the Column in the destination is set to ignore.
View 2 Replies
View Related
Sep 11, 2003
Hi,
Is there any way that I can script out all the jobs at a time (instead of doing one by one). I have mor than 100 jobs to transfer, I was just wondering if there were a way to do all at once.
Thanks.
View 6 Replies
View Related
Sep 17, 2015
I am trying to create a Datazen query against a Microsoft SharePoint List. The Data View successfully returns records from the list; however, it appears that the Query String parameter is entirely ignored. I want to use the query string to grab only records with a certain status value.
So far I have attempted specifying a View and Filters:
?View={B9FF1729-AB8E-4C55-B17C-C660480334BB}&FilterField1=Status&FilterValue1=Closed&FilterField2=Request%5Fx0020%5FType&FilterValue2=Project
Just the filters:
?FilterField1=Status&FilterValue1=Closed&FilterField2=Request%5Fx0020%5FType&FilterValue2=Project
And even:
?ThisWillDoNothing
All return the complete set of records in the list. I do not see a single example or comment online other than the claim that SharePoint List sources are supported. How I can specify a Query String value that will filter the list records?
View 2 Replies
View Related
Apr 13, 2015
I got this situation where my network admin observerd that there is a high network utilization between 2 nodes in our AG (the primary node & the DR site, 2 separate locations of course); then he advised to compress the data transfer between those 2 nodes as the previous DBA already did that before!
Ok, I have no clue about this, so decided to google it, got nothing. My backup is already compressed through some third party app (just in case if that matters to the subject).
View 3 Replies
View Related
Dec 11, 2006
I created a database that will be distributed to my customers. This database is running on an Instance of SQL Server 2005 Express edition. I removed the admin logins from my SQL Server Instance so in theory, only my application connecting itself using the Sql Server autenthication will be able to be access the data (using "sa" having a password that I set at the installation).
For now, all this is working fine and after some tests, I haven't been able to access the data in any ways except by using the "sa" and the password my app is the only one to know.
But the problem is coming from a security leak when using User Instance. Indeed, I've been able to create a program getting the content from my MDF file. If somebody try to get connected using User Instance on his own SQL Server instance, he will be able to reach the data.
How could I prevent this to happend? Is there a property or something that could be set into the database that would prevent the database (mdf file) to be used with User Instance?
Thanks!
View 5 Replies
View Related
Feb 11, 2003
Hello,
I want to move a db from a SQL server 7 to SQL Server 2K. I'll do a backup and restore function to move the database, but how do I handle the objects mentioned in the header ?
Bothe servers (old and new) will have the same name, as well as the DB
All help is appreciated !
Thanks
View 2 Replies
View Related
Apr 25, 2002
We've upgraded our SQL7.0 user db to a SQL2000 system using sp_detach_db and sp_attach_db stored procedures.
And we'd like to transfer our sched jobs from a SQL 7.0 to SQL2000 systems,
if the jobs are created outside a maintenance plan, we can script them out and run the script to recreate them on the 2000 system.
But if the jobs are within a maintenance plan, we would have to recreate these plans manually on the 2000 system, is this the only option we have ?
Any one has a better solution ?
I think "restore msdb from abackup" should work better in this case?
In SQL2000, there is a new DTS task for transferring jobs between 2000 systems (same version), but I don't think it work to transfer them from 7.0 to 2000 ??
Anthony
View 3 Replies
View Related
Feb 12, 2008
Hello,
I am currently running sql 2005 express on a W2k3 server, 4G ram. I would also like to install a named instance of SQL 2005 Developer Edition on this machine. Is this ok to do? I am assuming that I can access all of the instances through the SQL Server Management Studio, is that correct?
Thanks
Leo
View 3 Replies
View Related
Mar 20, 2014
I have a 3 node cluster on which I have installed SSAS as it's own insntance. I have created this as a named instance and can connect to it by serverinstance if I'm on the server itself. However from my desktop I get the error saying instance was not found on server name.
I have defined an alternate port and setup firewall rules and can connect via server:port but not serverinstance. Prior to making this change SSAS was running on default port of 2383 and I could connect just by servername.
I have read many articles for previous versions saying that clustered SSAS will always use 2383 and that you must connect just using servername. However and this is were it gets strange. I have a 2 node UAT cluster with SSAS setup exactly the same way I've described above and I can connect from my desktop as serverinstance.
Should I be able to connect as serverinstances for a named clustered instance in 2012 ?
View 4 Replies
View Related
Feb 13, 2015
in reference to both MSSQL 2k8R2 and 2012, if i have a named instance AND a default instance on the same installation, and i create agent jobs (backups, indexing, etc) on either one, those same jobs appear on the "other" instance without having actually created them while logged IN to that instance.
Is this a normal feature of MSSQL? Doesn't seem like it should be happening that way.
View 8 Replies
View Related
Feb 18, 2013
It is possible to create Analysis Services project (*.dwproj) in Visual Studio 2012 Data Tools?
View 5 Replies
View Related
Jun 19, 2015
I have trying to access a 2nd SQL Server instances running on my WHS 2011 server from my Windows 8.1 Pro client.
The first SQL Server instance I installed is SQL Server 2014 Developer. This has been in place for many months and access from my client pc is not a problem. The Developer instance was installed using standard settings so uses TCP port 1433 and I have verified this by checking the firewall rules. This instance name is <SERVERNAME>
As I want to host a personal website on my server I installed SQL Server 2014 Express as a 2nd instance on my WHS 2011 box for website database purposes. After this installation both instances are accessible via Management Console on the WHS 2011 box.
The second instance is named as <SERVERNAME>SQLEXPRESS.
However, I am unable to access the SQLEXPRESS instance using Management Console on my client pc. I know it is a firewall issue as when I turn off the firewall on the WHS 2011 box I can connect successfully. The errors reported have resulted in my trying many things to resolve the problem but none have worked, e.g.
Use the default SQLEXPRESS instance to listen on all TCP ports (default installation option). I checked the necessary firewall rules were set up correctly and they were, i.e. UDP ANY and TCP ANY for the SQLEXPRESS sqlservr.exe file.
Change the default SQLEXPRESS instance to list on a specific TCP port, e.g. 19344
Create an ALIAS for the SQLEXPRESS installation
The instance is definitely set up for Remote Access and is not Hidden.
Are there any other firewall rules I need to apply?
View 2 Replies
View Related
Jan 12, 2015
Was wondering if there was a best practice minimum permissions for creating a SQL login to use when setting up a new shared Data source for SSRS report manager?
Something along the lines of them being a data read for the DB and permissions to update tempdb?
Would have thought it not advisable to have the login be able to update the main db...
View 1 Replies
View Related
Jan 18, 2005
Someone please help me before I loose all of my hair :mad: !!!
I have two machines, a Windows Server 2003 machine running an SQL Server Standard Edition database instance and client tools (Enterprize Manager etc.), and a workstation PC running Windows XP SP2 which has just the SQL Server client tools (not an actual DB instance).
I'm trying to use the Enterprize Manager client tool on my WinXP machine to remotely connect to the server instance on the server PC (via 'New SQL Server Registration' option). Everytime I do this I get the following error message:
Login failed for user 'sa'. Reason: Not associated with a trusted SQL Server Connection.
Now I've tried using different usernames and passwords. I have login details for my SQL Server set to use Windows Login rather then SQL Server login, so I tried remotely connecting with the username and password for the server PC, tried 'sa' with server password, even tried setting up a new user on the server and logging in from the client with that, no luck. I've opened up correct ports 1433 on both PCs, even disabled the windows firewalls algother. Both server and client have SP3a installed, WinServer2003 has SP1 and XP SP2.
So I'm at a loss to how to remotely connect to my SQL Server. FYI I know the server itself is working fine because I can connect to it using Enterprize Manager/Query Analyser on the local machine. Any ideas? Many thanks for your help!
View 2 Replies
View Related
Aug 9, 2007
Hi,
The problem I have is this:
I have installed SQL Server 2005 Enterprise Edition on a W2K3 server that already has a SQL Server 2000 default instance installed. The installation appears to be a success. The problem is that having installed the client tools, and trying to connect, I get the following error message in a dialog:
"Cannot connect server SERVERNAMEINSTANCENAME
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: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)(Microsoft SQL Server)"
The funny thing is that I can connect to the SQL Server 2000 default instance that is on that server.
So it's obviously not a connection problem per se at my end i.e. bad dns etc which might stop me resolving the server name.
Googling for this error text gives the following advice on various web sites:
1. Check that the remote server allows connections.
It does, as other colleagues in my team have successfully installed the tools and can connect with no problems.
2. Check that the SQL Browser service is running.
It is, as without this running, my colleagues would not be able to connect either.
3. Open SQL Server Configuration Manager. Select "SQL Server 2005 Network Configuration | Protocols for MSSQLSERVER" then enable the protocols you need.
The required protocol (TCP/IP) is enabled. Named pipes is also enabled, although I don't think that we're using that. And shared memory is also enabled by default.
4. Open the firewall and add exceptions for sqlbrowser.exe or UDP port
1434
There is no firewall running at either end of the connection and as mentioned I can connect to the SS2K default instance.
5. Make sure that the server machine is reachable.
I can connect to the SS2K default instance so it must be reachable. I can't see that I would need to change any configuration settings as my colleagues are using exactly the same default settings for their connections and they work fine.
The only thing that I have done differently is that I originally installed the client tools using someone else's privileged account as I didn't have admin rights on my box. The client tools didn't work, so I got admin rights to my box, uninstalled the client tools and then reinstalled them using my account. Still no luck. Not sure if something is still hanging around after the uninstallation that would cause me a problem.
To help, I've also filled out the following questionnaire:
[1] Client side:
> What is the connection string in you app or DSN?
There isn't one. I'm trying to connect from the SQL Server Management Studio | 'Connect to Server' dialog.
> If client fails to connect, what is the client error messages?
"Cannot connect server Y
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: SQL Network Interfaces, error: 26 - Error Locating
Server/Instance Specified)(Microsoft SQL Server)"
Is the client remote or local to the SQL server machine? Remote
Can you ping your server? YES
Can you telnet to your SQL Server? YES
What is your client database provider? [SNAC | MDAC | ADO.NET1.0 | ADO.NET2.0| other (please specify] N/A
What is your client application? SQL Management Studio
Is your client computer in the same domain as the Server computer? Different domains
What protocol the client enabled? [Shared Memory | TCPIP | Named Pipes]. All 3 protocols are enabled
Do you have aliases configured that match the server name portion of your connection string? No
Do you select force encryption on server and/or client? No
[2] Server side:
What is the MS SQL version? SQL Server 2005
What is the SKU of MS SQL? Enterprise
What is the SQL Server Protocol enabled? [Shared Memory | TCPIP | Named Pipes ]. All 3
Does the server start successfully? YES. Other people can connect to the same instance.
If SQL Server is a named instance, is the SQL browser enabled? YES
What is the account that the SQL Server is running under? Domain Account
Do you make firewall exception for your SQL server TCP port if you want connect remotely through TCP provider? N/A. Firewall not running.
Do you make firewall exception for SQL Browser UDP port 1434? N/A. Firewall not running.
What tool or Application are you using to connect to SQL Server SQL Server Management Studio
[3] Platform:
What is the OS version? Windows 2003 (Server side), Windows XP Pro Version 2002 SP 2
Do you have third party antivirus, anti-spareware software installed? Symantec AntiVirus
[4] Misc:
If you have certificate configuration issue: N/A
Has anybody else come across this problem, as I've run out of ideas.
Many thanks,
View 1 Replies
View Related
Sep 27, 2015
Would it be possible to get a multi-database functionality on the same instance option available in SSMS anytime soon?
View 6 Replies
View Related
Jan 14, 2014
What is the best way to transfer data from the staging table into the main table.
Example:
Staging Table Name: TableA_satge (# of rows - millions)
Main Table Name: TableA_main (# of rows - billions)
Note: Staging table may have some data same as the main table.
Currently I am doing:
- Load data into staging table (TableA_stage)
- Remove any duplication of rows from the staging table (TableA_stage)
- Disable all indexes on main table (TableA_main)
- Insert into main table (TableA_main) from staging table (TableA_stage)
- Remove any duplication of rows from the main table using CTE (TableA_main)
- Rebuild indexes on main_table (TableA_main)
The problem with the above method is that, it takes a lot of time and log file size grows very big.
View 9 Replies
View Related
Jun 11, 2015
I need to transfer the logins and passwords from my prod server to my DR server. I've seen a number of articles about how to transfer logins; but, I'm not finding anything on how to transfer both logins and their passwords. SQL server R2008 R2
View 4 Replies
View Related
May 30, 2008
Is it possible/advisable when transfering very large amounts of data from server to server to:
trasnfer the data to a new table first
second alter new table adding indexes, defaults, ets based on original table
if it is what flow item would be used to transfer/alter the indexes and defaults?
I'm very new to ssis so the more detail you can give the better.
Thanks
View 5 Replies
View Related
Nov 15, 2007
The box I am trying to connect to is running two instances of SQL Server.
There is a SQL Server 2005 instance which is the default. There is a SQL Server 2000 instance which is named 'SQLSERVER'.
I can connect to the SQL Server 2000 instance no problem:<add key="ConnectionString" value="server=MYPCSQLSERVER;database=mydatabase;user id=****;password=****" />
However, I am having trouble connecting to the Default SQL Server 2005 instance. I have tried:
<add key="ConnectionString" value="server=MYPC;database=mydatabase;user id=****;password=****" />
but it doesn't work. I have tried explicitly setting SQL Server 2005 to use port 1434 (as SQL Server 2000 is running on port 1433), and then used:
<add key="ConnectionString" value="server=MYPC,1434;database=mydatabase;user id=****;password=****" />
but this doesn't work either.
Am I mssing something here? Any help much appreciated
Thanks...
View 2 Replies
View Related
Nov 21, 2007
my web app was working fine but as soon as i added in a page and few store procedures and 2 tables it just pop up with that error message.
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
i did go into the folder C:Documents and SettingsdnguyenLocal SettingsApplication DataMicrosoftMicrosoft SQL Server Datasqlexpress and delete the directory but this didnt help much. I still get the same error message. please help
View 2 Replies
View Related
Nov 13, 2007
Hello,
I have just reinstalled windows xp pro with service pack 2 and framework 2.0.
I am using SQL Express and visual studion 2005.
I am trying to run my website and i keep getting this error message, i followed few instructions but i still failed to make it work. could someone who faced similar issues guide me,
thanks
MM
Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.
Source Error:
Line 112: Public Overridable Property Theme() As String
Line 113: Get
Line 114: Return CType(Me.GetPropertyValue("Theme"),String)
Line 115: End Get
Line 116: Set
Source File: C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Fileswroxunitedeecd65d2e1a6a798App_Code.ws-dzmjl.6.vb Line: 114
Stack Trace:
[SqlException (0x80131904): Failed to generate a user instance of SQL Server due to a failure in starting the process for the user instance. The connection will be closed.]
System.Data.ProviderBase.DbConnectionPool.GetConnection(DbConnection owningObject) +437
System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection) +82
System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) +105
System.Data.SqlClient.SqlConnection.Open() +111
System.Web.DataAccess.SqlConnectionHolder.Open(HttpContext context, Boolean revertImpersonate) +84
System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +197
System.Web.Profile.SqlProfileProvider.GetPropertyValuesFromDatabase(String userName, SettingsPropertyValueCollection svc) +766
System.Web.Profile.SqlProfileProvider.GetPropertyValues(SettingsContext sc, SettingsPropertyCollection properties) +428
System.Configuration.SettingsBase.GetPropertiesFromProvider(SettingsProvider provider) +410
System.Configuration.SettingsBase.GetPropertyValueByName(String propertyName) +117
System.Configuration.SettingsBase.get_Item(String propertyName) +89
System.Web.Profile.ProfileBase.GetInternal(String propertyName) +36
System.Web.Profile.ProfileBase.get_Item(String propertyName) +68
System.Web.Profile.ProfileBase.GetPropertyValue(String propertyName) +4
ProfileCommon.get_Theme() in C:WINDOWSMicrosoft.NETFrameworkv2.0.50727Temporary ASP.NET Fileswroxunitedeecd65d2e1a6a798App_Code.ws-dzmjl.6.vb:114
Wrox.Web.GlobalEvents.ThemeModule.app_PreRequestHandlerExecute(Object Sender, EventArgs E) in C:WebsiteWroxUnitedApp_CodeThemeModule.vb:30
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.832; ASP.NET Version:2.0.50727.832
View 1 Replies
View Related
Aug 10, 2015
I've created an excel spreadsheet with a data connection. This data connection uses a query that runs against a read-only database.
The issue I'm having is that the query never seems to finish running against the database, whether I open the Excel spreadsheet to view the data or run the query in SSMS.
I created the connection on the Data ribbon by going to From Other Sources --> From SQL Server and using the Data Connection Wizard.
Is there some kind of setting or property I'm missing that would allow this query to finish running?
View 6 Replies
View Related
Jun 30, 2015
Following an upgrade to SQL Server 2012, our shop's Schema Compare tool (Redgate SQL Compare) is no longer supporting our environment.We are starting to evaluate various 3rd party products to find a possible replacement, and would be interested in what products are favored by other IT shops who do a lot of database work.
Our shop is split about 75% SQL Server, 20% Oracle, and 5% I'll call other. Ideally a product would support SQL Server and Oracle, but our focus is on SQL server right now. On that platform we have ~50 servers spread across DevUATProd environments.In basic terms, we need a tool that can identify schema differences between DBs and generate synchronization scripts to support deploys between environments. Real-time synchronization is not a requirement (nor desirable), as deploys are a gated DBA function in our shop.
View 2 Replies
View Related
May 28, 2014
I'm trying to find out if there is a way within sql server or via script of emailing when a particular job or jobs has over run?
View 1 Replies
View Related
Jul 10, 2015
I have just started to use Integration Services Catalog with SQL Server Agent. After I deploy a new version of the project the any Server Agent job that uses any of dstx still uses the old one. How do I fix this?
View 1 Replies
View Related
Mar 3, 2008
Hello everybody,I was configuring a SqlDataSource control using SQL Authentication mode.I first added a database file (testdb.mdf) through Solution Explorer-Add New Items. Then through Database Explorer I created a table named "info"Then while configuring the SqlDataSource control I used the SQL Authentication mode and attached the "testdb.mdf" database file.Test Connection showed success. But when I hit the Ok button of the wizard it displayed the following error message:Failed to generate a user instance of SQL Server. Only an integrated connection can generate a user instance.While configuring the SqlDataSource control I clicked "New Connection". Under Data Source section I tried both Microsoft SQL Server and Microsoft SQL Server Database File. And in both the cases I attached a databese file(testdb.mdf). Plz enlighten me on this.Thanks and Regards,Sankar.
View 1 Replies
View Related
Jun 15, 2005
Hello - where I can find the tools to view/see open connections, transactions not closed ... etc of a MS Sqlexpress database?
Thanks a lot - FOX
View 3 Replies
View Related
