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 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?
What query should I run? 1. To determine whether a column is a rowguid or not using C# .NET 1.1 2. To add/modify column information and be able to set/change: - Primary key - Column Name - Data Type - Length - Allow Null - Default value - Precision - Scale - Identity - Identity Seed - Identity Increment - Row guid
This is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
This is the case.... I would like to learn the statement that make therelation between these tables.Why? Cos these are separated in two different databases and if a usermake an update in a table from database X these changes must to beapplied in the other table in the another database:The tables are :Principal Database Name : Server Information 2004Table Name : ClientsFields : ID_Client, ClientSecondary Database Name : Index2003Table Name : ContratosFields : ID_Con, ID_Client, ClientI need to write a Trigger for Update the table Contratos everytime auser change the values in Clients.I´m using the follow Trigger :CREATE TRIGGER UPDate_Clients ON dbo.ClientsFOR UPDATEASupdate Contratosset Client = inserted.Clientfrom Clientsinner join inserted on Clients.Client = inserted.ClientWhen I update the register the follow message in the application raise :"Key column information is insufficient or incorrect. Too many rows wereaffected by update."If somebody can help me THANKS A LOT OF....Leonardo Almeida*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
Hi, all.. I want to know the query to retrieve Column information that we can see from table Design view of Enterprise manager, such as Column name, Pk or not, FK table, Data Type, Null or not, Description(Specially Descrition).
I'm trying to get data from as400 server to sql server, trough a job. The job executes a stored procedure that retrieves data from a view in an other database on the same server. That view gets the data from as400. Now, when i run the mentioned stored procedure in the query analyzer, everything works fine, but when the stored procedure is executed from the scheduled job, it fails each time giving the error
"Could not get the column information from the OLE DB provider 'IBMDA400'. [SQLSTATE 42000] (Error 7350). The step failed."
on random points during that process (by that i mean the lenght of time it runs)
does anyone know what the diffrence is between executing an sp in query analyzer or using a job, keeping in mind retrieving data from as400?
Dear Friends,I have table contain 2000 out of those some areduplicate when i select duplicate records by using Enterprise Managerand make modification to one of those duplicate records the followingmessage flashes/display.key columen information is insufficient or incorrect.Too many rows wereaffected by updatepls suggest what is this and how to solve this problemThanks in advanceDinesh Patwal
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.
Please provide me a column-level merge replication in SQL Server 2005. In the SQL Sever books online, I could able to get very few informaiton about this topic. Please provide me a best practices for using this kind of conflict resolution in Merge-replication.
I can preview the SQL command in the OLE DB Source Editor and bring back all columns and results just fine but when I click on the Columns I get
TITLE: Microsoft Visual Studio ------------------------------ The component reported the following warnings: Error at Data Flow Task [OLE DB Source [1]]: No column information was returned by the SQL command.
The columns are there in the preview - why can't SSIS get the column information?
I am in process of transfering data from Sybase to Sql Server using SSIS 2005
have taken a Data Flow Task in Control Flow tab
In Data flow tab, I have taken one Ole DB Source and One OLe DB Destination
For the source, I am using Sybase Adaptive Server Anywhere Provider 8.0
For Destination, I am using Sql Server 2005 database
In Ole Db Source Editor , For OLe Db Connection Manager, I choose Sybase Connection For Data access mode, I choose Table or View For Name of the table or the view, I choose a table by name Table1( it lists all the tables from Sybase database)
When i click on preview button or Columns link, I get the following Error
Error at Data Flow Task [OLE DB Source [1]]: An OLE DB error has occurred. Error code: 0x80040E21.
Error at Data Flow Task [OLE DB Source [1]]: Unable to retrieve column information from the data source. Make sure your target table in the database is available.
I am trying to set up a data flow task. The source is "SQL Command" which is a stored procedure. The proc has a few temp tables that it outputs the final resultset from. When I hit preview in the ole db source editor, I see the right output. When I select the "Columns" tab on the right, the "Available External Column List" is empty. Why don't the column names appear? What is the work around to get the column mappings to work b/w source and destination in this scenario.
In DTS previously, you could "fool" the package by first compiling the stored procedure with hardcoded column names and dummy values, creating and saving the package and finally changing the procedure back to the actual output. As long as the columns remained the same, all would work. Thats not working for me in SSIS.
Hey, I changed the database name in the initial cataloge in the web.config conncetion string so that it now connects to other databas that contains same tables as the old one,but now i am getting that error at the update stmt ! thank u in advance Hiba
Hello, I am getting very frustrated! I have got a Foreach loop container which I am processing files within a folder. I have a flatfile connection manager which I have set up using a test file and have updated the expressions attribute to be the package variable I set up in the collection for the loop container however everytime I run it I get the error: 0xC0202094 cannot retrieve the column information from the flatfile connection manager. I can only guess that it is either the variable being passed to the connection manager or the way I set up the connection manager. When I msgbox the variable in a script component before the dataflow step, the variable for the file seems fine. Any suggestions are REALLY appreciated.
I've developed a package that is working well at development machine from VS 2013 for a sample flat file. Also, over development machine, I've deployed it to SSISDB catalogue and even from there also it is running well for the same file.When the same package is deployed to production server's SSISDB catalogue database, it throws following error while processing the same sample flat file, “Unable to retrieve column information from the flat file connection managerâ€
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
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?
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.
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.
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.
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
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
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!
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?
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.Â
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,
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.
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!
