Transact SQL :: HOW CTE Works In Server

Jun 18, 2015

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

DECLARE @StartTime DATETIME, @EndTime DATETIME
SELECT @StartTime = '09:00:00'
SELECT @EndTime = '17:30:00'
;WITH interval_cte(StartTime, EndTime) AS

[code]....

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 ?

View 6 Replies


ADVERTISEMENT

SQL SERVER ACCESS DENIED!! BUT Everything Works On MSDE And Works SHOWING RECORDS ON SQL SERVER!! PLEASE HELP

Jul 26, 2004

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!!!??

View 3 Replies View Related

Transact SQL AND Works, OR Doesn't

Mar 31, 2006

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)
-------------------------------------------------------------------
All is working well except for the line of business:
------------------------------------------------------------
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,'') + '%'
---------------------------------------------------------------------------------
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?
Thanks

View 2 Replies View Related

Transact SQL :: Query Works Even If Column Not Exists In Subquery

Jul 23, 2015

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,

[Code] ....

View 3 Replies View Related

Transact SQL :: Executing Stored Procedure Within Trigger Failing But Separate Works

Nov 4, 2015

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.

View 5 Replies View Related

Transact SQL :: How Query Engine Works While Comparing String With Comparison Operators

Oct 11, 2015

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.

View 3 Replies View Related

Telnet Connection Works, Sql Cmd Connection Works, SQL Server Managment Studio 2005 Does Not

Jun 20, 2007

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



telnet sql5.hostinguk.net 1433 and

sqlcmd -S sql5.hostinguk.net -U username -P password



See below:



Active Connections

Proto Local Address Foreign Address State

TCP 0.0.0.0:25 0.0.0.0:0 LISTENING

TCP 0.0.0.0:80 0.0.0.0:0 LISTENING

TCP 0.0.0.0:135 0.0.0.0:0 LISTENING

TCP 0.0.0.0:443 0.0.0.0:0 LISTENING

TCP 0.0.0.0:445 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1026 0.0.0.0:0 LISTENING

TCP 0.0.0.0:1433 0.0.0.0:0 LISTENING

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:



TCP 81.105.102.47:1138 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1139 217.194.210.169:1433 TIME_WAIT

TCP 81.105.102.47:1140 217.194.210.169:1433 TIME_WAIT



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?

View 7 Replies View Related

SQL Server 2008 :: SSIS Package Fails Silently On Server But Works If Run Manually

Jul 7, 2015

I have two calls to stored procedures that in an SSIS package fails silently. They are simply not executed in production but works fine in test, nothing happens and the sql server agent reports that everything has gone just fine.

In test they have 1 server with db A and B. No issue here.

In prod they have 2 servers with db A and B. On server 1 sql server agent executes a job that includes an SSIS package that on server 2 runs a couple of sp's. That user is db owner on server 2 db B and yet nothing happens. The sp's are not executed.

If I in prod run the job manually then it works, but not when run with the sql server agent account that as said is even db owner.

View 2 Replies View Related

Does SQL Server 7.0 Works With Windows2000 Pro?

Jun 16, 2000

Does SQL Server 7.0 works with Windows2000 Pro?

We installed SQL 7.0 on Windows 2000 Professional and
cannot seems to find the server components of it.

Was wondering if anybody successfully installed SQL7.0
on Windows2000 Pro?

Pl. send an email.

TIA

DIN
dk@i1.net

View 1 Replies View Related

Case Works On One And Not The Other Server

Aug 7, 2001

Ok, this is in reference to the previous post about replicated server with difference.

I have a Case statement that checks for NULL values and works on one server and not the other. For example:

Select Case LineItems.Item When 'BILL' then Activities.InvoiceState When Null Then Activities.InvoiceState Else States.State End As Sate.

The second server is not recognizing the NULL in this statement. Any ideas??

Thanks alot for any help.

View 1 Replies View Related

Basic Help Understanding How SQL Server Works

Aug 4, 2005

I've been using databases for twenty years now and have just started using SQL Server 2000. I've used dbase III+, FoxPro and FileMaker Pro mostly and have a pretty good generally knowledge of databases. However, I'm constantly scratching my head over a few SQL Server foundational issues and would appreciate some help.

