3 of my fellow employees and I am having some strange issues with SQL Server...
When we are connected to the local network, we are unable to log into our SQL Server Instances locally through SSMS or programs that use SQL Server unless we have our firewalls "default"(no exceptions). Of course, this is useless if we need to make client connections from other computers. So, if we add an exception, say "port 1433", the client CAN connect, but we can no longer connect locally...to ANY instances.
We have tried adding exceptions to the firewall for all SQL ports and programs. We've also made sure we've got the latest hotfixes and service packs. If I log in as the local computer admin and do not connect to the domain and turn off my firewall, everything works fine.
1. Tried enabling Remote Connections
2. Allowed multiple ports through firewall
3. Allowed multiple programs through firewall
4. Re-installed SQL
It's almost as if something on the local network or domain is causing an issue with our firewall.....
Is it possible to configure transnational replication between two different domains also non trusted domains.
It's possible means what i need to take care before configure replication and how to configure transnational replication between two different domains.
Good afternoon,I have a bizarre question. When running the following query:select SomeColumnName from TableA where PK_TableA in(select PK_TableA from TableB)I get results. This should not be feasible, because the query withinthe in clause:select PK_TableA from TableBis not possible - there is no PK_TableA column within the TableB table.Running the sub-query alone gives an error, but when using it assub-query in the first statement, I get every row within TableA.If it helps any, the exact query I'm running is:select demonstratorid from DirectSalesAgent where DirectSalesAgentId in(select directsalesagentid from WebsiteSubscriptionPayment)Shouldn't a query return an error if the sub-query has an invalidcolumn name?
I'm doing a select which includes the following:casewhen (rtrim(ltrim(T464.COMMENT_4)) = '' or T464.COMMENT_4 is null)then ''elseconvert(datetime,left(replace(replace(T464.COMMENT _4,' QTR: ',''),' -',''),10))end as QuarterStartDateThe COMMENT_4 field is a char(51), and contains values like: ' QTR:03/01/2005 - 05/31/2005', '', a number of spaces, or NULL. If I removethe convert to datetime within the case, data returns as expected,retaining NULLs and blanks. If I leave the convert in, it convertsevery row to datetime and seems to disregard the case expression.I've attempted converting COMMENT_4 to a varchar first, and that didn'thelp either. Also this does not seem to be affected by removal of theLTRIM and RTRIM. Googling for this turns up no similar results. HaveI simply nested too far within the case, or what?Any and all help and advice would be greatly appreciated.Thanks!
Running into a very bizarre problem here. I'm querying a couple of tables based on a date range. Nothing fancy; simply stuff. The query runs fine when I hard-code the dates, but when I use variables, the query just hangs.
This works fine: SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate)) FROM A WITH(NOLOCK) INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID WHERE A.SomeDate>='2008-02-09 00:00:00.000' AND A.SomeDate<'2008-02-09 01:00:00.000' AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
This does NOT: NO clue why :confused: DECLARE @FromDate DATETIME , @ToDate DATETIME
SELECT AVG(DATEDIFF(ss, B.SomeDate, C.SomeDate)) FROM A WITH(NOLOCK) INNER JOIN B WITH(NOLOCK) ON A.BID = B.BID INNER JOIN C WITH(NOLOCK) ON A.CID = C.CID WHERE A.SomeDate>=@FromDate AND A.SomeDate<@ToDate AND DATEDIFF(ss, B.SomeDate, C.SomeDate)<100000
I previously posted about a problem where it seemed that changing thecase of the word "BY" in a SELECT query was causing it to run much muchfaster.Now I've hit the same thing again, where basically almost any change Imake to how the query is executed (so that it still performs the samefunction) causes the performance to jump from a dismal 7 or 8 secondsto instantaneous. It's a very simple query of the form:SELECT Min(MyValue) FROM MyTable WHERE MyKey = @0which was running fine until a moment ago, when it suddently startedrunning hopelessly slowly. If change anything in the query tolowercase (or the Min to uppercase), it runs fine again. Last timesomeone suggested something about a bad plan being cached, and after abit of research I found the commands DBCC DROPCLEANBUFFERS and DBCCFREEPROCCACHE. Sure enough, after running these, the query startedrunning fine again. The question isa) why is this happening? Is it a bug in my code, or in SQL server?b) is it worth detecting it and fixing it automatically? I.e, should Iput some code in that notices that a query is running far too slowly,then runs "DBCC DROPCLEANBUFFERS; DBCC FREEPROCCACHE"? Or will thatcause other problems?Thanks
We moved a very tiny db (< 100 mb) from 2000 to 2005 (via attach/ detach) recently and now there are a couple sprocs which take forever to run from the app. Yes, the stats have since been updated as I reorg the indexes weekly. Now here is where it gets really strange, I can run this sproc in 0 seconds via Query Analyzer (QA). In other words I:
Have a developer run it from the app while monitoring in Profiler. There is a setting in the app for a 30 timeout, so after 30 seconds the app blows up and I see the exact code that the developer tried to run.
I can grab that code from Profiler, plop it into QA, and run it. Results are returned instantly. We have done this test a number of times, so I know it's not just a one time thing. How can this be? The only thing that got changed in the app was the connection string to the new 2005 server.
Background: We have an Asp.Net page where you enter a serial number (for a part my company manufactures), and it performs some updates to the data. Before it does, it does some simple selects for validation.Problem: Testers reported that the page will work fine for a while (5 minutes?), but then suddenly start throwing command timeout exceptions. I discovered that the timeouts always occur at the same validation query. Other queries are executed before this one, and they never timeout. The query is nothing fancy. It joins five tables, has a simple where clause, and selects a count of the records. It runs in Management Studio in under one second. The system is in a development environment under very little load. There's never a valid reason for the timeout. The where clause is parameterized, and the query itself is stored in our data access layer as a string. We execute it using SqlHelper's ExecuteScalar method. There's truly nothing remarkable about the query or how we execute it.After some playing with the page for a bit, and getting lots of successes, I'll get the timeout myself, and it will continue to occur for each additional attempt. If I let the system "rest" for a period of time (a couple of hours), it will be working again.The fun bit: When the system is in "timeout mode", if I go into our data access layer and add a single space character anywhere in the command text (where it has no semantic meaning) the timeouts go away. Take the extra space out, timeout again. Put it back in, no timeout. I did this (jaw agape) for a dozen times and the result was always the same!In previous attempts at debugging this problem, it would be in "timeout mode" and I'd make a semantic change to the query (hoping to fix it) and the problem would go away. I'd tell the testers it was fixed just to have them come back a day later and say it's not. That's what made me finally try the whitespace idea. Changing the string itself made the problem go away temporarily. What I do understand about this:SQL Server will cache the execution plan of a text query, and re-use it on successive queries. It probably caches it based on some hash of the query (just a guess), and changing one character would cause the hashing function to generate a different hash, hence it's a "different" query to SQL Server. So it won't use the same cached execution plan, and will make a new one. If the original execution plan was somehow borked, then that would explain why the timeouts return once I put the query back to it's exact original form. ('Cause it will use that same broken execution plan). This would also explain why letting the system rest would alleviate the problem. I imagine the execution plan eventually expires from the cache.One more thing: If I stop using SqlHelper, and contruct the SqlCommand myself, I can specify a command timeout. If I set it really high, it will eventually complete the query. It will just never do it within the default command timeout period. What exactly is going on is a mystery to me. I thought of a fix: Make the query non-constant and append a random comment at the end to make it appear different each time to SQL Server! LOL! If it came to that, I would immediately submit my own code to TheDailyWTF.com. :D
I am getting a failure on the db backup job of one of my maintenanceplans. It is coming back with the generic error message of,"sqlmaint.exe failed. [SQLSTATE 42000] (Error 22029). The stepfailed."I then checked the Database Maintenance Plan History page, but thisshows all the steps having run successfully. If I check the drives forthe actual backup files, they exist and look healthy too!There is plenty of space on the drives, so it is not that.I've checked the NT logs and all they say is,"SQL Server Scheduled Job 'DB Backup Job for DB Maintenance Plan 'DBMaintenance Plan for All User Databases''(0xC06E15E2A9E1414087BE19541D167861) - Status: Failed - Invoked on:2005-06-29 21:00:04 - Message: The job failed. The Job was invoked bySchedule 35 (Schedule 1). The last step to run was step 1 (Step 1). "Which doesn't give me any clues.Since the backups have actually run to success, I am going to take offthe option on the maintenance plan to "Verfiy the integrity of thebackup upon completion". Maybe it is this that is causing problems,rather than the backup?Anyone had anything similar?
I have a maintenance plan that consists of several parts. It basically backs up all the databases, deletes old backups and then shrinks the databases.
The odd thing is that it appears to back up all the databases, can't tell if it does step 2 or 3 and then it fails with the errors below.
How do i correct this short of throwing it out and starting from scratch?
This is a package originally from one server that i'm trying to deploy to a 2nd server. Already using configuration files to chagne the target connections etc.
thx.
PackageStart,WSWT4361,NT AUTHORITYSYSTEM,ProdBackupPlan,{645B67A9-0377-4462-BE81-755D4B1CE9DD},{AB598508-1858-41BC-8844-CA793A7861D4},2/28/2006 5:09:16 PM,2/28/2006 5:09:16 PM,0,0x,Beginning of package execution.
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
Select CONVERT(nvarchar(38),@Guid) AS RunId" failed with the following error: "The INSERT statement conflicted with the FOREIGN KEY constraint "FK_sysmaintplan_log_subplan_id". The conflict occurred in database "msdb", table "dbo.sysmaintplan_subplans", column 'subplan_id'. The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
I have a controller package that coordinates the execution of a few child packages. The controller is responsible for logging errors/events to a logging database: each event hander has a corresponding Execute SQL Task that executes an stored proc in the logging database.
The ServerName of the logging database connection is set by a package configuration environment variable (ENV_DB_SERVER).
The controller runs seemlessly on development. However, when we move it to UAT, we get this strange, and truncated, error:
Log Job History (ORD_Daily_CMIDW1_Load)
Step ID 1 Server NAMCFMSSDDB602 Job Name ORD_Daily_CMIDW1_Load Step Name Controller CMIDW1 - Daily Load Duration 00:00:12 Sql Severity 0 Sql Message ID 0 Operator Emailed Operator Net sent Operator Paged Retries Attempted 0
Message Executed as user: RELXXXX. ... 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:25:46 PM Error: 2008-04-14 13:25:57.97 Code: 0x00000000 Source: Log Package error of Job Description: String or binary data would be truncated. End Error Error: 2008-04-14 13:25:58.03 Code: 0xC002F210 Source: Log Package error of Job Execute SQL Task Description: Executing the query "EXEC usp_Log_Event @App_ID = 'ORD', @Comp_ID = 'DailyLoad-CMIDW1', @Comp_Start_Dt = '4/14/2008 1:25:57 PM', @Task_Start_Dt = '2008-04-14 13:25:57.958', @Task = 'Log Package start', @Status = 'Error', @Msg = '0: String or binary data would be truncated.', @Node = 'xxxxxx', @Executor = 'RELXXXXX" failed with the following error: "The statement has been terminated.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connecti... The package execution fa... The step failed.
If you look at the stored procedure call, it has the message "0: String or binary data would be truncated." This is the message that should be logged to the logging db. However, the call itself appears to fail with "String or binary data would be truncated." Yet when we run the exact sp call, taken from the above error, against the db, it executes fine.
So we thought maybe when we moved to UAT the environment variable wasn't set properly and therefore the sp execution would fail.
Well, I tested that out on dev by changing the environment variable to trash and got this error (which is what one would expect):
Message Executed as user: RELXXXXX. ...o acquire connection "conn ALERTS". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2008-04-15 15:06:52.92 Code: 0xC00291EC Source: Log Package start Execute SQL Task Description: Failed to acquire connection "conn ALERTS". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Error: 2008-04-15 15:07:09.24 Code: 0xC0202009 Source: Controller CITILINK - Daily Load Connection manager "conn ALERTS" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005. An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Login timeout expired". An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "An error has occurred while establishing a connection to the . The step failed.
So it would seem that the UAT deployment is connecting to the logging database just fine, but is generating some kind of truncation error that seems impossible to reproduce on dev. We have tried everything we can to reverse-engineer the error on dev...
Anyone? There are bits of brick stuck to my forehead... =) Thanks a lot.
I have an excel file with 2 rows that I am importing into a staging table. The format on the excel sheet is a custom one which is DD-MMM-YY so in the actual cell the data appears as 01-NOV-07 and 30-OCT-07. The format of the data in the input window above the spreadsheet is 01/11/2007 and 30/10/2007.
The format in the Excel source of the data flow task is Unicode String [DT_WSTR] length 255. I then have a data conversion step that changes this to a string [DT_STR] length 255 as the staging table is non unicode. I then have a derived column function that simply does a ISNULL replace on NULL values to blank. Finally there is an OLE DB destination which is the staging table.
This is where the problem occurs. If I breakpoint the package here and look at the staging table the dates now read as 11/1/2007 and 10/30/2007. The data type in the staging table is varchar 255. I am not doing any other transformations or T-SQL stuff on the data - it is being flipped to MM/DD/YYYY during the import.
By the way I am english hence I need the english variation on the date. I know i could put some T-SQL in to flip it around to english again but wondered why it was happening like this
Hey all, I need your help again..We are having many domains. Actually we have created SQLgroups in sql and granted permissions in our domain. But guys from other domains are just entering into their query analyser and accessing the sql server..They are not at all falling under our SQLgroups. We have given trusted connections between domains.. My question here is Is there anyway that there should be an option to select the domain before they log in thru query analyser? Like Nobody should enter from their domain ...only through our domain they should enter..? Please help me out..
Hi, I have two SQL servers in different domains(A and B), I cant use the Enterprise Manager to register eachother...they show me "Sever doesnt exist" but when I use ping I can show them.... Please help me.....What can I do?
I'm attempting to set up a dts transfer SQL 7 box to SQL 7 box. These two servers are on two separate NT domains with no trust relationship, and I will be sending the info across a VPN.
Anyone out there have a similar situation? Offer any recommendations, pitfalls, ports used, ways to do this??? I'd appreciate any ANY ideas on how to make this work. Thanks in advance. -Tricia
Is there any way to connect to SQL Server from a non trusted domain. Passthrough authentication works for other NT Server resources (like exchange folder, printers, shared folders), but SQL Server 7.0 does not seem to accept this passthrough authentication (where the username and password are the same in both domains). There is no internet access required.
I need to move three sql 6.5 servers over from one domain to another domain. What is the best way to do it and what should I look out for? I am really new at this so any help would be great.
Company with one head office and one remote office. In the two offices I have two domains with two PDCs. The two networks are connected with eachother through leased line and the routers are configured properly. The SQL Server is on the PDC in head office and "local" clients connect fine. I cannot connect from the remote office. I think that I have folowing solutions:
1. make trust relationships between two domains - it will be hard a little bit because second PDC is samba on linux
2. make all clients in remote office to be members of the head office domain - potential problems if the leased line drops
3. make all clients to log in with same account as SQL Server logs locally - stupid
I have a sql-table. The domain of one of my columns a fix number of string values (like "blue", "green" and "yellow"). However, there is a big likelyhood that the domain will be expanded by more string values in the future (however, none of the existing string values will ever be discluded from the domain). How would I go about enforcing that all strings added to my column in my table is within my domain?
Should I use a check constraint, or create a domain, or sholud a create another table with the domain values and use a foreign key? What would be best practise for my problem?
I not too hot on networking. When I type "IPCONFIG /ALL" the "Prmary DNS Suffix" is empty. Does this mean I have no domain? Can I configure a mirror on this server?
I would like to replicate databases from an SQL 2000 Server to a SQL 2005 Server.
a. First can this be done?
b. Does it matter if both servers are on different domains?
Secondly
If all is possible from my above questions, in order for me to enable replication on the SQL 2000 Server, i need to rename the server from LOCAL to a realname. I get an error message saying that it can not use nicknames.
a. If I go ahead and make a New SQL Server Registration, could this have a major impact on the applications using the Database server?
b. When renaming, do I first Delete the current server registration or should I first create the new one then delete the old one?
Viual Studio 2003; ASP.Net; .Net Framework 1.1, SQL Server 2000Hi,We have two domains in our network - prod.domain.com and dev.domain.com. There is no trust relationship between the two domains. I am trying to connect to an SQL Server in dev.domain.com using SQL Server Authentication from prod.domain.com. My connection string uses the fully qualified domain name (xxxxxx.dev.domain.com) to connect. The error I receive is SQL Server does not exist or access denied. I can access the database through SQL Query Analyser and SQL Enterprise Manager across the domains with no problems. I can also connect through a .udl file using the same SQL Server Authentication information.If I run my application from dev.domain.com accessing a database that sits in prod.domain.com then the connection is fine.Can someone point me in the direction of any areas I can troubleshoot to see what is preventing the connection please? The only things I can see on the web are details of windows authentication across domains which isn't relevant to me in this instance. I've been pulling what little hair I have out over this for the last week.Thanks in advance.
We are testing merge replication. Between servers on one domain, it works fine. Between servers on different domains, appears to work while configuring the Publisher and Push subscribers, but then errors appear after configuration. One error is that the Merge Agent could not connect to the subscriber.
The SQL Service accounts and passwords are the same on both servers. We also configured a one-way trust (maybe it is the wrong way)Subscribers trust the Publisher, and SQL service accounts added to local admin group on subscribing systems.
Anything else I should try?...modify publisher access list?
Hello, I have a problem scheduling an Object Transfer task on a server that is in a different domain than the server it is getting the data from. I can set up the Object Transfer through the Tools/DatabaseObject Transfer... option and run it manually (works great), but if I schedule it to run, it stops after about 30 seconds and gives an error message "Process Exit Code 1. Cannot connect to source server." Any ideas would be much appreciated. Thank you Ryan
We have installed SQL servers in 3 domains. We cannot see the servers in Enterprise manager in the other domains from any other domain. Trusted security is set up. Named pipes is the set protocol as we made no changes at install time. We can see the NT servers in the Server manager. All services are running. Is there something that I missed? Any help will appreciated.
I have a network that has 10 machines-6 of them have sql server registrations. I am able to access all 6 from any machine while they are on the domain. However I also need to take 5 of them off of the domain to a remote site and set up a workgroup. I am able to log in to each sql server from the workgroup login but I am not able to run software on one machine that connects to a sql server registration on another machine in the workgroup. I get a "sql server does not exist or access denied" message. Further, when I open enterprise manager, the other sql servers don't show up and I am unable to add them using the wizard.
We have two seperate domains (connected via a VPN Tunnel over the internet) We want to replicate one table with a push publication between these two domains (both have sql server 2005 default instances) how do you figure out what ports to open up in order to be able to do transactional replication (of one table) between them????
Hi i wanna connect to a server that is installed with domain registration and using both sql and windows authentication.on this server i have created database and login for that respective database,but when i am trying to connect this server it gives me error. The login that i m using is able to connect when i m trying to connect server from console of the server.Please help me..
Hi everybody,there are several SQL-Server 2000 databases within a company locatedon different servers in different domains. On every database you canfind the same table X.I want to merge these tables X (UNION query) and print the result witha Crystal Report.Unfortunately I only have little knowledge on security, domains,distributed applications.Thanks for help.
I am testing the e-mail subscriptions to my hotmail account in anticipation of sending e-mail reports to our business partners. I have made the following to the RSreportserver.config file:
We have 2 servers each having a domain (i.e. 2 domains) running Windows Server 2003 Standard Edition Service Pack 1 We would like to have one of the servers replicate/mirror the data on to the other but we are getting this error The error produced from the mirroring part is:
TITLE: Database Properties ------------------------------ An error occurred while starting mirroring. ------------------------------ ADDITIONAL INFORMATION: Alter failed for Database 'CTSM'. (Microsoft.SqlServer.Smo) ------------------------------ An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo) ------------------------------ The server network address "TCP://UM_db_live.UMlive.local:5022" cannot be reached or does not exist. Check the network address name and that the ports for the local and remote endpoints are operational. (Microsoft SQL Server, Error: 1418) Thank you.
