Showing posts with label tables. Show all posts
Showing posts with label tables. Show all posts

Tuesday, March 27, 2012

Adding a new VarBinaryMax column to an existing table using SMO.

Consider the following code.

Table applicationSettingsTable = database.Tables["application_settings"];

Column logoColumn = new Column(applicationSettingsTable, "logo", DataType.VarBinaryMax);

applicationSettingsTable.Columns.Add(logoColumn);

applicationSettingsTable.Alter();

I don't understand why - but when I execute this code I end up with a column that has the data type varbinary(1) in the database rather than varbinary(MAX). I can't see anything in the msdn documentation that tells me I need to do something special for varbinary(max) columns, but maybe I do?

Any help much appreciated.

This appears to work tho...

Column logoColumn = new Column(applicationSettingsTable, "logo", DataType.VarBinary(-1));

Sunday, March 25, 2012

Adding a full text search across multiple tables (with text fields)

Hi, i'm trying to do a full text search on my site to add a weighting score to my results. I have the following database structure:

Documents:
- DocumentID (int, PK)
- Title (varchar)
- Content (text)
- CategoryID (int, FK)

Categories:
- CategoryID (int, PK)
- CategoryName (varchar)

I need to create a full text index which searches the Title, Content and CategoryName fields. I figured since i needed to search the CategoryName field i would create an indexed view. I tried to execute the following query:

CREATE VIEW vw_Documents
WITH SCHEMABINDING AS
SELECT dbo.Documents.DocumentID, dbo.Documents.Title, dbo.Documents.[Content], dbo.Documents.CategoryID, dbo.Categories.CategoryName
FROM dbo.Categories INNER JOIN dbo.Documents ON dbo.Categories.CategoryID = dbo.Documents.CategoryID

GO
CREATE UNIQUE CLUSTERED INDEX vw_DocumentsIndex
ON vw_Documents(DocumentID)

But this gave me the error:

Cannot create index on view 'dbname.dbo.vw_Documents'. It contains text, ntext, image or xml columns.

I tried converting the Content to a varchar(max) within my view but it still didn't like.

Appreciate if someone can tell me how this can be done as surely what i'm trying to do is not ground breaking.

Hi jgd12345,

After talking with my colleagues i find a solution:

First you need to change Content datatype from text to varchar(max);

Then set the following options:

SET CONCAT_NULL_YIELDS_NULL ON

|||Cheers, i'll test this out when i get back to the office. Thanks again.

Adding a database user

Hi,
Could someone please tell me how to add a database user using a query? I
know how to set the appropriate permissions for tables of existing users,
but I can't figure out how to add a new user.
Thank you,
SteveSteve,
Do you mean a User or a Login or both? There is a difference and a User is
no good without an associated Login. You may want to check out sp_adduser
and sp_addlogin in BooksOnLine.
Andrew J. Kelly
SQL Server MVP
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Could someone please tell me how to add a database user using a query? I
> know how to set the appropriate permissions for tables of existing users,
> but I can't figure out how to add a new user.
> Thank you,
> Steve
>|||Hi,
Also have a look into the system procedures which allocate fixed roles to
users/logins in books online.
sp_addsrvrolemember and sp_addrolemember
Thanks
Hari
MCDBA
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ORPqt3OREHA.3012@.tk2msftngp13.phx.gbl...
> Steve,
> Do you mean a User or a Login or both? There is a difference and a User
is
> no good without an associated Login. You may want to check out sp_adduser
> and sp_addlogin in BooksOnLine.
> --
> Andrew J. Kelly
> SQL Server MVP
>
> "Steve Caliendo" <scaliendo@.epion.com> wrote in message
> news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
I[vbcol=seagreen]
users,[vbcol=seagreen]
>|||To add to the other responses:
Use sp_addlogin to add a new SQL login.
Use sp_grantlogin to grant an existing Windows account access to SQL Server
Use sp_grantdbaccess to add a SQL login or Windows account as a database
user
For example:
EXEC sp_addlogin 'MySqlLogin', 'MyPassword'
EXEC sp_grantlogin 'MyDomain\MyAccount'
USE MyDatabase
EXEC sp_grantdbaccess 'MySqlLogin'
EXEC sp_grantdbaccess 'MyDomain\MyAccount'
See the Books Online for details.
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve Caliendo" <scaliendo@.epion.com> wrote in message
news:e4Rvg8NREHA.1308@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Could someone please tell me how to add a database user using a query? I
> know how to set the appropriate permissions for tables of existing users,
> but I can't figure out how to add a new user.
> Thank you,
> Steve
>

Thursday, March 22, 2012

Adding a column to tables in merge replication.