Probably the biggest question is this:
In FileMaker Pro (and FoxPro if I remember correctly), I would set the relationship once and then never have to think about it much again. Then I could create virtual tables and sub forms which contained data from several tables and view/update them with ease. However, in SQL Server I've noticed that while I can define the relationships in a database diagram, every query has to recreate the joines. If I try to use a view I can only update one table at a time which seesm whacky to me. So what on earth is the point of having relationships if you have to redefine them with each query? I've read that referrential integrity is the reason but that only baffles me further as I don't understand the point. Sure I get cascading deletes but there has to be more to the feature than that.

Also, when I want to insert data in multiple related tables I'm used to utilizing the existing relationship, mentioning the primary key once and then simply filling in the data. It seems in SQL Server that I have to also insert on the foreign key. This doesn't make sense as the database should know which record I'm talking about in the foreign key table(s) as they're related. Again though, I'm not only recreating the relationship in the insert but I'm also having to specify the foreign key. I guess it just makes me wonder why it's not simply checking the relationships which already exist.

So I'm a bit baffled and any help would be most appreciated.

Thanks in advance!

Kelly

View 7 Replies View Related

Pivot Component Works On Server A, But Not On B

Jul 6, 2007

Hi there,



So, I have a Pivot component working just fine on our Dev server. You can put a datareader before and after it, and as expected, 2092 rows go in, 1890 rows come out, and they're pivoted just fine.



I deploy the package to our Live box, and it doesn't pivot the incoming dataset at all. The rows are just being passed straight through - it's as if the Pivot component is doing nothing. Put a datareader on, you can see 2092 in, 2092 out.



I know it's not the data - I reconfigured the connection manager to point to the dev dB, so it's using exactly the same source data set, and it still passes the rows straight through.



But here's the really wierd bit. If I modify the data source to just select 2 rows from the data source (which I know need pivoting on the pivot key), the component pivots them just fine. So I can see that technically, the component has been built correctly. It does what it is meant to do if I send through 2 rows, 4 rows, 8 rows. But when I send through 2092, it just passes them straight through.



Dev server:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 2)

Live server:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 2)




Any ideas anyone?

View 5 Replies View Related

How Is Reporting Server Rendering Works?

Aug 26, 2007

I a bit confuse of the order of rendering a report in reporting server . the software that i used previously do in following order

1. document header -- exec once at start of report
2. page header -- exec every new page
3. group header
4. body
5. group footer
6. page footer
7. document footer

in this software there no retriction that certain function can only be used at certain partand also there function to exec new page at any time I want. also each part has event for me to do programming . another different with reporting services is that once i set the paper type (ex. A1 or A3) ,unlike reporting service on design screen I get restrict on that size.


I hope someone can explain how actually reporting server render a page.


thks

rgds,
charles

View 1 Replies View Related

Trusted SQL Server Connection -- Works Under C#, But Not C++

May 8, 2007

Hi All,



I have a web serivce written in C++ and I'm trying to connect to a database. I'm using the same connection string that works from an aspx page written in C#. but when I copy it over to the C++ environment, I get:



System.Data.SqlClient.SqlException: Login failed for user ''. The user is not associated with a trusted SQL Server connection.


Note the username shows up as null in the error... My C++ looks like:



using namespace System:ata:qlClient;

String^ cs2 = "server=192.168.1.47;database=MyDB;integrated security=true;uid=myuser;pwd=mypassword";

SqlConnection^ myConnection = gcnew SqlConnection(cs2);

myConnection->Open();



Again, this works in C#. Interestingly, a sniffer shows a difference in an NT Authentication Packet as it's sent from my development machine to the database across the network. For the C# call, the NT Authentication packet has a username of "myuser" - from the connection string. It then gets acknowledged from the database. For the C++ call though, the username is sent as "ASPNET" - seemingly ignoring the username in the connection string. It's also rejected by the database in the next packet with the message above.



Any idea what's up with this? -- Curt



View 1 Replies View Related

Three Instances Only One Works, Sql Server 2005 ... Help Please!!!

Aug 19, 2007

I just installed sql server 2005 and trying to pick it up before I start a new job as a developer using sql server 2005. The problem is that I have three instances installed, the one that works was installed prior to installing sql server 2005 when I installed System Architect a CASE tool which utilizes sql server for its encyclopedias.

My initial installed I used the default settings with the default instance and that does not work. I later ran set up again and installed another instance and that does not work. For some apparent reason the POKIN10SQL instance is over riding everything rendering every other instance non-functional.

When I try to connect to the one of the other instances, the error message is

"An error has occured while establishing a connection to the server. When connection to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL does not allow remote connections. (Provider Network Interface, error: 26 - Error locating Server/Instance Specified) (Microsoft SQL Server)"

