Problems Connecting Via Multi-Protocol
Jan 7, 1999
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
View 2 Replies
ADVERTISEMENT
Nov 15, 2004
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
View 2 Replies
View Related
Jan 7, 1999
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...
View 1 Replies
View Related
Mar 16, 2004
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.
View 3 Replies
View Related
May 9, 2008
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 Related
Aug 26, 2004
I 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!
View 1 Replies
View Related
Oct 13, 2006
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.
View 1 Replies
View Related
Apr 7, 2004
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
View 1 Replies
View Related
Jan 10, 2008
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
View 7 Replies
View Related
Dec 29, 2006
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
View 10 Replies
View Related
Dec 29, 2006
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 Related
Mar 25, 2008
Our 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.
View 1 Replies
View Related
Aug 28, 2007
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.
View 6 Replies
View Related
Jun 23, 2015
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.
View 3 Replies
View Related
Nov 19, 2013
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?
View 1 Replies
View Related
Jun 21, 2007
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!
View 2 Replies
View Related
Mar 4, 2006
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.
View 41 Replies
View Related
Feb 5, 2007
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
View 3 Replies
View Related
Sep 23, 2014
Disaster Recovery Options based on the following criteria.
--Currently running SQL 2012 standard edition
--We have 18000 databases (same schema across databases)- majority of databases are less than 2gb-- across 64 instances approximately
--Recovery needs to happen within 1 hour (Not sure that this is realistic
-- We are building a new data center and building dr from the ground up.
What I have looked into is:
1. Transactional Replication: Too Much Data Not viable
2. AlwaysOn Availability Groups (Need enterprise) Again too many databases and would have to upgrade all instances
3. Log Shipping is a viable option and the only one I can come up with that would work right now. Might be a management nightmare but with this many databases probably all options with be a nightmare.
View 1 Replies
View Related
Oct 5, 2015
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..
View 2 Replies
View Related
Aug 17, 2015
More often than not, I typically don't touch DTC on clusters anymore; however on a project where the vendor states that it's required. So a couple things here.
1) Do you really need DTC per instance or one for all?
2) Should DTC be in its own resource group or within the instance's group?
2a) If in it's own resource group, how do you tie an instance to an outside resource group? tmMappingSet right?
View 9 Replies
View Related
Feb 4, 2008
the stored procedure don't delete all the records
need help
Code Snippet
DECLARE @empid varchar(500)
set @empid ='55329429,58830803,309128726,55696314'
DELETE FROM [Table_1]
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
UPDATE [empList]
SET StartDate = CONVERT(DATETIME, '1900-01-01 00:00:00', 102), val_ok = 0
WHERE charindex(','+CONVERT(varchar,[empid])+',',','+@empid+',') > 0
TNX
View 2 Replies
View Related
Jul 20, 2005
Hello,I am trying to construct a query across 5 tables but primarily 3tables. Plan, Provider, ProviderLocation are the three primary tablesthe other tables are lookup tables for values the other tables.PlanID is the primary in Plan andPlanProviderProviderLocationLookups---------------------------------------------PlanIDProviderIDProviderIDLookupTypePlanNamePlanIDProviderStatusLookupKeyRegionIDLastName...LookupValue....FirstName...Given a PlanID I want all the Providers with a ProviderStatus = 0I can get the query to work just fine if there are records but what Iwant is if there are no records then I at least want one record withthe Plan information. Here is a sample of the Query:SELECT pln.PlanName, pln.PlanID, l3.LookupValue as Region,p.ProviderID, p.SSNEIN, pl.DisplayLocationOnPCP,pl.NoDisplayDate, pl.ProviderStatus, pl.InvalidDate,l1.LookupValue as ReasonMain, l2.LookupValue as ReasonSub,pl.InvalidDataFROM Plans plnINNER JOIN Lookups l3 ON l3.LookupType = 'REGN'AND pln.RegionID = l3.Lookupkeyleft outer JOIN Provider p ON pln.PlanID = p.PlanIDleft outer JOIN ProviderLocation pl ON p.ProviderID = pl.ProviderIDleft outer JOIN Lookups l1 ON l1.LookupType = 'PLRM'AND pl.ReasonMain = l1.LookupKeyleft outer JOIN Lookups l2 ON l2.LookupType = 'PLX1'AND pl.ReasonSub = l2.LookupkeyWHERE pln.PlanID = '123456789' AND pl.ProviderStatus = 0ORDER BY p.PlanID, p.ProviderID, pl.SiteLocationNumI know the problew the ProviderStatus on the Where clause is keepingany records from being returned but I'm not good enough at this toanother select.Can anybody give me some suggestions?ThanksDavid
View 5 Replies
View Related
Mar 27, 2007
I am new to Reporting Services and hope that what I am looking to do is within capabilities :-)
I have many identical schema databases residing on a number of data servers. These support individual clients accessing them via a web interface. What I need to be able to do is run reports across all of the databases. So the layout is:
Dataserver A
Database A1
Database A2
Database A3
Dataserver B
Database B1
Database B2
Dataserver C
Database C1
Database C2
Database C3
I would like to run a report that pulls table data from A1, A2, A3, B1, B2, C1, C2, C3
Now the actual number of servers is 7 and the number of databases is close to 1000. All servers are running SQL2005.
Is this something that Reporting Services is able to handle or do I need to look at some other solution?
Thanks,
Michael
View 5 Replies
View Related
May 22, 2006
I've read the other posts related to this issue, but I'm just REALLY confused as to whats happening in my case. Like everyone else it was working fine in SQL 2000 but now in SQL 2005 there is an issue. I'm calling a stored procedure with parameters defined like this:
@action varchar(10),
@GLTransactionID int = NULL OUTPUT ,
@GLBatchID int = NULL ,
@GLAccountID int = NULL ,
@CurrencyID int = NULL ,
@LocalDebit decimal(28, 13) = NULL ,
@LocalCredit decimal(28, 13) = NULL ,
@BaseDebit decimal(28, 13) = NULL ,
@BaseCredit decimal(28, 13) = NULL ,
@TransID int =NULL,
@Description varchar(255) = NULL
I am calling this proc from VS.NET 2003 using the .Net SqlClient Data Povider (C#). I'm setting the values of the parameters like this:
cm.Parameters.Add("@action", "insert");
cm.Parameters.Add("@GLBatchID", _gLBatchID.DBValue);
cm.Parameters.Add("@GLAccountID", _gLAccountID.DBValue);
cm.Parameters.Add("@CurrencyID", _currencyID.DBValue);
cm.Parameters.Add("@LocalDebit", _localDebit.DBValue);
cm.Parameters.Add("@LocalCredit", _localCredit.DBValue);
cm.Parameters.Add("@BaseDebit", _baseDebit.DBValue);
cm.Parameters.Add("@BaseCredit", _baseCredit.DBValue);
cm.Parameters.Add("@TransID", _transID.DBValue);
cm.Parameters.Add("@Description", _description.DBValue);
When I execute the call to the stored proc I get this:
"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 8 ("@BaseDebit"): The supplied value is not a valid instance of data type numeric. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision."
Using the VS.NET command window I then inspect that parameter to see what the heck is going on and get this:
?cm.Parameters["@BaseDebit"].SqlDbType
Decimal
?cm.Parameters["@BaseDebit"].Precision
0
?cm.Parameters["@BaseDebit"].Scale
22
?cm.Parameters["@BaseDebit"].DbType
Decimal
?cm.Parameters["@BaseDebit"].Value
1000000
[System.Decimal]: 1000000
So I set a decmial parameter to 1,000,000, that parameter in the DB is defined as decimal(28,13) so should fit no problem, but it seems the Sql data provider is confused and thinks 1,000,000 is decimal (0,22)???
View 5 Replies
View Related
Jul 20, 2005
Greetings,We are trying to set up a set of "Leading Practices" for ourdevelopers, as well as ourselves, and hope some gentle reader canrecommend some documentation in favor of what appears to be the rightposition to take.We do not allow third party applications to run on our SQL Servers. Wewant to include DTS Packages under the definition of third partyapplications, insisting instead that the developers save theirpackages as COM Formatted files into their source code control systemsand run them from their app servers. The devlopers would like to hearthis from someone besides ourselves.While strong recomendations to remove guest access to MSDB altogetherabound, I have been unable to find a straight forward discussion ofthe advantages of structured file storage and app server off load ofDTS packages.Can anyone suggest any articles, white papers, rants, etc attemptingto formulate a solution to the benefits of taking msdb away fromguest, with the advantages of running DTS from an App server orworkstation platform, with the packages protected in source codecontrol?Thank youJohn Pollinsjpollins @ eqt . com
View 2 Replies
View Related
Mar 22, 2001
Is it possible to create a stored procedure that can be used on multiple dBs??
For instance, I want to create a stored procedure and use it on DB1 and DB2.
Right now, I can create a stored procedure in DB1 but only I can run it.
I want to run the stored procedures on DB2 that I created in DB1..
I hope this makes sense..
Any insight into the workings of SPs would be most appreciated...
View 3 Replies
View Related
Mar 13, 2007
-- declare table declare @Table1 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table1select 1 , 2 union allselect 2 , 1 union allselect 7 , 2 -- declare table declare @Table2 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table2select 1 , 2 union allselect 5 , 1 union allselect 3 , 2 -- declare table declare @Table3 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table3select 1 , 2 union allselect 2 , 1 union allselect 3 , 2 -- declare table declare @Table4 table(ID int, Value int )-- Insert sample data provided into tableinsert into @Table4select 5 , 2 union allselect 2 , 1 union allselect 10 , 2 /*Is there anyway I can write one sql query which will give me the following result:IDs = All unique ID from all tablesSumOfAllValueFromAllTables =for the same ID, value from Table1+for the same ID, value from Table2 + so on ...IDs - SumOfAllValueFromAllTables===============================1 - 62 - 33 - 45 - 37 - 210 - 2*/--I have tried by the following way, but for more tables, it is becoming complicated, because I have 12 tables.--Is there any better way to do it? Thanks for the help.select coalesce(t1.ID,t2.ID) as IDs, coalesce(sum(t1.value),0) +coalesce(sum(t2.value),0) as SumOfAllValueFromAllTablesfrom @Table1 t1full join @Table2 t2 on t1.id=t2.idgroup by t1.ID,t2.IDorder by IDs
View 1 Replies
View Related
Jul 7, 2004
I have a search form that takes 5 inputs from textboxes or Drop Down Lists.
I built a query that works just fine in Analyizer, but the second i try to turn it into a sproc that takes parameters I can only get it to work with one parameter so far....
Here is the query that works just fine
Select ftr_location.loc_name, ftr_file.loc_id, ftr_file.file_date, ftr_file.file_type_id, ftr_file.acct_no,
ftr_file.cust_fname, ftr_file.cust_lname, ftr_status.status_name, ftr_file.destruction_date
FROM dbo.ftr_file
Inner Join ftr_location On ftr_location.loc_id = ftr_file.loc_id
Inner Join ftr_Status On ftr_status.status_id = ftr_file.status_id
Inner Join ftr_doc_types on ftr_doc_types.file_type_id = ftr_file.file_type_id
WHERE ftr_file.Loc_id = 18 and ftr_file.file_date= '12/4/2004'
GO
The sproc version that I cant get to work is:
CREATE PROCEDURE dbo.usp_ftr_searchfile_s
(
@ResultLocation int = null,
@ResultFileDate datetime = null
)
AS
DECLARE @SQLString VARCHAR(4000)
SET @SQLString = 'Select ftr_location.loc_name, ftr_file.loc_id, ftr_file.file_date, ftr_file.file_type_id, ftr_file.acct_no,'+
'ftr_file.cust_fname, ftr_file.cust_lname, ftr_status.status_name, ftr_file.destruction_date' +
' FROM dbo.ftr_file' +
' Inner Join ftr_location On ftr_location.loc_id = ftr_file.loc_id' +
' Inner Join ftr_Status On ftr_status.status_id = ftr_file.status_id' +
' Inner Join ftr_doc_types on ftr_doc_types.file_type_id = ftr_file.file_type_id' +
' WHERE 1=1 '
If @ResultLocation Is Not Null
Set @SQLString = @SQLString + 'And ftr_file.loc_id=' + cast @ResultLocation
If @ResultLocation Is Not Null
Set @SQLString = @SQLString + 'And ftr_file.file_date=' + cast @ResultFileDate
EXEC (@SQLString)
GO
Any feedback would be most helpful, as I can get it to work with one parameter but not when I add teh second in the mix.
View 1 Replies
View Related
Apr 12, 2005
I have a dataadapter on my asp.net page and am having issues passing values to the sql statement. See below:
SELECT tblTemp.SensorID, tblSensor.SensorNum, tblTemp.TempTime, tblTemp.TempVal FROM tblTemp
INNER JOIN tblSensor ON tblTemp.SensorID = tblSensor.SensorID
WHERE (tblTemp.TempTime BETWEEN @From AND @To)
AND (tblTemp.SensorID IN (@Sensor))
ORDER BY tblTemp.TempTime
@Sensor is populated in vb with the selected values from a checkboxlist control. I attempted to test this in the "Data Adapter Preview" window, but continually get an error. Is there a way to pass multiple values to a parameter?
Any help would be appreciated, thanks!
View 2 Replies
View Related
Jan 5, 2004
Hi All,
I am trying to update a huge table with about 70 million records and the table do not have a primary key.
I want to update a "FLAG" field in the table with value 'N' default value of flag fields for all records is NULL.
I want to update the whole table in chunks say 50,000 records a time...
like
update tablename (first 50000)
set flag = 'N' where flag = NULL.
Hope I am clear..:-)
thanks
hardlyworking..
View 7 Replies
View Related
Jul 21, 2004
I have an app that is critical to our business. It handles and syncronises several SQL Servers, checks integrety etc. I need to make the app so it can run a few things at once. Does anyone have any experience with this? Currently we use Delphi and ADO. I have been fiddling with DMO to get more performance - I am not sure ADO is very quick for some I tasks I need to do.
I suppose my main question *really* is does ADO/DMO multi-thread and has anyone tried it. If not how do people do it?
View 3 Replies
View Related
Nov 5, 2013
Im trying to filter the word starts with SAG,WAG,DGA from the table:version of column versiondescription
tablename:version
versionid versiondescription
********** *******************
1 sag buildagreement
2 wag buildagreement
3 dga buildagreement
My existing query:
Declare @GuildFilter varchar(1000)
declare @Guild varchar(1000)
set @Guild='DGA,WGA'
set @GuildFilter= '['+ replace(@Guild,',','-') +']%'
[code]...
when i try to pass only DGA and WGA,query also picks SGA .
View 2 Replies
View Related
