I have a stored procedure with a where clause like this:
WHERE
Q.EffectiveDate >= @FromEffectiveDate
AND
Q.EffectiveDate <= @ToEffectiveDate
AND
I.InsuredName LIKE '%' + isnull(@PreQuoteDesc,I.InsuredName) + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
AND
rsu.FirstName LIKE '%' + isnull(@OwnerFirstName, rsu.FirstName) + '%'
AND
rsu.LastName LIKE '%' + isnull(@OwnerLastName, rsu.LastName) + '%'
AND
Q.quoteID = isnull(@quoteID,Q.QuoteID)
AND
Q.QuoteStatusID = isnull(@quoteStatusID, Q.QuoteStatusID)
AND
rsu.AspNetUserID = isnull(@ASPNetUserID, rsu.AspNetUserID)
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%'
AND
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
If the user checks just 'Property' results look like:
Property
Property
Property, General Liability
If the user checks just 'General Liability' the resultes look like:
Genral Liablility
General Liability
General Liability, Inland Marine
If the user checks both Property and General Liability all they get back is:
Property, General Liability
They should get back everything including just Property or just General Liability or both.
So I tried to change the ANDs to ORs and it doesn't work.
-----------------------------------------
AND
(
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBProperty,Q.LineOfBusinessDescription,'') + '%'
OR
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBGeneralLiability,Q.LineOfBusinessDescription,'') + '%'
OR
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBInlandMarine,Q.LineOfBusinessDescription,'') + '%'
OR
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBMotorTruckCargo,Q.LineOfBusinessDescription,'') + '%'
OR
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBOwnersContractorsProtective,Q.LineOfBusinessDescription,'') + '%'
OR
isnull(Q.LineOfBusinessDescription,'') LIKE '%' + coalesce(@LOBSpecialEvents,Q.LineOfBusinessDescription,'') + '%'
)
I know this is incredibly hard to follow because its incredibly hard to write out.
Is there anyone smart out there who can figure this out?
I'm trying to connect to my SQL, and both ways I tried with CLI work. However, I have no luck with native C++, with the same string. Any ideas why? Here's the error message I get, and the source.
However, I cannot get the same to work via RMO. The error i get back is "The process could not connect to subscriber "subscribername". What am I missing? Any help and feedback is greatly appreciated.
Dim _mergeAgent As MergeSynchronizationAgent = New MergeSynchronizationAgent()
I have a longstanding problem where Stored Procedures or complex T-SQL called from VB.NET will not populate a DataTable object, but will work fine with a DataSet. For example: 'oConn is defined elsewhere... Dim sErr as String = "" Dim dt As New DataTable If Not oConn Is Nothing Then Try Dim sSQL as String = "select 1" Dim oCommand As New OdbcDataAdapter(sSQL, oConn) oCommand.Fill(dt) Catch ex As Exception sErr = "Database Error: " & ex.Message Finally sqlCloseConnection(oConn) End Try End Ifthis works fine and my dt DataTable object gets one row. However using this as the SQL: Dim sSQL as String = "declare @foo table(mycol integer);insert @foo select 1;select mycol from @foo;"does not work. It executes with no errors, but the DataTable has no rows. Finally, if I replace the DataTable with: Dim ds as DataSetI can then get the data in ds.Tables(0) no problem.So, if the results of the sql are a single result table being put at index 0 of a DataSet, why are they not being put in a single DataTable?When a sql is a simple select statement it always works directly to a DataTable. Only when it's a SP or sql with some logic does it require the DataSet approach. This is a reporting utility so I need to standardize the code though the sql will be dynamic. Any ideas?
I have scheduled a job in Management Studio, but it doesn't work. However, when I run it maually in Visual Studio it works. I have connected an outside server by mapping it to mine. Maybe this is the problem?
I have also tried to configure a linked server, but I cannot find out how to connect my SSIS package to the linked server.
I have many jobs on sql 05 and all work but one. This one writes to an Access DB on the same server as SQL. The package works fine. But when executed in the context of the SQL Agent job, it fails.
Jobs that write to a text file work fine. The Access DB has no password required. By the way, that job in sql 2000 worked fine.
I have an SSIS package is made up of SQL tasks and dataflows. The dataflows connect to an Oracle database using Native OLE DBOracle Provider for OLE DB (10g). This is the first package dealing with oracle that runs on the server. I can execute the package manually by right clicking and going to 'Run Package' while logged in remotely from the server, but it gets hung up and does nothing if I run it as a job. I always have to quit the job. I can disable everything but the dataflows in the package and the job completes and runs fine.
I have the following query which works fine when it's executed as a single query. but when i union the result of this query with other queries, it returns a different set of data.
any one know why that might be the case??
select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone where contact.phone_num is not null and contact.status = 'New Contact' group by contact._id order by "recency" desc, "frequency" desc
i've included the union query here for completeness of the question
begin declare @current_date datetime set @current_date = GETDATE()
select top 100 _id, callback_date, priority, recency, frequency from ( ( select top 10 _id, callback_date, 10 priority, @current_date recency, 1 frequency --, DATEPART(hour, callback_date) "hour", DATEPART(minute, callback_date) "min" from topcat.class_contact where status ='callback' and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) -- all call backs within that hour will be returned and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) and (DATEPART(hour, callback_date) <> 0) order by callback_date asc --order by priority desc, DATEPART(hour, callback_date) asc, DATEPART(minute, callback_date) asc, callback_date asc ) union ( select top 10 _id, callback_date, 9 priority, @current_date recency, 1 frequency from topcat.class_contact where status = 'callback' and callback_date is not null and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) and (DATEPART(hour, callback_date) = 0) order by callback_date asc ) union ( select top 10 _id, callback_date, 8 priority, @current_date recency, 1 frequency from topcat.class_contact where status = 'No Connect' and callback_date is not null and (DATEPART(year, callback_date) <= DATEPART(year, @current_date)) and (DATEPART(dayofyear, callback_date) <= DATEPART(dayofyear, @current_date)) and (DATEPART(hour, callback_date) <= DATEPART(hour, @current_date)) order by callback_date asc ) union ( select top 100 max(contact._id) "_id", max(old_trans.date) "callback_date", 7 "priority", max(old_trans.date) "recency", count(*) "frequency" --contact._id, contact.callback_date from topcat.class_contact contact inner join topcat.MMTRANS$ old_trans on contact.phone_num = old_trans.phone where contact.phone_num is not null and contact.status = 'New Contact' group by contact._id order by "recency" desc, "frequency" desc ) ) contact_queue order by priority desc, recency desc, callback_date asc, frequency desc
I have a SP that works on SQL 2000 but not on 2005
It is just suppose to step through my code and insert values into tables where it finds the "ticked" values
here is apiece of my code. I hope it Helps.
Code Snippet INSERT INTO Members (ClientID, Name, Surname, Email, Username, Password, Active, WlcSent) SELECT ClientID, [Name], Surname, Email, Username, Password, Active, [Welcome Sent] FROM StageMemberUploading WHERE ID = @numValues
SET @CurrentValue = (SELECT SCOPE_IDENTITY()) IF @ClientID IS NOT NULL BEGIN INSERT INTO MemberUsergroup (MemberID, ClientID, UsergroupID) VALUES (@CurrentValue, @ClientID, @UsergroupID) END IF @DateOfBirth IS NOT NULL BEGIN INSERT INTO _MemberProfileCharacterValues (MemberID, OptionID, OptionValue) VALUES (@CurrentValue, 1, @DateOfBirth) END -------------------My Code Stops here ------------------------------ IF @Male = 'x' BEGIN INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID) VALUES (@CurrentValue, 2, 1) END IF @Female = 'x' BEGIN INSERT INTO _MemberProfileLookupValues (MemberID, OptionID, ValueID) VALUES (@CurrentValue, 2, 3) END
Hello to all, i have a problem with IN-Operator. I cann't resolve it. I hope that somebody can help me. I have a IN_Operator sql query like this, this sql query can work. it means that i can get a result 3418: declare @IDM int; declare @IDO varchar(8000); set @IDM = 3418; set @IDO = '3430' select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 )) but these numbers (3428 , 3430 , 3436 , 3452 , 3460 , 3472 , 3437 , 3422 , 3468 , 3470 , 3451 , 3623 , 3475 , 3595 , 3709 , 3723 , 3594 , 3864 , 3453 , 4080 ) come from a select-statement. so if i use select-statement in this query, i get nothing back. this query like this one:select * from wtcomValidRelationships as A where (A.IDMember = @IDM) and ( @IDO in (select B.RelationshipIDs from wtcomValidRelationships as B where B.IDMember = @IDM)) I have checked that man can use IN-Operator with select-statement. I don't know why it doesn't work with me. Could somebody help me? Thanks I use MS SQL 2005 Server Management Stadio Express Thanks a million and Best regards Sha
SELECT favorites.FID, favorites.filename, favorites.username, files.status, files.private, files.views, files.title FROM favorites INNER JOIN files ON favorites.filename = files.filename WHERE (favorites.username = @username) AND (files.status IS NULL) AND (files.private = @private)@private is manually set to 'no'@username is set to profile.usernamewhen I run the above query in microsoft sql server mgmt studio express on the database, it presents all the information i'm asking for very easily. however when i try to implement this query on a aspx .net2.0 page with teh sqldatasource and a gridview, no data is displayed.anyone know what my problem is?
Hello. I'm having troubles with a query that (should) return all therecords between two dates. The date field is a datetime type. The db isSQL Server 2000. When I try thisSELECT RESERVES.RES_ID, PAYMENTS_RECEIVED.PYR_ID,PAYMENTS_RECEIVED.PYR_VALUE, PAYMENTS_RECEIVED.PYR_DATE,CUSTOMERS.CUS_NAMEFROM RESERVES LEFT OUTER JOINPAYMENTS_RECEIVED ON RESERVES.RES_ID =PAYMENTS_RECEIVED.RES_ID LEFT OUTER JOINCUSTOMERS ON RESERVES.CUS_ID = CUSTOMERS.CUS_IDWHERE (PAYMENTS_RECEIVED.PYR_DATE >= '2006-03-20 00:00:00') AND(PAYMENTS_RECEIVED.PYR_DATE < '2006-03-27 00:00:00')on a "query builder" in visual studio, I get the results that I want.But when I use exactly the same query on an asp 3 vbscript script, Iget no results (an empty selection).I've done everything imaginable. I wrote the date as iso, ansi, britishformat using convert(,103) (that's how users will enter the dates),i've used cast('20060327' as datetime), etc. But I can't still get itto work. Other querys from the asp pages work ok. Any ideas?thanks a lot in advance
I am trying to set a variable with this default value using expression. This works in tsql but doesn't in ssis. Can anybody tell me what is wrong with this?
works fine in designer but when i load the report services I get the following error anybody know what to do there is one subreport with this report maybe the passing value but what could be wrong ????
Item has already been added. Key in dictionary: '9' Key being added: '9'
Anyone live in seattle (meet on cap hill)? I have been stuck for weeks now and can't wait any longer wasting time - i will pay someone to set me up correctly quickly. I doubt this will be resolved online, but here goes... I need a datagrid to bring up simple database info (northwind) instead of showing the error 'login failed for mycomputeraspnet'. I am using windows authentication and my string is connectionstring = datasource=(local)\netsdk;initial catalog=Northwind;integrated security=sspi; persistent security=false;, I have seen online they say alter the authorization in the app virtual directory-- is that the C/inetpub/wwwroot or the web config of my project? I did so in the web config of my project, and also I added the impersonate=true to same already. I shared all the folders and made sure they are not read-only. I am flabbergasted. Paul XP pro 2004 sp 2 vis studio 2002 w updates C# Asp web app project
i like to know how CTE works in sql server. i need to understand the flow of CTE. here i am pasting one example of CTE which i need to understand how works
1) when CTE is declared then why fields name is used ;WITH interval_cte(StartTime, EndTime) AS ? what is the use of these field name.i do not understand how this line works
SELECT EndTime, DATEADD(mi, 30, EndTime) FROM interval_cte WHERE EndTime < @EndTime
it has no alias define DATEADD(mi, 30, EndTime) ?how end time is increasing in loop ?which line increase end time value ?
When I execute the below queries it works perfectly where as my expectation is, it should break.
Select * from ChildDepartment C where C.ParentId IN (Select Id from TestDepartment where DeptId = 1) In TestDepartment table, I do not have ID column. However the select in sub query works as ID column exists in ChildDepartment. If I do change the query to something below then definately it will break - Select * from ChildDepartment C where C.ParentId IN (Select D.Id from TestDepartment D where D.DeptId = 1)
Shouldn't the default behavior be otherwise? It should throw error if column doesnt exists in sub query table and force me to define the correct source table or alias name.
create table TestDepartment ( DeptId int identity(1,1) primary key, name varchar(50) ) create table ChildDepartment ( Id int identity(1,1) primary key,
insert into [dbo].[tbl_FG_Alert_Count_All_Report] ([Date] ,[Count] ,[Rule Type]) SELECT TOP 10 [Date] ,[Count] ,[Rule Type] FROM [dbo].[tbl_FG_Alert_Count_All] where Count <>'0' and DATEDIFF(dy,date,GETDATE()) = 1 order by Date desc
When I ran this T-SQL statement in SSMS; I don't get any error and as expected, I can see new data in [dbo].
[tbl_FG_Alert_Count_All_Report] table.
Now I created one job with same T-SQL Statement. Job completes successfully with out giving any error message; But unfortunately I don't see any new data in [dbo].[tbl_FG_Alert_Count_All_Report] table. What would be the reason that I don't see new data when job completes successfully but I can see new data after executing same T-SQL statement in SSMS?
I have stored procedure on Server A which goes to ServerB to check and update table and then update on Server A as well.I have Trigger which suppose to execute stored procedure (as i mentioned above). But it failed with this error:--
Trigger code:-- CREATE TRIGGER [tr_DBA_create_database_notification] ON ALL SERVER AFTER CREATE_DATABASE AS --execute dbadmin.dbo.usp_DBA_Refresh_DBAdmin_Tables
Error:--The operation could not be performed because OLE DB provider "SQLNCLI11" for linked server "xxx" was unable to begin a distributed transaction.Process ID 62 attempted to unlock a resource it does not own: DATABASE 21. Retry the transaction, because this error may be caused by a timing condition. If the problem persists, contact the database administrator.
Same stored procedure, if i execute manually or if i create sql job and execute this stored procedure, it works just fine..In trigger also, if i execute start job which has stored procedure, it works.My question is,why it failed when i execute stored procedure in TRIGGER.
DECLARE @Teams AS TABLE(Team VARCHAR(3)) INSERT INTO @Teams SELECT 'IND' UNION SELECT 'SA' UNION SELECT 'AUS' select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
I have this sql stored procedure in SQL Server 2012:
ALTER PROCEDURE [dbo].[CreateBatchAndSaveExternalCodes] @newBatches as dbo.CreateBatchList READONLY , @productId int , @cLevelRatio int , @nLevelRatio int AS set nocount on;
I am using SQL 2014 RTM (may be it's time to upgrade).
I have the following view:
create view [dbo].[SiriusV_Max4SaleList] as select m.id as Max4SaleId, mt.[Description] as [TypeDescription], CAST(m.[type] as tinyint) as [Type], m.start_time as [StartTime], m.end_time as [EndTime],
[Code] ....
I am thinking I may want to remove CAST for department, category, item later on as I don't really care if these columns would be defined as key for my EF model, but I do want to search by these columns. Anyway, this is my current view.
I executed the following select statement once
select * FROM dbo.siriusv_max4saleList where department like 's%' or category like 's%' or item like 's%'
And I believe I got 29 rows initially. However, when I execute this statement now I'm getting just 13 rows. If I execute just the department like 's%' I am getting 0 rows although I can see in the first result a row where department has s in in.
I guess I keep it here since I've created the message already but now I figured out why I am not getting the expected result. I used the condition like 's%' and not like '%s%' which application is doing.
We have a maintenance plan running everyday for rebuild and re-organisation of indexes. But, somehow its getting failed. Here is the script that we are running for rebuild or re-org.
/* Script to handle index maintenance Tuning constants are set in-line current values are; SET @MinFragmentation SET @MaxFragmentation SET @TrivialPageCount
I have a a Group By query which is working fine aggregating records by city. Now I have a requirement to focus on one city and then group the other cities to 'Other'. Here is the query which works:
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Kansas City 800 Columbia 700 Jefferson City 650 Joplin 300
When I add this Case When statement to roll up the city information it changes the name of the city to 'Other Missouri City' however it does not aggregate all Cities with the value 'Other Missouri City':
Select [City]= CASE WHEN [City] = 'St. Louis' THEN 'St. Louis' ELSE 'Other Missouri City' END, SUM([Cars]) AS 'Total Cars' From [Output-MarketAnalysis] Where [City] IN ('St. Louis','Kansas City','Columbia', 'Jefferson City','Joplin') AND [Status] = 'Active' Group by [City]
Here is the result:
St. Louis 1000 Other Missouri City 800 Other Missouri City 700 Other Missouri City 650 Other Missouri City 300
I have setup SSRS 2000 and gotten it to work but I am having trouble with SSRS 2005. I can't access to reportserver anywhere on the network. The only way to get to reportserver is termserv into the server and hit it with http://localhost/reportserver The server is Windows 2003 server Standard Ed. running SQL 2005 SP2 and Sharepoint Portal Server 2007. Can somebody please help? Thank you.
If I just use a simple select statement, I find that I have 8286 records within a specified date range.
If I use the select statement to pull records that were created from 5pm and later and then add it to another select statement with records created before 5pm, I get a different count: 7521 + 756 = 8277
Is there something I am doing incorrectly in the following sql?
DECLARE @startdate date = '03-06-2015' DECLARE @enddate date = '10-31-2015' DECLARE @afterTime time = '17:00' SELECT General_Count = (SELECT COUNT(*) as General FROM Unidata.CrumsTicket ct
I've got a popular problem so i get a message that server acces denied! ..
But that what is different in my error.... When i use same setting same database and connection string (on MSDE server) there is no problem...
On SQL server i have got windwos authentication but i added all accounts as ASPNET and SA.... and when i try to connect by
RETTO - name of my server
server=RETTO;uid=sa;pwd=password;database=db1; or by Integrated Security=SSPIserver=RETTO;uid=RETTOASPNET;database=db1;
I CAN BROWSE RECORDS THERE ARE NO PROBLEMS WITH CONNECTION!!! but when i try to update or iinsert or delete something in database there becomame this error that access denied or server does not exist!!!
PLEASE HELP I'm FIGHTING WITH THAT FOR OVER 5 DAYS!!!
I MADE FOR MY ACCOUNTS (SA, ASPNET) ALL THINGS ALLOWED AS EXECUTING stored procedures.. OR ACCESING datatables with insert delete and update query WHERE IS THE PROBLEM!!!??
I'm having a strange problem with this but I know (and admit) that the problem is on my PC and nowhere else. My firewall was causing a problem because I was unable to PING the database server, switching this off gets a successful PING immediately. The most useful utility to date is running netstat -an in the command window. This illustrates all the connections that are live and ports that are being listed to. I can establish a connection both by running
TCP 81.105.102.47:1134 217.194.210.169:1433 ESTABLISHED
TCP 81.105.102.47:1135 217.194.210.169:1433 ESTABLISHED
TCP 127.0.0.1:1031 0.0.0.0:0 LISTENING
TCP 127.0.0.1:5354 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51114 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51201 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51202 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51203 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51204 0.0.0.0:0 LISTENING
TCP 127.0.0.1:51206 0.0.0.0:0 LISTENING
UDP 0.0.0.0:445 *:*
UDP 0.0.0.0:500 *:*
UDP 0.0.0.0:1025 *:*
UDP 0.0.0.0:1030 *:*
UDP 0.0.0.0:3456 *:*
UDP 0.0.0.0:4500 *:*
UDP 81.105.102.47:123 *:*
UDP 81.105.102.47:1900 *:*
UDP 81.105.102.47:5353 *:*
UDP 127.0.0.1:123 *:*
UDP 127.0.0.1:1086 *:*
UDP 127.0.0.1:1900 *:*
Both these utilities show as establishing a connection in netstat so I am able to connect the database server every time, this worked throughout yesterday and has continued this morning.
The problem is when I attempt to use SQL Server Management Studio. When I attempt to connect to tcp:sql5.hostinguk.net, 1433 nothing shows in netstat at all. There is an option to encrypt the connection in the connection properties tab in management studio, when I enable this I do get an entry in netstat -an, see below:
Amost as if it's trying the different ports but you get this time_wait thing. The error message is more meaningful and hopefull because I get:
A connection was successfully established with the server, but then an error occurred during the pre-login handshake. (provider: SSL Provider, error: 0 - The certificate chain was issued by an authority that is not trusted.) (.Net SqlClient Data Provider)
I would expect this as the DNS has not been advised to encrypt the conection.
This is much better than the : Login failed for user 'COX10289'. (.Net SqlClient Data Provider) that I get, irrespective of whether I enter a password or not.
This is on a XP machine trying to connect to the remote webhosting company via the internet.
I can ping the server
I have enabled shared memory and tcp/ip in protocols, named pipes and via are disabled
I do not have any aliases set up
No I do not force encryption
I wonder if you have any further suggestions to this problem?
I use the code below for updating data from a AS400 Liked server. I dont understend how the WHERE NOT EXISTS( sections work however usualy they do, in this case it does not andt I can't seem to find out why.
Does anyone see the error?
Thanks
--========================================= --Create a local temporary table that hold --all the data from the source table --=========================================
SELECT * INTO #TEMP FROM dbo.LINK_LTTSTOC
--========================================= --Remove table entries that are no longer --needed or that have to be updated --=========================================
DELETE FROM LTTSTOCK
WHERE NOT EXISTS( SELECT * FROM #TEMP
WHERE LTTSTOCK.WarehouseNo = LTWHLO
AND LTTSTOCK.Location = LTWHSL
AND LTTSTOCK.ItemNo = LTITNO
AND LTTSTOCK.NumberAvail = LTAVAL
)
--=========================================
--Insert data that is missing or that
--needed to be updated and was previously
--deleted
--=========================================
INSERT INTO dbo.LTTSTOCK(WarehouseNo,Location,ItemNo,NumberAvail,rowguid)
I'm running a DTS package that works correctly when I do "Execute Package" directly, by right clicking the package.
But, if I schedule the job, it fails.
It's trying to write data to another server, but I can't see why it doesn't work as a scheduled job. I tried changing all the parameters I can think of. I'm no Windows expert, so if you have any ideas, please feel free to explain as if you're talking to a "newbie" (since I am one)
I am going to tangle with our ERP system for the rest of my natural life, and in so doing, will need to create new SQL queries on an almost-daily basis. Based on the handful of queries that I have created against it so far, I recognize that I need to get better at authoring them.
Towards that end, I was wondering if anyone with a little firmer grasp on the subject could take a look at this query (which works & does exactly what I need it to do) and make suggestions as to what I might have done better/differently.
I've changed the names of everything to make it more comprehensible (I hope).
The query returns one row for each unique record in DetailTable, with data from the SummaryTable and a PartDescription from ThirdTable.
FROMtheDatabase.dbo.DetailTable LEFT JOIN theDatabase.dbo.SummaryTable ON DetailTable.RecordID=SummaryTable.RecordID LEFT JOIN theDatabase.dbo.ThirdTable on SummaryTable.PartNumber = ThirdTable.PartNumber AND SummaryTable.PartRevision = ThirdTable.PartRevision
WHERE (SummaryTable.IssueCategory='1' OR SummaryTable.IssueCategory='2' OR SummaryTable.IssueCategory='3' OR SummaryTable.IssueCategory='D' OR SummaryTable.IssueCategory='E' OR SummaryTable.IssueCategory='L' OR SummaryTable.IssueCategory='O' OR SummaryTable.IssueCategory='R' OR SummaryTable.IssueCategory='S' OR SummaryTable.IssueCategory='V' OR SummaryTable.IssueCategory='X' OR SummaryTable.IssueCategory='Z') AND SummaryTable.Facility='Default' AND SummaryTable.Status='OPEN'