SQL Server 2008 :: How To Write A SELECT Statement To Get Data From A Linked Server

Feb 23, 2015

I have the linked server connection created and works perfectly well. I mean I am able to see the tables while I am on my database.

how do I write a SQL statement to reference the linked server ?

I tried the following:

Select top 100 * from casmpogdbspr1.MPOG_Collations.dbo.AsaClass_Cleaned

Then I get the error message....

Msg 7314, Level 16, State 1, Line 1

The OLE DB provider "SQLNCLI10" for linked server "casmpogdbspr1" does not contain the table ""MPOG_Collations"."dbo"."AsaClass_Cleaned"". The table either does not exist or the current user does not have permissions on that table.

View 2 Replies


ADVERTISEMENT

How To Write A Delete Statement In Linked Server

Oct 6, 2007

Hi all,
We are retriving tables from DB2 and loading into a SQL server. while retriving temporary tables are getting created in the linked server. we need to delete the data in those temporary tables in Linked server. I don't know how to write delete statement for the tables in linked server. any pointers would be appreciated.

Thanks in advance.
Shriram

View 1 Replies View Related

SQL Server 2008 :: Write A Cursor To Fetch Required Data From Table?

Oct 21, 2015

I have been trying to write a cursor to fetch required data from table but somehow its running forever and inserting duplicate records.

I have a temp table named getInvoice where I have five important columns

1. invoice number
2.group
3.invoice status
4. Invoice Expiration date
5. Creation date time

and some other columns.One invoice number can belong to one or more group and there can be one or more records for a particular invoice number and group.

An example is below :

InvoiceNumber Group InvoiceStatus InvoiceExpirationDate CreationDateTime

579312 01 3 NULL 2003-03-24 00:00:00
579312 01 2 2015-12-14 00:00:00 2005-12-24 00:00:00
579312 02 2 2003-12-21 00:00:00 2005-10-12 00:00:00
321244 01 2 2015-12-21 00:00:00 2005-10-12 00:00:00
321244 01 3 2010-12-21 00:00:00 2010-12-21 00:00:00

My query condition is complex and that is why Im facing problem retrieving the output.I need a cursor for getting distinct invoice number from the table and for each invoice number I need to get the latest record for each invoice number and suffix combination based on creationdateand time column and if that record has invoice status of 2 and also the invoice expiration date can be either null or greater than today's date, then I need to get that record and put it in a temp table.

The query I wrote is below

declare myData cursor for
select distinct invoiceNumber from #getInvoice
declare @invoiceNumber varchar(30)
open myData
fetch next from myData into @invoiceNumber
while @@FETCH_STATUS = 0

[Code] .....

This query runs forever and doesn't stop.

View 6 Replies View Related

SQL Server 2008 :: Extracting Data From Linked Server

Dec 17, 2010

I am using MS SQL 2008, and I am trying to extract data from a MySQL database. I am having trouble extracting the data I need.

In SQL Server management studio, I can see the linked server...I can browse the different databases on the server. I can see user and system tables in all of the databases.

When I try and query a linked table (select * from server.db.table) I get Invalid object name 'servername.databasename.tablename'.

When I try script the table.. right_click on the linked table, Script table as, Select to, New Query Editor window, I get an error '[servername].[databasename]..[tablename] contains no columns that can be selected or the current user does not have permissions on that object.'

This leads me to believe that it is a permissions problem, but if I have access to the MySQL database using MySQL and the same login/password) and can retrieve the data there, then I think my login credentials should be enough using MS SQL. I guess I think its odd that I could have enough credentials to get in and see table names, but not do a select against it.

View 8 Replies View Related

SQL Server 2008 :: Incorrect Prefix Error (select Count Statement)

Oct 7, 2015

Naming convention and what am I doing wrong here:

