Hi,
I have succesfully created a Stored Procedure which runs under 2 seconds locally.
However when i run the same proc from another machine in the LAN, the response times vary from 5 sec to over 40 Secs and even occassionally times out.
My server is SQL 2005 Dev Edition (32 Bit) running on a Dual Core Box with 2GB memory.
Any Ideas why this would be happening?
Hi all,My client PC is very slow to connect to all SQL server databases.Other client machines are having no problem at all. I even triedconnecting to my local MSDE database, and the same behavior results.If I try to connect via a .NET application it is also slow.The connection is always made if I wait long enough, but then when I goto query a table it is very slow again, even if I'm only returning onerow from a table with 5 fields.Does anyone have any suggestions on how to troubleshoot this?Thanks.
View 4 Replies View RelatedSQl server 2005 version: Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) Mar 23 2007 16:28:52 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 6.0 (Build 6000: ) .
Our customer has a SQL Server 2005 installed on a machine running Windows Vista.
Our application is running on a XP machine and on the Vista machine and its connected to the SQL server running on Windows Vista.
The customer table has 5498 records.
IF she runs the query "SELECT * FROM CUSTOMER WHERE dateadd(dd,0, datediff(dd,0,Lastupdated))>'20070101' ORDER BY [CustomerID]" using our application from the XP machine it takes a few seconds - 3 to 6 seconds.
If, instead, she runs the same query from the Vista machine it takes more than 5 minutes!!
This problem happens on every Vista machine.
We even installed the SQL Server 2005 on Windows Server 2003 but every time our application is run from a Vista machine this delay occurs.
The problem doesn't seem to be SQL SERver 2005 related because when we run from the XP machine everything is ok.
Maybe there is some problem with ADO (I even tested with the SQLNCLI provider: same bad results) or Vista...
We really need to solve this problem; the number of customers complaining about this behavior on Vista is growing.
NOTE: The LastUpdated column holds date + time info so we can't ignore the time part! I know, I know, bad design...
Thanks for your help.
P.S.:
We have just received another report from a customer that is running SQL Server 2000 on a Windows Server 2003 machine and using two machines (one Vista, one XP) to connect to the database.
Same behavior. Running the sql statement from the XP machine takes 3 to 6 seconds; on the Vista machine takes more than 5 minutes...
Thanks..
J.C.
Sample code:
Dim rsData As adodb.Recordset
Set rsData = New adodb.Recordset
rsData.CursorLocation = adUseClient
rsData.Open "SELECT * FROM CUSTOMER WHERE ( CASE WHEN [LastUpdated] IS NULL THEN NULL ELSE CAST(CONVERT(char(10),[LastUpdated], 112) AS datetime) END > CAST('20070101' AS datetime)) ORDER BY [CustomerID]", mcnMainDatabase, adOpenStatic, adLockReadOnly
rsData.Close
Hi,
Some of my queries are running too slow.It's taking as long as 30secs .Earlier the same query was taking less than 5 secs.
I understand the db has grown BUT I do not know to look at this query where should i start from and what should I look into.
It is on production server.
the db size is 15GB and unallocated is 9GB.
log space used is 4%.
TIA.
Howdy. I have a table in my DB that has about 2 million records. The search times are taking 15 - 30 seconds depending on the number of records I am returning. Is this normal? The machine is NT 4 sp6a Dual PIII 866's with 1 GB of RAM on RAID5 SCSI disk. This seems like a long time to me. What kind of performance should I expect? Any kind of tuning steps I can take?
Thanks
Shane
Hi All,
Am very new to SQL server so don't really understand what effects the speed of queries. I have the two below queries, which are nearly the same apart from one has a right join and the other doesn't. The both return about 5000 records, and I am implementing this query from an accss databse with an odbc link to sql server. What I don't understand is it takes about 8 seconds for the query with the right join in to return the records and only about 4 seconds for the one without. What I'm after really is just some general advice on how to bulid fast queries, and any advice on the two below queries would be nice. Thanks
SELECT Employees.Name, Calls.CallDate, Calls.CallTime, Calls.Callername, Contacts.CompanyID, Contacts.ContactID, Calls.CallerNumber, Calls.CallerCompany, Calls.ActionTakenID, Calls.OperatorID, Calls.Confirmed, Calls.Charged, Calls.Notes, Company.CompanyName, Operators.Operatorname, Calls.CallID, Calls.ShortMessage
FROM (Contacts INNER JOIN Company ON Contacts.CompanyID = Company.CompanyID) INNER JOIN (Operators INNER JOIN (Employees RIGHT JOIN Calls ON Employees.EmployeesID = Calls.EmployeesID) ON Operators.ID = Calls.OperatorID) ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.ContactID)=1442))
ORDER BY Calls.CallDate DESC , Calls.CallTime DESC;
SELECT Employees.Name, Calls.CallDate, Calls.CallTime, Calls.Callername, Contacts.CompanyID, Contacts.ContactID, Calls.CallerNumber, Calls.CallerCompany, Calls.ActionTakenID, Calls.OperatorID, Calls.Confirmed, Calls.Charged, Calls.Notes, Company.CompanyName, Operators.Operatorname, Calls.CallID, Calls.ShortMessage
FROM (Contacts INNER JOIN Company ON Contacts.CompanyID = Company.CompanyID) INNER JOIN (Operators RIGHT JOIN (Employees RIGHT JOIN Calls ON Employees.EmployeesID = Calls.EmployeesID) ON Operators.ID = Calls.OperatorID) ON Contacts.ContactID = Calls.ContactID
WHERE (((Contacts.ContactID)=1442))
ORDER BY Calls.CallDate DESC , Calls.CallTime DESC;
Hi, I have absolutely no knowledge of PHP or SQL .... I moderate a PHPBB forum at www.savingshelterpets.com
Our web host (SiteGround) has taken our site down temporarily because we are overloading the server. I have no idea how to fix the problem, so hopefully someone here can help me out! Smiley
PHP version 4.4.4
MySQL version 5.0.27-standard-log
Here's the info sent to me by SiteGround (I don't understand a word of it!):
quote:Upon further investigation, it turned out that the following queries in your account are slow and heavily consume server resources:
# User@Host: savingsh_phpb1[savingsh_phpb1] @ localhost []
# Query_time: 4 Lock_time: 0 Rows_sent: 1 Rows_examined: 1284
use savingsh_phpbb2;
SELECT user_id, username, user_password, user_active, user_level, user_login_tries, user_last_login_try
FROM phpbb_users
--
delete from rs_stat_ip where platnost_do<'2007-08-03 16:49:43';
# User@Host: savingsh_phpb1[savingsh_phpb1] @ localhost []
# Query_time: 5 Lock_time: 3 Rows_sent: 1 Rows_examined: 0
use savingsh_phpbb2;
SELECT * FROM phpbb_optimize_db;
# User@Host: binaryte_lhlp1[binaryte_lhlp1] @ localhost []
--
# Time: 070803 16:50:27
# User@Host: savingsh_phpb1[savingsh_phpb1] @ localhost []
# Query_time: 4 Lock_time: 2 Rows_sent: 1 Rows_examined: 0
use savingsh_phpbb2;
SELECT t.topic_id, t.topic_title, t.topic_status, t.topic_replies, t.topic_time, t.topic_type, t.topic_vote, t.topic_last_post_id, f.forum_name, f.forum_status, f.forum_id, f.auth_view, f.auth_read, f.auth_post, f.auth_reply, f.auth_edit, f.auth_delete, f.auth_sticky, f.auth_announce, f.auth_pollcreate, f.auth_vote, f.auth_attachments
FROM phpbb_topics t, phpbb_forums f
In order to have the limitations removed, please optimize your script.
Some queries take a long time to complete.
Setup is:
- SQL Express SP2
- Windows Vista Business
- 2 GB RAM
- Core 2 Duo processor
- Connecting to (local) server with SQL Authentication
- only 1 Instance of MSSQLSERVER
Simple queries (SELECT * FROM TableName) wher the table has only a few records. This query may take up to 30 or more to execute. This slowness is consistent to certain tables. Other much larger tables run queries fine.
If a different computer logs in to the same server, queries provide instantaneous results.
Hi All
I struck up with Slow perfornace query,Please some body help me how to analyze Slow perforamnce queris.
I'm working with a table with about 60 million records. This monster is growing every minute of the day as well, by 200,000 - 300,000 records/day. It's 11 columns wide, and has one index on a datetime column. My task is to create some custom reports based on three of these columns, including the datetime one.
The problem is response time. Any query executed on this table takes forever--anywhere between 30 seconds and 4 minutes. Queries such as this one below, as simple as it is, can take a minute or more:
select
count(dt_date) as Searches
from
SearchRecords
where
datediff(day,getdate(),dt_date)=0
As the table gets larger and large, the response time is going to get worse and worse. Long story short, what are my options to get the speed of queries down to just a few seconds with a table this big? So far the best I can come up with is index any other appropriate columns (of which there is one for sure, maybe two).
Hi,i have developed an web-database enabled application, wherein the admin will be importing the data from a remote machine to the server.Is it possible to run the DTS remotely? Also, is there need to install the sqlserver in the remote machine on which the admin is working ? i mean to say, other than server, do i need to install sqlserver on the remote machine too..Thanx in advance
View 9 Replies View RelatedI've been using MS-SQL Server for many years but never come across this problem before.
When I try and run a very simple query from Query Analyzer it takes a LONG time. Even when there are no tables involved!
Even:-
select 1
go
takes 28 seconds to return '1' when running against the local server. i.e. both QA and the Server are running on the same machine.
Can anyone help explain how to get my performance back! Thanks.
I have some VB.NET code that starts a transaction and after that executes one by one a lot of queries. Somehow, when I take out the transaction part, my queries are getting executed in around 10 min. With the transaction in place it takes me more than 30 min on one query and then I get timeout.
I have checked sp_lock myprocessid and I've noticed there are a lot of exclusive locks on different objects. Using sp_who I could not see any deadlocks.
I even tried to set the isolation level to Read UNCOMMITED and still have the same problem.
As I said, once I execute my queries without being in a transaction everything works great.
Can you help me to find out the problem?
Thanks,
Laura
Hi,
I currently have a large table (35 million rows, over 80GB). I have one varchar(max) column on the table that is used in the fulltext index.
To query the complete index is fast, for example:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
This took 70 seconds (which I can live with). However, I seldom run queries like this, most are more like:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], 'ipod') CT
JOIN Pages ITP ON ITP.PageID = CT.[Key]
JOIN Feeds ITF ON ITP.IPID = ITF.IPID
JOIN Buyers ITB ON ITB.IBID = ITF.IBID
WHERE ITB.ID IN (1342,246)
These queries are much slower (this example took 17 minutes). I understand that FT searches the index and returns all rows that match the query to SQL. SQL then performs the joins and counts only the correct results. (Correct me if I'm wrong here).
One solution I've seen to this to put data or "tags" into the FT column - so my Body column would become something like:
'{ID:1342}' + [Body]
That sounds like a very good idea. I could then change the 2nd query above to be:
SELECT 'ipod', COUNT(*)
FROM CONTAINSTABLE(MyDB.dbo.Contents, [Body], '("ID:1342" OR "ID:246") AND "ipod"') CT
That all works well until I want to select 1000 different ID's because the FT query will become very long and complex. Also I'm only including one column (ID) in this example - but I have about 7 or 8 columns that I would need to include in these "tags". Quering multiple columns become very complex quickly and no doubt I will reach a query limit at somepoint.
If anyone has any other suggestions to the above I'd love to hear them. Another thought I'm having is to partition the table. I can find very little online about how FT behaves on partitioned tables - I fear it behaves exactly the same, what I'd like to think is that I could partition the table on an ID say 100 per partition or something, and then fulltext would only search the relevant partitions. If it behaves like this it may work. If no-one knows then I'll give it ago, but this will take me a while due to the table size - so I'm hoping one of you clever lot know!
Many thanks for any advice.
Simon
Hi
I have a SQL db with a hosting company that appears to have gone bust, so am moving my site from them to a new provider. I am able to access the database on the SQL server but I need to transfer all the data and objects to my desktop PC. I have tried scripting all data/objects and DTS but these just generate errors and fail (I suspect due to permissions).
I did this once before and got a .bak file from the providers, which I was successfully able to restore elsewhere, so this is what I need to do again. However, now I'm getting no response to support requests so need to do this myself. The question, then is....
Is there a way to backup a SQL db to a .bak file on the server and then somehow transfer it to my own machine? Or is it possible to somehow run the backup to a different machine and get it off their server?
Any help would be appreciated, as I'm worried about how long I'll continue to have access to the server before they all disappear without a trace!
Hello,
I've developed an application with .NET and I would like this application to access a SQL server database which is situated on a remote machine. I've set the connectionString like this :
\serverSQLExpress ...
but it doesn't work
Is there anything else to do ?
I guess I have to set some trust settings but I don't know how...
Could anyone help me to solve this problem ?
Thank you in advance,
mathmax
Hi,
In my localmachine everything is working fine. But online only Update, Delete and Select statements are working correctly, while INSERT doesn't work and I got a similar error to this when I try to insert any data into any table:
Server Error in '/' Application.
Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.The statement has been terminated. 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: Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.The statement has been terminated.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
[SqlException (0x80131904): Cannot insert the value NULL into column 'messageId', table 'db.dbo.mail'; column does not allow nulls. INSERT fails.
The statement has been terminated.]
System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +925466
System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +800118
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +186
System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1932
System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +149
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) +1005
System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) +132
System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +149
System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135
System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +404
System.Web.UI.WebControls.SqlDataSourceView.ExecuteInsert(IDictionary values) +447
System.Web.UI.DataSourceView.Insert(IDictionary values, DataSourceViewOperationCallback callback) +72
System.Web.UI.WebControls.FormView.HandleInsert(String commandArg, Boolean causesValidation) +388
System.Web.UI.WebControls.FormView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +602
System.Web.UI.WebControls.FormView.OnBubbleEvent(Object source, EventArgs e) +95
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.FormViewRow.OnBubbleEvent(Object source, EventArgs e) +109
System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163
System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33
System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1746
Version Information: Microsoft .NET Framework Version:2.0.50727.1433; ASP.NET Version:2.0.50727.1433
So wat's wrong?
Note:1-The Insert statement work correctly in localmachine.2-The application and the sql database are both in remote hosts.3-The application configuration had been checked and is correct.
Hi folks,
We are trying to import a flat file from a remote machine using the BULK INSERT command.
We have mapped the remote directory on the server.
we have used the following command.
Bulk insert table_name from my_servermy_sharefilename.txt
The error it gives is operating system Error number 5.( Access denied)
We are able to access the same file from the Windows explorer. We also refered the books online and as suggested,
have set the System Path to the same share name.
But it does not work.
Could you help us in this regard.
Thanks.
-Rajesh
Hi all,
I have scheduled a backup on a remote machine. Everyday I FTP these backups from the remote machine to the local drive.
A local drive is maped to the remote machine in Windows NT Explorer but SQLSERVER7 Enterprise Manager can't see this drive so that I could do the backup to it.
Can any one please tell me how to make this work?
Thanks in advance.
Attaullah
I was just trying out Microsoft SQL Server 2000. I created a sample application which reads in 2 values from a textbox and writes it into a databse. This application works fine on my computer and the values are written to the database (In the connection string, I give my IP address as the datasource so that anyone on the internet can hit this database). However, when I copy this application to another computer and try to run it, it gives me an error "SQL Server does not exist or access is denied." I know this is a minor problem with some setting, but I haven't been able to find the solution on the internet. Can someone whose worked with Microsoft SQL Server 2000 please help me out.
View 9 Replies View RelatedHi there,
This is my first post in these forums. Actually i am having some problem in transfering Database from one server to another.
From my source server, i have managed to make a .BAK file(size Around 1.5 GB) through SQL Backup. Now i have that file on my local machine. I want to transfer that database to another remote machine.
I tried 1 thing: i restore that .bak file to my local machine and tried to use export utility...... it started uploading. but after some time. it shows the following error.
I am from programming background. and i am not sure that what i am trying to do is the best solution for my problem. Can you please suggest me any method with which i can acomplish this task. And if i am going right then can u please tell me where i am going wrong.
I am using SQL Server 2005. and i want to transfer the full Database including all the objects i.e. Store Procedures, triggers etc.
Thanks Alot
Gurpreet Singh Dhaliwal
Environment:SQLServer Developer Edition on Machine AEnterprise Manager running on remote machine BBoth machines are in the same subnetI open Enterprise Mgr on Machine A, right click on the Stored Proc, enterthe parameters and the debugger stops on the first line and I can singlestep through it just fine.I open Enterprise Mgr on Machine B and connect to the same database onMachine B, right click on the same Stored Proc, enter the same parametersand the debugger doesn't stop, but it runs the proc al the way through.Even if I set a break point on one of the lines from this machine it doesn'tstop. I don't get any errors, it just runs right thought the proc.I've been through all the DCOM settings, Logins with Admin rights and not aSYSTEM account. Checked all the debug files and have them all registered.Anyone else expericened this? Anyone have any ideas on how to fix it?-Steve
View 4 Replies View RelatedHi,
Server A houses the Production DB which services an OLTP system. Transactional replication is configured on this server which has two subscribers - on two separate servers (servers B and C). The Distribution database currently resides on Server A (which is also the Publisher).
Server B is on the same LAN as Server A (on the same rack). I'm considering moving the Distribution database from Server A to Server B to offload some of the processing overhead from Server A.
Are there any gotchas/performance problems associated with having the Distribution database (remote distributor) and the Subscriber on the same server (server B) in a transaction replication topology?
Please advise or point me to the appropriate documentation - I haven't found anything that addresses this specific question...thanks in advance,
Smitha
Hi,
I have developed a report in SSRS. When I preview it, the report looks fine.
However when I view the same report on the ReportServer of a remote machine it looks differnt.
Some additional rows with some values appear and I have no idea from where they are coming.
This same report works fine on my local PC and ReportServer.
However this problem only appears when I deploy it on a remote machine and view it on the web there.
Any guidance will be helpful.
Thanks in advance.
I'm trying to connect to a sql server machine from another machine on the network and I'm getting 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)"
More information about the machines:
All machines are on the same private netwrok, all in the same workgroup.
Machine1: this is the DB server, Win 2003 server, it has SQL 2005 developer edition, .Net runtime 2.0.
Machine2: trying to connect to machine 1, Win 2003 server, .Net runtime 2.0
On the DB server, TCP/IP is enabled with port 1433, also named pipes are enabled.
I checked the ERRORLOG and it says it's ready for connections and it's listening...Also there are no firewalls between the machines, I also added the sql server service .exe and the sql explorere .exe to the windows firewall exceptions on the DB server (although it's disabled)...
I'm not sure what else to do, I checked all sources online, applied anything related to this issue but not luck. This has wasted 3 hours of my time, this was a task that wouldn't take you a minute in older versions!! , Microsoft thinks it's making our life easier but in fact, I spend more time now on trouble shooting than before...
Let me know if you have a solution..
thanks,
Tamer
What is requirement to make remote access to other machine ?
View 5 Replies View RelatedI posted this question in the past and did not get any answer.
I put all my sources on a remote server mainly for backup purposes (the remote server is backuped every day) but when I have the .sdf file I can not open it from the visual studio 2005 configuration server here is the description of the problem:
I have an application that runs on a PDA and manage a database (.sdf) file I use C# with visual studio 2005
What I want to do is to add the database file (.sdf) to my project files and open a connection to it.
The problem is that all my source file are stored on remote machine and not locally on my machine and the .sdf file is also stored with the project source files.
When I try to add the file to the project I get the following error fro the data source configuration wizard :
"An error occured while retrieving the information from the database"
DRIVE_REMOTE
Parameter Name : N:
I get the same error also when I try to open a new connection to the file from the server explorer. When i copy the file to drive "C:" I can open a connection to it with no errors.
We are trying to take advantage of the new Security Context for SSIS but users are unable to connect remotely to the SSIS Service unless they have been added to the Administrators Users Group on the server. I have tried adding them to Guests, Power Users, Remote Desktop Users, Users and SQLServer2005DTSUser$machine but the user is unable to connect.
The message received when the user is unable to connect is:
Cannot connect to 192.x.x.x
Additional Information
-> Failed to retrieve data for this request. (Microsoft.SqlServer.SmoEnum)
-> Connect to SSIS Service on machine "192.x.x.x" failed:
Access is denied
What is the list of permissions and privileges that a user that does not belong to the server's Administrator group and have sysadmin server role, must have to allow them to create and run a package through SSIS.
Hi,
I deployed SSRS reports to my local report server and set the connection string to access a server on the network with Analysis Server.
Result:
I can access and use the reports from my computer. But when I use any other computer on the network I can't use the reports. I can access them but they don't find the database ant therefor they don't present any data.
Error message as folows: "An error has occurred during report processing. (rsProcessingAborted) Get Online Help
Cannot create a connection to data source 'dsAnalys'. (rsErrorOpeningConnection) Get Online Help
Database 'Analys' does not exist. "
This works fine on my local machine, the error comes only when i try to browse on a remote machine. I am using SQL Server 2005
Any help would be appriciated!!!
Thanks in advance,
Chanduu
How do I execute a query on a database on a remote server?
I'm clear how to execute queries on databases on a local server.
You simply execute:
select * from database_name.owner_name.table_name
Is there some way to set up a trusted alias to that remote database???
Stumped in Seattle
Hi and thanks in advance for your help.
I have a dilemma and I need some expert help with this. I am trying to make a copy of a remote Database and then replicated the DB and it's contents on my local machine so I can build a test site to run data with. I tried to remot into the server machine but I guess that feature is not allowed by the honest of the remote machine. Since I build on my local machine and would like to be able to test data on my local machine.... I would like to copy the remote DB to my local computer and then I can run my test app on my local server. The version of SQL on the remote machine is SQL Express 2005 and I have SQL 2005 on my machine. Please give me how to accomplish this please.
Dollarjunkie
We just installed an application, WhatsUpGold, to monitor our servers. Management won't let me host that on my primary SQL box. So, we are using MSDE on the local machine (call that box my_netmon).
I can access the db with very basic functionality using osql while on the my_netmon machine. I've added my domain user account as a System Admin using the following commands.
exec sp_grantlogin @name
go
exec sp_addsrvrolemember @name, 'sysadmin'
But when I go to the my_sql machine and try to attach to it with Enterprise Manager I get an error that the server does not exist.
What gives??? Is it just not possible, or am I missing something here?
My goal is to just be able to see the data stored in the MSDE db. Write views, etc...
Please advise,
Marc
OS: Windows 2012, standard, 64-bit
SQL: 2012 + SP1 + CU2 = 11.0.3339
We recently decided to "break apart" our BI environment. We used to have everything on one box, DB Engine, SSIS, SSAS & SSRS. Everything has been running fine, but we now have other projects using these services, so we decided to break them apart into their own boxes.
We now have DB Engine on one Server, SSIS & SSRS on another server and SSAS on yet another server, so we now have three boxes that replaced one box. All are Windows 2012, standard, 64-bit with SQL Server 2012 + SP1 + CU2.
Since some of our SSIS packages have to access external resources, we used a domain account for it's service account. The DB Engine and SSAS box are using the default service accounts when installed. I can execute the packages fine on the SSIS server, I can even execute them via SQL Agent jobs on the SSIS box (we did install a default instance of SQL on the SSIS box), however when I try to execute a package from my laptop, it fails with the ugly "Login failed for user 'NT AUTHORITYANONYMOUS LOGON'".
I immediately double checked my SPNs and they all looked correct for the SSIS server and the service account we are using (and we had no duplicates). I also double checked the User Rights Assignment in the Local policy editor and all the correct Rights have been assigned (Log on as a service, Bypass traverse checking, Impersonate a client after authentication).