Can anyone point to a reference which documents the pros and cons of the various connection protocols, such as Shared Memory vs. TCP/IP? I thought I saw something indicating that shared memory is fastest, which would explain why this protocol is tried first, but now I can't find it. This resource has information on creating connection strings, but not the advantages and disadvantages.
http://msdn2.microsoft.com/en-us/library/ms187662.aspx
I am trying to find some info on the fastest connection transport for an app that is running on the same box as a SQL 2005 instance. The app does a large number of updates (high volume of data).. win32 using native ODBC. I am trying to find info on which connection mechanism is best... socket, pipes, etc. I read somewhere that there is a file mapped method available but i cannot find info on that either. Also, is there any performance difference between the old SQL OCBD drive and the new SQL Native Client OCBD drive?
Thanks.
Hi,
In a stored procedure I have, I need to make a call out to a DR server, I believe the named pipes protocol is causing errors for long running sql. Is there anyway I can change the call
exec svr_dr.msdb.go
to ensure that it uses the tcp protocol rather than named pipes?
Thanks,
JS
Hi everyone,
I want to use TCP/IP network protocol on the server and have my clients using TCP/IP Net library. I have having some problems with this...The server will not register if I only have TCP/IP without Named Pipes using Standard Security in SQL Server. If I have TCP/IP and Named Pipes, the client can't register the server. Can someone explain in detail to me on using TCPIP on the server and client?
Thanks...
Has anyone ever seen TCP/IP become a disabled protocol without manual intervention from a user?
My websites just stopped talking to my MSSQL db and when I went into sql client config utility on the web boxes, tcp/ip was disabled. Any clues? Can't find anything to support this in the event or web logs.
If using sql server authentication between two SQL Server instances (firewalled and on different networks),how are the connection details transported, specifically, is the IP addess, username and password sent in clear or is their some internal obsfuscation/de-obsfuscation mechanism native to SQL Server in operation under the covers?
View 1 Replies View RelatedI am connection to a SQL server that has force protocol encryption checked. From my ASP.net application, specifically in my connection string, what parameter do I need to use to take advantage of this encryption?
Thanks!
Hi everybody,
I dont know if this is the right place to put in this question.
The problem is
I have an application developed using VB 6.0 and SQl Server 2000. It was working fine on my machine. I had MS XP installed on my system alongwith SP1. But when i installed MS XP SP2, i got the error while saving the record.
the error is
"Protocol Error in TDS Stream".
I went to microsoft site searcing for the resolution of this error. They gave the solution of installing MDAC 2.8 SP1. But when i install MDAC 2.8, it gives me this error
"MDAC 2.8 RTM is incompatible with this version of Windows. All of its features are currently part of Windows."
And since MDAC 2.8 is not installed so i can not install MDAC 2.8 SP1 also.
I have MDAC 2.5 installed on the system.
How can i resolve it. Please give a solution as soon as possible. I am in great need of it.
I am using SQL Server 6.5. By default TCP/IP protocol is not enabled (1433 port is not opened by the server). How can I enable this so that I can use a JDBC driver to connect to it.
In SQL Server 2000, the "SQL Server Network Utility" helps us to enable the TCP/IP protocol. But not able to figure our how/where this can be done in 6.5.
Thanks for any help
hi all,
Happy new year!
is anybody here familiar with the different informix ODBC protocol
such as Sesoctcp and onsoctcp and the rest of the protocols
I think my SSIS design is affected by this protocols i'm having problems with
the unicode and non unicode conversion.
many many thanks
Joey
Hello,
I cannot connect to a SQL Server that is in domain "x" from my client which is in domain "y" using
Multi-Protocol. There is no trust between domains x and y. The SQL Server has Multi-Protocol installed and is working
correctly ie. I can connect to it from other clients in the same domin via Multi-Protocol.
I have tried multiple things to make this work. Adding the SQL Server to my LMHOST file. Adding advanced settings for the
SQL Server in my "SQL Client Configuration Utility".......All with no avail. I have received different error messages which I have
found little about. "ConnectionOpen (RPC open())", "OS ERROR 1703", "OS ERROR 11".......
Has anyone had similar problems? If so, is there a fix or something that I could be missing?
Thanks in Advance!!
Tim
Hello ,My question is:does ms sql support two phase commit protocol?and Why if no.How if yes.Thank you
View 2 Replies View RelatedOur client is trying to access the report builder from there machine via a proxy and it does not work, What would be all the ports and protocol (http,https,ftp) needed to be open for the report builder application to work normally behind a proxy? or does anyone know what are the ports and protocol used by report builder ? because we need to provide this information to our client so they can access our report server.
Chi.
I have a 2 node SQL Server 2005 cluster with 2 named instances. Both appear
to be working properly with no issues but I have the following warning
message in the event viewer and I would like to know if it is something I
should be worried about.
Source: SQL Browser
Type: Warning
Category: None
User: N/A
Description: The configuration of the AdminConnectionTCP protocol in the
SQL instance XX is not valid.
The servers in the cluster are running with Windows Server 2003 X64 Enterprise
Edition, SQL 2005 Enterprise Edition SP2 X64.
I have failed the cluster over and I continue to get the message when I
start the instances in the cluster admin, but I don't appear to have any
other symptons. Client machines can connect without any problem and
transactional replication works no matter what node I bring the instance up
on.
I changed default TCP/IP port and disable/enable Named pipes, but no luck so far.
I see lots of postings about this message but it seems to be related to
SQLExpress and I am not using that here.
Any info would be appreciated.
Scenario: Private SQL server that has a v6 public facing address. How can I disable the interface over my public address only but keep functionality over the local address?
IP1: 192.0.0.1
Active: Yes
IP2: 2600::5
Active: Yes
IP3: fc2e::5
Active: Yes
When I try to change IP1 and IP2 to Active: No it tells me file is read only.
I am loading data from one server to another server using SSIS,After loading some rows i am getting the below mentioned Error.Each time i run i am getting the error in different Dataflow tasks in the package.
Error :
"
[OLE DB Source [199]] Error: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Server Native Client 11.0" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
[SSIS.Pipeline] Error: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED.
The PrimeOutput method on OLE DB Source returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing.There may be error messages posted before this with more information about the failure.".IS this SSIS package Error or Network related?
Can anyone help with this problem?
I have a green light on my Report Services Confuration, and can configure the server within sharepoint integrated mode. I have also assign the appropriate permission for aspnet etc..etc.
From within Business Intelligent studio when I tried to publish my reports I get the below error:
The server commited a protocol violation
Here is a copy of the report server services log file.
<Header>
<Product>Microsoft SQL Server Reporting Services Version 9.00.3042.00</Product>
<Locale>en-US</Locale>
<TimeZone>GMT Daylight Time</TimeZone>
<Path>C:Program FilesMicrosoft SQL ServerMSSQL.3Reporting ServicesLogFilesReportServerService__main_06_21_2007_10_47_38.log</Path>
<SystemName>DMTXDW01VM2</SystemName>
<OSName>Microsoft Windows NT 5.2.3790 Service Pack 1</OSName>
<OSVersion>5.2.3790.65536</OSVersion>
</Header>
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing ConnectionType to '1' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing IsSchedulingService to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing IsNotificationService to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing IsEventService to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing PollingInterval to '10' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WindowsServiceUseFileShareStorage to 'False' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MemoryLimit to '60' percent as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing RecycleTime to '720' minute(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaximumMemoryLimit to '80' percent as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaxAppDomainUnloadTime to '30' minute(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaxQueueThreads to '0' thread(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing IsWebServiceEnabled to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaxActiveReqForOneUser to '20' requests(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing MaxScheduleWait to '5' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing DatabaseQueryTimeout to '120' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing ProcessRecycleOptions to '0' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing RunningRequestsScavengerCycle to '60' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing RunningRequestsDbCycle to '60' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing RunningRequestsAge to '30' second(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing CleanupCycleMinutes to '10' minute(s) as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing DailyCleanupMinuteOfDay to default value of '120' minutes since midnight because it was not specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WatsonFlags to '1064' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WatsonDumpOnExceptions to 'Microsoft.ReportingServices.Diagnostics.Utilities.InternalCatalogException,Microsoft.ReportingServices.Modeling.InternalModelingException' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WatsonDumpExcludeIfContainsExceptions to 'System.Data.SqlClient.SqlException,System.Threading.ThreadAbortException' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing SecureConnectionLevel to '0' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing DisplayErrorLink to 'True' as specified in Configuration file.
ReportingServicesService!library!4!21/06/2007-10:47:39:: i INFO: Initializing WebServiceUseFileShareStorage to 'False' as specified in Configuration file.
ReportingServicesService!servicecontroller!9!21/06/2007-10:47:46:: i INFO: Total Physical memory: 1073094656
ReportingServicesService!servicecontroller!4!21/06/2007-10:47:47:: i INFO: RPC Server started. Endpoint name ='ReportingServices$MSSQL.3'
Urgent help required!
Hello,
I am getting the following warning in the Application Event Log:
The configuration of the AdminConnectionTCP protocol in the SQL instance SQLEXPRESS is not valid.
The warning is logged by the SQLBrowser service.
This is happening on Windows 2003 server with SQL Server Express 2005. On the same machine SQL Server 2000 is installed.
I am currently unable to connect from remote clients to the instance of SQL Express and I believe it has to do with this warning (I've tried all the usual remote connection troubleshooting without success - enabled protocols, SQL Browser Service running, there is no firewall on the server, I disabled firewall on the client when trying to connect).
Does anyone know how to resolve this issue?
If you need additional information please let me know.
I am accessing a SQL2005 Express SP1 Server via TCP/IP (all machines fully up to date with windows update). For ease of configuration the Server IP is mapped to an Alias in Native Client Configuration Manager. On the Client Force Protocol Encryption is set to Yes and Trust Server Certificate is set to No. The clients are accessing the DB via SQL 2005 Management studio express and Ms Access 2003 (an access adp front end to the sql database).
As a test i turned off force encryption on the server. A Network trace shows no encryption! Turning on force encryption on the server corrects this.
As a further test I configured the server with a certificate the client doesn't trust. No error is generated by the un-trusted certificate! The only way to generate the certificate error is to enable encryption in the connection properties of each application. Why isn't the Native Client Configuration Manager utility enforcing this???
This brings me on to a second (off-topic problem). In my access adp file, if I enable €œuse encryption for data€? advanced option under the database connection dialog all works as expected (encryption + certificate verification). However, I am programmatically updating the connection in VBA
Application.CurrentProject.OpenConnection "Provider=SQLOLEDB.1;Data Source=AliasName;Use Encryption for Data=True;..."
If I do this no encryption is enabled!!! Access seems to just ignore the encryption statement.
Has anybody got any ideas as to why either of these issues are occurring or if there are any workarounds?
Thanks
Stuart
I've been reading about the protocols and i've been trying to connect to my SQL SERVER using each one of the protocols, but i think I'm missing something, i dont have any problems using Shared Memory and TCP protocols, but I'm not pretty sure how to connect to my remote SQL SERVER using NAMED PIPES protocols. The books On Line of Microsoft refer that you can connect using this protocol using the next syntax: Connecting to a default instance by specifying a named pipe name:
APPHOSTpipeunitapp
Connecting to a named instance by specifying a named pipe name:
APPHOSTpipeMSSQL$SQLEXPRESSSQLquery
Connecting to default instance by name, forcing a named pipes connection:
np:APPHOST
Connecting to named instance by name, forcing a named pipes connection:
np:APPHOSTSQLEXPRESS
But I can't connect to my remote SQL SERVER, if i try the same with my local SQL SERVER i don't have any problems. I've been reading the articles so many times searching something about Named Pipes is only for local connections, but Microsoft say that it's posible to connect to a remote SQL SERVER, but i don't know how to do that. The firewall is disabled, I changed the APPHOST in the above syntax for the IP address of my SQL SERVER but it didn't work..
right now I have a stored procedure that goes through each of the Line and Body fields using a cursor. The problem is that this method is very slow. How would you experts solve this problem? any Hints or suggestions?
BEFORE
EXAMPLEPartLineBodySeriesEngineYear
11234A,BWETC1998
25678991,93,94,95WET01997
3345656S,R5,6,12WENC1995
AFTER
EXAMPLEPartLineBodySeriesEngineYear
11234AWETC1998
11234BWETC1998
25678991WET01997
25678993WET01997
25678994WET01997
25678995WET01997
3345656S5WENC1995
3345656S6WENC1995
3345656S12WENC1995
3345656R5WENC1995
3345656R6WENC1995
3345656R12WENC1995
Hi,
In my SQL server 7.0, I have got 250 store procedures in each database.
Before using them for my application, I want to ecyption all.
I must add "WITH ENCRYPTION" string in each SP in all database and it'll take me a long time. Is there fastest way to encryption all SPs in all DBs? Have anyone got an utility SP ( or anyway else) to do this?
Thanks in advance.
What is the fast way a stored procedure can copy a table from a linked server?
I would like to tune this statement, possibly with hints or other logging options. Assume that table_A and table_B have the exact table structure and that I want to preserve table_A and all its indexes and contraints. The table will be truncated before this load, if that helps in any way.
insert into table_A select * from OpenQuery(Server,'select * from Table_B')
TIA, Mike
In relation to my last post, I have a question for the SQL-gurus.I need to update 70k records, and mark all those updated in a specialcolumn for further processing by another system.So, if the record wasKey1, foo, foo, ""it needs to becomeKey1, fap, fap, "U"iff and only iff the datavalues are actually different (as above, foobecomes fap),otherwise it must becomeKey1, foo,foo, ""Is it quicker to :1) get the row of the destination table, inspect all valuesprogramatically, and determine IF an update query is neededOR2) just do a update on all rows, but addingand (field1 <> value1 or field2<>value2) to the update querythat isupdate myTablesetfield1 = "foo"markField="u"where key="mykey" and (field1 <> foo)The first one will not generate new update queries if the record hasnot changed, on account of doing a select, whereas the second versionalways runs an update, but some of them will not affect any lines.Will I need a full index on the second version?Thanks in advance,Asger Henriksen
View 2 Replies View RelatedHi,
1)I need to transfer 500 gb of data from one server to other, which is faster, DTS/BCP/Restore.
2)Which are the best methods for checking blocking, dead locks & Indexes!
Thanks you all in advance
Richard..
I have a master table which has demographic data such as name, dob, location along with a primary key id. It will have about 10-12000 records. We get a refresh file every hour which may or may not have corrections for these records hourly with about 3,000 records. I put this data into a table. This data should be considered always to be correct. To handle the update to the master table I need to create an update process. I can take one of two approaches, just update all the records in the master table regardless if they are correct or not, or do some type of left join on those that do not match (in other words, only update the ones where thae names or dob don't match) There is an underlying update trigger on the patient master which will also fire if these values are changed. An opinions on a best approach?
View 1 Replies View RelatedHi,
I have a production server that has an 8Gb db. It is dual Xeon with 5x HDD - 2 mirrored and 3 striped. db on stripe, log and OS on mirror. 2x Gb network cards.
The application goes slow (ie users notice) when a backup is running so i have placed a crossover cable from one NIC to a test server so that it can back up to a HDD on that server, and then to tape. The test server has 2xGb NIC and the link between the two servers is on a seperate subnet to However, in the first trial of this the back up and verify takes 3 minutes longer.
Is this because the target server doesnt have a disk stripe?
What is the best config for the production server (ie will a slower backup but to another server be less load to contend with the application)?
thanks
Fatherjack
I've got a view that is driven from a 80 million record table in a data warehouse. I am trying to populate an aggregate table in a datamart, but am running into preformance problems. The datamart table needs to be updated daily. I understand there are many factors that effect performance, but in general would the fastest approach be:
1) Truncate the datamart table
2) Perform a bcp of the view to a text file
3) Bulk Insert to the datamart table
If you need more information to answer this please let me know.
Thanks,
Matt
Hi!We have Sql Server 2000 in our server (NT 4). Our database have now about+350.000 rows with information of images. Table have lot of columnsincluding information about image name, keywords, location, price, colormode etc. So our database don?t include the images itself, just a path tothe location of every image. Keywords -field have data for example likethis:cat,animal,pet,home,child with pet,child. Now our search use Full-TextSearch which sounded like good idea in the beginning but now it have hadproblems that really reduce our search engine?s performance. Also searchresults are not exact enough. Some of our images have also photographer?sname in keywords -column and if photographer?s name is, for example, PeterMoss, his pictures appears in web-page when customer want to search "moss"(nature-like) -pictures.Another problem is that Full-Text Search started to be very slow when queryresult contains thousands of rows. When search term gives maximum 3000rows, search is fast but larger searches take from 6 to 20 seconds tofinish which is not good. I have noticed also that first search is alwaysvery slow, but next ones are faster. It seems that engine is just"starting" when first query started to run.Is there better and faster way to handle the queries? Is it better torebuild the database somehow and use another method to search than Full-Text Search? I don?t know how to handle the database other way when everyimage have about 10 to even 50 different keywords to search.We have made web interface and search code with Coldfusion. ColdfusionServer then take care of sending all queries to Sql Server.I hope that somebody have some idea how to speed up our picture search.--Message posted via http://www.sqlmonster.com
View 2 Replies View RelatedIm trying to dedupe a table with only one field on it. The table has40 million records in it. What is the fastest way?1) create a table with a unque constraint on it insert into thattable?2) create a table without a unique constraint on it and use insertinto table select distinct un from table2?3) another way?Michael
View 1 Replies View RelatedHi,
I'd like to know the fastest way to add named instances to SQL server 2005 (I need to add 5 named instances)
Thank you!
John
Hi All,
Im bulk loading a ton of data into MSSQL SERVER 2005 Standard Edition. I used to do this process in version 2000. It seems there is some more overhead in 2005. Is there a way to drop logging to almost null to speed up insert?
This is my current sql statment to load data.
EXEC sp_dboption 'my_stuff', 'select into/bulkcopy', 'true'
SET ANSI_WARNINGS OFF
BULK INSERT mystuff.dbo.[v1]
FROM 'c:myfile.txt'
WITH
(
FIRSTROW = 1,
FORMATFILE = 'c:scriptsv1.fmt',
MAXERRORS=2000,
ROWS_PER_BATCH=100000
)
Thanks,
Mike
I'm writing a program that allows users to upload a csv file. This file is then seperated into 4 datatables based on certain criteria then each datatable is uploaded into my database. I'm essentially adding new rows to the datatables then running an update command on each using a tableadapter. The problem is that these csv files can be large and can end up with 4000+ new records being added to the database and the update commands take a while to do it. I've sat for about five minutes on one run while it updated. I put in some time variable to see where all the time is spent and it takes only seconds to parse the data and seperate into the datatables, but minutes on the update commands. Is there a more efficient way to insert this much data?
View 4 Replies View Related