Hi, we need to add a column to the table that is used as the subscriber table
in a merge replication, will this break the replication.
We also need to add a column to the table that is used as the publisher
table in a merge replication, will this also break the replication?
Thanks,
Pingx
You should not touch the subscriber tables - they will be locked and you
will be unable to make modifications to them.
Use sp_repladdcolumn to add the column in SQL 2000. In SQL 2005 is the
replicate_ddl statement is set to true table modifications will be
replicated.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:2E4682B7-BD3D-4765-984F-2EC1A83C3BD0@.microsoft.com...
> Hi, we need to add a column to the table that is used as the subscriber
> table
> in a merge replication, will this break the replication.
> We also need to add a column to the table that is used as the publisher
> table in a merge replication, will this also break the replication?
> Thanks,
> Pingx
|||I used the statement
ALTER TABLE table_name ADD <column> at the publisher.
It got replicated to the subscriber.
Is this OK? do I need to use replicate_ddl statement ?
Thanks.
Pingx
"Hilary Cotter" wrote:

> You should not touch the subscriber tables - they will be locked and you
> will be unable to make modifications to them.
> Use sp_repladdcolumn to add the column in SQL 2000. In SQL 2005 is the
> replicate_ddl statement is set to true table modifications will be
> replicated.
> --
> http://www.zetainteractive.com - Shift Happens!
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "Pingx" <Pingx@.discussions.microsoft.com> wrote in message
> news:2E4682B7-BD3D-4765-984F-2EC1A83C3BD0@.microsoft.com...
>
>
|||It appears that you are using SQL 2005 where replicate_ddl is enabled by
default.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Pingx" <Pingx@.discussions.microsoft.com> wrote in message
news:1991127A-39EC-492C-8FBE-EAAB44EE0C3C@.microsoft.com...[vbcol=seagreen]
>I used the statement
> ALTER TABLE table_name ADD <column> at the publisher.
> It got replicated to the subscriber.
> Is this OK? do I need to use replicate_ddl statement ?
> Thanks.
> Pingx
> "Hilary Cotter" wrote:

Adding a article causes ALL articles to be refreshed

I have setup Transactional replication in SQL 2005 between two servers and have about 200 tables being replicated. The problem is that every time, I add or drop a table to replication, and start the Snapshot agent, it re-initializes every article and re-loads every article. This process takes 1 hour to complete and CPU usage goes to 100% during that time.

This behaviour seems very different from SQL 2000 where I would start Snapshot agent and only the relevant tables were added/dropped.

Has that functionality changed from 2000 to 2005? Am I not doing something right?

Thanks,

Amir

This is the default behavior for anonymous subscribers. IIRC through the GUI for named subscribers a complete snapshot will be generated. However if you use sp_addarticle only the mini snapshot for the one table will be generated.|||

I have tried using sp_article. The problem is that it gives me the error:

Cannot make the change because a snapshot is already generated. Set @.force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

And in order to avoid the error, if I use the Option of @.force_invalidate_snapshot=1, then again I am back to where I was before and it re-initializes everything.

BTW, What does IIRC mean?

Thanks,

Amir

|||

Any more thoughts on this? If my replication is setup and running, is there NO WAY to add "one" table to the publication without having to do the Bulk copy on ALL tables? Adding a table to replication seems one of the most basic maintenance tasks. I'm still surprised that MSFT has made that basic task so difficult in 2005.

adding 2 records

Hello,
I've been converting/normalizing data and inserting it to other tables.
What i would like to do is to create 2 records for a particular value if
it occurs in the denormalized table.
Any idea how i should do this?I assume you mean something like:
CREATE TABLE DenormTable
(
DenormID int PRIMARY KEY
, MainVal int
, Year2005Value int
, Year2004Value int
)
If so, you merely have to create an insert like so:
CREATE TABLE NormTable1
(
Norm1ID int PRIMARY KEY
, MainVal int
)
CREATE TABLE NormTable2
(
Norm2ID int IDENTITY(1,1) PRIMARY KEY
, Norm1ID int NOT NULL
, Val int
, Year int
)
INSERT INTO NormTable1 (Norm1ID, MainVal)
SELECT DenormID, MainVal FROM DenormTable
INSERT INTO NormTable2 (Norm1ID, Val, Year)
SELECT DenormID, Year2005Val, 2005 FROM DenormTable
INSERT INTO NormTable2 (Norm1ID, Val, Year)
SELECT DenormID, Year2004Val, 2002 FROM DenormTable
You can combine the two in a single statement, if necessary, with some CASE
statements or creating a temporary VIEW with a UNION.
Gregory A. Beamer
MVP; MCP: +I, SE, SD, DBA
****************************************
*******
Think Outside the Box!
****************************************
*******
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:u$%23PCme6FHA.564@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been converting/normalizing data and inserting it to other tables.
> What i would like to do is to create 2 records for a particular value if
> it occurs in the denormalized table.
> Any idea how i should do this?|||In my Delphi app connecting to Sql Server using ODBC.. When 3 or more users
updating the same table at the same time (Not the same record) there is a
delay for approx 10 sec, I tried to use Enterprise manager from another
machine to update the same table and found the same problem so the problem
is not in my program code..
Finally after trying to solve this i noticed that 1 pc is the reason of the
delay, and after changing this Client IP.. it work now great and if i change
the IP back to it's old value the problem raise again!!.
Could i know what is the relation between Sql server or ODBC and client
machine IPs'
any information will appreciated
"Jason" <jasonlewis@.hotmail.com> wrote in message
news:u$%23PCme6FHA.564@.TK2MSFTNGP10.phx.gbl...
> Hello,
> I've been converting/normalizing data and inserting it to other tables.
> What i would like to do is to create 2 records for a particular value if
> it occurs in the denormalized table.
> Any idea how i should do this?

