I have an sql 7.0 server. I need to run a query on a remote oracle server then take that output and inner join it with another table on the remote oracle server... then I need to put it a table in a database on my sql server... Anyone know how to do it? Help would be greatly appreciated...
View 2 RepliesI am trying to use the Import Wizard to setup a daily job to import new records based on an ID field (PK). The source database is remote and a replica. I am inserting new records to update my table called the same thing. Both are SQL Native Client
Code Snippet
select *
from [CommWireless].[dbo].[iQclerk_SaleInvoicesAndProducts] as S1
join [IQ_REPLICA].[dbo].[iQclerk_SaleInvoicesAndProducts] as S2
on S1.SaleInvoiceID = S2.SaleInvoiceID
where S1.SaleInvoiceID > S2.SaleInvoiceID
When I parse the query, I keep getting an error message.
Deferred prepare could not be completed.
Statement(s) could not be prepared.
Invalid object name 'IQ_REPLICA.dbo.iQ_SaleInvoicesAndProducts'. (Microsoft SQL Native Client)
Anyone know an easy why to get this to work? Or should I add a create table to verify new records?
I have a very simple Join Query like
Select A, B , C
From Database1.dbo.Table1 Inner Join Database2.dbo.Table2
Now problem is that Database name for the project freuqently changes though Table1 and Table2 names remain constant.
How can I store Datbase name at a central place in a table or as a constant so that I can use them in above query. It will allow me to change them at one place without making changes in all the sps where they are used
- When I disable "allow remote connections to this server" from server properties>connection page, I can still remotely connect to the server from SSMS...so what is the impact of enable/disabling it?
- what is the impact of changing the remote query timeout (on the same page) from default value?
I'm trying to use the following JOIN to access tables on a remote server.SELECT *FROM mywebsite.myweb_db.dbo.[VOD] LEFT JOIN mywebsite.myweb_db.dbo.[VOD_Category]ON mywebsite.myweb_db.dbo.[VOD].Category_VOD = mywebsite.myweb_db.dbo.[VOD_Category].IDX_VODCIf I do a select on one table to the remote server it works. (i.e. SELECT * FROM mywebsite.myweb_db.dbo.[VOD]
Once I do a JOIN I get the following error, does anyone know why? Thanks. :)The number name mywebsite.myweb_db.dbo.VOD' contains more than the maximum number of prefixes. The maximum is 3.
I'm having trouble with a multi-table JOIN statement with more than one JOIN statement.
For each order, I need to return the following: CarsID, CarModelName, MakeID, OrderDate, ProductName, Total ordered the Car Category.
The carid (primary key) and carmodelname belong to the Cars table.
The makeid and orderdate belong to the OrderDetails table.
The productname and carcategory belong to the Product table.
The number of rows returned should be the same as the number of rows in OrderDetails.
I'm using SQL Server 2005.
A piece of software I wrote starting timing out on a query that left outer joins a table to a view. Both the table and view have approximately the same number of rows (about 170000).
The table has 2 very similar columns, one is a varchar(1) and another is varchar(100). Neither are included in any index and beyond the size difference, the columns have the same properties. One of the employees here uses the varchar(1) column (called miscsearch) to tag large sets of rows to perform some action on. In this case, he had set 9000 rows miscsearch value to "g". The query then should join the table and view for all rows where miscsearch is set to g in the table. This query takes at least 20 minutes to run (I stopped it at this point).
If I remove the "where" clause and join all rows in the two tables, the query completes in about 20 seconds. If set the varchar(100) column (called descrip) to "g" for the same rows set via miscsearch, the query completes in about 20 seconds.
If I force the join type to a hash join, the query completes using miscsearch in about 30 seconds.
So, this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER HASH JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
and this works:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE descrip = 'g' ORDER BY balance DESC
But this does't:
SELECT di.File_No, prevPlacements, balance,'NOT PLACED' as status FROM Info di LEFT OUTER JOIN View_PP pp ON di.ram_file_no = pp.file_no WHERE miscsearch = 'g' ORDER BY balance DESC
What should I be looking for here to understand why this is happening?
Thanks,
john
I have an intermittent issue where some remote PC's occasionally fail to execute select queries that have a join or return multiple result sets, however simple one table select queries continue to work okay. When it does happen the PC's needs to be rebooted to get to work again. This may only happen some PC's while others continue to work away okay.
I am using a VB6 application and ADO to connect to the database and the error message I get is a General Network Error, Server Not Found when it fails to execute the query. I have ran SQL Profiler on the server and while simple select queries continue to run away okay, a query a join does not even seem to show up in the profiler. The program has been working fine for 15 years with 1000's of users and has only now become an issue on one site for a number of users. Have tried moving the database to a different server and swapping network cards on the local PC's but can't seem to find the cause. The processor and the memory don't seem to be under load, but I am not sure if there is something else in SQL that is causing it to hang under certain conditions.
There have been network analysts experts in to run scans on the network, but I have not had the results of this back yet. Other applications do not seem to be affected so if this analysis does not show up anything.
I have 2 tables, I will add sample data to them to help me explain...Table1(Fields: A, B)=====1,One2,Two3,ThreeTable2(Fields: A,B)=====2,deux9,neufI want to create a query that will only return data so long as the key(Field A) is on both tables, if not, return nothing. How can I dothis? I am thnking about using a 'JOIN' but not sure how to implementit...i.e: 2 would return data- but 9 would not...any help would be appreciated.
View 3 Replies View RelatedI just want to find out what the difference between a remote login and a remote query is. I'd have thought that a remote query inherently requires a remote login.
View 6 Replies View Related
I've used the ADS sample program and it works well. I looked at the code and changed it to VB (sorry, I'm not biligual yet), and have tried to 'adjust' it so I can connect to a remote PC (XP running IIS) instead of the local PC using the workgroup information database (system.mdw). Any ideas of the connection string that could make the connection?..my intention is to RDA once a week between a mobile device (3g/gsm) and a 'poor man's' IIS web server.
I've tried everything I can think of, and I'm sure its possible since all I'm doing is changing the connection path.
Hello
Can any one tell me the difference between Cross Join, inner join and outer join in laymans language
by just taking examples of two tables such as Customers and Customer Addresses
Thank You
I can query another server from the query analyzer -
SELECT COUNT(*) FROM Dbmsql09.address.dbo.str_name
But if I try to do this from within a stored procedure I get the error msg "set ansi_nulls and ansi_warnings then retry"
My question is this: how do I set the 2 options? Ive tried set ansi_nulls on within the stored procedure and its ignored.
Ive set it, then recreated the stored procedure - same error.
Any suggestions?
Thanks a lot.
Scott
Hi, all -
I know you can execute stored procedures on remote servers in SQL Server 6.5, but can you run regular queries as well using the 4 part name qualification?
Example: select * from server1.database1.dbo.table1
If not, is there another way I can manipulate data on remoter servers?
I am getting the following error:
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [DataDirect][ODBC Sybase Wire Protocol driver]Insufficient information to connect to the data source.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IDBInitialize::Initialize returned 0x80004005: ].
Any idea?
Thanks, YB
I have a user who is complaining of delays when she queries a database over a remote desktop connection. As I have no experience with this, I was wandering if there were any experts out there on how to improve remote performance in SQL Server 05. Any suggestions are appreciated.
View 3 Replies View RelatedI need to run a DELETE query based on 2 tables. I can't use JOIN with delete queries, so how do I do this?
What I initially tried to do was:
Code:
DELETE FROM tblProductState
JOIN tblProduct
ON tblProduct.id_Product = tblProductState.id_Product
WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
Basically, I need to delete FROM tblProductState, WHERE tblProductState.id_State = 54 AND tblProduct.id_ProductType = 1
How can I do this without using JOIN. Use a sub-query? How?
Thanks
Code:
SELECT ISNULL(count (agencyCandidate.JobID) ,0) as Total,
MAX(RecJobAds.PostedDt) as PostedDt,
MAX(RecJobAds.JobTitle) as JobTitle,
RecJobAds.JobId,
MAX (AgencyCandidate.AgencyId) as agentID,
MAX(RecJobAds.AdStatus) as status,
MAX(RecJobAds.CompanyId) as CompanyId,
MAX(RecJobAds.RecId) as RecId
FROM RecJobAds
LEFT JOIN AgencyCandidate
ON RecJobAds.JobId = AgencyCandidate.JobId
GROUP BY RecJobAds.JobId
ORDER BY Total ASC
i have the above query. but i would like to join in a third table, where my query is
Code:
select * from RecruiterMA where Activated = '1'.
anyidea in which place i can put the second query in the first query? thank you very much!
I have to queries I need to combine with a left join and I am having trouble figuring out the syntax. I need to join the first query with a query that contains Unions. The queries need to by joined on File_NBR which is contained in vw_SBC_Employee_Info, vw_ADPFile and SBC_Best_Scores.
Query 1
SELECT
e1.File_NBR,
e1.Division,
e1.Department,
e1.Program,
e1.UNIT,
(e6.Mngr_FName + ' ' + e6.Mngr_LName) AS President,
[Code] .....
Query 2
SELECT
'Skill Rating' as Assessment_Type,
bs.File_NBR as ID,
bs.Skill_NBR,
bs.Best_Score as Score,
bs.Assesment_RND
[Code] .....
A SQL server 7 database I have been developing has recently been moved outside a firewall (for various reasons). I have found that I cannot access query analyser or enterprise manager via an IP address. Is their any way around this?
View 2 Replies View RelatedWhat is a remote connection and query ? And what is a local connection and query? If i'm on management studio on my pc connected to a SQL Server 2012 that is on a server, am I remote ? I didn't had a timeout in SSMS for the following query. Considering the remote query timeout setting is 600
USE Database_name
go
BEGIN
WAITFOR DELAY '00:11';
select * from [dbo].[Table_Name]
END;
GO
Hi,Im running into a strange problem here!I have created a linked server on one of my SQL Servers (local server).Lets call the linked server as LS1.I have an sproc which resides on the remote server(LS1) and uses a FORXML query.On running this sproc from my local server using linked server LS1, Iget a strange serialized data as output instead of the usual XMLoutput.I run the proc asexec [LS1].[DBName].[UserName].[SprocName] '12/1/2004','12/3/2004'the proc accepts 2 parameters as dates.The output generated is smth like:0x44046E0061006D0065004404440061.......(long string of hex codes)Looks like the ouput is getting serialized. How can I avoidserialization OR How to de-serialize this data?Please HelpThanks,Geo
View 5 Replies View RelatedHow can I connect to a remote database, based on Microsoft sql server? Is there any article that can help? I have read few over the net, but not working for me yet
View 3 Replies View Relatedthe sql serve on my machine can connect to a database existing on a server on the network, and i can see the tables and everything for this database. but once i choose sql query analyser from the menu it hangs (goes away) and the cursor changes to hour glass.
any help please.
Hello
DTS is notoriously faster than making the following statement:
insert into synonym_MyRemoteTable
select * from myLocalTable
Why is it so?
Thanks a lot.
I have 2 server A and Server B(remote 3rd party) both SQL server 2012. We need to query the data from remote Server B, we are planning to use either view or stored procedure. The query contain multiple joins to fetch data from around 10 tables(minimum 10 tables is there in initial analysis may be more tables will be added in future).If we are going to access the data through VIEW then we are going to write the below query
SELECT * FROM view-name WHERE Column1 ='abc' My first question
Q1. I have a doubt whether this query will load all the data from table joined in the view and then the WHERE condition implement as in the VIEW definition we are not implementing the filter condition.
Q2. Which will has better performance a stored procedure or view.
Q3. How to call a remote stored procedure or View. linked server is not allowed.
Does the 4 part server.DB.Schema.SPorView name will work.
I had search and find about OPENROWSET/OPENQUERY does it works without linked server.
I was able to do this on a Windows XP machine but I am not able to get this to work on a Windows 2003 Server machine.
I am connecting to the Windows 2003 Server and MS SQL Express via remote desktop on the lan.
This is what worked for me before:
EXEC sp_grantlogin '4800X2ASPNET'
USE MyDB
EXEC sp_grantdbaccess '4800X2ASPNET'
EXEC sp_addrolemember 'db_owner', '4800X2ASPNET'
But for some reason now I am getting this error:
Msg 15401, Level 11, State 1, Procedure sp_grantlogin, Line 49
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15401, Level 16, State 1, Line 1
Windows NT user or group '4800X2ASPNET' not found. Check the name again.
Msg 15410, Level 11, State 1, Procedure sp_addrolemember, Line 75
User or role '4800X2ASPNET' does not exist in this database.
I thought what I needed to add was the name of this pc that will connect remotely, but that doesn't seem to be working.
What weird is the old copy of this query I used had the local pc as the computer name. If I exchange the computer name from the remote pc name to the local 2003 Server machine name, the query works fine. Although that still doesn't let me add connections via Web Developer Express without error. It doesn't make sense to me anyhows, how could I have used the local machine's name and not the remote machine's name to get this to work previously. So confused.
Thank you in advance for help.
Hello.I have a slow response with a system that I am setting up. The OS isWin 2000 Server with SQL Server 2000. My first execution of the SQLprocedure is slow (about 40 seconds), while the second execution isfaster (approx. 3 seconds and what I expect).Using query analyser I can leave the window up and come back ( aftersay 10 minutes ) and my next response is will be slow (and fasterstraight after this).The query itself is the "TOP 10" rows from a table of a remote DB (MSDE2000 - NT station).This is similar to;---------------------DECLARE @SelectRowsFromRemoteDB NVARCHAR(400)SET @SelectRowsFromRemoteDB = "SELCT a.* FROM OPENROWSET('SQLOLEDB', 'Remote', ....) a"EXEC sp_executesql @SelectRowsFromRemoteDB---------------------The CPU usage goes high (with Task Manager), but oddly the task withmost usage is the "Idle Process".Please help with an answeror a good starting point to known where the resources are being used.Regards JC.....
View 2 Replies View RelatedHi, im searching for some help, here is my problem:i have an Appointments Table ex:Appointments AppointmentID : int ContactsList : xmlstruture of the ContactsList column:<ArrayOfContactsInfos> <ContactsInfos> <ContactID>1</ContactID> </ContactsInfos> <ContactsInfos> <ContactID>2</ContactID> </ContactsInfos></ArrayOfContactsInfos>And my Contacts Table:Contacts ContactID : int FirstName : varchar(200) LastName : varchar(200)What i want to do... is find the right query for retreive a list of Appointment based on the FirstName or LastName of a Contact in the ContactsList collumnis this possible to do a sort of join between the xml and the Contacts Table?and if yes how?Please can you help me?
View 1 Replies View RelatedHello,There are 2 tables A(bot_id,bot_date,gomA,gomB) B(gom_id,hist_date, gom_name)
table B holds the names for gomA and gomB in tableA.I wish to select A table and inner join to it gom_name for gomA and gomB IDs.The problem begins when in table B there is multiple values for gom_id, with difference names.So, to retrieve the correct B.gom_id, there is 2 conditions. first bot_date>B.hist_date and after this, if we still gets multiple records then the top record will be selected (order by date)
Any ideas?
I really need some help. I'm going nuts. I been trying to get this to work all morning and i cant get it it work. the simple version is I am trying to join these two sql queries and make them one.
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
WHERE news_id =41
SELECT CONCAT('users.user_fname', 'users.user_lname') *AS edit FROM users WHERE users.user_id=54
my previous attempts are
trouble with query.
I have a news table which has the ID of the original author and a column for the author who last edits the news article. both columns store the id of the author.
now when I'm editing the article I want to get the name of the author from the users table.
I have done a left join to get the first author (original author) but I'm having a real hard time trying to get the name of the author who last edited the record.
they both need to lookup the user_fname and user_lname fields from the users table.
my current sql is below
Code:
SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author FROM news LEFT JOIN users ON news.news_author=users.user_id WHERE news_id=41
which gives me
Code:
news_id *news_subject *news_article *news_author *news_date *news_edited *edit_author *org_author *
41InterclubBunbury IC club has asked us all to attend a inter...771090247547109041836254Adam Green
Now how do i adapt it to get the name of the author to last edit the article? do I do a sub query another left join ???
every way I try doesn't work.
this is what I'm currently trying to get it with.
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (
SELECT CONCAT( users.user_fname, " ", users.user_lname ) AS edit_author
FROM news
WHERE news.edit_author = users.user_id
)
WHERE news_id =41
Heres a different approach that I thought would work
Code:
SELECT news. * , CONCAT( users.user_fname, " ", users.user_lname ) AS org_author
FROM news
LEFT JOIN users ON news.news_author = users.user_id
LEFT JOIN (
SELECT concat( users.user_fname, users.user_lname ) AS edit
FROM users
WHERE users.user_id =54
) AS edit
WHERE news_id =41
but no i get this error
Code:
MySQL said:
#1064 - You have an error in your SQL syntax near '(
SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE' at line 4
I also tried
Code:
SELECT news.*, CONCAT(users.user_fname, " ",users.user_lname) AS org_author, *(SELECT concat(users.user_fname, users.user_lname) AS edit from users where users.user_id=54) AS edit FROM news LEFT JOIN users ON news.news_author=users.user_id
WHERE news_id=41
but got this error
Code:
MySQL said:
#1064 - You have an error in your SQL syntax near 'SELECT concat( users.user_fname, users.user_lname ) *AS edit
FROM users
WHERE us' at line 2
can anyone help me with this query its been driving me up the wall all morning.
Hi,
Please help me in writing a join query.
I have three tables with three columns each. Now I want to retrieve data from all the three
tables as one.
Table 1: EmpId, Date, Points
Table 2: EmpId, Date, Points
Table 3: EmpId, Date, Points
These are related to three different divisions. So, on analysis we have get the no. of points
accumulated on a day. On any day the points can be in all three divisions or in any one or two
divisions. My database SQL Server 2000.
So, how to get this sort of output.
EmpidDatePts(DIv1) Pts(DIv2) Pts(DIv3)
V00107-24-2004NullNull25
V00207-24-200420Null25
V00307-24-2004Null30NUll
V00107-23-200415NullNUll
V00207-23-2004102525
V00107-22-2004Null1025
I'm badly in need of help. Any sort of help is appreciated.
M.L.Srinivas
how do I add in a 3rd and 4 table to the mix and get their sums and group by ...The top select works fine the lower one is what I was thinking is this.....
Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal
FROM dbo.Cust P
INNER JOIN
(SELECT
CustID, Sum(Days) as Trips, SUM(MoneySpent) as MoneySpent,
Sum(Balance) as Bal
FROM dbo.CAsh
Where Accumulator='DP'
GROUP BY CustID) A ON P.CustID = A.CustID
The 2 new tables are ViewDis (X) - Sum (Distance)and ViewACC (Y) Sum Fields required Hours, Min, (Group by) Building and Date.
Select Distinct
P.CustID, P.Acct, P.Title, P.FirstName, P.LastName,
A.Trips, A.MoneySpent, A.Bal, X.Distance, Y.Hours, Y.Min, Y.Building
FROM dbo.Cust P
INNER JOIN
[code]....