ADO Table Schema Doesn't Show Primary Key
Jan 8, 2007
I'm trying to use the following code to examine table schema for SQL 2000. But when I do, the IsKey value is null or blank. I tried this on two different tables - a State table with a char(2) primary key containing the state abbreviation and a Trend table which has an identity column as the primary key. Public Shared Function GetSchema(ByVal sTable As String) As String
Dim sb As New StringBuilder
Dim oConn As SqlConnection = OpenConn()
Dim cmd As SqlCommand = New SqlCommand("SELECT * FROM " & sTable, oConn)
Dim dr As SqlDataReader = cmd.ExecuteReader(CommandBehavior.SchemaOnly)
Dim dt As DataTable = dr.GetSchemaTable()
dr.Close()
oConn.Close()
For ic As Integer = 0 To dt.Columns.Count - 1
sb.Append(ic & ":" & CNull(dt.Columns(ic).ColumnName) & " ")
Next
sb.Append(vbCrLf)
For ir As Integer = 0 To dt.Rows.Count - 1
For ic As Integer = 0 To dt.Columns.Count - 1
sb.Append(ic & ":" & CNull(dt.Rows(ir).Item(ic).ToString) & " ")
Next
sb.Append(vbCrLf)
Next
Return sb.ToString
End Function
Here's the output from the function:
? datahelper.GetSchema("State")
"0:ColumnName 1:ColumnOrdinal 2:ColumnSize 3:NumericPrecision 4:NumericScale 5:IsUnique 6:IsKey 7:BaseServerName 8:BaseCatalogName 9:BaseColumnName 10:BaseSchemaName 11:BaseTableName 12:DataType 13:AllowDBNull 14:ProviderType 15:IsAliased 16:IsExpression 17:IsIdentity 18:IsAutoIncrement 19:IsRowVersion 20:IsHidden 21:IsLong 22:IsReadOnly 23:ProviderSpecificDataType 24:DataTypeName 25:XmlSchemaCollectionDatabase 26:XmlSchemaCollectionOwningSchema 27:XmlSchemaCollectionName 28:UdtAssemblyQualifiedName 29:NonVersionedProviderType
0:StateCode 1:0 2:2 3:255 4:255 5:False 6: 7: 8: 9:StateCode 10: 11: 12:System.String 13:False 14:3 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:char 25: 26: 27: 28: 29:3
0:State 1:1 2:50 3:255 4:255 5:False 6: 7: 8: 9:State 10: 11: 12:System.String 13:False 14:22 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:varchar 25: 26: 27: 28: 29:22
0:CountryCode 1:2 2:2 3:255 4:255 5:False 6: 7: 8: 9:CountryCode 10: 11: 12:System.String 13:False 14:3 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:char 25: 26: 27: 28: 29:3
"
? datahelper.GetSchema("Trend")
"0:ColumnName 1:ColumnOrdinal 2:ColumnSize 3:NumericPrecision 4:NumericScale 5:IsUnique 6:IsKey 7:BaseServerName 8:BaseCatalogName 9:BaseColumnName 10:BaseSchemaName 11:BaseTableName 12:DataType 13:AllowDBNull 14:ProviderType 15:IsAliased 16:IsExpression 17:IsIdentity 18:IsAutoIncrement 19:IsRowVersion 20:IsHidden 21:IsLong 22:IsReadOnly 23:ProviderSpecificDataType 24:DataTypeName 25:XmlSchemaCollectionDatabase 26:XmlSchemaCollectionOwningSchema 27:XmlSchemaCollectionName 28:UdtAssemblyQualifiedName 29:NonVersionedProviderType
0:TrendID 1:0 2:4 3:10 4:255 5:False 6: 7: 8: 9:TrendID 10: 11: 12:System.Int32 13:False 14:8 15: 16: 17:True 18:True 19:False 20: 21:False 22:True 23:System.Data.SqlTypes.SqlInt32 24:int 25: 26: 27: 28: 29:8
0:Description 1:1 2:50 3:255 4:255 5:False 6: 7: 8: 9:Description 10: 11: 12:System.String 13:False 14:22 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlString 24:varchar 25: 26: 27: 28: 29:22
0:Length 1:2 2:4 3:10 4:255 5:False 6: 7: 8: 9:Length 10: 11: 12:System.Int32 13:False 14:8 15: 16: 17:False 18:False 19:False 20: 21:False 22:False 23:System.Data.SqlTypes.SqlInt32 24:int 25: 26: 27: 28: 29:8
"
Column 6 is IsKey but just displays 6: whereas IsIdentity displays correctly for Column 17. Can someone help me? Thanks in advance.
View 1 Replies
ADVERTISEMENT
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
Feb 22, 2006
I have added a Slowly Changing Dimension transformation to an SSIS package and have launched the Wizard to edit it. After selecting the source (a SQL Server 2005 instance), if I select a very large table (9+ million rows), I'm encountering two strange behaviors:
1. The wizard hangs for several minutes before displaying the columns from that table.
2. The wizard does not display the primary key column. This, of course, is the column I want to designate as the "business key", but can't because it's not displayed.
I know this is more like a fact table than a dimension, but this is not a data warehouse. This is just a very large table, and I need to update a field in certain records based on the contents of a source text file. Is there another transformation I should use to perform updates?
View 2 Replies
View Related
Jan 18, 2008
Hello,
Since a week we have a strange problem.
We are using SQL Server 2005 with SP2 on a Windows Server 2003 updated last time in November. We have 2 servers like this, one is a virtual machine and the second one, a physical one.
Since a week, sometimes, the reports in the web report viewer just don't show. They keep loading for hours without showing anything. Beside, everything's fine, the server has plenty of free resources (CPU, RAM...), Visual Studio works normally, SSMS too. We can even go in http://localhost/reports and http://localhost/reportserver and see the folders, files etc... but when trying to load a report (what ever it is, even a small one) nothing comes, not even the message "Report is loading".
It happens not every day, sometimes two or three times a day at different hours.
In the Windows event viewer, nothing.
In the SSRS logs, nothing (as far as I can understand these logs).
It looks as if a job or a process was running and was blocking the reports or a service which would stop automatically.
To have it work again, a reboot of the server works or within 2 or 3 hours, it comes back to normal state.
If someone has an idea of what can be the problem or some tips, please feel free because we are running out of ideas.
Thanks in advance
View 10 Replies
View Related
May 28, 2002
Hi,
After doing a database restore on my development server, It only shows DBO As the db user..When I query SYSUSER all other users are still there..But doesn't show up in enterprise manager...
Anybody came acrosss the same situation ?
Thanks for you help in advance.
View 1 Replies
View Related
Mar 21, 2006
I have a question that has a co-worker and myself confused. We are using the SQL Server Enterprise Manager. When I select return all rows in my tables, any content that is too long will appear as <Long Text> on my co-workers machine, but the column is blank on my machine. Is there a property or configuration that enables/disables <Long Text>? I am confused as to why it appears on other machines, but not mine.
Your help is appreciated.
Live Life.
View 8 Replies
View Related
Oct 10, 2006
when i pass a input parameter, the SP doesn't recognize it.
like
exec SP 00004
it will print:
select * from table where idname = 4
i want it to print
select * from table where idname = '00004'
how can i do that?
View 11 Replies
View Related
Jan 28, 2008
SS2005 question.
Why doesn't securables show when I add a user to have select permission on a table?
Like after adding a user I click ok to close the window then double click on the user to open it up, go to securables but it doesn't show. It's empty.
http://www.sqlserverstudy.com
View 3 Replies
View Related
Jan 28, 2008
Hi,
I've designed a report and assigned it a datasource that binds with a SP with a parameter. In the XML code of the dataset I can see the input parameter has been recognised by the wizard. Then, I built a report with that Dataset as the source. Everything is fine till this time. Now through my aspx code I pass on the value of the parameter to the report. But the report doesn't show any values at all. The aspx code is like
Code Snippet
Dim reportParameter As New ReportParameter("@UserID", Convert.ToInt32(TextBox1.Text), True)
Dim test() As ReportParameter = {reportParameter}
ReportViewer1.LocalReport.SetParameters(test)
Any ideas what I could be doing wrong?
VS 2005 & SSRS 2005.
TIA.
View 2 Replies
View Related
Jan 15, 2004
I can't seem to find a sample code, either here or on the net - - so I'll go ahead and ask...
What I'm looking for is a sample of how to query a database, so that I can populate a listbox with the table names from a database - - and then, populate another listobx with the field names from the database, in order to build a user-driven sql statement builder....
either of the above (at least the field name part) - either a code sample, or a link, will be greatly appreciated) - -
Thanks
View 1 Replies
View Related
Dec 16, 2005
I've created a very simple custom connection manager, and I followed the deployment instructions at http://msdn2.microsoft.com/en-us/library/ms345276.aspx. Basically, that was just copying the assembly to %programfiles%Microsoft SQL Server90DTSConnections, and then registering the asssembly in the gac.
However, when I open VS, right-click on the connection manager pane, and then choose new connection, my custom manager does not appear. What am I missing?
Here is the class:
using System;
using System.Collections.Generic;
using System.Text;
using Microsoft.SqlServer.Dts.Runtime;
using System.Data.SqlClient;
using System.Xml;
namespace MyCoolCompany
{
[DtsConnection(DisplayName="Sharepoint Connection Manager",
Description="Connect to Sharepoint lists",
ConnectionType="Sharepoint",
ConnectionContact="My Cool Company",
UITypeName="SPDataFlowComponent, MyCoolCompany.SharepointConnectionManagerUI")]
class SharepointConnectionManager: ConnectionManagerBase
{
}
}
View 6 Replies
View Related
Sep 28, 2006
I have created a custom connection manager and it was showing up fine as was the UI I created for it. For some reason now no matter what I do I cannot get it to show up in the list when right-clicking and choosing New connection...
I have also created two custom tasks. One of them works perfectly. The second was working but now causes an error when you open the package. Here is the error message:
TITLE: Microsoft Visual Studio
------------------------------
There were errors while the package was being loaded.
The package might be corrupted.
See the Error List for details.
There are no additional errors in the list only this one. I have tried everything I can think of to fix these two issues. Both problems emerged at the same time so may be related but that would not explain why the second custom task I have created works fine and doesn't cause errors.
Help!
Thanks,
Dave
View 5 Replies
View Related
Jun 5, 2007
Here's my problem. I have a subreport that shows data and labels if data is returned. However, if no data is returned, the subreport doesn't show at all. I have a message on the subreport I would like to show if no data is returned, but the subreport won't show at all. Any ideas??
Thanks
View 1 Replies
View Related
Jan 29, 2007
I already have VS2005 SP1 installed and after installing :
SQLServerCE31-EN.msi
SSCE31SDK-ENU.msi
SSCE31VSTools-ENU.exe
When I create a Smart Device project and and try to add a reference to SQL Server CE only the old version (3.0.3600.0) shows up. How do I get the new one (3.1) to show up?
View 8 Replies
View Related
Apr 30, 2007
Hello,
When I open Report Builder I do not see any of the models that I've created. The "Select Site or Server" is blank and I cannot get to any of the models. I can't change the dropdown from "Recent Sites and Servers."
I am not sure what I did, because it used to work. I did install SP2 but that did not cause a problem originally.
I can create new models (on both the BIDS and the ReportServer site) and I see that they were created just not in Report Builder. At this point Report Builder is non-functional for me.
The other thing that may be related is that I've never been able to run Report Builder from the website. When I click the button nothing happens. I made a shortcut to ReportBuilder.exe to get around this.
Thank you for the help.
-Gumbatman
View 2 Replies
View Related
Oct 19, 2006
I've installed SQL Express SP1 on a Windows 2003 server and I'm trying to get a new MySQL datasource to be recognized in the DTSWizard. A MySQL database needs to be moved over to a MS SQL server due to software requirements (which weren't looked at before the site was created). I've installed the ODBC Connector/MySQL software and created the System DSN (named "MySQL DB Import) in the Administrative Tools/Data Sources (ODBC) tool. When I start up the DTS wizard for SQL Express however, I do not get my new data source in the selection options for data sources. I am following the instructions on this page: http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/mysql.mspx#ERJAE What am I missing?
View 2 Replies
View Related
Jun 11, 2007
Hi All,
Can somebosy help with this dilema in my where clause I've got the followingWHERE (Customers.Owner = ISNULL(@Contract,Customers.Owner)) AND (Workorders.DateReceived = ISNULL(@DateReceived,Workorders.DateReceived)) AND (Workorders.DateRequired = ISNULL(@DateRequired,Workorders.DateRequired)) AND
(Workorders.EngineerID = ISNULL(@EngineerID,Workorders.EngineerID)) AND (Workorders.DateFinished = ISNULL(@DateFinished,Workorders.DateFinished)) AND (Workorders.JobTypeID = ISNULL(@JobTypeID,Workorders.JobTypeID)) AND
(Workorders.JobStatus = ISNULL(@JobStatus,Workorders.JobStatus)) AND (Workorders.PriorityID = ISNULL(@PriorityID,Workorders.PriorityID))
This is so if one of the parameters passed is null you still get a result is trouble is if the substitute value of the ISNULL statement is null no result is shown how can I get round this.
Any help much appreciated
View 2 Replies
View Related
Mar 26, 2001
Hi, all,
I found that the SQL2000 EM does not show database space allcoated information, as well as tables and indexes size while SQL 7.0 does.
Someitmes these information are fairly handy.
is there any other easy ways to find out the same info from SQL2000
through Em, or elsewhere ??
Thanks
Anthony
View 1 Replies
View Related
Jul 20, 2005
I thought I'd give the SQL Server 2000's log shipping wizard a try (inthe past I've always done it with Perl scripts), so I attemted to setit up on one of my servers. But in the maintenance plan wizard, Idon't get the checkbox at the bottom of the window to "Ship thetransactions logs to other SQL Servers (log shipping)" as it shows inthis article:http://www.microsoft.com/technet/pr...n/logship1.mspxCan anyone tell me what's wrong here? Please post to the newsgroup.Thanks,Steve
View 7 Replies
View Related
May 30, 2001
Te first record of a Recordest obtained from a Command Object executing a Stored Procedure, doesn't show the first record when I asociate this to a data report.(VB6 - SQL7) (ADO 2.1)
If I execute the stored procedure directly from query analizer, I have obtained the right resultset.
Does anyone Knows what could be happening?
Thank You ...
View 1 Replies
View Related
Oct 26, 2015
We are running 2014 enterprise. I noticed recently that my spreadsheet's column A, while not being used by user, doesn't show up in excel source preview. F1 is column B and so on.
View 4 Replies
View Related
Oct 12, 2007
I am trying to import an Excel file into SQL Server 2005 using the SSIS import/export wizard; however, Microsoft Excel doesn't show up in the list of the data sources. I am assuming that something else must be install from either Microsoft Office or SQL Server 2005. I am using Microsoft Office 2003 on a Window XP machine. Does anyone know what I need to do to correct this.
Thanks,
Tim
View 11 Replies
View Related
Jul 24, 2015
I'm inserting from TempAccrual to VacationAccrual . It works nicely, however if I run this script again it will insert the same values again in VacationAccrual. How do I block that? IF there is a small change in one of the column in TempAccrual then allow insert. Here is my query
INSERT INTO vacationaccrual
(empno,
accrued_vacation,
accrued_sick_effective_date,
accrued_sick,
import_date)
[Code] ....
View 4 Replies
View Related
Aug 28, 2002
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View 9 Replies
View Related
Sep 13, 2005
I’ve got a situation where the columns in a table we’re grabbing from a source database keep changing as we need more information from that database. As new columns are added to the source table, I would like to dynamically look for those new columns and add them to our local database’s schema if new ones exist. We’re dropping and creating our target db table each time right now based on a pre-defined known schema, but what we really want is to drop and recreate it based on a dynamic schema, and then import all of the records from the source table to ours.It looks like a starting point might be EXEC sp_columns_rowset 'tablename' and then creating some kind of dynamic SQL statement based on that. However, I'm hoping someone might have a resource that already handles this that they might be able to steer me towards.Sincerely,
Bryan Ax
View 9 Replies
View Related
Oct 25, 2015
we have a table in our ERP database and we copy data from this table into another "stage" table on a nightly basis. is there a way to dynamically alter the schema of the stage table when the source table's structure is changed? in other words, if a new column is added to the source table, i would like to add the column to the stage table during the nightly refresh.
View 4 Replies
View Related
Jul 28, 2006
Hello,
I'd like to create a temporary table with the same schema as an exiting table. How can I do this without hard coding the column definitions into the temporary table definition?
I'd like to do something like:
CREATE TABLE #tempTable LIKE anotherTable
..instead of...
CREATE TABLE #tempTable (id INT PRIMARY KEY, created DATETIME NULL etc...
I'm sure there must be a simple way to do this!
Many thanks,
Ben S
View 3 Replies
View Related
Aug 2, 2006
Hallo
i have two tables, MainTable and MainTableAudit: the second one keeps DML auditing via triggers.
I'm trying to build a query to highlight changes occurred to fields between the MainTable record and its audited records in MainTableAudit, for example, let's suppose i entered an item with some wrong attributes, and edited it three times:
MainTable record contains the latest and current version
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|240 pages
MainTableAudit contains edited ID002 versions
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|232 pages|2006-07-08 08:32:12
ID002|Hitchhikers Guide to the Galaxy|Sci-fi|212 pages|2006-05-08 10:54:02
ID002|Hitchhikers Guide to Galaxy|Sci-fi|222 pages|2006-07-04 11:42:16
I would like to build a report like this:
first insertion: Hitchhikers Guide to Galaxy|Sci-fi|222 pages
modified on 2006-07-04 11:42:16: field "Title" changed from "Hitchhikers Guide to Galaxy" to "Hitchhikers Guide to the Galaxy", field "PageNo" changed from "222" to "212"
modified on 2006-05-08 10:54:02: field "PageNo" changed from "212" to "232"
modified on 2006-07-08 08:32:12: field "PageNo" changed from "232" to "240"
current version: Hitchhikers Guide to the Galaxy|Sci-fi|240 pages
i'd prefer to use T-SQL and keep all into a single place (a view or storedprocedure), or at least to use reporting services; btw i would like to avoid coding web pages or hosted applications.
View 1 Replies
View Related
Jul 31, 2007
I am using the default ASPNETDB.MDF database for a project in SSE 2005 and VWD 2005. I need to use the UserName field from the aspnet_Users table as a foreign key in another table I have in the database. I am doing this so that I can grab data generated from additional fields that I'm adding to the membership registration wizard. However, I am obviously missing some steps since the user name doesn't show up in my second table. What should I do besides creating a relationship between the two fields and tables? Should I be writing some sort of SQL statement to accomplish this?
View 6 Replies
View Related
May 11, 2005
I'm having a tough time understanding my current problem and why I'm having it.
I'm using an ODBC db connection to connect to my sql server and trying
to insert data into a table I've created, but I keep getting an error
saying that the table doesn't exist. This is the first time I've
ever used ODBC and DSN to connect to a db so I don't konw if there are
qerks associated with this, but I've tried everything I can think
of. I've changed the name of the table, placed it in brackets,
placed "dbo" infront of the name, and still I get the same error about
the table not existing. My "Intial Catalog" in my connection
string is the correct db, I just can't seem to get it going.
Any suggestions?
View 27 Replies
View Related
Jan 28, 2012
I need to add a child table that will tell us who the participants counselor is, what I did was I did a Make Table query based off the primary key of the Parent table and made that the link (foreign key) for the People_tbl and the Counselor_tbl, so if the counselor changes then the user adds the record to the counselor tbl and then puts in the Effective date. The problem is that when I run a report it doesn't show the present counselor always shows the old counselor?
Code:
SELECT Student_ind.StudentFirstName, Student_ind.StudentLastName, Student_ind.[Student ID], People_tbl.[Family ID], People_tbl.FirstName,
People_tbl.LastName, People_tbl.[Parent ID]
FROM People_tbl RIGHT OUTER JOIN
Student_ind ON People_tbl.[Family ID] = Student_ind.[Family ID]
WHERE (People_tbl.LastName = @Enter_LastName) AND (People_tbl.FirstName = @Enter_FirstName)
View 5 Replies
View Related
May 18, 2004
HI
I have created a stored procedure in SQL Server which outputs all records in a table.
How can I execute that procedure in access please? ( show that table in access?)
View 1 Replies
View Related
Jan 15, 2007
Hi
I come from a MySQL background and have been having some trouble finding the MSSQL command that corresponds to MySQL's "describe." I Googled for around 1/2 hour but can't seem to find it.
Any help appreciated
View 3 Replies
View Related