Tuesday, March 20, 2012

Added text field to database. New entries not equal to ""

Hi!
I'm accessing an MS Access database from an ASP server.
I just added a new text column (field) to one of my tables. I have not added
any data to this new field in any of the rows.
I read the field in and compare it to "" and it's coming up as false.
How do identify the empty fields?
'ASP code - Never produces "Blank"
if rsSet("region") = "" then response.write "Blank" else response.write
rsSet("region")
Try
If IsNull (rsSet("region")) Then Response.Write "Null" Else Response.Write
rsSet("region")
Alan
"Noozer" <dont.spam@.me.here> wrote in message
news:IVmAc.755870$Pk3.730376@.pd7tw1no...
> Hi!
> I'm accessing an MS Access database from an ASP server.
> I just added a new text column (field) to one of my tables. I have not
added
> any data to this new field in any of the rows.
> I read the field in and compare it to "" and it's coming up as false.
> How do identify the empty fields?
> 'ASP code - Never produces "Blank"
> if rsSet("region") = "" then response.write "Blank" else response.write
> rsSet("region")
>
|||Thanks!
"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
news:ONCUTnKVEHA.2928@.tk2msftngp13.phx.gbl...
> Try
> If IsNull (rsSet("region")) Then Response.Write "Null" Else Response.Write
> rsSet("region")
> Alan
> "Noozer" <dont.spam@.me.here> wrote in message
> news:IVmAc.755870$Pk3.730376@.pd7tw1no...
> added
>
|||And there are sometimes occasions when it could be a Null, or it could be a
blank. If you want to check for both at once, use:
If Len(rsSet("region") & "") = 0 Then Response.Write "Nothing" Else
Response.Write rsSet("region")
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Noozer" <dont.spam@.me.here> wrote in message
news:4MnAc.791748$oR5.380643@.pd7tw3no...[vbcol=seagreen]
> Thanks!
> "Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
> news:ONCUTnKVEHA.2928@.tk2msftngp13.phx.gbl...
Response.Write[vbcol=seagreen]
response.write
>

Added text field to database. New entries not equal to ""

Hi!
I'm accessing an MS Access database from an ASP server.
I just added a new text column (field) to one of my tables. I have not added
any data to this new field in any of the rows.
I read the field in and compare it to "" and it's coming up as false.
How do identify the empty fields?
'ASP code - Never produces "Blank"
if rsSet("region") = "" then response.write "Blank" else response.write
rsSet("region")Try
If IsNull (rsSet("region")) Then Response.Write "Null" Else Response.Write
rsSet("region")
Alan
"Noozer" <dont.spam@.me.here> wrote in message
news:IVmAc.755870$Pk3.730376@.pd7tw1no...
> Hi!
> I'm accessing an MS Access database from an ASP server.
> I just added a new text column (field) to one of my tables. I have not
added
> any data to this new field in any of the rows.
> I read the field in and compare it to "" and it's coming up as false.
> How do identify the empty fields?
> 'ASP code - Never produces "Blank"
> if rsSet("region") = "" then response.write "Blank" else response.write
> rsSet("region")
>|||Thanks!
"Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
news:ONCUTnKVEHA.2928@.tk2msftngp13.phx.gbl...
> Try
> If IsNull (rsSet("region")) Then Response.Write "Null" Else Response.Write
> rsSet("region")
> Alan
> "Noozer" <dont.spam@.me.here> wrote in message
> news:IVmAc.755870$Pk3.730376@.pd7tw1no...
> added
>|||And there are sometimes occasions when it could be a Null, or it could be a
blank. If you want to check for both at once, use:
If Len(rsSet("region") & "") = 0 Then Response.Write "Nothing" Else
Response.Write rsSet("region")
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Noozer" <dont.spam@.me.here> wrote in message
news:4MnAc.791748$oR5.380643@.pd7tw3no...
> Thanks!
> "Alan Howard" <Xalan.howardX@.Xparadise.net.nzX> wrote in message
> news:ONCUTnKVEHA.2928@.tk2msftngp13.phx.gbl...
Response.Write[vbcol=seagreen]
response.write[vbcol=seagreen]
>sql

'Addding' Column In View