I don't think the remote connection is the problem as I went into the properties settings and checked the connection settings and allow remote connections is checked.

In terms of locating the instance, I installed SQL Server as specified in the handbook ... with the default instance and then a named instance.

Something seems to be wrong with the POKIN10SQL instance which was installed with System Architect, I need System Architect so I need a work around rather then an uninstall.

Can someone help please?

View 3 Replies View Related

I Don't Understand How This Works, However It Works (sometimes)

Sep 26, 2006

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)

SELECT DISTINCT LTWHLO,LTWHSL,LTITNO,LTAVAL, NEWID()

FROM #TEMP

WHERE NOT EXISTS( SELECT * FROM LTTSTOCK

WHERE WarehouseNo = LTWHLO

AND Location = LTWHSL

AND ItemNo = LTITNO

AND NumberAvail = LTAVAL

)

--========================================

--Remove local temporary table.

--========================================

DROP TABLE #TEMP

View 2 Replies View Related

Please Help.. Can't Get ASP.NET App To Work On Web Server, Works On Local System

Jul 27, 2006

I have an application I developled for a shopping cart function using Wrox ASP.NET book as a guide. I have everything working great on my local system using SQL 2005 Express. When I move the app to an IIS 6.0 Web Server with SQL 2005 Express installed I get the following error.
Line 103:    Public Overridable Property Cart() As Wrox.Commerce.ShoppingCartLine 104:        GetLine 105:            Return CType(Me.GetPropertyValue("Cart"),Wrox.Commerce.ShoppingCart)Line 106:        End GetLine 107:        Set
It has some comments up top about not being able to establish a connection to the SQL Express Database. I have made the changes to allow TCP/IP and Named-Pipes
The App has to be connecting to the database because it displays grid views during the order process.
The Database is located in the App Data folder
IIS is set to Integrated Authentication and configured as a Web Application.
Can someone please tell me what is wrong.
thanks.....

View 3 Replies View Related

Database Works On Local Host But Not On Web Server

Apr 23, 2008

I have a application which uses the automatic asp.net membership stuff to make it work. (In app_data). It works fine on local host, but when I use it on the web server I get the error message (after clicking the log in button):An error has occurred while establishing a connection to
the server.  When connecting to SQL Server 2005, this failure may be
caused by the fact that under the default settings SQL Server does not
allow remote connections. (provider: SQL Network Interfaces, error: 26
- Error Locating Server/Instance Specified) Please help me fix this. Thank you for your help,Sam 

View 6 Replies View Related

Can't Connect To Remote Server, My App Works In Local Only???

Feb 3, 2004

Hello,
i've developed a website using VB.NET, and SQL SERVER 2K, in my dev environment works properly (SQL Server and WEB SERVER are in the same machine).
When i put everything in the target machine, where the webserver is but not the sql, i get the following error:
Sql SErver does not exist or access denied

I've tried so many ways to do this, ODBC, ADO.NET, OLEDB.....and everything ends in the same error.
Also if i try to connect with ODBC (from odbc manager) it seems to work (test connection succesfull), but in asp.net nothing works.
I've tried to access the server with:
INSTANCE_NAME
SERVER_NAMEINSTANCE_NAME
IP,PORT

I think (they are checking this), that the auth mode is set to MIXED MODE.

Any idea to save my life?? ;)

Thanks ppl

View 1 Replies View Related

SQL Server 2014 :: CLR Works With Trustworthy OFF And No Asymmetric Key

Apr 28, 2015

We have a curious situation on a SQL 2014 DB, with Trustworthy set to OFF. There is a job that runs a data export to a file via a CLR. The assembly as PERMISSION_SET = EXTERNAL_ACCESS, however there is no Asymmetric key for the assembly. Therefore what I trying to work out is why this is NOT failing. Some further information on this specific database that may or may not be relevant is:

1. It was upgraded a few weeks ago (Backup/Restore) from a SQL 2012 - SQL 2014 server
2. It as a Compatibility Level = 110 (2012)
3. The Previous 2012 database DID have Trustworthy ON
4. The CLR are actually being run against a snapshot of the database (Actually I think this one is a red herring. The SP is getting data from a table in the snapshot, but the CLR used it the one from the main DB)

View 2 Replies View Related

Linked Server: Works Local, But Not Remote

Nov 14, 2007

I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).

However, when I execute a query from a remote machine I get an error (see below for the exact message).

I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?

Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?


Errors:

OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".

OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".

Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".


