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
Thanks a lot!
Hi all and I appreciate any help
I recently been assigned to a project that has an ms-access front end and sql server 2000 sp4 as the database engine.
The database was replicated (merge replication) but for some reason they drop replication and when they tried to put it back on they got allot of problems and so they paused it.
My task now is to make it work again. After allot of reading I desided to clean the database and start from the beginning. Using some scripts and instructions on a copy of the database I cleaned all the tables related to merge replication but when I went back to ms-access to update a record I got an error
"invalid column name 'rowguid'" . I checked all my tables in my database but I find not such column as it was dropped using the scripts.
Any ideas that I can use?
Thanks allot,
George
Hi all,
I'm new to replication and database management so I will like some understanding and guiding as this is a new task for me.
I was given a copy of a database with merge replication on it and doing allot of reading and using some scripts I cleaned replication (or so Im thinking) and tryed to use it again on the ms-access front end. When I try to update some data I get "invalid column name 'rowguid'" , but no such column exist in my database as it was dropped.
Any help on this?
thanks George.
I'm trying to set up a merge publication in MSSQL 2005 SP1.
I have a database which I have successfully made into a merge publication. When I run the snapshot agent, several articles are processed successfully, then I encounter an error with the following: Invalid column name 'rowguid'
Note that none of the tables contain 'rowguid' columns before this process - I let MSSQL automatically create these as needed.
The replication monitor details the following error:
Error messages:
· Message: Invalid column name 'rowguid'.
Command Text: sp_MSpublicationview
Parameters: @publication = RelMS Publication
Stack: at Microsoft.SqlServer.Replication.AgentCore.ReMapSqlException(SqlException e, SqlCommand command)
at Microsoft.SqlServer.Replication.AgentCore.AgentExecuteNonQuery(SqlCommand command, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate, Int32 queryTimeout)
at Microsoft.SqlServer.Replication.AgentCore.ExecuteDiscardResults(CommandSetupDelegate commandSetupDelegate)
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.MakePublicationViews()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoRegularMergeSnapshotPreparations()
at Microsoft.SqlServer.Replication.Snapshot.MergeSnapshotProvider.DoPreArticleFilesGenerationProcessing()
at Microsoft.SqlServer.Replication.Snapshot.SqlServerSnapshotProvider.GenerateSnapshot()
at Microsoft.SqlServer.Replication.SnapshotGenerationAgent.InternalRun()
at Microsoft.SqlServer.Replication.AgentCore.Run() (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615
· Server [XXXXXXXXXX], Level 16, State 1, Procedure , Line 1
Invalid column name 'rowguid'. (Source: MSSQLServer, Error number: 4615)
Get help: http://help/4615
Can anyone shed further light on this?
Thanks.
Hello Everyone,
View 4 Replies View RelatedHello,
I am looking to preserve the ROWGUID column that is created automatically when a table is added to a publication as I have noticed that the column is removed and readded (with new values) each time the publication is deleted and recreated. We are planning on using the values in the rowguid column for a non-replication purpose, and don't wish the column to be removed or the values to be reset in the event the 'owning' publication is dropped for whatever reason. I've done some research and have identified the preserve_rowguidcol column of the sysmergearticles table as a candidate for achieving the desired goal. Before moving forward, I wanted to ask for input regarding this. Are there any best practices I've overlooked on this topic? Neither Google nor this forum's search results yield any real discussion on this topic.
Thanks!
DanD
Hi all,
After I setup the merge replication in SQL SERVER 7.0, there is a rowguid column (datatype uniqueidentifier) inserted into each table in the both the source and destination database.
I need to get rid of the rowguid column in all tables. I deleted the replication, but the column still existed.
Is there an easy way to get rid of all the rowguid column in the database? Thanks for your reponse in advanced.
Hi there,
I have setup merge replication which successfully synchronizes with a group of desktop users using SQL Compact Edition.
However now I have setup Article Filters and when I attempt to regenerate the snapshot I get the following error:
Invalid column name 'rowguid'.
Failed to generate merge replication stored procedures for article 'AssignedCriteria'.
When I look at publication properties at the Articles page.. All my tables have the rowguid uniqueidentifier successfully added to tables and selected as a compulsory published column, apart from the table above "AssignedCriteria".. Even when I attempt to select this column in the article properties page and press ok, when I come back it is deselected again. ( The Rowguid column is however physically added to the table)
I have scripted the publication SQL and then totally reinstalled from scratch, including the database but for some reason it doesn't like this table. I remove the article filters, but still this "rowguid" is never "selected" in article properties.
We are using Uniqueidentifiers in other columns as well for historical reasons, but this doesn't appear to be a problem in other tables..
DDL For this problematic table is as follows
CREATE TABLE [dbo].[AssignedCriteria](
[AssignedCriteria] [uniqueidentifier] NOT NULL,
[CriteriaName] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[TargetScore] [numeric](5, 0) NULL,
[HRPlan] [uniqueidentifier] NULL,
[ActualScore] [numeric](18, 0) NULL,
[Criteria] [uniqueidentifier] NULL,
[Employee] [uniqueidentifier] NULL,
[IsActive] [bit] NULL,
[addDate] [datetime] NULL,
[totalscore] [numeric](5, 0) NULL,
[isCalc] [bit] NULL,
[Weight] [decimal](18, 2) NULL,
[ProfileDetail] [uniqueidentifier] NULL,
[rowguid] [uniqueidentifier] ROWGUIDCOL NOT NULL CONSTRAINT [MSmerge_df_rowguid_7FF25DF903B6415FBFF24AC954BC88E4] DEFAULT (newsequentialid()),
CONSTRAINT [PK_AssignedCriteria] PRIMARY KEY CLUSTERED
(
[AssignedCriteria] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
Thanks.
Do anyone know how to find out programmatically is a particular column in
a table is being used as a foreign key?
Thanks,
Kurt
Do anyone know how to find out programmatically is a particular column in
a table is being used as a foreign key?
Thanks,
Kurt
sorry all help does not work , if the value like this
table = test
magusageid playid msgtype mchangeprice
------------- --------- ---------- ---------------
35 6 a 400
36 8 a 450
======================================
and other question is if magusageid is use int IDENTITY(1,1)
how can i edit char in my magusageid which like this
magusageid playid msgtype mchangeprice
------------- --------- ---------- ---------------
A000_35 6 a 400
A000_36 8 a 450
sorry the question is
when i insert one row into this table and the data type my want to auto increase 1 to z
in sql i can use IDENTITY this data type to increas but the column of data use only number without char
so i need to know how can i use IDENTITY + char to save data into one row
thank's
Using VS05 Pro, VB, Sql Express...
For a DBConcurrency exception, is there a way to determine the Column(s) that triggered the excepction? The exception itself does not seem to contain the information. You could probably write a very complex method comparing
DataRowVersions
, to determine which value is different, but that seems overkill.
Thanks!
Bob
Does anyone know how to determine the base datatype of a column? I've tried using sp_columns but if there are user defined datatypes on the column it returns that name instead of the base datatype. I've also tries accessing systypes and syscolumns to determine the base datatypes.
Any tips or trick would really be apreciated!
Kurt
I need to determine if a value for a group of rows is the same or different and I am not sure where to begin. I am fairly new to writing T-Sql code. I am working with SQL Server 2005. This is a work assignment; I am not a student.
I have two tables: One for Course and another for CourseDays. As the Course table implies, it lists courses for a school. The CourseDays tables has a row for each day a course is held. For instance, one course maybe held on Monday, Wednesday, and Friday, so there would be three rows, one row for each day. A given course could be held in a different room for each day. I am trying to write a query that returns one row for each course, and if the room number is the same for each day the class is held, return that room number in the row, else return 'Various'.
Course table:
CourseID, Desc
1 English
2 History
3 Science
CourseDays table:
CourseID, Day, RoomID
1 M 320
1 W 445
1 F 680
2 T 123
2 Th 123
3 M 514
3 T 514
3 W 521
3 Th 901
3 F 521
Expected results:
CourseID, Desc, RoomID
1 English Various
2 History 123
3 Science Various
I simplified the example, as there are about 30 columns in the table and about half a million rows.
Hi all,
I'm new to this whole trigger 'thing', so forgive me if this question has been asked and answered a few times already.
I'm in the process of writing a trigger that will send an e-mail to an application admin when any table within a given database is altered (IE, a column is added or deleted). I can get the e-mail to fire off when that happens without any issue, but I'd like to be able to let the admin know which table was tweaked and what new column was added.
Is this a relatively easy thing to do and I'm just not finding the right built-in variable name, or does something more need to be done?
Thanks,
-Mike
I would like to determine the largest numeric datatype scale of a column.
Decimal Largest Scale Solution found here.
Sample Data:
2222.0002430
2222.0000245
2222.0023455
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
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.
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!
View 5 Replies View RelatedThis 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!
View 1 Replies View RelatedHi, 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).
Is there any sp for this or any query for this?
Thank you all...
Hello
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?
thanks
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
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
View 1 Replies View RelatedI have visual studio 2005 with XP home. I'm trying to find out which service pack my SQL server is running as I've been told that Service pack 2 has a few bugs.
For example ADO Applications Report a Syntax Error When Column Names Contain Spaces. This appears to be an error i am recieving. I just wanted to confirm that it is a microsoft bug rather then something Im doing wrong.
Microsoft meantions the bug here: http://support.microsoft.com/kb/264687
I've been looking everywhere. I do not have the "query analyzer window" where you can find this out as I have VS 2005. How would I find this out.
Thank you
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.
View 5 Replies View RelatedI 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?
Hi All,
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.
------------------------------
ADDITIONAL INFORMATION:
Exception from HRESULT: 0xC020204A (Microsoft.SqlServer.DTSPipelineWrap)
Please help me out.
Thanks in Advance
Srinivas
I'm moving data from one database to another (INSERT INTO ... SELECT ... FROM ....) and am encountering this error:
Msg 8114, Level 16, State 5, Line 6
Error converting data type varchar to numeric.
My problem is that Line 6 is:
set @brn_pk = '0D4BDE66347C440F'
so that is obviously not the problem and my query has almost 200 columns. I can go through one by one and compare what column is int in my destination table and what is varchar in my source tables, but that could take quite a while. How I can work out what column is causing the problem?
Hi,
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.
Thanks in advance.
Asim.
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.
Yours
Jayne
Hi,
I have been trying to export data from a Microsoft Excel 2007, Tester.xlsx to a SQL Server 2005 table as :
SELECT * INTO CUSTMAS FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:Tester.xls;Extended Properties=Excel 8.0')...[Providers$]
I have an error:
Cannot get the column information from OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"(null)"