Hi! Here is what I need to do:
I have 2 tables; the first called "IDs" and the second called "Test".
This 'Test' doesn't have an identity field, and I need to have one to
do some filtering, but for burocracy I can't simply add another column
to the table. So, as as it is, I had to create another table
called 'IDs' with only 1 field called 'ID' filled with autogenerated
numbers from 1 to 99999999^9999 :D. Then, the thing that I can't do is
to "append" this table to the "Test".
When I do, for example:
<select IDs.id, Test.Name from IDs, test>, what I get is something
like:
id name
--
----
1 ABC
2 ABC
3 ABC
4 ABC
1 DEF
2 DEF
3 DEF
4 DEF
1 GHI
2 GHI
3 GHI
4 GHI
1 JKM
2 JKM
3 JKM
4 JKM
Notice the repetition of the values...
I would need it to be like:
id name
--
----
1 ABC
2 ABC
3 ABC
4 ABC
So... is there something I can do to get this? And also, what would
happen if one of the tables had more registries than the other? Would
it work?
Very very thanks in advance;
Bye.See if this helps:
How to dynamically number rows in a SELECT Statement
http://support.microsoft.com/defaul...kb;en-us;186133
AMB
"Dan_Aykroyd" wrote:

> Hi! Here is what I need to do:
> I have 2 tables; the first called "IDs" and the second called "Test".
> This 'Test' doesn't have an identity field, and I need to have one to
> do some filtering, but for burocracy I can't simply add another column
> to the table. So, as as it is, I had to create another table
> called 'IDs' with only 1 field called 'ID' filled with autogenerated
> numbers from 1 to 99999999^9999 :D. Then, the thing that I can't do is
> to "append" this table to the "Test".
> When I do, for example:
> <select IDs.id, Test.Name from IDs, test>, what I get is something
> like:
> id name
> --
> ----
> 1 ABC
> 2 ABC
> 3 ABC
> 4 ABC
> 1 DEF
> 2 DEF
> 3 DEF
> 4 DEF
> 1 GHI
> 2 GHI
> 3 GHI
> 4 GHI
> 1 JKM
> 2 JKM
> 3 JKM
> 4 JKM
> Notice the repetition of the values...
> I would need it to be like:
> id name
> --
> ----
> 1 ABC
> 2 ABC
> 3 ABC
> 4 ABC
> So... is there something I can do to get this? And also, what would
> happen if one of the tables had more registries than the other? Would
> it work?
> Very very thanks in advance;
> Bye.
>|||SQL2005 will have 'rownumber', which will let you do it really easily.
But for the time being, you will have to do it the nasty way.|||Wow... it wasn't as easy as I thought! :)
I thought that what I needed to do would be easy, because it
requires... "no processing"; I mean, no combination of rows, no
relations, no linking, etc etc; it's just adding a column to a view in
the way it's stored in the table...
Thanks very much Alejandro; I'm gonna read it now!
And if there's an easier way... I'd be glad to hear it ;)sql

Monday, March 19, 2012

Add the NOT FOR REPLICATION Option to Existing Tables

Hello,

How to add the NOT FOR REPLICATION Option to Existing Tables( IDENTITY column,FOREIGN KEY constraints )- TSQL.

Thanks,

Kanna.

alter table dbo.testtable

alter column [id] add NOT FOR REPLICATION

Sunday, March 11, 2012

Add table and column descriptions/comments using script

Is it possible to add descriptions/comments to tables and columns using a
system stored procedure?Yes. See sp_addextendedproperty in the Books Online
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Billy" <Billy@.discussions.microsoft.com> wrote in message
news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> Is it possible to add descriptions/comments to tables and columns using a
> system stored procedure?|||Just what I was looking for!
Thanks a lot Dan.
Rgds
Per Christian Paasche
Norway
"Dan Guzman" wrote:
> Yes. See sp_addextendedproperty in the Books Online
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Billy" <Billy@.discussions.microsoft.com> wrote in message
> news:605D7315-2B68-409D-9560-997417CAC941@.microsoft.com...
> > Is it possible to add descriptions/comments to tables and columns using a
> > system stored procedure?
>
>

Add similar fields to many tables

Folks, i have to create four fields in every user table within my database:

CREATED_BY VARCHAR(25), CREATED_DATE [DATETIME], MODIFIED_BY VARCHAR(25), MODIFIED_DATE [DATETIME]

There are more than hundred tables, so i wanna automate this. i am tryin to do this in a cursor: please guide!

declare @.name VARCHAR (50)
declare cur cursor
fast_forward
for select name from sysobjects where type='u' and status not like '-%'
open cur
WHILE (1=1)
BEGIN
FETCH NEXT
FROM cur
INTO @.name
IF @.@.fetch_status = 0
BEGIN
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD created_date [DATETIME]
GO
ALTER TABLE @.name
ADD modified_by [VARCHAR] (25)
GO
ALTER TABLE @.name
ADD modified_date [DATETIME]
END
ELSE
BREAK
END
DEALLOCATE cur

