When running a query with a table from another SQL7 Server that is linked, if only 1 table from each server is specified, the query runs fast and returns only the data rows needed from the linked server. When another table is added on to query (on the server running the query) the query processor tries to run a remote query and return over 400,000 rows over the network. Any suggestions?
View 1 RepliesHello All,
I created two database instances in the same MS SQL SERVER 2005 named hafeez and local. I created a linked server from hafeez database to local named HTOL
I created a dummy table in local database named samplocal.
Now i am able to list the records using the linked server, for this i used the following query.
SELECT * FROM OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL');
Now the problem is, i am not able to insert the rows into the samplocal table using linkedserver. I am using the following query to insert the rows and getting the following errro.
INSERT INTO OPENQUERY(HTOL,'SELECT nameandval FROM SAMPLOCAL') VALUES('tertertetttwertw');
Msg 7356, Level 16, State 1, Line 1
The OLE DB provider "SQLNCLI" for linked server "HTOL" supplied inconsistent metadata for a column. The column "nameandval" (compile-time ordinal 1) of object "SELECT nameandval FROM SAMPLOCAL" was reported to have a "Incomplete schema-error logic." of 0 at compile time and 0 at run time.
Is the above query correct? or shall i miss anything?
Please guide me.
Thanks in Advance
Hafeez Shaik.
Hi All,
I am using sql server 2005. I stuck out in a strange problem.
I am using view in my stored procedure, when I run the stored procedure some of the rows get skipped out means if select query have to return 10 rows then it is returning 5 rows or any other but not all, also the records displyaing is randomly coming, some time it is displaying reords 12345 next time 5678, other time 2468.
But if I run seperately the querys written in SP then it returns all the rows. Please give me solution why it is happening like this.
There are indexes in the tables.
Once I shrink the database and rebuild the indexes, from then this problem is happening. I have rebuild the indexes several time, also updated the statistics but nothing improving.
But nothing is improving
Does sql server have a mechanism (aside from count()) that for any given SELECT query will tell you only how many rows it will return without actually returning the data?
The reason for this is that we have a generic lookup form in an application that is used on almost every screen (we have a lot of screens, so it gets a lot of different, sometimes complicted, queries passed to it to use for the lookup, and having to manually edit the query to use count over all the select clauses doesn't seem like the best way to handle this. If we could do a kind of 'trial run' against the server just to get the number of rows and use that to help set up the form, that would be ideal.
When querying any of our database tables (returning all rows from within the enterprise manager) we are getting the following error message:
"The query cannot be executed because some files are either missing or not registered" Can anyone help me with this....
I have three tables X,Y,Z. Table 'Y' is having foreign key constraints on tables 'X' and 'Z' (which happen to be primary key tables).
I would like to run a query in which I can retrieve rows from Table 'X' only if the matching rows in Table 'Y' have "ALL" their matching rows available in a simple query being run on Table "Z".
The "All" part is very important.
For more clarification, let me give you an example. Table "X" is equivalent to a mathematical "Equation" table which consists of an equation made up of several "Fields". These fields are stored in Table "Z". Table "Y" contains the primary keys from Tables "X" and "Z". i.e. Table "Y" determines what fields are required for an equation to be complete.
I am having a query "Q" on Table "Z" (Fields table) which returns me a bunch of Fields. Now, on the basis of these fields, I want to retrieve only those Equations (Table "X") which have "ALL" their required Fields present in the bunch retrieved by the Query "Q".
I hope I am clear enough.
Does anyone have any solutions???
HI there,
Can someone please help with a query I have? Basically I want to return all rows in a table that have multiple date entries that are different. For example:
163610737464753422005-12-30 00:00:00.000
163610737464753592006-03-10 00:00:00.000
This security 1636 has two entries in the DB with different dates. They are lots of securities with multiple entries with the same date but I need a list of the ones with different dates. Any ideas please?
Thanks!!!!!
S
Hello SQL gurus!I am trying to write a query that will return a set of continguous rowsfrom a table, and limit the number of rows returned when a maximumtotal has been reached by adding a value in one of the columns.For example, the two columns below represent 2 columns in a table.a 2b 2c 2d 3e 4f 5g 5I want to start at, say "c", and return all the rows after it as longas the sum of the numbers in column 2 (starting at "c") don't exceed10. The result I'm after would be thusc 2d 3e 4....because 2 + 3 + 4 = 9 < 10Any ideas? Many thanks.
View 4 Replies View Related
Given the following data how do I make a SQL query that returns only 1 row per product?
The returned rows need consist of only currently active products (that is WHERE (DateEffective <= { fn NOW() }).
The twist: Sometimes a product will have duplicate DateEffective records. In that case, only return the record created latest because that's the most current data that exists for a product. RowTimeStap is when the record was created.
Example Data:
HistoryID ProductID Name Color DateEffective RowTimeStamp
(auto-number PK)
1 1 Wheel Red 2/1/2008 2/1/2008
2 1 Wheel Blue 3/5/2008 3/1/2008
3 1 Wheel Orange 3/5/2008 3/2/2008
4 1 Wheel Black 1/1/2010 3/3/2008
5 2 Knob Blue 3/2/2008 3/2/2008
6 2 Knob Green 3/3/2008 3/3/2008
Query should return:
3 1 Wheel Orange 3/5/2008 3/2/2008
5 2 Knob Green 3/3/2008 3/3/2008
The query I've created fails on the twist part. I have to allow duplicate DateEffective to keep a history of changes.
Can anyone help?
How to design at database level such a way so that when I implement a SQL query that returns one hundred thousand rows only display 25 rows at the client (Web page at a time). How can I accomplish this?
Once I display first 25 rows then how do I bring next 25 rows and so on. Can it be done via paging or there are other techniques. However I asked to design this in the database level. I am using MS SQL Server 2008. Front end Visual Studio 2010. I use C# for coding.Â
I know this table is designed wrong for what I am doing but I hope Ican do it. I have a table like this.Prod_A_Jan, Prod_A_Feb, Prod_B_Jan, Prod_B_FebI want a query that returns data like this (two rows of data)"ProdA", Prod_A_Jan, Prod_A_Feb"ProdB", Prod_B_Jan, Prod_B_FebI know two queries can get it but I want one. Any Help would begreat!!!Sheila T.
View 3 Replies View RelatedHi Everybody,
I have 2 servers one has SQL 7.0 running on NT4.0 and another has SQL 2000 running on W2K Server. I have established a linked server for SQL 2000 from SQL 7.0.
I don't have any problem of creating linked servers for sql 2000 from sql 7.0. I am also able to create linked server logins to access the sql2000 tables. Even I am able to see the tables on the Databases.
EXEC sp_addlinkedserver 'SQL2K', '','SQLOLEDB','GENOMESQL2000'
sp_addlinkedsrvlogin @rmtsrvname = 'sql2k' ,@useself = 'false' ,@locallogin = 'genomeACCT1',@rmtuser = 'test2k',@rmtpassword = 'test2k'
exec sp_tables_ex N'sql2k' (Upto this I don't have any problem)
When I tried to execute a particular table from the database, I am getting the following error.
Select * From sql2k.northwind.test2k.payroll
Error:
******
Server: Msg 7314, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' does not contain table '"master"."test2k"."ven"'.
Can anybody give suggestions to solve this problem.
tks in advance,
Vasu
Hi
Im sorry if im in the wrong section. My problem is a very common one and it has been hard for me to find the fix.
Ive gone thru Component Services, Firewall settings, Registry and Microsoft Sites, but to avail.
"The operation could not be performed because the OLE DB provider 'SQLOLEDB' was unable to begin a distributed transaction.
[OLE/DB provider returned message: New transaction cannot enlist in the specified transaction coordinator. ]
OLE DB error trace [OLE/DB Provider 'SQLOLEDB' ITransactionJoin::JoinTransaction returned 0x8004d00a]."
My Begin Tran doesnt start at all.
begin tran
use smartpos
go
set identity_insert smartpos.dbo.smp_product on
insert into smartpos.dbo.smp_product
(prodId
,[Description]
,barcode
,BuyPriceExclVat
,BuyPriceVatTYpeId
,point
,SupplierID
,Ref
,QtyType
,QtyFactor
,MinStock
,MaxStock
,Active
,ItemType
,ClientLastUpdate
)
select prodId
,[Description]
,barcode
,BuyPriceExclVat
,BuyPriceVatTYpeId
,point
,SupplierID
,Ref
,QtyType
,QtyFactor
,MinStock
,MaxStock
,Active
,ItemType
,LastUpdate
from server.smartpos.dbo.smp_product svr
where not exists (select prodid from smartpos.dbo.smp_product where prodid=svr.prodid)
set identity_insert smartpos.dbo.smp_product off
Can anyone help me please.
Thanks
I added a Linked Server, like this:
EXEC sp_addlinkedserver
@server = 'ServidorPrincipal',
@srvproduct = 'SQLEXPRESS',
@provider = 'SQLNCLI',
@datasrc = 'TIC-IISQLEXPRESS',
@catalog = 'BDPrincipal'
GO
But now I can't do a selection:
SELECT * FROM ServidorPrincipal.BDPrincipal.dbo.tblCADENA;
Because it sends this message:
Server: Msg 7356, Level 16, State 1, Line 1
OLE DB provider 'SQLNCLI' supplied inconsistent metadata for a column. Metadata information was changed at execution time.
OLE DB error trace [Non-interface error: Column 'ID_CADENA' (compile-time ordinal 1) of object '"BDPrincipal"."dbo"."tblCADENA"' was reported to have changed. The exact nature of the change is unknown].
The linked Server is called "ServidorPrincipal".
The database is: BDPrincipal.
And the table is dbo.tblCADENA.
"changed at execution time" ¿What does it mean?
What is happening here?
HI All,
I need help in sp. I have the sp that return the result but then i also want count how many rows are the result. Does anyone know how to do that?
This is my sp, and when it returns the data i also want it to return the how many rows are the result.
CREATE procedure dbo.ph_getphonebookoption
@country nvarchar(100),
@company nvarchar(100),
@office nvarchar(100)
as
select
Ext,
FName,
LName,
Title
from
phonebook
where country =@country
and company=@company
and office =@office
select count (*)
GO
Trying to do a paging scheme without using #Temp tables in MS SQL 7.0
Client calls a sp passing 1 and sql returns the first 100 records.
Client sends a sp passing 100 and gets the next 100 records.
Process continues till @@fetch_status <> 0 or the client can stop sending requests.
I implemented it easily using fetch absolute into a #temp table but this has dissaster potential in a multiuser environment since everyone will be using this query continously and there is no user limit.
What other options do I have?
Thanks,
John
is there is any way to find out whether my query return 0 rows inside a stored procedure.
View 2 Replies View RelatedHi All,
I have a question,
Select top 10 * from employee
the above statement return top 10 row.
but i want the rows from the table other than the top 10. Can any one help me to get it.. iam using SQL server 2005
Thanks for the help
Thanks
Bhaskar
Hi all,
I'm trying to change the datasource of a SQL Server LinkedServer using SMO
I've got the relevant linkedserver as an object, changed the DataSource property, and selected it again to confirm the change.
However, the change is lost as soon as the object is destroyed.
Looking at BoL I think I need to be using alter() method of the linkedserver class but I just get an error
Alter failed for LinkedServer '<servername>'.
any ideas how I should be using this class to do what I want?
I can post my script (PowerShell) if it would help.
Thanks, Robin.
I'm copying almost all contents of one table from one server/db to another. In relation to speed, what is the difference of using SSIS vs a linked server and sp? I'm going to do the benchmarks, but I'm curious about behind the scenes kinda stuff, theoretically which one if any should be faster and why?
View 5 Replies View Relatedhi,i have a stored procedure like this in SQL server ,it returns proper value if data is there for a given id.But if there is no data,it returns row/rows of NULL value and that is counted towards "number of row returned"..Shouldn't it be like,if there are null values in a row,that row should not be counted towards rows returned value .?Rightnow if no value returned from either of the select,it still returns as 2 rows instead of 0 rows.How do handle this situation in SQL? thanks for your help
SELECT SUM(col1) AS SUM_COL1, SUM(col2) AS SUM_COL2, SUM(col3) AS SUM_COL3, SUM(col4) AS SUM_COL4FROM TABLE1WHERE (ID = nn) UNION all
SELECT SUM(col22) AS SUM_COL22 ,cast(null as int) as c1,cast(null as int)as c2,cast(null as int) as c3FROM table2WHERE TABLE2 = nn)
Hi,
Any idea how to write a (T-)SQL Stored Procedure which uses a SubQuery calling
a SELECT query from another SP??
Something like...
CREATE procedure spThisSP(@param varchar(20))
AS
SELECT theColumn FROM theTable WHERE theColumn NOT IN (EXEC spAnotherSP @param)
Cheers
I have a database that has 100,000+ records in a table. Am I better off returning all of the records from a search even if it is 50,000 records or is it better to do a SELECT COUNT(*) And nested SELECT TOP x statements to only return 1 page of results? What is the best practice for a situation like this?
SELECT * FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another.... AND...
OR
SELECT COUNT(*) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @OrderAnd
SELECT TOP(@pagesize) FROM (SELECT TOP(@pagesize * @pagenum) FROM myTable LEFT OUTER JOIN ... LEFT OUTER JOIN......WHERE something=@something AND another... AND... ORDER BY @Order)ORDER BY @revOrder
What is the easiest way to return rows 200 through to 300 of a 500 row result set using SQL? Is there a simple way of doing this or do I need to write some Transact SQL? Any ideas would be appreciated.
Thanks
Rod
Hi.
How does one return a range of rows.
I know that "Top 5" will return rows 0 - 5
but, how do I get 6 - 10?
thanks
The following query returns 2142 rows which is correct.
Code:
select COUNT(*), sum(v.currentMarket)
from TRMaster m
inner join TRValue v on v.Year = m.Year and v.Parcel = m.Parcel
inner join TRProp p on P.PropCode = V.PropCode and p.PropType = 'A'
where m.Year = 2013 and m.deleted = 0 and m.ReviewDateTime is null and m.Status = 1
group by m.Year, m.Parcel
having SUM(v.currentmarket) > 0
How can I convert this query so that it returns just the count of 2142?
I have a Dataset that I am populating from a SQL Query. I am then using the dataset to populate a report in Reporting Services. What I want to do is return a standard number of rows in my dataset. (Let's say 10.) Even if my query does not have any rows in it, I want 10 empty rows returned to the dataset. If my query has 7 rows in it then I want to add on 3 empty rows and return it. I will not have more than the standard number of rows.
I cannot get the table in the report to show up if the dataset is empty, but still want the table to display with 10 empty rows. I have searched how to do this online but am getting nowhere. (I know how to add one empty row but not a set number.
Hello All
Please can anyone advice me how I can fetch list of all tables which have no records in it in sql server
Thanks
Hi.How does one return a range of rows.I know that "Top 5" will return rows 0 - 5but, how do I get 6 - 10?thanks
View 17 Replies View RelatedHow can a SQL statement be written to return a specified range ofrows? For example:-- tblContact-- (-- SSN char(9),-- FirstName varchar(50),-- LastName varchar(50)-- )-- This table contains 500 rows.Select * from tblContact -- Return only rows 5 through 10Thanks
View 2 Replies View RelatedAre there any way to execute a procedure and return N random rows?
I have a question when I work on LinkedserverThe Linkedserver name is [Hp-server],the Datebase name isNewexec,the Table name is Customers_CoypTestThe SQLScript is below:Update [Hp-server].Newexec.dbo.Customers_CoypTestset Unitname=b.Unitnamefrom [Hp-server].Newexec.dbo.Customers_CoypTest a joinNewexec.dbo.Customers_CoypTest bon a.Cid=b.Cid and b.Cid='Tony'The server returns ERROR like this:a) can not open this table '"Newexec"."dbo"."Customers_CoypTest"'(come from OLE DB provide server 'SQLOLEDB'). provide server do notsupport index scan on the data source.b) [OLE/DB provider returned message:Error occured whenmulti-operate.If possible, please check each OLE DB status value.Nowork had been completed.]c) OLE DB Error trace[OLE/DB Provider 'SQLOLEDB'IOpenRowset::OpenRowset returned 0x80040e21:[PROPID=DBPROP_COMMANDTIMEOUT VALUE=600 STATUS=DBPROPSTATUS_OK],[PROPID=Unknown PropertyID VALUE=True STATUS=DBPROPSTATUS_OK],[PROPID=DBPROP_IRowsetIndex VALUE=TrueSTATUS=DBPROPSTATUS_NOTSUPPORTED]].
View 5 Replies View RelatedHi,
I am not expert on SQL server, i need to be able to access different server from withing sproc,
1. Is Linked Server is the best aproach.
2. If yes then how i set it up that it take all authenticated users of this server to remote, i tried with impersonate but it gives error that NT authority dont hae access etc etc (when i try run a sql that accesses remote server)
3. The reason i need to access remote server is ... one of the that table in my db (logTable) is getting to big it is 10 times the size of rest of the db altogether so i though i move it out not just from same db actully put it on a seprate server. so that all the tasks about main db would become easy etc.
any idea help
Sajjad