Replication :: Reserved Column Names?
Mar 8, 2010
I am attempting to define a Transactional Publication with Updateable Subscriptions.
One of the articles in the definition has a column called "insertDate" The attempt fails with"A .NET Framework error occurred during execution of user-defined routine or aggregate "sp_MSmakeconflicttable_ sqlclr" System.Data.SqlClient.SqlException: Column names in each table must be unique. Column name insertdata in table 'conflict_ <Publication Name>_<TableName>' is specified more than onceDoes sp_MSmakeconflicttable_sqlclr attempt to create a derived table with this additional column.
Must I rename my column.Are there any other names I should watch out for (other than the standard reserved keywords).
View 8 Replies
ADVERTISEMENT
Nov 8, 2007
Hi there,
I'm trying to recreate a MS-SQL database in MySQL. One particular table has a column names "Precision", a reserved word in MySQL. I woudl really prefer to keep this name if possible as it will be referenced in all sorts of places. I've tried single and double quotes, that didn't work.
Is it possible to use reserveds word as column names? How?
cheers
View 6 Replies
View Related
Jan 8, 2008
Here we go,
I have a great question for all of you.
What is the problem in using reserved words in table columns name? Any one have a compeling reason not to use?
Here is an exemple:
/ ******** Table ********/
Entity
EntityId
Name
When you query this table you should use the [ ] like this:
SELECT EntityId,[Name] FROM Entity
Anyone have any objection to this, with actual facts?
The question why use this?
This about the programing object in C#
Entity oneEntity = new Entity();
oneEntity.Name = "Test Entity";
It could be:
oneEntity.EntityName = "Test Entity";
Any one???
View 9 Replies
View Related
May 8, 2007
I have one column name that is: description
when i write a query the world lights up with blue, I think I saw someone using [ ] around the word but I no longer remember if this is the way to handle reserve words that have been use as columns names
View 10 Replies
View Related
Jan 22, 2004
Hi
I was wondering if anyone has an idea of how we could find the table names and column names of the tables in our Sql server database at runtime/dynamically given our connection string? Please let me know.
Thanks.
View 5 Replies
View Related
Apr 28, 2008
I need to create the following table in reporting services
PRODUCT April March Feb
2008 2007 2008 2007 2008 2007
chair 8 9 7 4 4 4
table 3 4 5 6 4 6
My problem is the month names are a column in the dataset, but I dont know how to get it to fill as column headers???
Thanks in advance!!!
View 1 Replies
View Related
Jul 23, 2005
I have a TSQL script to add daily tables to replication and then runthe snapshot agent to distribute them to two subscribers. The scriptexecutes without errors, but when I check the running jobs for eachserver I see the following:JUST AN EXAMPLEJob1'Category' REPL-SnapshotJob2'Category' REPL-DistributionWhat is the difference between these two categories? Also 'Job1' worksproperly and receives the 3 new replicated tables, while 'Job2' seemsto be stuck on Step 2 and isn't receiving the 3 new replicated tables.Below is a copy of the stored procedure for reference.GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS OFFGOCREATE PROCEDURE dbo.sp_TESTaddDailyTablesToReplication (@@IOI_TABLEvarchar(64), @@TRADE_TABLE varchar(64), @@CHAT_TABLE varchar(64) ) ASDECLARE @SUBSCRIBER_NYPROD2 varchar(64)DECLARE @SUBSCRIBER_CTDEV2 varchar(64)DECLARE @SP_INSERT_PREFIX varchar(24)DECLARE @SP_DELETE_PREFIX varchar(24)DECLARE @SP_UPDATE_PREFIX varchar(24)DECLARE @INSERT_SP varchar(24)DECLARE @DELETE_SP varchar(24)DECLARE @UPDATE_SP varchar(24)SET @SUBSCRIBER_NYPROD2 = 'INDII_NY2_PROD'SET @SUBSCRIBER_CTDEV2 = 'D02'SET @SP_INSERT_PREFIX = 'CALL sp_MSins_'SET @SP_DELETE_PREFIX = 'CALL sp_MSdel_'SET @SP_UPDATE_PREFIX = 'CALL sp_MSupd_'SET @INSERT_SP = @SP_INSERT_PREFIX + @@IOI_TABLESET @DELETE_SP = @SP_DELETE_PREFIX + @@IOI_TABLESET @UPDATE_SP = @SP_UPDATE_PREFIX + @@IOI_TABLEDECLARE @SCHEMA_OPTIONS intSET @SCHEMA_OPTIONS = 0x000000000000CEA3exec sp_addarticle @publication = N'Indii', @article = @@IOI_TABLE,@source_owner = N'dbo', @source_object = @@IOI_TABLE,@destination_table = @@IOI_TABLE, @type = N'logbased', @creation_script= null, @description = null, @pre_creation_cmd = N'drop',@schema_option = @SCHEMA_OPTIONS, @status = 16, @vertical_partition =N'false', @ins_cmd = @INSERT_SP, @del_cmd = @DELETE_SP, @upd_cmd =@UPDATE_SP, @filter = null, @sync_object = null, @auto_identity_range =N'false'exec sp_addsubscription @publication = N'Indii', @article =@@IOI_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'exec sp_addsubscription @publication = N'Indii', @article =@@IOI_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'SET @INSERT_SP = @SP_INSERT_PREFIX + @@TRADE_TABLESET @DELETE_SP = @SP_DELETE_PREFIX + @@TRADE_TABLESET @UPDATE_SP = @SP_UPDATE_PREFIX + @@TRADE_TABLEexec sp_addarticle @publication = N'Indii', @article = @@TRADE_TABLE,@source_owner = N'dbo', @source_object = @@TRADE_TABLE,@destination_table = @@TRADE_TABLE, @type = N'logbased',@creation_script = null, @description = null, @pre_creation_cmd =N'drop', @schema_option =@SCHEMA_OPTIONS, @status = 16,@vertical_partition = N'false', @ins_cmd = @INSERT_SP, @del_cmd =@DELETE_SP, @upd_cmd = @UPDATE_SP, @filter = null, @sync_object = null,@auto_identity_range = N'false'exec sp_addsubscription @publication = N'Indii', @article =@@TRADE_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'exec sp_addsubscription @publication = N'Indii', @article =@@TRADE_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'SET @INSERT_SP = @SP_INSERT_PREFIX + @@CHAT_TABLESET @DELETE_SP = @SP_DELETE_PREFIX + @@CHAT_TABLESET @UPDATE_SP = @SP_UPDATE_PREFIX + @@CHAT_TABLEexec sp_addarticle @publication = N'Indii', @article = @@CHAT_TABLE,@source_owner = N'dbo', @source_object = @@CHAT_TABLE,@destination_table = @@CHAT_TABLE, @type = N'logbased',@creation_script = null, @description = null, @pre_creation_cmd =N'drop', @schema_option =@SCHEMA_OPTIONS, @status = 16,@vertical_partition = N'false', @ins_cmd = @INSERT_SP, @del_cmd =@DELETE_SP, @upd_cmd = @UPDATE_SP, @filter = null, @sync_object = null,@auto_identity_range = N'false'exec sp_addsubscription @publication = N'Indii', @article =@@CHAT_TABLE, @subscriber = @SUBSCRIBER_NYPROD2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'exec sp_addsubscription @publication = N'Indii', @article =@@CHAT_TABLE, @subscriber = @SUBSCRIBER_CTDEV2, @destination_db =N'Indii', @sync_type = N'automatic', @update_mode = N'read only',@offloadagent = 0, @dts_package_location = N'distributor'DECLARE @SNAPSHOT_JOB_NAME varchar(64)-- Run Snapshot for NY2 serverSET @SNAPSHOT_JOB_NAME = 'INNYWPP01PRODUCTION-Indii-Indii-1'EXEC msdb.dbo.sp_start_job @job_name = @SNAPSHOT_JOB_NAME-- Run Snapshot for CT2 serveSET @SNAPSHOT_JOB_NAME = 'innywpp01production-Indii-Indii-D02-3'EXEC msdb.dbo.sp_start_job @job_name = @SNAPSHOT_JOB_NAMEPRINT 'added ' + @@IOI_TABLE + ' from replication'PRINT 'added ' + @@TRADE_TABLE + ' from replication'PRINT 'added ' + @@CHAT_TABLE + ' from replication'GOSET QUOTED_IDENTIFIER OFFGOSET ANSI_NULLS ONGO
View 1 Replies
View Related
Apr 23, 2007
This is strange.
We use Merge (pull) replication to get data from the "mother ship" updated to the laptops of 15 sales reps before they head out into the field, where they work disconnected. At end of day, they all reconnect and all the data is once again shared.
We've been having problems with the replication, but that's another topic.
This morning, I logged onto the server to check the replication monitor, and found that all of the subscriber names for that particular subscription are blank! They show the open/close bracket ( [] ) followed by the database name. This is under the My Publishers >> [server name] >> [publication name] node in the explorer tree. If I try to double-click on one of the subscribers to get their detail, I get tne message, "Replication Monitor could not open the Detail Window." and "Specified cast is not valid".
If I look at the publication in SQL Server Management Studio, all of the subscriber names show up just fine. But they do NOT show up under Replication Monitor.
No one touched the database over the weekend, and this was working just fine on Friday afternoon.
Has anyone seen this before? What to do?
View 3 Replies
View Related
Dec 29, 2003
Hello,
Im trying to get the column names from a database and display them in textboxes. someone has already helped me by tellnig me that i need to use the FillSchema command. Which works just fine and I can see only the colum names in a datagrid when i bind it to that.
The problem is that I do not know how to extract the name of a column and put it in to a textbox ?
does anybody know how I can fo this ?
Thanks a million
Rob
View 3 Replies
View Related
Aug 24, 2005
Hi,How do I display the column names from my Sql server table?In asp3 the recordset allowed thisforeach key in rs ColumnName = key.name ColumnValue = key.valuenextHow do I do this in .Net?I want to use a DataReader so I can read through each record and only display the ones I want.TABLE_ONEColumn_OneColumn_TwoColumn_Threethanks,
View 1 Replies
View Related
Mar 30, 2004
Hi, i need a query to have the columns names !!
If you have another solution, let me know !!
superj !!!
View 12 Replies
View Related
Apr 10, 2008
Hi
When I run this query..
SELECT COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME='TableTest'
I get all the column names, but the result is not in the same order as when I check the design for the table, why is that?
View 2 Replies
View Related
Dec 27, 2005
At my work, we are upgrading a number of MS Access 2000 databases to SQL Server 2000. Many of the columns have names that do not follow the rules for Identifiers. For example there are columns with a numeric names 1,2,3,4,5,6,7,8,9. There are also columns with a hyphen in the name and columns that begin with a number and not an underscore or an alpha character. Plus there are columns with names like first, last, position, etc. (There was also columns with reserved words used as the names. I have changed those column names in the databases that I have already converted) Will leaving these names alone create a problem? The database serves as the backend to ASP pages.
Miranda
View 4 Replies
View Related
Jan 1, 2008
Hello,I'm still extremely novice to SQL and I've tried googling how to produce this result but I've been encountering a lot dynamic sql commands which isn't exactly what I want. If this is an ignorant question I do apologize but heres my scenario: I have a table with columns of the day, for instance: Monday_hasData (bit)Monday_DataAnd what I want to do is essentially pass in "Monday" as a parameter and rather than doing an If statement on each row, I would like to just like to do something like assign @dayCheck + "_hasData" to a variable and then use @dayCheck as part of my query. Is this possible or am I going to have to have 7 cases? Thank you for any input,Chance
View 2 Replies
View Related
May 30, 2008
Hi all,
By using below query i can get no of tables having the give column name in a particular database,
SELECT COUNT(*) AS CounterFROM syscolumnsWHERE (name = 'empno')
but i want to know the table names too?
any one please suggest me how to find table names too......
In other words i know the particular column name and right now i want to know the table names in which this column name exists.
View 2 Replies
View Related
Jul 12, 2002
Hi,
I have a table in which out of 20 columns, there will be data only in a few columns. So, I need to find those column names which have data in them. I was able to do it using the information_schema and using a cursor to loop through all the column names and find out which columns have data in them. But i need to know if there is a more efficient way of doing this without using the cursor. Can somebody please let me know how this can be done without using a cursor? Thank you.
View 2 Replies
View Related
Nov 26, 2002
Can anyone help me with a SQL statement that will list all the column names in a table please ?
I just want to list out the column name so that i can develop asp/vb more effectively than having to use SQLEM and Design table to see the field names.:confused:
thanks
FatherJack
View 2 Replies
View Related
Jun 10, 2004
Hi is possible to create dynamic column name
example
Declare
@StartDate as dateTime
Select @StartDate = '2004-06-05'
select
SUM(Case When table1_date BETWEEN dateadd(day,-6,@StartDate) and @StartDate then 1 else 0 end)AS [dateadd(day,-6,@StartDate)],
SUM(Case When table1_date BETWEEN dateadd(day,-13,@StartDate) and dateadd(day,-7,@StartDate) then 1 else 0 end)AS [dateadd(day,-13,@StartDate)]
from
table1
View 2 Replies
View Related
Aug 19, 2004
I would like to pass into a stored proc the column names I want it to return; how do I do this please?
So I pass in say:
@p_AccountNumber with a value of 'AccountNum'
and the SELECT that the sp fires is of the form
SELECT AccountNum from AccountTable
Any help appreciated
View 2 Replies
View Related
Jan 19, 2005
thanx for the help in advance,
i have a table with Monday, Tuesday, Wednesday. .... Sunday
i get the weekday by
select @stat = datename(dw, getdate()) which in this case is Wednesday
but when i do something like
select top 2 * from myTable
where 'myTable.'+@stat = 1
which is as same as
select top 2 * from myTable
where myTable.wednesday = 1
i receive a syntax error. how can i dynamically select myTable.Wednesday ?
i also tried using
select top 2 * from Intercon
where '@stat' = '1'
but then this doesn't return anything where it should.
any help?
View 2 Replies
View Related
Mar 7, 2005
Is there a way that you could get the column names for each table in a database using 1 query?
something like:
tbl colname
t1 catID
t1 catName
t2 prodID
t2 prodDesc
t3 cartID
...
...
I know it would be long, but I would just be searching through the saved output for specific names.
View 1 Replies
View Related
Nov 15, 2005
Hi All,
I was wondering how would I get the column names from a table? Not the results just a listing of column names. What is the command to get this information? I am using MS Access, but I posted here because MS SQL is the closest thing on this forum and the syntax is usually similar.
Thanks a bunch.
Val
View 5 Replies
View Related
Oct 13, 2005
SQL server throws an exception when I try to run the following code:
ALTER TABLE contacts ADD default ntext NULL;
The reason for that is that "default" is an SQL keyword.
The way I handle this now is I put an underscore at the front:
ALTER TABLE contacts ADD _default ntext NULL
Is there a cleaner way to handle this, i.e. to keep the column name "default" and forse sql server to create a column with this name?
Thank you!
View 9 Replies
View Related
Jun 22, 2007
Morning guys,
I have a project where Iam suppose to script all columns in every database/table/columns on the network.
I figured the best way to do this would be using sql-dmo (Sql Server 2000)
Does anyone have any suggestions on getting started with this project?
thanks,
Jonathan
View 14 Replies
View Related
Nov 20, 2007
Hello, everyone:
I used bcp to generate a txt file as:
EXEC master..xp_cmdshell 'bcp "SELECT * FROM Test..Orders" queryout "c:/test.txt " -U tester -P tester -c'
It works fine except without column names of table. Does any one have idea that can bring out column names from table?
Thanks
ZYT
View 9 Replies
View Related
Jan 25, 2004
I have been looking in the SQL Server Help Files, but I cant seem to find the syntax to change the name of a column name..?
Can it be done? if so, what is the syntax?
View 4 Replies
View Related
Mar 15, 2004
I am writing a stored procedure in which I have a query that selects the Headings of Columns from another table...
I want to then create a loop that will contain a variable with the value of the column heading and then set the column to a value of NULL...
Is there any way to accomplish this???
I thought about placing these values into a temp table...
This is what I have so far...
Declare @QueryX nvarchar(500)
Declare @FieldName varchar(20)
Create Table #UpdateRejDoc
(
Abbreviation varchar(20)
)
Insert into #UpdateRejDoc
Select Abbreviation
From tbl_Titles
left JOIN tbl_TitleRouting on tbl_Titles.Title_ID = tbl_TitleRouting.Title_ID
Where tbl_TitleRouting.Application_ID = @Application_ID
While Exists (Select Abbreviation from #UpdateRejDoc)
Begin
Set @QueryX = 'Update DBLandfillUser.tbl_ObjectApprovals' +
'@AppName + Set @FieldName = null Where object_id =' + Cast(@object_id as VarChar(20))
End
View 1 Replies
View Related
Apr 22, 2008
Hi All,
Heres my problem...
If I create a table T1 with a couple of columns; MyPKCol, ColA
I then create a view V1 as "Select * From T1"
At this stage, if I run "Select * From V1" the result will, as expected, include following columns:
MyPKCol,ColA
If I now add another column, ColB, to T1 and then again run "Select * From V1", I still get the result with only 2 columns;
MyPKCol,ColA
In order for V1 to return MyPKCol,ColA,ColB I have to drop V1 and then recreate it again.
Can someone explain why this is and how I can clear this type of cache?
I've tried the following commands, but it didn't work:
DBCC FREEPROCCACHE;
DBCC FREEsystemCACHE( 'ALL' );
DBCC DROPCLEANBUFFERS;
There has to be an easier way than having to recreate SPs, Function and Views just because you make a change to a table.
I hope I made sence
Thank you in advance for any help.
roamso
View 8 Replies
View Related
Dec 2, 2013
I have a SQL table with 5000 rows in it. Some of the other SQL columns have different values but if I wanted to swap say 500 of the rows would it be something like
UPDATE Coupins
SET Name='test1,test2,test3'
WHERE Name='test4,test5,test6';
I need basically to swap the Name value of these without affecting any other values.
View 4 Replies
View Related
Feb 15, 2006
I'm trying to perform an insert on a table from another table, but
the fieldnames are different. The data is similar, but there are
less fields as well. Does anyone know how to accomplish this? All
the material I've read on the Internet so far pertaining to Insert
statements, has all the column names matching up.
Any help would be greatly appreciated.
View 3 Replies
View Related
Aug 28, 2007
Got my first real job to do in SSIS and it's not really much fun. Here goes. I have excel files from various vendors, none of which are in the same format. Now I'm less concerned with the names, I have those in variables so that's not a big deal. My issue is around the column names. Let's say we have file a, b and c. In file a the first name field is called [first name], in file b it's first_name and in file c it's firstname. What I want to do is create 1 package that can handle these files regardless of how first name is spelled. I was thinking I could open the file and somehow search for a column name that contained the word "first%" and then map the column name to a variable. I'm sort of stuck though on how to actually do that. Help. And please don't tell me to tell the vendors to standardize. That's the end goal but it's like herding cats.
Mike
"oh, that monkey is going to pay"
View 3 Replies
View Related
Jul 23, 2005
I have set up a query where I am using a cursor to pass result from onequery to use as select parameters in another query. The problem I amhaving is that when I pass the results into the second query I get thecolumn headers?How do you suppress the column headers from showing in the query? Isee how you select options - print headers.ThanksJason
View 2 Replies
View Related
Jul 23, 2005
Hi,I am learning Ms Sql and I found that a lot of the tables I am finding( in different tutorials ) are using special names for their columnnames such like au_username, au_salery ...Is the any naming convention for column names, or just at all is thereany reason for naming the table columns in specific way.Thanks in advance.
View 4 Replies
View Related