Environment: SQL 2005, Standard Edition SP2

Thanks in advance for any suggestions.

David Rueter
drueter@assyst.com

View 2 Replies View Related

Access Query Against SQL Server Works Only Without Criteria

Jun 23, 2006

Getting a weird error while trying out a query from Access 2003 on aSQL Server 2005 table.Want to compute the amount of leave taken by an emp during the year.Since an emp might be off for half a day (forenoon or afternoon), havethe following computed field:SessionOff: ([ForenoonFlag] And [AfternoonFlag])The query works fine when there's no criterion on SessionOff.However, when I try to get the records where the SessionOff equals 0, Iget the following error:~~~~~ODBC--call failed. [Microsoft][SQL Native Client][SQL server]Incorrect syntax near the keyword 'NOT'. (#156)~~~~~I checked the SQL of the Access query, but there's no NOT anywhere init:~~~~~SELECT tblWorkDateAttendance.*FROM tblWorkDate INNER JOIN tblWorkDateAttendance ONtblWorkDate.WorkDate = tblWorkDateAttendance.WorkDateWHERE (((([ForenoonFlag] And [AfternoonFlag]))=0) AND((tblWorkDateAttendance.WorkDate)<Date()) AND((Year([tblWorkDate].[WorkDate]))=Year(Date())) AND((Weekday([tblWorkDate].[WorkDate])) Between 2 And 6) AND((tblWorkDate.HolidayFlag)=False));~~~~~What gives?

View 4 Replies View Related

UNION Statement That Works In SQL Server But Not Access

Nov 29, 2006



Hi,

Is there a way I can get this select Union statement to work in Access.

SELECT '' AS Router UNION SELECT DISTINCT Router FROM IPVPNRouterUpgradeCharges WHERE SchemeID = 12 AND

Router <> 'IPVPN Lite' AND Router <> 'VPN Bridge'

AND Router <> 'IPVPN Aggregated Bandwidth' ORDER By Router

I get this message in Access: Query input must contain at least input of query

Thanks for any help

Chris

View 1 Replies View Related

Access To SQL Server Via WCF Works Only Part Time

May 16, 2007

We have 2 databases ( Guider and Talker ) and we have a WCF service that is logged in with a domain identity.



In our SQL Server we have the service ID added to the Data Server Logins and both Guider and Talker are given access to the user.



When we access Guider we have no problems getting data.

When we access Talker we have a login failure:



Cannot open database 'Talker' requested by the login. The login failed.

Login failed for user 'AcornCommunicationServices'.



The thing that gets me is that the user is created at the Server level, in both Databases, and at the server level both databases are checked for the user. master has been set as the default database for the user.



Basically, as far as I can see Talker and Guider are configured identically! So I cannot figure out why I cannot login to the second database!



Is there a specific setting I'm missing somewhere to grant login access to the user? I'm using

Management Studio Express to manage the database.

View 1 Replies View Related

SSIS Package Works, But Not In SQL Server Agent Job

Feb 1, 2007

I have a problem running an SSIS package in a SQL Server job. The package runs fine if I run it from the MSDB location, but if I try to run the job it fails. The job is set to Run as: SQL Agent Service Account. The SQL Service Agent service runs as a domain user SQLExec. I have logged in as this user and run the SSIS package and it runs fine, but if I create a job with only this step it fails. There isn't much information about where there is a problem. Any ideas or ways to troubleshoot this problem would be very much appreciated.

Thanks, john

View 3 Replies View Related

'Run Package' Works On Server, But Doesn't Complete As Job.

Mar 10, 2008



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.

Anyone have any ideas or similiar situations?

Thanks.

View 5 Replies View Related

Package Works In BIDS, But Not In SQL Server 2005

May 3, 2007

Most of my packages that I've created in BIDS will NOT run in SQL Server 2005. The simplest one that I have fails during a script task that calls external managed code. I've done all the steps outlined in "Referencing Other Assemblies...", but I'm still getting "Object reference not set to an instance of an object." Here's a sample of a script that's having a problem. The line in green is the one that seems to be cause of the error. This is extremely frustrating. This code will even run from a command line console without error. Why is it so difficult to deploy one of these projects with managed code?



Code Snippet