,(Select Count(C2.AppID) From Channels c left join Applications a on c.ChannelID = a.SourceID left join Contracts2 c2 on a.AppID = c2.AppID Where Channels.ChannelID = c.ChannelID and c2.DateContractFunded > (Select dateadd(yy,-1,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) and c2.DateContractFunded < (Select dateadd(ms,-3,DATEADD(yy, DATEDIFF(yy,0,getdate() ), 0))) ) As FundedLastYear
FROM Channels AS C
INNER JOIN ChannelContacts AS CC ON C.ChannelID = CC.ChannelID
INNER JOIN ChannelProductPlan AS CPP ON C.ChannelID = CPP.ChannelID
INNER JOIN tblLuMktReps AS MR ON C.MarketRepID = MR.MarketRepID
INNER JOIN tblLuHoldingCo AS HC ON C.HoldingCoID = HC.HoldingCoIDError message:

Msg 107, Level 16, State 3, Line 1
The column prefix 'Channels' does not match with a table name or alias name used in the query.

View 9 Replies View Related

SQL Server 2008 :: Parsing Data To Select Certain Values From XML

Mar 13, 2015

I have results that are XML data and I am trying to figure out how to parse the data to select certain values from the xml.

example
<InformationRequest xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" teamid="TEAM003341507" playerid="PL341508" gameid="G000000852" playertype="Starter" FolderName="Test" CurrentYear="2015" Ultimateid="P00000688505" xmlns="http://schemas.sports.com/Messages/Stats" />

I would like to write a statement that just pulls the game id G000000852. So just the id right of gameid=.

Not really sure where to start. Table is GAME, and column is XMLDATA.

View 0 Replies View Related

SQL Server 2008 :: Querying XML Data With Column Value In Same Select Clause

Aug 3, 2015

I'm working on a query in which I need to get few nodes values from the XML data by using the value from SQL column (MessageContentType) in this query. I'm able to get the nodes value when i hard code the value in the query but the problem is MessageContentType will vary from some records in the table, due to that I'm not getting the corresponding node values. I have tried few ways to get this value dynamically but I'm missing something.

Sample Table Data
MessageContentType | BodySegment
xx:ADT_A03_26_GLO_DEF | <ns0:ADT_A03_26_GLO_DEF xmlns:ns0="http://microsoft.com/HealthCare/HL7/2X">.....

Current Query - HardCode Script

SELECT
ID,MsgContentType
BODYSEGMENT,
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /xx:ADT_A03_26_GLO_DEF[1]/colxx[1]/colxx[1]','varchar(300)') AS TimeSpan
FROM
s

When i tried the below line of script, I'm getting this error "[color=#FF0000]The argument 1 of the XMLdata type method "value" must be a string literal.[/color]"

Concat MsgContentType Column
BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /'+MsgContentType+'[1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan

To overcome that error i used sql column but I'm getting this error [color=#FF0000]XQuery [S.bodysegment.value()]: Syntax error near '[', expected a "node test"[/color].

BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /[sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan

I tried this line of script, i didn't get any error by timespan is coming as null, I do hope this script not pointing the correct node to traverse the sibling node.

BODYSEGMENT.value('declare namespace xx="http://microsoft.com/HealthCare/HL7/2X"; /*[local-name()=sql:column("MsgContentType")][1]/EVN_EventType[1]/EVN_2_RecordedDateTime[1]','varchar(300)') AS TimeSpan

View 9 Replies View Related

How To Write Select Statement Inside CASE Statement ?

Jul 4, 2006

Hello friends,
I want to use select statement in a CASE inside procedure.
can I do it? of yes then how can i do it ?

following part of the procedure clears my requirement.

SELECT E.EmployeeID,
CASE E.EmployeeType
WHEN 1 THEN
select * from Tbl1
WHEN 2 THEN
select * from Tbl2
WHEN 3 THEN
select * from Tbl3
END
FROM EMPLOYEE E

can any one help me in this?
please give me a sample query.

Thanks and Regards,
Kiran Suthar

View 7 Replies View Related

SQL Server 2008 :: Linked Server Tests Fine But Query Does Not Work

Apr 16, 2015

Using a 32-Bit SQL Server 2008 Express on my LOCAL PC. I downloaded the Advantage 8.1 OLE DB Provider and created a linked server to a REMOTE Advantage 8.1 database server. There is no data dictionary on the Advantage server.

Here is my linked server:

EXEC master.dbo.sp_addlinkedserver @server = N'1xx.1xx.xx.1xx', @srvproduct=N'Advantage', @provider=N'Advantage OLE DB Provider', @datasrc=N'1xx.1xx.xx.1xxeccET', @provstr=N'servertype=ads_local_server;tabletype=ads_cdx;'--tabletype=’ADS_ADT’ (this test works too)
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'1xx.1xx.xx.1xx',@useself=N'False',@locallogin=Null,@rmtuser='adssys',@rmtpassword=Null

Testing the link succeeds with above. Using “ads_REMOTE_server” instead of “ads_local_server” and the test fails. Here is my problem, using the following queries will not work. Perhaps it’s a permissions issue? When I specify my local credentials for the remote server in the linked server it still does not work.

SELECT * FROM OPENQUERY([1xx.1xx.xx.1xx], 'SELECT * FROM ActType')

OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx" returned message "Error 7200: AQE Error: State = HY000; NativeError = 5004; [Extended Systems][Advantage SQL][ASA] Error 5004: Either ACE could not find the specified file, or you do not have sufficient rights to access the file. Table name: ActType SELECT * FROM ActType".
Msg 7321, Level 16, State 2, Line 2

An error occurred while preparing the query "SELECT * FROM ActType" for execution against OLE DB provider "Advantage OLE DB Provider" for linked server "1xx.1xx.xx.1xx".

View 0 Replies View Related

SQL Server 2008 :: View Creation Using XML Column On Linked / Distributed Server?

Sep 4, 2015

A recent SharePoint upgrade has rendered several views obsolete. I am redefining them so that our upper level executive reports show valid data.(yes, I know that doing anything to sharepoint could cause MS to deny support, having said that, this is something I've inherited and need to fix, pronto) The old view was created like so:

USE [AHMC]
GO
/****** Object: View [dbo].[vwSurgicalVolumes] Script Date: 09/04/2015 09:28:03 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE VIEW [dbo].[vwSurgicalVolumes] AS
SELECT

[code]....

As I said, this view is used in a report showing surgical minutes.SharePoint is now on a new server, which is linked differently (distributed?) I've used OPENQUERY to get my 'new' query to work;

SELECT *
FROM OPENQUERY ([PORTALWEBDB], 'SELECT
--AllLists
AL.tp_ID AS ALtpID
,AL.tp_WebID as altpwebid
,AL.tp_Title AS ALTitle

[code]....

My data (ie surgical minutes, etc) seems to be in the XML column, AUD.tp_ColumnSet . So I need to parse it out and convert it to INT to maintain consistency with the previous view. How do I do this within the context of the view definition?Here is a representation of the new and old view data copied to excel :

<datetime1>2014-08-14T04:00:00</datetime1><float1>2.000000000000000e+000</float1><float2>4.190000000000000e+002</float2><float3>1.600000000000000e+001</float3><float4>8.110000000000000e+002</float4><sql_variant1 xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:sqltypes="http://schemas.microsoft.com/sqlserver/2004/sqltypes"

[Code] ....

can't format it to make it look decent. InHouseCases =2, InHouseMinutes=419, OutPatientCases =16, OutPatientMinutes=1230. This corresponds to the new data I can see in the XML column; 2.000000000000000e+000 is indeed 2 and 4.190000000000000e_002 is indeed 419.

View 4 Replies View Related

SQL Server 2008 :: Last Table READ And WRITE Dates

Feb 26, 2015

How would I find the last read/write dates for all the tables within a database.

View 6 Replies View Related

SQL Server 2008 :: Write Query For Date Logic?

May 25, 2015

I have a below query which have a date filter like "EST_PICK_DATE between '2015-02-01' and '2015-06-01'", where the logic is EST_PICK_DATE should be 3 months from the current month and 1st date of next month. Ex for current month MAY, EST_PICK_DATE shoulc be between '2015-02-01' and '2015-06-01'. I need to write below query dynamically. In below query i have hardcoded the value ("EST_PICK_DATE between '2015-02-01' and '2015-06-01'"), but it should take dynamically. How to achieve this?

I am using this query in SSIS package, So Shall i do in SQL level or we should implement this logic in package? If yes, How?

INSERT INTO STG_Open_Orders (Div_Code, net_price, gross_price) SELECT ord.DIV_CODE AS Div_Code, ord_l.NET_PRICE AS net_price, ord_l.gross_price AS gross_price, FROM ORD ord inner join ORD_L ord_l ONord.ORD_ID=ord_l.ORD_ID WHERE ord_l.EST_PICK_DATE BETWEEN '2015-02-01' AND'2015-06-01'

View 1 Replies View Related

Trying To Write A Select Statement.

Jul 26, 2007

I want to write a select statement that will show only distinct Student IDs where every record in the table with that student id has a grade above 80?
Lets say for example the table looks like this:




Student_ID

Grade


11276

93


56879

81


11276

76


44327

92


11276

84


56879

97


56879

82 The select statement should only show students, 56879 & 44327 because student 11276 made a 76 in one class.Anyone know how to word this select statement?Thanks for any help!

View 8 Replies View Related

Is It Possible To Write If/Then Else Within SQL Select Statement For Vb.net?

Aug 1, 2007

I'm running a select statement from within my VB.net code. However, if the software package name that I chose from a drop down list in my form is not available in the search (in the TableColumn called Software.Package_Name), I would like to be able to write the message "Record Not Found" to the Package_Name "ListBox" column.

Here's the select statement:
"SELECT n.Workstation_Name , s.install_status , s.install_date , s.name , s.version , s.build , s.Package_Name , n.city , n.state FROM software s , network n where ( s.mac=n.mac AND n.Workstation_Name='B001617CA709E' ) AND ( s.name='Adobe Reader' AND s.Install_status='1') ORDER BY n.Workstation_Name ASC"

My ListBox control is named lstQueryResults and uses a Structure that is one to one with each row of the lstQueryResults control.
i.e.


Public Class LIST_CTRL_DATA

Public WksName As String

Public InstallStatus As String

Public InstallDate As String

Public PackageName As String

Public AppName As String

Public Version As String

Public Build As String

Public City As String

Public State As String

Public Percent As UInteger

Finally, here's the While..End routine that plugs the data into the ListBox called lstQueryResults.


While sqlReader.Read()

currec += 1

Dim msg As THREAD_MESSAGE

msg.msgid = 101

Dim message As New LIST_CTRL_DATA

message.WksName = sqlReader("Workstation_Name").ToString

message.InstallStatus = sqlReader("Install_Status").ToString

message.InstallDate = sqlReader("Install_Date").ToString

message.AppName = sqlReader("Name").ToString

message.Version = sqlReader("Version").ToString

message.Build = sqlReader("Build").ToString

message.PackageName = sqlReader("Package_Name").ToString

message.City = sqlReader("City").ToString

message.State = sqlReader("State").ToString

message.Percent = (currec * 100) / maxrecs

msg.msgdata = message

m_ParentWindow.Invoke(m_NotifyMainWindow, msg)

End While

Any ideas on whether or not you can write an If..Then Else statement within the SQL Select Statement and if so how?
I'm still somewhat new to SQL. Any assistance would be great.

Regards,
NBK

View 1 Replies View Related

How To Write The Select Statement?

Sep 26, 2007

I create a table that is named T1(C1,C2) and insert some data,as following:

C1 C2
1 1
4 NULL
NULL 5
7 NULL
NULL 9
12 12
14 NULL
NULL 16
I want to write a select statement to obtain the following result:

C1 C2
1 1
4 5
7 9
12 12
14 16
how to write the select statement?

 

View 2 Replies View Related

SQL Server 2008 :: No-lock Across Linked Server With Join?

Mar 9, 2015

I have two servers (lets call them sA and sB) connected from sA -> sB via a linked server (i.e. sA pulls data across from sB).

I'm building a temp table full of stock symbols on sA, and then I need to update some values on sA using content on sB. The tables on sB are very large (about 500m rows) so I don't want to pull even close to everything across the linked server. Ordinarily I'd do this by joining to the linked server table, but the target table needs to have nolock on it due to their high use.

update t
set someValue = s.SomeValue
from #myTab t
inner join lnk_sB.xref.dbo.Symbols s with (nolock)
on t.id = s.id

From reading around I gather that nolock doesn't work across linked servers. It was noted in another SSC article that you could use nolock by using an OPENQUERY, but then I can't join to my local temp table, and I end up pulling all .5B rows across the linked server.

Is there some way I can limit the content on sB by my temp table on sA but still use nolock?

View 9 Replies View Related

SQL Server 2008 :: Run A Query From A Linked Server (ABCD)?

Aug 4, 2015

I am trying to run a query from a Linked server "ABCD"

Set @SQLCMD = 'Select * from TableName"
Insert into @TempTableName
Execute (@SQLCMD) AT ABCD

I am getting below error while running the above statements. When I Remove the Insert into @TempTableName it is working fine.

The operation could not be performed because OLE DB provider "SQLNCLI10" for linked server "ABCD" was unable to begin a distributed transaction.

View 2 Replies View Related

SQL Server 2008 :: How To Bring Write Log Wait Type Count Down

Oct 29, 2015

This is the scenario in my environment

WaitType Wait_Sec Resource_Sec Signal_Sec Wait Count Wait Percentage
WRITELOG920039.89887485.89 32554.00 23446032975.02

View 9 Replies View Related

How To Put Condition In Select Statement To Write A Cursor

Mar 29, 2008

col1 col2 col3 col4
36930.60 145 N . 00
17618.43 190 N . 00
6259.20 115 N .00
8175.45 19 N .00
18022.54 212 N .00
111.07 212 B .00
13393.05 67 N .00
In above 4 col
if col3 value is B then cursor has to fectch appropriate value from col4.
if col3 value is N then cursor has to fectch appropriate value from col1.
here col2 values are unique.

Can any one reply for this..............

View 3 Replies View Related

SQL Server 2008 :: Create Authentication Account With Read-write Access To Only 1 Table?

Apr 7, 2015

How can I create a SQL authentication account with read-write access to only 1 table in a SQL database.

View 1 Replies View Related

How To Write A SELECT Statement And Store The Result In A Session Variable

Nov 6, 2007

I'm sure this is a very simple piece of code, but I'm having trouble understanding how to do this.
First I have a database with three columns


ContactID

View 1 Replies View Related

SQL Server 2008 :: Getting Error While Creating Linked Servers

Sep 15, 2015

Below is the syntax I am using for creating Linked server from SQL Server i.e windows 2008 R2 standard to Postresql database running on Linux 32 bit Debian (Linux turtle 3.2.0-4-686-pae #1 SMP Debian 3.2.46-1+deb7u1 i686 GNU/Linux) and the version of Postresql is 8.3

/****** Object: LinkedServer [HGCDEV] Script Date: 09/15/2015 17:03:37 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'HGCDEV', @srvproduct=N'', @provider=N'MSDASQL', @datasrc=N'172.16.20.159',@provstr=N'UID=web;PWD=dev123'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'HGCDEV',@useself=N'False',@locallogin=NULL,@rmtuser='web',@rmtpassword='dev123'

This the error I am getting " Cannot initializee the data source object of OLE DB provider "MSDASQL" for linked server "HGCDEV".

How to setup the linked server........... Below are the drivers installed on the SQL server

PostgreSQL35W
PostgreSQL30

View 2 Replies View Related

SQL Server 2008 :: How To Force Server To Select Not From Master

Jul 13, 2015

I need to investigate about what happened to our production server at the last weekend.i restored it to another server which is development. I restored it under a name "old_master_2015_07_10".

But if I run a query

SELECT *
FROM [old_master_2015_07_10].[sys].[servers]

it actually selects from master.sys.servers, not from my old_master... In order to prove it, I created a linked server in this, development server, and if I run SELECT * FROM [old_master_2015_07_10].[sys].[servers], it selects it. And in database selection drop-down box I also selected old_ master_ 2015_07_10. What I think it apparently recognizes familiar names like sys.servers and redirects the query to the master.

What I can do to select really from old_master_2015_07_10 database? I already thought about renaming sys.servers to something different, but did not do it not to break something in master in case if SQL Server will run it in master as well.

View 9 Replies View Related

Linked Server Update Statement

Jan 11, 2008



I've got the output from a cursor that generates the following sql statement.


Update SERVERNAME.DATABASENAME.dbo.TABLENAME set [update] = 'Y', status = NULL, completed = NULL where trigger_id = 10255

The statement generates the following error if the statement is run remotely

Msg 8180, Level 16, State 1, Line 1

Statement(s) could not be prepared.

Msg 156, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'update'.


But the statement executes without error as formatted if run directly on the linked server.

Both servers are running 2000 sp 3a. The servers will be converted to 2005/2008 without patching to sp4.

View 5 Replies View Related

Created Linked Server, Cant Do A Select On That Server

Mar 1, 2008

I created a linked server on server A to server B as follows...


USE master;

GO

EXEC sp_addlinkedserver

'B',

N'SQL Server'

GO

...in mgt studio (in a connection to A) from my local where as a person with sys admin rights on both A and B I'm having no problems connecting to either and running queries. When I run a query from a connection on A as select * from .[a db on B] .[an owner on B].[a table on B], I get an error that says Login failed for user 'NT AUTHORITYANONYMOUS LOGON'.

I believe both A and B are in the same domain

the default behavior that I saw checked in the linked server's security properties was "connection will be made using the logins current security context".

when change the default and clicked and entered "this security context" as domainuserid and password, rerun the query, I get error Login failed for user 'domain nameuserid'.

is there a simple way to get this to work, at least for test purposes? I even tried playing with the login mappings but nothing seemed to work.

View 7 Replies View Related

SQL Server 2012 :: Create Dynamic Update Statement Based On Return Values In Select Statement

Jan 9, 2015

Ok I have a query "SELECT ColumnNames FROM tbl1" let's say the values returned are "age,sex,race".

Now I want to be able to create an "update" statement like "UPATE tbl2 SET Col2 = age + sex + race" dynamically and execute this UPDATE statement. So, if the next select statement returns "age, sex, race, gender" then the script should create "UPDATE tbl2 SET Col2 = age + sex + race + gender" and execute it.

View 4 Replies View Related

Unidata - SQL Linked Server Insert Statement

Dec 29, 2006

Hi -

I am using linked server to insert data to a table. When I do select, it does show me results but when I do insert, it does not work. My source/destination has exact same data types defined. Any idea?

insert into dbo.tb_PERSONNEL

select * from openquery(CADC1, 'SELECT PERSONNEL_1_NF.ID, PERSONNEL_1_NF.NAME, PERSONNEL_1_NF.PNUM, PERSONNEL_1_NF.PN

FROM PERSONNEL_1_NF')

I get:

Msg 8152, Level 16, State 14, Line 2

String or binary data would be truncated.

The statement has been terminated.

View 1 Replies View Related

SQL Server 2012 :: Using Unions To Write Out Each Select Query As Distinct Line In Output

Aug 20, 2015

Basically I'm running a number of selects, using unions to write out each select query as a distinct line in the output. Each line needs to be multiplied by -1 in order to create an offset balance (yes this is balance sheet related stuff) for each line. Each select will have a different piece of criteria.

Although I have it working, I'm thinking there's a much better or cleaner way to do it (I use the word better loosely)

Example:
SELECT 'Asset', 'House', TotalPrice * -1
FROM Accounts
WHERE AvgAmount > 0
UNION
SELECT 'Balance', 'Cover', TotalPrice
FROM Accounts
WHERE AvgAmount > 0

What gets messy here is having to write a similar set of queries where the amount is < 0 or = 0

I'm thinking something along the lines of building a table function contains all the descriptive text returning the relative values based on the AvgAmount I pass to it.

View 6 Replies View Related

SQL Server 2008 :: Aggregate In Update Statement

Apr 16, 2015

I'm getting the following error message 'An aggregate may not appear in the set list of an UPDATE statement' What is the proper way to carry out an update on aggregates?

My code is below:

t1.TotalTest1 = coalesce(Sum(t2.AmountTest1), 0.00),
t1.TotalTest2 = coalesce(Sum(t2.AmountTest2), 0.00),
t1.TotalTest3 = coalesce(Sum(t2.AmountTest3), 0.00),
t1.TotalTest4 = coalesce(Sum(t2.AmountTest4), 0.00),

from #tbl_CHA t1
inner join

[Code] ....

View 4 Replies View Related

SQL Server 2008 :: Deadlocks With INSERT Statement

Sep 28, 2015

Have any seen Insert statement deadlocking itself ? Most of the articles published by Microsoft says to change the transaction isolation level from Read Committed to Read Committed Snapshot.Below is the XML file on the deadlock

<deadlock>
<victim-list>
<victimProcess id="processe259948" />
</victim-list>
<process-list>

[code]...

View 0 Replies View Related

Linked Server (Oracle) - No Select Possible

Mar 7, 2008

Hello,

I created a linked server to Oracle on SQL-Server 2005. I have stored the remote login username and password in the security settings (option "...Be made using this security context" in the Security tab of the Linked Server Properties.

The "Test connection" function works fine, but a select doesn't for every table the user owns. What we found out is: if the table consists only of columns defined as varchar2, the select works. As soon as the table contains only one column defined as number (without scale and precision), it doesn't work. The error displayed is:

The OLE DB provider "MSDAORA" for linked server "NEXUS_FVADM" supplied inconsistent metadata for a column. The column "WEBVORZUGSTYP" (compile-time ordinal 2) of object ""FVADM"."AKZ"" was reported to have a "DBTYPE" of 130 at compile time and 5 at run time.

The Column WEBVORZUGSTYP is defined as number.

Any ideas?

Regards,
Ralf

View 1 Replies View Related

SQL Server 2008 :: How To Select First Name Only

May 30, 2015

There is a column for Name with data type varchar 50 the input data is like Alok Kumar, Sunita kuamri, Rohit Gupta Like that. The column contains the data as Combination of First name and second name.I would like to write a select query for selecting first part of name like Alok, Sunit, Rohit and so on only, ignoring the second part of name.

View 3 Replies View Related

SQL Server 2008 :: Delete Before Insert Trigger Statement

Apr 6, 2015

What statement do I use, as part of an insert trigger, to insert xml data from the xml database to a flat file database, to check if a record with a specific ID exists to delete first then insert the changed record, instead of adding the changes or an updated from the original xml database.

What I’m trying to do is take the xml formatted data out of one sql server database and insert the data only in that xml into a another sql database. So I can play with the data.

Problem is if the data in the xml is updated or changed for a specific record on the original xml database then the trigger inserts another copy into the created database (which I don’t want).

This is on SQL Server 2008R2.

View 2 Replies View Related







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