I also want that if one column for a table exists; the other columns should be created rather than it quits.

Howdy!Folks, please help!
Or do i have to add the columns manually! :confused:

Howdy!|||Maybe if you are concerned about whether a column already exists it would be easier to run the four different ALTER statements separately rather than coding around possibilities...y'know, four scripts that you run against all the tables instead of one.

Just a suggestion.

But for a one-time modification like this, you have to be careful that you don't spend more time trying to devise the most optimal and elegant solution than it would take you to just knuckle down and do the dirty work.|||declare @.tablename varchar(50)
declare @.add_field varchar(255)

declare cur_tables cursor for
select name from sysobjects where type='u' and status not like '-%'

open cur_tables
fetch next from cur_tables into @.tablename
while @.@.fetch_status = 0
begin
--print 'table name is ' + @.tablename

set @.add_field = 'alter table ' + @.tablename + ' add created_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add created_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_by varchar(25)'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

set @.add_field = 'alter table ' + @.tablename + ' add modified_date smalldatetime'
--print '@.add_field is ' + @.add_field
exec (@.add_field)

fetch next from cur_tables into @.tablename
end
close cur_tables
deallocate cur_tables|||-- Run this and cut results to query window & run it.
-- Tim S

SET NOCOUNT ON;
SELECT
CASE WHEN col1.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_BY VARCHAR(25);' + CHAR(10) ELSE '' END +
CASE WHEN col2.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD CREATED_DATE [DATETIME];'+ CHAR(10) ELSE '' END +
CASE WHEN col3.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_BY VARCHAR(25);'+ CHAR(10) ELSE '' END +
CASE WHEN col4.COLUMN_NAME IS NULL THEN 'ALTER TABLE ' + tab.TABLE_NAME + ' ADD MODIFIED_DATE [DATETIME];'+ CHAR(10) ELSE '' END
FROM INFORMATION_SCHEMA.TABLES tab
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col1
ON tab.TABLE_NAME = col1.TABLE_NAME AND tab.TABLE_SCHEMA = col1.TABLE_SCHEMA AND 'CREATED_BY' = col1.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col2
ON tab.TABLE_NAME = col2.TABLE_NAME AND tab.TABLE_SCHEMA = col2.TABLE_SCHEMA AND 'CREATED_DATE' = col2.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col3
ON tab.TABLE_NAME = col3.TABLE_NAME AND tab.TABLE_SCHEMA = col3.TABLE_SCHEMA AND 'MODIFIED_BY' = col3.COLUMN_NAME
LEFT JOIN INFORMATION_SCHEMA.COLUMNS col4
ON tab.TABLE_NAME = col4.TABLE_NAME AND tab.TABLE_SCHEMA = col4.TABLE_SCHEMA AND 'MODIFIED_DATE' = col4.COLUMN_NAME
WHERE tab.TABLE_TYPE = 'BASE TABLE' AND -- tab.TABLE_NAME = 'test2' AND
( col1.COLUMN_NAME IS NULL OR col2.COLUMN_NAME IS NULL OR col3.COLUMN_NAME IS NULL OR col4.COLUMN_NAME IS NULL)

Thursday, March 8, 2012

Add one column in a table

I got ten database , and want to add one column in serveral tables.
However, I need to add that column in the fifth column .
Any easy script I can use 'thanks a lot"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:elCV8ntPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
NO. There isn't any easy script.
The table has to be dropped and recreated.
WHY to you need to insert the column at a specific position?|||Hi Agnes,
You can't insert column between existing columns. The physical order of
columns in rows in data files however doesn't matter.
The syntax for adding coulmnt to table is described in ALTER TABLE statement
in BOL
here's an example from BOL
ALTER TABLE doc_exf
ADD AddDate smalldatetime NULL
Since you are writing about order of columns I assume that you use * in your
statements. Don't do it. Select fields that you need and in order you need.
HTH
Peter|||That is not necessary because you can rearrange the column when you use the
select syntax.
i.e.
Select col1, col2, col3, col4, col12, col5 .....
"Agnes" <agnes@.dynamictech.com.hk> wrote in message
news:elCV8ntPGHA.1040@.TK2MSFTNGP12.phx.gbl...
>I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
>|||> However, I need to add that column in the fifth column .
WHY? Are you doing SELECT * in your code? Create a view (and stop using
SELECT * anyway). Are you not bothering to list column names in your insert
statements? Please name the columns.