Public Sub Main()
Dim variable1 As String = DirectCast(Dts.Variables("packagevariable1").Value, String)
Dim variable2 As String = DirectCast(Dts.Variables("packagevariable2").Value, String)
Dim variable3 As Integer = DirectCast(Dts.Variables("packagevariable3").Value, Integer)
Dim variable4 As String = DirectCast(Dts.Variables("packagevariable4").Value, String)
Dim filePath As String = DirectCast(Dts.Variables("filePath").Value, String)
Dim variable5 As String = DirectCast(Dts.Variables("packagevariable5").Value, String)
Dim results As Boolean
Dim fileGenerator As IProviderInterface
Dim intFactory As integrationServiceFactory = New ProviderIntegrationServiceFactory()

fileGenerator = intFactory.GetProviderEnrollmentGenerator(variable2, variable5)
results = fileGenerator.GenerateFile(variable3, variable1, filePath, variable2)

If results Then
Dts.TaskResult = Dts.Results.Success
Else
Dts.TaskResult = Dts.Results.Failure
End If

End Sub

View 9 Replies View Related

How SSIS Works With Data Sources Other Than SQL Server?

Oct 2, 2006

Hi:

I am new to SSIS. I would like to know if I want to transfer data from one Oracle schema to another Oracle schema and also to do scheduling of the packages, can I still use SSIS? If yes, what are the components that need to be installed on the database server and the development environment? I hope I don't need the full SQL Server database installation in order to use SSIS.

Thanks!

MuiSukYuen

View 1 Replies View Related

SQL Server Exists And Access Works From My Webforms. But Not The Webservice! HELP!

Sep 19, 2006

When our production site was deployed on the client's WinServer2003, my webservice is throwing a "server does not exist or access denied" exception. I'm using the same connection string (typed once) as i'm using in my web forms on the user visible sections of the site. the service also works fine on my XP testing machine. unfortunately, I'm not a 2003 admin. If anyone can help, i would greatly appreciate it, trying to find what is misconfigured on the client's server is driving me bonkers.

View 2 Replies View Related

Primary Key In Datarow After Update Works In Access Not In Sql Server

Feb 15, 2005

Heys

a while back i had to do a project with an access database, one of the biggest problems i had back then was gettting the primary key
of a datarow you had just inserted into the database.

After a long set of trial and error i came up with the following:

- add the tablemappings of a table
- call the dataadapte.fillschema method

then after inserting a new row into the database the primary key gets filled in automatically!

now thing is

i was hoping to duplicate this in sql server

but it doesn't seem to work at all

so after i insert a row into my datatable
and update it
the row is in the database
but in vb the datarow primary key is not filled in!
anyone have an idea?

prefereabely one that does not resort to stored procedures with return parameters etc

thx a million in advance!

View 1 Replies View Related

Login Fails For Network SQL Server But Works For Localhost

Jul 18, 2005

I have an ASP.NET webform:This connection works:   "Server=localhost;uid=sa;pwd=;database=pubs"but this connection DOES NOT work:  "Server=dnrsqlt1;uid=sa;pwd=;database=pubs"dnrsqlt1 is a sql server my network.Do I have to do something to users ASPNET or  IUSER_Machinename on the remote machine

View 6 Replies View Related

How To Convert Pl/sql Code Of Oracle To Something Equivalent Which Works On Sql Server 7.0?

Dec 21, 2000

i had worked on oracle 8i and i am planning to work on sql server 2000,i am requested by a company to help in converting there pl/sql code of oracle 8.0 to something equivalent which works on sql server 7.0 as they want to have similar code on both..i had not worked on sql server 7.0 ,but as pl/sql code works only on oracle stuff..so could kindly anyone guide me in this as to whether there is any product which coverts pl/code (the existing pl/code runs into thousands of line) automatically..i will be very grateful if anyone can enlighten me with such a product(software) or script.. along with its information and site address..any resources and any guidance as to how to go about about this conversion will be very invaluable..hope to hear soon from you guys...early response....will be appreciated..

with regards,

vijay.

sql server 7.0 on winnt
pl/sql code on oracle 8.0

View 2 Replies View Related

SQL Server 2012 :: How Update Works For Unique Values

Nov 25, 2014

Look at the following code,

Create table #test
(
id int primary key,
Name varchar(100)
)
insert into #test values (1,'John')
insert into #test values (2,'Walker')

[Code] ....

-- Query 1 :
update #test set name = 'Joney' where id = 1

-- Query 2 :
set rowcount 1
update #test set name = 'Joney' where id = 1
set rowcount 0

1. #test table have primary key & clustered index.
2. Obviously only one row will be available for an id.
3. In query 1, will the sql server look for matching rows even after it found 1 row?
4. Will query 2 really gains some performance?

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved