Query To Get Linked List Kind Of Data From The Table
Sep 18, 2006
hi Experts,
I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of
ID
Duplicate_of
State
77637
65702
Duplicate
65702
42217
Duplicate
42217
-
Verified
i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.
Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate
i appreciate if somebody can help me think in some line to give me some idea.
/soni
View 1 Replies
ADVERTISEMENT
Sep 18, 2006
hi Experts,
I have a Issue table which stores the below data for many issue. some issue are duplicate to other and they are stored in a field Duplicate_of
ID
Duplicate_of
State
77637
65702
Duplicate
65702
42217
Duplicate
42217
-
Verified
i wanted to write a query or some stored procedure when passed 77637 should help me get 42217.
Hint : 77637 when passed has field Duplicate_of which point to 65702 and his state will be Duplicate, 65702 will be duplicate to 42217 and state will be duplicate and 44217 is not duplicate to anything and state will be other then Duplicate
i appreciate if somebody can help me think in some line to give me some idea.
/soni
View 5 Replies
View Related
Feb 13, 2008
I have two tables that share a common identity row. I need to build a query where data that exists in one table does not contain data in the other table. For example, table 1 has columns of Owner_ID, LastName, FirstName and table 2 has columns Auto_ID, Owner_ID, AutoMake. Both tables are joined by the Owner_ID column. I need a query that provides all owners from table 1 who do not have an entry in table 2.
Thanks in advance,
Mark
View 5 Replies
View Related
Jan 20, 2008
I have a drop down list populated using sqldatasource1 it is populated with [item]
However, I need it to be poplulated with the three fields below AND, I don't want duplicates.
SELECT [Item], [Alias1], [Alias2] FROM [Authors]
View 6 Replies
View Related
Feb 5, 2015
Say you have a table that has records with numbers sort of like lottery winning numbers, say:
TableWinners
num1, num2, num3, num4, num5, num6
33 52 47 23 17 28
... more records with similar structure.
Then you have another table with chosen numbers, same structure as above, TableGuesses.
How could you do the following comparisons between TableGuesses and TableWinners:
1. Compare a single record in TableGuesses to a single record in TableWinners to get a count of the number of numbers that match (kind of a typical lottery type of thing).
2. Compare a single record in TableGuessess to ALL records in TableWinners to see which record in TableWinners is the closest match to the selected record in TableGuesses.
View 8 Replies
View Related
Aug 9, 2013
I'm using the following syntax to extract a list of all the table names on a linked server:
EXEC sp_tables_ex
@table_server = MY_SERVER_NAME
It outputs a list of tables into 4 columns in the result window.Is there a way an can use this as a 'SELECT * FROM ... " command so that I can organize records, insert into, etc etc ?
View 3 Replies
View Related
Jun 7, 2012
I have a database that has 370 tables that match %_REF_% naming. All of these tables have a column call ds_name.
I need to get a list of all values of ds_name for each table.
View 3 Replies
View Related
Jun 18, 2004
Strange one here - I am posting this in both SQL Server and Access forums
Access is telling me it can't append any of the records due to a key violation.
The query:
INSERT INTO dbo_Colors ( NameColorID, Application, Red, Green, Blue )
SELECT Colors_Access.NameColorID, Colors_Access.Application, Colors_Access.Red, Colors_Access.Green, Colors_Access.Blue
FROM Colors_Access;
Colors_Access is linked from another MDB and dbo_Colors is linked from SQL Server 2000.
There are no indexes or foreign contraints on the SQL table. I have no relationships on the dbo_ table in my MDB. The query works if I append to another Access table. The datatypes all match between the two tables though the dbo_ tables has two additional fields not refrenced in the query.
I can manually append the records using cut and paste with no problems.
I have tried re-linking the tables.
Any ideas?
Thanks,
Brad
View 4 Replies
View Related
Sep 1, 2006
How can I list indexes with a (SQL) query ?
Thanks
View 1 Replies
View Related
Sep 19, 2005
Hi,
I have a query which gives me the following results:
lLedgerCode sGLCode bDebit TotalSum sGLDesc
61 6843000701 0 600ALPS Holding
33 8345000701 0 1116ALPS Premium Due
56 1000000701 0 1116Regular Premium Income
63 6836000701 1 516ALPS Group Holding
61 6843000701 1 600ALPS Holding
30 6842000701 1 600ALPS Policy Clearing
33 8345000701 1 1116ALPS Premium Due
The Column bDebit has either value '0' or value '1' in it ('0' being debit - positive amount, and '1' - credit, negative amount).
I would like it to show the net amount for each account. Therefore in plain English I would like to take all GLCodes that are the same (eg 6843000701) and sum all amounts that have debit value of '0' and subtract all amounts that have debit value '1'. Therefore I would only see '6843000701' code once, and the amount would be '0' becase 600 - 600 = 0.
The current query is:
SELECT dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, SUM(curAmount)As TotalSum, dbo.tbLedgerCode.sGLDesc
FROM dbo.tbGLTransactions
INNER JOIN dbo.tbLedgerCode
on dbo.tbGLTransactions.lLedgerCode = dbo.tbLedgerCode.lLedgerCode
WHERE dbo.tbGLTransactions.lGLExtractRun = '452'
Group By dbo.tbGLTransactions.lLedgerCode, dbo.tbGLTransactions.sGLCode, dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Order By dbo.tbGLTransactions.bDebit, dbo.tbLedgerCode.sGLDesc
Is someone able to help me as to how i need to modify this query to get the desired result?
Thanks!
View 8 Replies
View Related
Jul 26, 2007
Hello all,
I am writing Stored Procedure to perform various kind of search on Database Tables.
I having requirement like Exact Phrase,Any of the word,All Words and Refine search.
I am using Full text Queries for this.
I am stuck with "All Words" search query. Can anyone please guide me how to write query to perform this kind of search. Below is the little elaboration about All words search.
Query should return all the results which contain all the words from search phrase irrespective of order.
e.g. if i search am searhing "Green House Effect" phrase then query should return all the records which having these three words in any order. like Green fds dfasd House fdsd fdsdf Effect or House fds fds Green Fd fds Effect or Effect fsda fdsa Green fd fdsa HOuse.
all the records selected by query must have all words exist in any order.
Suggest me the logic or query or any SQL Server keyword which i can use for this.
Please do the needful ASAP. Thanks.
View 1 Replies
View Related
Mar 15, 2004
Hi,
from a single numeric value, say 'n', in a field in a table, i'd like to create a SELECT query that produces a list from 1 to n, e.g
if the value was 6, I would like selected back:
1
2
3
4
5
6
Any pointers?
Greg
View 5 Replies
View Related
Feb 2, 2004
Hi masters
well got a SQL server 7 and a table
and got drop down list in my asp page
what I want to do is that the content and value of the drop down list will be grabbed from the database, when page loads.
can you please guide me
many thanks
S
View 1 Replies
View Related
Jul 23, 2005
Hello,I'm not an expert in SQL, if you could help me for that littleproblem:I had tree simple tables with their fields:[Client] IdClient, Param[Sale] IdSale, IdClient, Param[Param] IdParam, ValueHow can I retrieve a recordset with this columns ?IdClient, IdSale, ValueOfParamClient, ValueOfParamSaleThe problem is that I can retrieve a Param for one table (Client orSale) like this request :SELECT Client.IdClient, Sale.IdSale, Param.ValueFROM(ClientINNER JOIN SaleON Sale.IdClient = Client.IdClient)LEFT JOIN ParamON Param.IdParam = Sale.ParamBut how can I retrieve the Param of the another table in a simplequery ? (because I would also like that it works for access)Thank for your help,Marc
View 4 Replies
View Related
Apr 9, 2008
Hi,
I need a query to get the index names of particular tables. for eg.. i have some tables like emp_data,emp_job....etc..Now i want to find all indexe names for those tablenames that starts with emp........ Plz help me...
View 6 Replies
View Related
Jan 8, 2006
I need to save MicroStation "elements" along with other general information. Is that possible to do with SQL Server 2005?
View 1 Replies
View Related
Jun 13, 2001
I have an ODBC DSN that lets me see the data via Access, however, in EM I set up the DB2 server as a linked server using the OLEDB provider for ODBC, with a Linked server name of DB2DB, a product name of MY DB2 connection and the data source of db2 (which is the DSN name)
The Provider string, Location and Catalog I leave blank
Under security, I assign all users to be a valid username/password
I can then see the tables in EM, but when I try to query them in QA I get this
Select * from db2db...customer
Server: Msg 7313, Level 16, State 1 Line 1
Invalid Schema or catalog specified for provider 'MSDASQL'
I am using SS7.0, and DB2 connect V7.1
I do not have/use SNA server
My actual requirement is to have a job autmatically run every day to copy certain records onto the SQL Server, but the first step is to be able to query the DB2 data
any ideas ? I think I need to enter something in the catalog box, but what ?
thanks in advance.
View 1 Replies
View Related
Oct 12, 1999
I am trying to run a select statements against linked server.
I have NT server running SQL7 Enterprice and Rumba2000. I used Rumba driver to configure a DSN (ODBC based) to a AS400/DB2. Then I created a linked server. Good news are : I can see a list of tables.
Bad news : when I am trying to run a select statement either through stored procedures or directly in SQL analyzer I am receiving error message Object 'linkedservername.databasename.tablename' doesn't exist.
What am I missing here?
View 2 Replies
View Related
Jan 24, 2008
Hey there! I've take a look around the forum and didn't find anything about my problem. The SelectCommand of my SqlDataSource get data from 3 tables. SqlDataSource.SelectCommand = "SELECT [sa_Profil].[ID], [Nom], [Prenom], [Telephone], [ID_Reference], [ID_Appel], [Nom_Reference], [Type_Appel] FROM [sa_Profil], [sa_Suivi], [sa_Reference], [sa_Appel] WHERE [sa_Profil].[ID] = [ID_Profil] AND [ID_Reference] = [sa_Reference].[ID] AND [ID_Appel] = [sa_Appel].[ID]" How can I wrote my InsertCommand & UpdateCommand ? I mean, INSERT INTO Table1 Table2 ... doesn't exist ... Thanks for your help!
View 2 Replies
View Related
Feb 23, 2007
Hi,
I am likely to work on moving our ETL system from SQL server 2000 to SQL server 2005. Code is being re written. I wanted to know from anyones previous experience like what kind of activity/data can cause a package to fail. I need to check that a certain package fails and logs error to the server error log and stops. And also check that a certain package fails, logs an error to the server error logs but continues to run.
Any thoughts in this regard would be very helpful for me.
Thanks
Melissa.
View 2 Replies
View Related
Jun 9, 2006
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?
When I tried to import system.data.oracleclient, it was not available. Do I need to install anything for being able to use it ?
Thanks a lot.
View 4 Replies
View Related
Aug 2, 2007
hello,guys,
my question is :
how do I query data from the linked foxpro database?
more:
I have linked a visual foxpro server to my sql server database by using the addserver clause.
Two server is in a local network.
My linked foxpro server named 'fox'.
its datasourse is not a dbc file, but a directory of dbf files ,and its full path is d:foxpro object.
In the directory ,there are three table,'show2003.dbf','sysu.dbf','szszj.dbf'.
And the .dbf files are not in any database.
They are just three files in the same directory.
There is no dbc file.
Now I can see the table list on the right page of the linked server.
But there is something wrong with my sql clause.
sample:
select * from fox..sysu
then the message is:
server: message 7313，level 16，status 1，row 1
the appointed constructure or directory to the provider is inefficacious 'MSDASQL'
I know I may use 'openquery',or 'openrowset'.
The problem is that variable is not valid in 'openquery' and 'openrowset'.
But I must use variable.
so ,please give me some advice.
Thank you very much
View 4 Replies
View Related
Aug 16, 2007
Hi,
Please bear with me if my vocabulary or phraseology is wrong (and if it is, I wouldn’t mind being corrected).
Background:
We have an application that uses a Cache database (which is a complete black box for me). The data is exposed to us through ODBC. We have set up a linked server that I can see the names of the tables and views in Enterprise Manager and can query on them with Query Analyzer.
Problem:
There are a lot of extra long string fields. I can see the data in both Crystal Reports and Access (in Crystal they are shown defined as sting [32395] and in Access they are shown as a Memo field).
I cannot get Query Analyzer to display the data. I have tried the following select statements:
select definition_text from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as ntext) from cwsavpmtest_live..system.problem_def_data
select convert(ntext,definition_text) from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as text) from cwsavpmtest_live..system.problem_def_data
select convert(text,definition_text) from cwsavpmtest_live..system.problem_def_data
select cast(definition_text as varchar(8000)) from cwsavpmtest_live..system.problem_def_data
select convert(varchar(8000), definition_text) from cwsavpmtest_live..system.problem_def_data
… and get the same error for every one:
quote:
Server: Msg 7341, Level 16, State 2, Line 1
Could not get the current row value of column '[cwsavpmtest_live]..[system].[problem_def_data].definition_text' from the OLE DB provider 'MSDASQL'.
[OLE/DB provider returned message: Requested conversion is not supported.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowset::GetData returned 0x80040e1d].
So my question is: Am I out of luck and have to be content with looking at the data in Access or Crystal or is there some way I can look at these fields through Query Analyzer?
Thanks,
Laurie
View 1 Replies
View Related
Jun 8, 2006
I need to link some data from SQL Server 2005 with Oracle 10 data.One way is to link Oracle server to SQL Server and use ROWSOURCE forretrieving data.What other ways for joing data from both databases exist ?Can I do it from SQLCRL VB - Stored Procedure ? If yes, what objectsshall I use for opening database and running an sql ?Thanks a lot.
View 3 Replies
View Related
Mar 21, 2008
Hi Friends,
I want to have solution for one of the problem.
The requirement is like this :
I want to write stored procedure or function which will take parameter as SQL Server name, DB name, UserName and passwod.
This Stored proc will connect to Remote server using these parameters and will get the count of the rows in one of the table.
I created the connection using the linked server
EXEC sp_addlinkedserver @SerevrName,N'SQL Server'
EXEC sp_addlinkedsrvlogin @SerevrName, False, Null, @ServerUserName,@SerevrPws
Now I am trying to get count using following query :
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'
But the question is that the execution goes this way :
exec(@SQLQuery)
Now how to assign this count value to some variable so that I can use it later ...?
Going forword I want to use cursor and get the rows in these table using cursor ...?
How can I assign values returned from any runtime query to temporary variable or table ...?
I tried another approach also:
I put remote connection and query execution in inner stored proc called usp_GetTableRowCount
set @SQLQuery = 'SELECT count(*) FROM [' + @SerevrName + '].' + @SrcDataBaseName +'.dbo.<<TableName>>'
exec(@SQLQuery)
and in outer stored proc : referenced the inner stored proc like this
exec @AFSDataRowCount = dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>>
The execution of dbo.usp_GetTableRowCount <<Server Name>>,<<User Name>>, <<Password>>, <<DBName>> gives me exact no of rows
but when I see value of AFSDataRowCount, I get 0.
Kindly help me out whereever I am making mistake or else pls tell me any other approach to follow.
Thanks in advance.
View 6 Replies
View Related
Feb 25, 2014
We are having xml data in a column. Is it possible to write a trigger to generate a mail if particular kind of data get inserted in a tag.
For ex:
<File AF="910" PTO="ATN_P76035_PSQO" NNO="54545465" KTNNN="AX2" KL="" AD="99" PrqnT="AX2" Stab="21545" KE="45454" TE="65465" Rsaa="BBBB" AK="54544.AX2.POEAX2.546546546.NONTP.NONTP" AK2="">
In the above xml data if we have the value 21545 in Stab tag the trigger needs to be executed and mail needs to be sent to a distribution list.
View 1 Replies
View Related
May 12, 2015
I know how to use Linked server query to retrieve data from two different sql on premise instances.I would like to try the same on sql server instances hosted on azure.When I connect to sql instance, I don't see ServerOBjects->Linked server. I just see Database and security.Is this possible on sql azure, if so how can we achieve it
View 4 Replies
View Related
Sep 17, 2015
I am trying to create a Datazen query against a Microsoft SharePoint List. The Data View successfully returns records from the list; however, it appears that the Query String parameter is entirely ignored. I want to use the query string to grab only records with a certain status value.
So far I have attempted specifying a View and Filters:
?View={B9FF1729-AB8E-4C55-B17C-C660480334BB}&FilterField1=Status&FilterValue1=Closed&FilterField2=Request%5Fx0020%5FType&FilterValue2=Project
Just the filters:
?FilterField1=Status&FilterValue1=Closed&FilterField2=Request%5Fx0020%5FType&FilterValue2=Project
And even:
?ThisWillDoNothing
All return the complete set of records in the list. I do not see a single example or comment online other than the claim that SharePoint List sources are supported. How I can specify a Query String value that will filter the list records?
View 2 Replies
View Related
Oct 18, 2007
Hi,
I am trying to delete a row in excel [Sheet1$], where this data in that row is used in a pivot table in same excel [Sheet2$] which should also get deleted, when i try to delete that row using "delete ... from [Sheet1$] " it is throwing an error message "Deleting data in a linked table is not supported by this ISAM. (Microsoft Office Access Database Engine)"
Can you please guide me in overcoming this error...........
Thanks in advance,
Warm Regards,
gchanduu
View 2 Replies
View Related
Jul 20, 2005
ciao a tutti...ci risono...devo migliorare delle performance di accesso ad una tabella...la tabella non ha indice primario, ne altri indici...sulla tabella ci accedo con select di questo tipo..select @ExistInOAG = count(*)FROM caprs05dev.dbo.OAGWHERE Air_Carrier = @Aircarrier andcast(ltrim(rtrim(Flt_nbr)) as int) =cast(ltrim(rtrim(@ComFltNbr)) as int) andAipt_Dpt = @AIPTDep andAipt_Des = @AIPTDst andDT_FLIGHT = @Date_Rif andconvert(varchar, STD, 102) = convert(varchar, @STD,102) andconvert(varchar, STA, 102) = convert(varchar, @STA,102)ho provato a creare diversi tipi di indici, ma le performance migliorile ottengo se lascio stare tutto come era senza nessun'indice!!!! visembra possibile???le prove che ho fatto sono queste...inserire un indice tipo Primary con i campiFlt_nbr,Aipt_Dpt,Aipt_Des,STD,STAcreate Unique, tipo Index, con Ignore duplicate key...create as clustered checcato e fill factor 100%... ci accedopraticamente solo in select nel file...avete consigli per migliorare le performance di accesso allatabella????grazie.. stefano!!!
View 2 Replies
View Related
Oct 25, 2013
I linked an SQL server table into Access, but when I open the table in Access, no data shows. Do I need to set some permissions on the sql table?
View 1 Replies
View Related
Jan 20, 2005
Ok, I want to write a stored procedure / query that says the following:
Code:
If any of the items in list 'A' also appear in list 'B' --return false
If none of the items in list 'A' appear in list 'B' --return true
In pseudo-SQL, I want to write a clause like this
Code:
IF
(SELECT values FROM tableA) IN(SELECT values FROM tableB)
Return False
ELSE
Return True
Unfortunately, it seems I can't do that unless my subquery before the 'IN' statement returns only one value. Needless to say, it returns a number of values.
I may have to achieve this with some kind of logical loop but I don't know how to do that.
Can anyone help?
View 3 Replies
View Related
Apr 18, 2008
I am new to sqlserver.
1)I created a linked server to a oracle database, works fine. Now I am writing queries, using "Query Desgner", when I do a "Add table" linked server tables do not appear. Is this a limitation. I can get around the problem by creating a view.
2) When I use "Microsoft OLE DB Provider for Oracle" for linked server, some times connection seems to get lost. On one occassion, the next day it was fine, it re-established itself.
Any ideas thnx.
View 5 Replies
View Related