> Any easy script I can use '
No, T-SQL does not support this. What Enterprise Mangler does is dump the
data into a #temp table, re-create the table, and move the data back. So
not exactly something you'd want to do on a large data set, even if you
could justify it.
A|||Use this procedure :
IF EXISTS(SELECT [NAME] FROM dbo.sysobjects WHERE NAME='SP_ADDCOLUMN')
DROP PROCEDURE SP_ADDCOLUMN
go
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
GO
CREATE PROC sp_addcolumn
@.TableName sysname, --FieldName
@.FieldDefine nvarchar(1000), --COLUMNNAME,DATATYPE,[NULL OR NOT NULL]
'column_b VARCHAR(20) NULL'
@.Position int=-1 --POSITION,For example the value 1,so
need add to the first column,
AS
IF ISNULL(OBJECTPROPERTY(OBJECT_ID(@.TableNa
me),'IsUserTable'),0)=0
BEGIN
RAISERROR(N'Invalid "%s"',12,16,@.TableName)
return
END
--Add Field
DECLARE @.s nvarchar(4000)
SET @.s=N'ALTER TABLE '+QUOTENAME(@.TableName)+N' ADD '+@.FieldDefine
BEGIN TRAN
EXEC sp_executesql @.s
IF @.@.ERROR=0
BEGIN
DECLARE @.maxcolid int
SELECT @.Position=ISNULL(@.Position,0),@.maxcolid=
MAX(colid)
FROM syscolumns
WHERE id=OBJECT_ID(@.TableName)
IF @.Position>0 AND @.Position<@.maxcolid
UPDATE syscolumns SET
colid=CASE colid WHEN @.maxcolid THEN @.Position ELSE colid+1 END,
colorder=CASE colid WHEN @.maxcolid THEN @.Position ELSE colid+1 END
WHERE id=OBJECT_ID(@.TableName)
AND colid>=@.Position
COMMIT TRAN
END
ELSE
ROLLBACK TRAN
GO
--Close the System table write ability
EXEC sp_configure 'allow updates',1
RECONFIGURE WITH OVERRIDE
--
After that run command:
EXECUTE SP_ADDCOLUMN 'B','column_b NVARCHAR(20) NULL',5
Here B is your table name, 5 is your new column position,column_b is your
column name
I have test it, it is ok.
Grant
"Agnes" wrote:

> I got ten database , and want to add one column in serveral tables.
> However, I need to add that column in the fifth column .
> Any easy script I can use 'thanks a lot
>
>|||Grant wrote:
> Use this procedure :
[snip]

> --
> After that run command:
> EXECUTE SP_ADDCOLUMN 'B','column_b NVARCHAR(20) NULL',5
> Here B is your table name, 5 is your new column position,column_b is your
> column name
> I have test it, it is ok.
>
DO NOT do it! This does not work for several reasons. In any case,
updating system tables is the surest way to corrupt your data
permanently.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--

Add new tables to replication

Using SQL Server 2000 with sp4, I have setup a merge replication with a snapshot, currently if we do a schema change we have to:
-break replication,
-stop the subscription,
-make our modifications to the publisher,
-then create a backup of the database,
-push the backup to all of the subscribers,
-restore the database to the subscriber,
-reinitialize the subscription and push the subscription to the subscribers without pushing the contents of the initial snapshot

the reason why we do it this way is that the various servers are located hundreds of miles apart from the publisher and even with broadband connections, re-initializing the subscription always fails.

this is incredibly cumbersome and time consuming and i know there must be a better way to do this.

i need to add two tables to an existing database that is currently being replicated via merge replication, is there an easier way to make the schema changes and have them replicate to all the subscribers?

Rather than making a schema change to an existing table article in the merge publication, it seems like you are trying to add an incremental article to an existing table article. Also from what I see in the scenario, it seems like the subscriptions are set up to be NO SYNC (SP_addmergesubscription @.sync_type='none'). The NO SYNC means you are populating the subscriber server with the initial data manually via backup/restore.

In order for you to add a new table or two to the existing merge publication you may not have to break replication. Here are some steps to try:

1) Create the new tables at the publisher. These new tables must include a rowguid column (i.e. [rowguid] uniqueidentifier ROWGUIDCOL NOT NULL) because merge will use this for change tracking. This scenario will not work without rowguid columns on the new tables.

2) Populate the table

3) Add the new table as a table article in the existing merge publication. You will be warned appropriately that the full snapshot needs to be rerun.

4) Run the full snapshot agent job.

5) BCP out the data from the new table article to a file and send it to the remote subscriber server

6) Create the new table schema at the subscriber

7) BCP in the data on the subscriber server

8) Execute the merge agent. This is required to have the merge triggers built on the subscriber for change tracking. *** Do not do any DML changes on the subscriber server until the merge agent is executed at least one time.

9) Now you should be able to execute DML on the new table at both the publisher and subscriber and sync the changes.

Try this out on a test system to verify it works. I tried on my system and it accomplished what you wanted.

Tom

This posting is provided "AS IS" with no warranties, and confers no rights.

Add new tables to replication

