Hello:
I have an app that needs to retrieve the list of tables in an SQL database. I see that there is a way to do this with the OLEdb provider (GetOleDbSchemaTable() ), but I can't find a sibling method in SQL. IS there one/ Any Ideas?
Mike
Hi all
I have a Table with Following structure ( a Tree Structure )
PK Parent Level Code
--- -------- ------- ------
1 0 0 100
2 1 1 101
3 1 1 102
4 2 2 103
5 3 2 104
6 4 3 105
The same as following Tree as you can see
1__
| 2__
| 4__
| 6
| __
3__
5
I need a query to return the following Result. I think it is possible only through Nested sub-Queries But i don't know how to do that
Could any one help me.?
PK Parent Level Value First-Parent' Code 2nd-Parent's Code 3rd-Parent's Code
---- -------- ------ ------- --------------------- ---------------------- -----------------------
1 0 0 100 NULL NULL NULL
2 1 1 101 100 NULL NULL
3 1 1 102 100 NULL NULL
4 2 2 103 101 100 NULL
5 3 2 104 102 100 NULL
6 4 3 105 103 101 100
Any help greatly would be appreciated.
Kind Regards.
I want to make a query, stored procedure, or whatever which will only display the primary key where there does no exist a foreign key in linked table.For example. If I had two tables with a one to many relationship.A [Computer] has one or more [Hard Drives]. I want to select only those computers which do not have a Hard Drive(s) associated with them. That is, show all computers where the Computer_ID field in the [Hard Drives] table does not exist. This seems simple but I'm drawing a blank here.
View 1 Replies View RelatedSQL question here:
I am setting up a blog, and I would like the data list to default to the most recent post ONLY. How would I define the filter to get the most recent post?
I have a publishing date in my table, and I can sort the post, but I have no clue how to select just one row to display.
I can get it to work by adding a dropdown list the is bound to the publishing date column, then retreving the post based on the control, but I don't want to use the control. (Making the control not visible is not an option.
I am storing images within and image field in sql, however when I go to get the data I have two issues.
the first issue is that my image is linked to information on another table but the only way that i have seen getting the image back is to do a:
response.binarywrite(mydatareader.item("image"))
but i am hoping to be able to find a way to use the template functionality with datagrid to align the picture with the information on the table. i am storing the image type and size with the image as well.
the second problem might be related to the load, i am not sure. sometimes, for some images and it just shows the frame of the image with the red x in the middle. The load appears to work (no errors) but since we cannot view them properly we dont know.
Our application wants to store all data in sql so that we can secure it and relate it to projects. in addition, we hope to be able to use this function to upload all sorts of binary documents from jpg, gif, doc, xls, visio, and others as we can. any issues with that process???
we would like to be able to display them directly in the browser where we can but all users have standard office.
any suggestions on either and hopefully both of these situations? we have sort of hit a brick wall.
hi. I am at a new stage in our project and need to figure out how to insert/retreive MP3/Wav files. The way we want to do this is have the file stored on the harddrive while a link to its location is stored in the database.
I have posted this search in other newsgroups and have gotten a lot of philosophical responses, but what i really am looking for is code examples. Please, what i would like to see is "how" this id done in code. If you have done this on your site, i'd really appreciate your posting code here or emailing me examples. Or, if you know of a really good explainaition of how to go about storing/retreiving audio files in the way i mentioned using MS SQL Sever 2000, I'd be equally indebted.
I hope I have explained myself clearly. Thank you for your assistance!
have a peaceful day.
Last week I discovered a server runing a sql database for a vendor application. Last year sometime one of our former employees created a dts to populate some data in peoplesoft from this database. But, over this last weekend, the vendor came in and uninstalled and reinstalled sql server and only restored their specific database. All I have is backups of msdb and master and I'm not sure how to restore our dts package. Any clues would be appreciated.
View 4 Replies View RelatedI Somehow restored the latest database onto the test database. Can I now retrieve the lost data. If Yes , How is it possible.
Akash
How would I get three fields out of the database if they are duplicates?
I got it to work with one field but can't get it to work with the other two fields (BIC and PaymentAmt)
SELECT Claim,
COUNT(Claim) AS NumOccurrences
FROM Info
GROUP BY Claim
HAVING ( COUNT(claim) > 1 )
Hi all
I have the following table schema and their details as follows:
Code Block
CREATE TABLE #Tbl_Project
(
ID varchar(40),
Name varchar(50)
)
GO
CREATE TABLE #Tbl_User_Project
(
ID int,
ProjectID varchar(40),
Role varchar(20)
)
GO
INSERT INTO #Tbl_Project VALUES('PRJ1','Project 1')
INSERT INTO #Tbl_Project VALUES('PRJ2','Project 2')
INSERT INTO #Tbl_Project VALUES('PRJ3','Project 3')
GO
INSERT INTO #Tbl_User_Project VALUES(1,'PRJ1','PM')
INSERT INTO #Tbl_User_Project VALUES(2,'PRJ1','TL')
INSERT INTO #Tbl_User_Project VALUES(3,'PRJ1','TM')
INSERT INTO #Tbl_User_Project VALUES(4,'PRJ2','PM')
INSERT INTO #Tbl_User_Project VALUES(5,'PRJ2','TL')
INSERT INTO #Tbl_User_Project VALUES(6,'PRJ3','PM')
INSERT INTO #Tbl_User_Project VALUES(7,'PRJ3','TL')
INSERT INTO #Tbl_User_Project VALUES(8,'PRJ3','TL')
GO
--Required Output
--ProjectID ProjectName PM:TL:TM Ratio HeadCount
--PRJ1 Project 1 1:1:1 3
--PRJ2 Project 2 1:0:1 2
--PRJ3 Project 3 1:0:2 3
DROP TABLE #Tbl_Project,#Tbl_User_Project
This is just sample of the values. The ratio obtained should be as follows:
Assuming for a Project 1 there are 2 - PM's , 6-TL's , 15 - TM's then the ratio should be 1:3:7.5 . So please help me in this regard.
Hi every one,
I have a serious problem struggling with sql query the problem is i have a table below
Sno CarId carcharacterstics Charactersticvalue
1. Mercides01 Color Red
2. Mercides01 EngineType Desile
3. Mercides01 Speed/Hr 350KM
4. Maruthi01 Color Red
5. Maruthi01 EngineType Desile
6. Maruthi01 Speed/Hr 250KM
7. BMW Gears Automatic
8. BMW EngineType Desile
9. BMW Speed/Hr 250KM
Now i need to retrieve the carid whose color is red and Engine type is desile the query i have written is
select carid from carchar where ((characterstics='Color' and charactersticvalue=Red) and (characterstics='EngineType' and charactersticvalue=Desile))
But I am not able to retrieve the values. I can retrieve the values correctly if i mention only one characterstics and its value but i cant retrieve if i mention more than that
Hi,
In my Sql Sever i have added one Database "Devtools", by restoring it from .bak file. Now In my asp page I want to Display a Datagrid with data from a table "Devices". I want to Generate the Gridview dynamically. The columns in the table are dynamic they are not constant so I want to generate my Gridview dynamically. So I want to get the columns info of the table and based on that I want to dynamically add the columns to the table. So how to get the info of the table first. Is there any query where i can get the table info
In oracle there is a describe command:
describe table_name
This command will tell you the columns names, datatypes, and length of the columns for that particular table. Does anyone know if there is a simliar command in SQL server that would do that? The only way that I have found so far is to do a join of the syscolumns with sysobject by table name.
Okay, I'll admit it: I am not a Guru. Far from it. So here is one of the zillion tasks I cannot do - unfortunately it is one that I need to have done... :
I need a recordset containing the Name, Datatype, Size, Precision, and Name_of_parent_table of all columns in all non-system tables in a given database.
Most likely this can be accomplished with one or two SPs, but which ones? And how?
Is there any Kind Soul out there, perhaps?
Peter
i need help printing only certain rows from within a table.. for instance, if my table looks like this :
+-------------------+----------------+----------------------+
| Description | Heading | Link |
+-------------------+----------------+----------------------+
| Google | Heading 4 | www.google.com |
| Toms Hardware | Heading 3 | www.tomshardware.com |
| Anandtech | Heading 2 | www.anandtech.com |
| check this out | Heading 1 | www.henryadams.com |
| another heading 4 | Heading 4 | www.heading4.com |
| test | Heading 3 | www.test3.com |
| heading 4 agagin | Heading 4 | laksjdf;alkf |
| new heading test | newHeadingTest | www.newheadingtest |
+-------------------+----------------+----------------------+
and i want to only print out the results for "Heading 4" under the "Heading" column, and only have the results print out once, using php.. how would i do this?
The Headings are inputed by the user through a web interface i created where the user may either select an exisiting heading or type in a completely new one that has nothing to do with any of the others.
thanks for any and all of your help..
Hi,
Is it possible to retrieve column information alone in the sp_columns command? I saw that it has an additional option "@column_name". But when I include it in my sql, it doesn't work the way I had expected it to..
I had tried sp_columns @table_name='mytable', @column_name='COLUMN_NAME'
I expect just the column names alone in the resultset. What am I doing wrong here?
Thanks and Regards,
Celia.
Hi,
I have two tables - a stock table and a transaction table. The stock table is called stock and the transaction table is called trans. The table make a point of sale database. Both table have a joining field called prod_no.
I want to extract the stock line by querying stock.prod_no that are not present in the trans table for a period of time specified.
Basically, I want to find dead stock lines that have not been sold and, therefore, are not in the transaction table but that have a stock figure. I have to enter in a start and end date paramater using the ddate field in the trans table. I have looked at left, right and outer joins but with no luck and don't even know if this is the correct query type to use.
any help would be much appreciated,
I am new to using sqlserver and have been given the task to get the columnname, data type and the description from a given table and put this intoanother table. Can this be done?Thanks in advance.Jeff Magouirk
View 2 Replies View RelatedHi
I have a problem I am trying to ascertain the quality of the data in the table of a system we are migrating from one database to another.
We have already taken the step of converting it into SQL server to make sense of it now I wnat to query the data itself
To do this I set up a query that went through sysobjects and syclumns and listed the tables and their columns
What I want to do now is use this information to query the data elements themselves to determine where we should concentrate our data mapping on.
I started out with a cursor doing this
declare @table varchar(50)
declare @col varchar(50)
declare @result int
declare count_cursor cursor scroll
for select tname,cname from infotemp
open count_cursor
fetch from count_cursor
into @table,@col
while @@fetch_status=0
BEGIN
exec ('select count('+@col+') from '+@table)
Fetch next from count_cursor into @table,@col
END
close count_cursor
deallocate count_cursor
But I am having problems getting the counted value into the result value from the exec statement
Anybody got any ideas
Thanks
Hi, im trying to insert values into 2 different tables. The primary key in the first table is an auto increment field, which is then the foreign key in the 2nd table.Is there any way to retrieve the autogenerated value before performing the second insert. I have done some searching on the matter, but to no avail.I tried using LAST_INSERT_ID() but i get the error message that it is not a recognized function name. The other option is using @@identity but i cant see how to put that into my SQL statement.Can anyone help please.Thanks in advanceGareth
View 2 Replies View Relatedhello dudes. i've got the following table:
table name: assassins
fields:
assassinid int, identity, primary key
assassinname nvarchar
assassincallsign nvarchar
knifekills int
sniperkills int
stealthkills int
fibergarotekills int
sample data:
1, Dana White, tapout, 12, 0, 12, 1
2, Chuck Liddell, iceman, 30, 0, 0, 2
3, Timothy Oliphant, FortySeven, 100, 20, 150, 30
supposed i want to retreive only those records whose type of kills is on a given list, how do i do that? the list is actually dynamic since the kill types is actually coming from a checkbox on my page:
[] Knife Kills
[] Sniper Kills
[] Stealth Kills
[] Fibergarrote Kills
supposed i will only check Knife Kills, it will only retreive those records whose knifekills > 0
or if i check Snipers Kills and Stealth Kills, then those records whose sniperkills and stealthkills is > 0.
any of you know how to do that?
U + U + D + D + L + R + L + R + Sel + Start...
We are having a Windows Installer Program (32 bit) which calls the SQLDMO to get the list of installed instances in a 64 bit 2005 sql server machine. This fails as the windows installer tries opens up the 32 bit sqldmo and there is no instance of 32 bit sql server on this machine.
Is there any way by which sqldmo can return all 64 bit sql server instance when called from a 32 bit Application?
Regards,
Ashish
I would like to know how to (if it is at all possible) in SQL (or even ADO) to retrieve all the data concerning database X's
a) Tables
b) Tables column names
c) Tables column's data types
I don't want to use the doa.tabledefs object.. .i would prefer to do it in SQL, but using ADO objects (since I am developing stuff in VB) would be ok too.
Please Help.. i have been going crazy trying to find anything useful.. email me back please!
Thanks,
Matt
Hi,
I have a database with two handred tables.I want to get a list that have table name and numbers of record in each table.
Would you please give me a idea if you know how to do this with query or build-in stored procedure.
Thanks a lot
Mark
Hi,
I need to provide a report that lists the databases in each instance, then the tablenames and the number of rows in each table.
Is there any easy way to do this in SQL 2005?
Cheers
Gregg
I remember once using a command in Query Analyser that gave me all theinformation about a table. It showed details about the columns,constraints, keys, indexes etc.I can't remember how I did this? Can anyone help me?It could of been a stored proc that I called or a query on the systemtables.I do know that it was very simple to do and it was executed in QueryAnalyser.Can anyone help me?
View 2 Replies View RelatedI am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).
The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN,Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.
The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.
The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.
Example:
Old Table
Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description
Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL
Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad
Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL
Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL
I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.
Due to circumstances beyond my control, I have to restore a full database backup from October 1, 2005 and then over 4400 transaction logs to bring a database up to Jan 01, 2006 on a development server (storage team lost all daily full backups from 10/02/2005 to 01/01/2006). Since the backups were not taken on this machine originally, the information I would pull from the sysjobhistory tables to get the transaction backup names is not available. Does anyone have a link or a script to pull the filenames from the backup directory into sql server so I can build a dynamic script to apply the tran logs instead of running all 4400+ of them by hand? Thanks, Tom
View 5 Replies View RelatedHi,
I'm trying to figure out how to retreive connection strings from the machine.config instead of storing the conn strings in a *.dtsconfig file. The reason that I'm trying to use the machine.config is for the simple reason that I'll be able to manage all of the conn strings in one location on each server. (Instead of mutliple dtsconfig files.) Is it posible to utilize the machine.config in SSIS? If so, could someone explain how, or better yet, have an example?
thanks for the help!
Hi all,
Im not really sure if this is a PHP or and SQL problem but here goes.
Im using MSSQL and have developed a webpage that enables users to run various PACKAGES manually, however I need to display if the package has been successfully run.
Is there a system table that logs package information or is there a PHP function that I can use.
Thanks
P.s I know there are some system tables with the information for jobs but I do not want to create a job for each package.
I need to insert FK information into a temp table using database name and table name as a parameter.
I've been trying different ways, even with global temp table, but still doesn't work. Below is the code showing what I am trying to achieve. I want to avoid global temp table if possible.
IF OBJECT_ID('tempdb..##FKs') IS NOT NULL DROP TABLE ##FKs
CREATE TABLE ##FKs (
[ForeginKeyName] [nvarchar](128) NULL,
[TableSchema] [sysname] NULL,
[TableName] [nvarchar](128) NULL,
[RelatedTableSchema] [sysname] NULL,
[Code] .....
I am working on moving information from one of our databases into a newer one the company has recently setup. I am working in MS SQL Server 2012 and am trying to Inset/Update (depending on the information) a part table. I will list the two tables I am working on as Old (where I am getting the information) and New (where it is to be inserted into).
The old table has about 250 columns of information, most of which is not needed. The needed information is as follows: Name, ID, Component1, Component1_PIN, Component1_SN, Component1_Description, Component2, Component2_PIN, Component2_SN. The component section repeats up to Component12.
The new table has columns setup as such: Name, ID, Case, CasePIN, CaseSN, Case_Desc, Processor, ProcessorPIN, ProcessorSN, Processor_Description, Memory, MemoryPIN, MemorySN, Memory_Description, etc.
The issue I am having is that in the old table each component can have Case, Processor, Memory, etc in that one column. I need to insert Case, Memory, etc into the correct column in the new table while keeping intact the rest of the information.
Example:
Old Table
Name | ID | Component1 | Component1_PIN | Component1_SN | Component1_Description | Component2 | Component2_PIN | Component2_SN | Component2_Description
Nest8 | 5682 | Case | 901834 | 237848117 | Black, rectangular | Memory | 9081234 | 5398798134 | NULL
Nest8 | 5978 | Case | 901463 | 237848138 | Black, rectangular | Processor | 2394875 | 2903857809 | Bad
Reds3 | 5683 | Memory | 2405 | 89752342 | Crucial | HardDrive | 92387595 | 457982234 | NULL
Bass | 5644 | HardDrive | 79872346 | 5321675789 | NULL | Case | 10984528 | 3498769872 | NULL
I am not sure how to loop through and grab each part and place it in the column it needs to be while keeping it with the ID.
Hi, I'm getting this error
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not retrieve replication information for table 735393739. Verify that the table has a primary key, and then rerun the Log Reader Agent.Please help me!