I have setup transactional replication with no snapshot. I created a new
table on publisher side and I would want the table also to be replicated.
Here is what i did-
- drop the subscription
- create/dts table to subscriber
- create insert update stored procedures at subscriber(i have created them
during testing)
- add article to publication
-recreate subscription
Is there any other way to do it as I donot want my subscription to be
dropped each time as it has some other tables that are replicated
continuously...
Regards,
Ravi
Ravi,
to avoid dropping the subscription: as you did before...
> - create/dts table to subscriber
> - create insert update stored procedures at subscriber(i have created them
> during testing)
> - add article to publication
Then
exec sp_addsubscription @.publication = 'tTestFNames'
, @.article = 'tEmployees'
, @.subscriber = 'RSCOMPUTER'
, @.destination_db = 'testrep'
, @.sync_type = 'none'
ie add the subscription to the particular article.
HTH,
Paul Ibison, SQL Server MVP (www.replicationanswers.com)

Tuesday, March 6, 2012

add new record - weird results

I have written a generic script in asp to add records to a table. The
script works fine with one table but in the other tables it updates
the first record in the table with the values for the new record and
adds a new record with all null values?!? Here is the script:

adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
'INSERT******************************************* ******************
'Open connection to sub-table
if action = "insert" then
cnnFormToDB.Open "SELECT top 1 * FROM " &subtable,
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta",
adOpenKeyset,adLockOptimistic
cnnFormToDB.AddNew
else
cnnFormToDB.Open "SELECT top 1 * FROM " & subtable & " WHERE ID = " &
ID, "DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
End If

if not cnnFormToDB.eof then
cnnFormToDB.MoveFirst
end if
'DELETE******************************************* ********************
if action = "delete" then
cnnFormToDB.Delete
cnnFormToDB.Close
else
'Build 2nd SQL String
For i=0 To Ubound(aFields)
cnnFormToDB(aFields(i)) = aValues(i)
Next

'Insert record into sub-table
cnnFormToDB.Update

The even weirder thing is I know that values in aFields and aValues
are OK because this test script I wrote for one of the tables works
just fine:

adOpenKeyset=1
adLockOptimistic=3
Set cnnFormToDB = Server.CreateObject("ADODB.Recordset")
cnnFormToDB.Open "SELECT top 1 * FROM FlightsDirect",
"DSN=Barrheadsql;UID=barrhead;PWD=ty93eta", adOpenKeyset,
adLockOptimistic
cnnFormToDB.AddNew

cnnFormToDB("fkCity") = 198
cnnFormToDB("fkDepartureAirport") = 159
cnnFormToDB("ValidFrom") = "17/09/2003"
cnnFormToDB("ValidTo") = "15/10/2003"
cnnFormToDB("fkType") = 1
cnnFormToDB("ReturnFlight") = 1
cnnFormToDB("fkReturnAirport") = 184
cnnFormToDB("Price") = yyyyyy
cnnFormToDB("fkATOL") = 5346

cnnFormToDB.Update

Any suggestions appreciated

Thanks

AlisonButtercup (alison_clark20@.hotmail.com) writes:
> I have written a generic script in asp to add records to a table. The
> script works fine with one table but in the other tables it updates
> the first record in the table with the values for the new record and
> adds a new record with all null values?!? Here is the script:

I cannot really say what is going on. The problem with ADO is that
while it tries to hides to the SQL from you, it does actually makes
you more confused, because you don't know what is going on under the
covers.

You can use the Profiler to see what ADO submits to SQL Server.

However, rather than relying on ADO doing things right by chance, I
would encourage you to use stored procedures instead. Then you don't
use these .AddNew or .Update methods.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Add mode problem on field setup with Foreign Key

Hello,

I have two tables (one-to-many relation) related thru a common key. I have setup the Relation using the Database Designer. The Form is populated using Drag-and-drop which automatically adds the BindingSource, TableAdapter, and Binding Navigator. The Relation works great in the change mode, and life is good.

In the Add mode the field setup with the Foreign Key is not automatically populated as it is in Change mode. The field can be updated manually, but that defeats the purpose of setting up the Relation.

Any ideas how to fix this?

I know this is difficult to visualize without seeing it.

Thank you for any ideas,

Tom

The detail grid (or some other graphic control) should be linked to the relation itself not the underlying detail database table. Master's PK should be FK in Detail.

See here on MSDN2:

One BindingSource is bound to the parent Customers table in the data set. This data is displayed in the master DataGridView control. The other BindingSource is bound to the first data connector. The DataMember property of the second BindingSource is set to the DataRelation name. This causes the associated detail DataGridView control to display the rows of the child Orders table that correspond to the current row in the master DataGridView control.

add leading zero to date column?

Hello All,

None of the solutions I have found in the archives seem to solve my
problem. I have a date column in my tables (stored as a char(10))
which I would like to append a leading zero to for those dates that
start with 9 or lower.

Any ideas?

Thanks,

MikeMyk wrote:
> Hello All,
> None of the solutions I have found in the archives seem to solve my
> problem. I have a date column in my tables (stored as a char(10))

No, you have a char() column in your table that you store a string in
that is supposed to represent a date. It may or may not.

> which I would like to append a leading zero to for those dates that
> start with 9 or lower.

I'd recommend you actually make the column a date data type since that's
what it is for, but you can append like this:

CASE WHEN LEFT(YourColumn, 1) BETWEEN '1' AND '9' THEN '0' + YourColumn END

Now, there are tons of things wrong with what I just wrote in that it
assumes the first column will always be 0-9, which, given your specs
above, isn't garunteed. It assumes that there are no spaces in the first
character position. It assumes that if there is a 1 through 9 that the
length of that string plus the new '0' is still within 10 chars. All in
all, it is a crappy solution.

Make your data column type match your data and this problem goes away.

Zach

> Any ideas?
> Thanks,
> Mike|||Database Modeling Sin # 58 - Confusing Data Presentation with Data
Representation.

If you can't convert the column to its proper datatype (highly , greatly,
strongly, and "i mean it dude!" recommended),
add the following to a trigger on the table to insure that the format of the
string is consistent.

update mytable
set DateLikeCharColumn = IsNull( convert( char(10) , convert( datetime,
DateLikeCharColumn ) , 110 ) , "" )
from mytable
join inserted on mytable.keycolumn = inserted.keycolumn

(substitute 110 with what ever style you need)

"nib" <individual_news@.nibsworld.com> wrote in message
news:304ot7F2qrbinU1@.uni-berlin.de...
> Myk wrote:
>> Hello All,
>>
>> None of the solutions I have found in the archives seem to solve my
>> problem. I have a date column in my tables (stored as a char(10))
> No, you have a char() column in your table that you store a string in that
> is supposed to represent a date. It may or may not.
>> which I would like to append a leading zero to for those dates that
>> start with 9 or lower.
> I'd recommend you actually make the column a date data type since that's
> what it is for, but you can append like this:
> CASE WHEN LEFT(YourColumn, 1) BETWEEN '1' AND '9' THEN '0' + YourColumn
> END
> Now, there are tons of things wrong with what I just wrote in that it
> assumes the first column will always be 0-9, which, given your specs
> above, isn't garunteed. It assumes that there are no spaces in the first
> character position. It assumes that if there is a 1 through 9 that the
> length of that string plus the new '0' is still within 10 chars. All in
> all, it is a crappy solution.
> Make your data column type match your data and this problem goes away.
> Zach
>>
>> Any ideas?
>>
>> Thanks,
>>
>> Mike
>

Saturday, February 25, 2012

Add into 2 tables (VS 2005)

Hi, not sure if I've posted at the right area but here goes:

I'm working on a project using Visual Studio 2005 and SQL express server for the database.

Since I'm using VS 05, I use datasources to select, insert, delete, update from my db.

Question is, how do i insert, delete, update from 2 tables using datasource? is it even allowed?

If it's not allowed, are there any solutions other than using the codebehind?

Please help. Thank you.

hi galovesongs,

you can you any of these two solutions

1. on the vs.net

Making use of system.transaction

you can put two of your delete, insert, update inside a transaction

placing two statement in a transaction context

is the best way to go.

pls find a four page article on this link

http://www.devx.com/vb/Door/10608

2. on the database

you can make use of the trigger

i moved the thread here. for better opinions

add from seperate tables

Hi There
How do i add 2 summaries (ie counts) from 2 seperate tables ?
ThanksCan you give an example of what you're trying to do?
"Tango" wrote:
> Hi There
> How do i add 2 summaries (ie counts) from 2 seperate tables ?
> Thanks|||Thanks for your interest George.
table1 has a group with a count of dates summary & table 2 on same report
has a group with a count of a field (happens to be a date field as well)
id like to add a field which is the sum of both & grouped by date
todd
"George Friend" wrote:
> Can you give an example of what you're trying to do?
> "Tango" wrote:
> > Hi There
> > How do i add 2 summaries (ie counts) from 2 seperate tables ?
> > Thanks|||Sounds like:
select
dt,
sum(myValue)
from
(
select
dt,
count(x) as myValue
from
table1
UNION ALL
select
dt,
count(y)
from
table2
) as combinedTable
group by
dt
This takes the output from each and concatenates them together (basically, a
0-cost operation), then summarizes accross them.
"Tango" wrote:
> Thanks for your interest George.
> table1 has a group with a count of dates summary & table 2 on same report
> has a group with a count of a field (happens to be a date field as well)
> id like to add a field which is the sum of both & grouped by date
> todd
>
> "George Friend" wrote:
> > Can you give an example of what you're trying to do?
> >
> > "Tango" wrote:
> >
> > > Hi There
> > > How do i add 2 summaries (ie counts) from 2 seperate tables ?
> > > Thanks