RO tables in SQLServer from Access

Hi all
I used the import utility to move some access tables to a SQL Svr db, and
while it all seemed to go AOK, whatever I do the resultant SQL tables appear
to be read-only. There are other tables in the SQL db that will read & write
OK.
Can any one help and tell me how to make the SQL tables writable'
thanks
JimboJim
Does the user who operates with a table have an apropriate permissions?
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:e0o7na$jib$1$8302bc10@.news.demon.co.uk...
> Hi all
> I used the import utility to move some access tables to a SQL Svr db, and
> while it all seemed to go AOK, whatever I do the resultant SQL tables
> appear
> to be read-only. There are other tables in the SQL db that will read &
> write
> OK.
> Can any one help and tell me how to make the SQL tables writable'
> thanks
> Jimbo
>
>|||Each table must have a unique column(s) (e.g. primary key) in order to be
updatable by database APIs. The SQL generated by the API includes the key
columns in the WHERE clause to ensure only one row is updated/deleted. This
is one reason why all tables should have a primary key.
IIRC, Access prompts you to specify the unique column(s) when a SQL Server
table is linked. This defaults to the primary key, if one exists.
Hope this helps.
Dan Guzman
SQL Server MVP
"Jim McDonald" <jim@.missile.demon.co.uk> wrote in message
news:e0o7na$jib$1$8302bc10@.news.demon.co.uk...
> Hi all
> I used the import utility to move some access tables to a SQL Svr db, and
> while it all seemed to go AOK, whatever I do the resultant SQL tables
> appear
> to be read-only. There are other tables in the SQL db that will read &
> write
> OK.
> Can any one help and tell me how to make the SQL tables writable'
> thanks
> Jimbo
>
>

RMO Programming in VB.net

Hi,

Currently I am using the BCP functionality to import/export tables between different SQL Server 2005 databases. The process is working ok, but it creates alot of manual work, when 70 or more tables need to be transferred, every week. I am new to RMO development and am interested if the following may be possible.

- I would like to build an interface in VB.net allowing users to select a source database, and select the tables that need to be transferred from the Source DB, to the Destination DB.

-Put in place RMO funtionality to replicate the source database tables into flat files.

-Within the Vb.net app transfer the created flat files into the destination database.

I would greatly appreciate any advice on this, or if there may be a better approach at replacing the BCP functionality.

Thanks,

Since the number of tables needed will vary from user to user, then it might make sense to stick with what you have or use DTS or SSIS in some way. YOu can get creative by setting up Snapshot Replication and use your VB app to read the BCP files from the snapshot folder, but that could get tricky.

|||

Thanks Greg.

If the tables were to remain consistant would an RMO approach be feasible?

|||

Yes, if the tables were to remain constant then you could create a snapshot publication (assuming you don't need up-to-the-minute changes) and use RMO to create or sync your subscriptions. More detail/information regarding your app and business requirements would be helpful, but yes, this should work for you. You can read more about RMO in books online, just search for RMO. You can also read up on the different types of replication and what snapshot, transactional and merge replication have to offer.

|||

hi,

i saw this post & i also want to implemnt an vb.net application to provide replication.

so incase u have made the application plz guide me to make the same for my organisation.

i m also new to sql RMO

i actually made it with sql SMO

sql

RMO Programming in VB.net

Hi,

Currently I am using the BCP functionality to import/export tables between different SQL Server 2005 databases. The process is working ok, but it creates alot of manual work, when 70 or more tables need to be transferred, every week. I am new to RMO development and am interested if the following may be possible.

- I would like to build an interface in VB.net allowing users to select a source database, and select the tables that need to be transferred from the Source DB, to the Destination DB.

-Put in place RMO funtionality to replicate the source database tables into flat files.

-Within the Vb.net app transfer the created flat files into the destination database.

I would greatly appreciate any advice on this, or if there may be a better approach at replacing the BCP functionality.

Thanks,

Since the number of tables needed will vary from user to user, then it might make sense to stick with what you have or use DTS or SSIS in some way. YOu can get creative by setting up Snapshot Replication and use your VB app to read the BCP files from the snapshot folder, but that could get tricky.

|||

Thanks Greg.

If the tables were to remain consistant would an RMO approach be feasible?

|||

Yes, if the tables were to remain constant then you could create a snapshot publication (assuming you don't need up-to-the-minute changes) and use RMO to create or sync your subscriptions. More detail/information regarding your app and business requirements would be helpful, but yes, this should work for you. You can read more about RMO in books online, just search for RMO. You can also read up on the different types of replication and what snapshot, transactional and merge replication have to offer.

|||

hi,

i saw this post & i also want to implemnt an vb.net application to provide replication.

so incase u have made the application plz guide me to make the same for my organisation.

i m also new to sql RMO

i actually made it with sql SMO

Right-Click Table -> Import data GONE?

Hi all
Apologies if this should be a tools question!

In the 'good old days' you could right-click a table, select Import data and have the target pre-selected....not only does that no longer exist for SQL 2005 you now always have to explicitly select the source and target....

Is there a work-around or is this a security cleanup gone mad and is thus a 'live with it :( ?Right-click on the database-->Tasks-->Import Data...

-Jamie|||Hi Jamie
You are of course correct, however this does mean that you have to explicitly select the source and target. My point is that beforehand you could implicitly select the target by right-clicking the table and select import data thereafter typically performing a text file import.

Surely we should not have to open the BI Studio or have to run an import at the database not table level?|||

Charl wrote:

Hi Jamie
You are of course correct, however this does mean that you have to explicitly select the source and target. My point is that beforehand you could implicitly select the target by right-clicking the table and select import data thereafter typically performing a text file import.

Surely we should not have to open the BI Studio or have to run an import at the database not table level?

It does seem an oversight I agree. However, is it really that much of a hardship to have to explicitly select the table? In my opinion its not - its only my opinion however!

I dare say MS will have had some UE type people on this (if they didn't - they should have done) and so will be able to explain the decision.

-Jamie|||Hi Jamie
It's not so much that it is a hardship to explicitly select the table rather that you have to jump through more hoops.

One of the beauties of the right-click-->import data on a table was that you were left with a visual clue as to the table to be imported into. Now you first select the target, set the text qualifier (and silently lose information if you don't - my .csv file imported successufully but lost everything after the first comma),set the source column types (and if these are too short if you have done the basic suggest types it will error with truncation warnings, but not where you expect?!) then select the target Server & DB.

Finally the summary screen shows your "quick import" - you now have to expand the window and the source / destination columns to see what you are trying to do - not something you want to go through again if it fails!

All I am doing here is to populate a set of static tables, nothing fancy required as these are simple .csv files that will be imported infrequently.

Thanks for your replies

ATB

Charl

reverse only the text parts of the field

i have to import addresses from an text file.
the text is reversed but numbers are not reversed
"abcd 12 def" comes as "dcba 12 fed"
if i use the reverse function it reveses also the number
and the it becomes "abcd 21 def"
how i can reverse only the text parts of the field?
thanksChop the parts of the string, cehck every single string for ISNUMERIC and
REVERSE it if it evaluates to false, then put the string together in a new
string.
--
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Sam" wrote:

> i have to import addresses from an text file.
> the text is reversed but numbers are not reversed
> "abcd 12 def" comes as "dcba 12 fed"
> if i use the reverse function it reveses also the number
> and the it becomes "abcd 21 def"
> how i can reverse only the text parts of the field?
> thanks
>
>|||how to Chop the parts of the string?
"Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
wrote in message news:9044AF5F-E604-4EEC-974C-A2CB06AE5756@.microsoft.com...
> Chop the parts of the string, cehck every single string for ISNUMERIC and
> REVERSE it if it evaluates to false, then put the string together in a new
> string.
> --
> HTH, Jens Suessmeyer.
> --
> http://www.sqlserver2005.de
> --
>
> "Sam" wrote:
>|||Loomk for PATINDEX() in bol
DECLARE @.S Varchar(100)
SET @.s='Some text 123 text'
SELECT PATINDEX('%[0-9]%',@.s)
SELECT LEFT(@.s, PATINDEX('%[0-9]%',@.s)-1)
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Sam" <focus10@.zahav.net.il> wrote in message
news:eYbeeAamFHA.268@.TK2MSFTNGP10.phx.gbl...
> how to Chop the parts of the string?
> "Jens S'meyer" <Jens@.[Remove_that][for contacting me]sqlserver2005.de>
> wrote in message
> news:9044AF5F-E604-4EEC-974C-A2CB06AE5756@.microsoft.com...
>|||Try this.. should handle all the cases...
/*
ddl
Create table TestRev (Id int, Name Varchar(255))
Insert into TestRev values (1, 'abc 123 def 256')
Insert into TestRev values (2, 'olleh 321 dlrow 987')
Insert into TestRev values (3, 'abc 123456789 reversethis 256')
Insert into TestRev values (4, 'sqlserver 2000 or yukon')
*/
set nocount on
declare @.str Varchar(255)
declare @.id char(15)
declare @.strRev Varchar(255)
declare @.word Varchar(255)
declare @.newWord Varchar(255)
declare @.len int
declare @.pos int
Declare Cur_Rev Cursor
For select id, name from TestRev
open Cur_Rev
Fetch next from Cur_Rev into @.id, @.str
while @.@.fetch_status = 0
begin
set @.str = @.str + ' '
set @.strRev = @.str
set @.len = LEN(@.str)
while (@.len > 1)
begin
set @.pos = PATINDEX('% %',@.str)
set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
if IsNumeric(@.word) <> 1
Begin
set @.newword = reverse(@.word)
set @.strRev = Replace(@.strRev, @.word, @.newWord)
End
set @.str = substring(@.str, @.pos+1, @.len)
set @.len = len(@.str)
if @.pos = 0
break
end
select @.strRev 'Reversed'
fetch next from Cur_Rev into @.id, @.str
set @.strRev = @.str
end
Close Cur_Rev
Deallocate Cur_Rev
go
Thanks,
Prad
"Sam" <focus10@.zahav.net.il> wrote in message
news:eIlxouZmFHA.3648@.TK2MSFTNGP10.phx.gbl...
>i have to import addresses from an text file.
> the text is reversed but numbers are not reversed
> "abcd 12 def" comes as "dcba 12 fed"
> if i use the reverse function it reveses also the number
> and the it becomes "abcd 21 def"
> how i can reverse only the text parts of the field?
> thanks
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:544016
Pradeep Kutty wrote:
> Try this.. should handle all the cases...
> /*
> ddl
> Create table TestRev (Id int, Name Varchar(255))
> Insert into TestRev values (1, 'abc 123 def 256')
> Insert into TestRev values (2, 'olleh 321 dlrow 987')
> Insert into TestRev values (3, 'abc 123456789 reversethis 256')
> Insert into TestRev values (4, 'sqlserver 2000 or yukon')
> */
>
> set nocount on
> declare @.str Varchar(255)
> declare @.id char(15)
> declare @.strRev Varchar(255)
> declare @.word Varchar(255)
> declare @.newWord Varchar(255)
> declare @.len int
> declare @.pos int
>
> Declare Cur_Rev Cursor
> For select id, name from TestRev
>
> open Cur_Rev
> Fetch next from Cur_Rev into @.id, @.str
> while @.@.fetch_status = 0
> begin
> set @.str = @.str + ' '
> set @.strRev = @.str
> set @.len = LEN(@.str)
> while (@.len > 1)
> begin
> set @.pos = PATINDEX('% %',@.str)
> set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
> if IsNumeric(@.word) <> 1
> Begin
> set @.newword = reverse(@.word)
> set @.strRev = Replace(@.strRev, @.word, @.newWord)
> End
> set @.str = substring(@.str, @.pos+1, @.len)
> set @.len = len(@.str)
> if @.pos = 0
> break
> end
> select @.strRev 'Reversed'
> fetch next from Cur_Rev into @.id, @.str
> set @.strRev = @.str
> end
> Close Cur_Rev
> Deallocate Cur_Rev
> go
>
> Thanks,
> Prad
>
Doesn't work for if the string contains a string and it's reverse:
insert into TestRev values (5,'abc cba abc cba')
and run again (I get) 'abc abc abc abc'
I cannot work out how to fix that (other than to build up StrRev one
word at a time rather than doing replaces.
Damien|||yes what you said is correct:
heres a fix for that.. this should work try...
set nocount on
declare @.str Varchar(255)
declare @.id char(15)
declare @.strRev Varchar(255)
declare @.word Varchar(255)
declare @.newWord Varchar(255)
declare @.len int
declare @.pos int
Declare Cur_Rev Cursor
For select id, name from TestRev
open Cur_Rev
Fetch next from Cur_Rev into @.id, @.str
while @.@.fetch_status = 0
begin
set @.str = @.str + ' '
set @.strRev = ''
set @.len = LEN(@.str)
while (@.len > 1)
begin
set @.pos = PATINDEX('% %',@.str)
set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
if IsNumeric(@.word) <> 1
Begin
Select @.newword = reverse(@.word)
set @.strRev = @.strRev + ' ' + @.newWord
End
if IsNumeric(@.word) = 1
set @.strRev = @.strRev + ' ' + @.word
set @.str = substring(@.str, @.pos+1, @.len)
set @.len = len(@.str)
if @.pos = 0
break
end
select ltrim(rtrim(@.strRev)) 'Reversed'
fetch next from Cur_Rev into @.id, @.str
set @.strRev = @.str
end
Close Cur_Rev
Deallocate Cur_Rev
go
Thank,
Prad
"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
> Pradeep Kutty wrote:
> Doesn't work for if the string contains a string and it's reverse:
> insert into TestRev values (5,'abc cba abc cba')
> and run again (I get) 'abc abc abc abc'
> I cannot work out how to fix that (other than to build up StrRev one
> word at a time rather than doing replaces.
> Damien
>|||If you have a value seperator (delimiter) you could use this.
e.g
-- digits table needs to be created once
select top 8000 digit=identity(int,1,1)
into digits
from sysobjects,syscolumns
go
create function dbo.xreverse(@.input varchar(8000))
returns varchar(8000)
as
begin
declare @.tb table (i int identity primary key, value sysname)
declare @.s varchar(8000)
declare @.delim char
set @.delim=space(1)
set @.input = @.delim+rtrim(ltrim(@.input))+@.delim
insert @.tb
select case when isnumeric(value)=0 then reverse(value) else value end
from (
select substring(@.input, n.digit+1,
charindex(@.delim,@.input,n.digit+1)-n.digit-1) value
from digits as n
where n.digit<len(@.input)
and substring(@.input,n.digit,1) = @.delim
)x
select @.s=isnull(@.s+@.delim,'')+value from @.tb
return @.s
end
go
select dbo.xreverse(Name)
from TestRev
-oj
"Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
> Pradeep Kutty wrote:
> Doesn't work for if the string contains a string and it's reverse:
> insert into TestRev values (5,'abc cba abc cba')
> and run again (I get) 'abc abc abc abc'
> I cannot work out how to fix that (other than to build up StrRev one
> word at a time rather than doing replaces.
> Damien
>|||in most cases it's work nice
but in cases like "cba (2000) fed" or "cba 2/3 fed" or "cba 2-3 fed"
it's wrong
sam
"Pradeep Kutty" <pradeepk@.healthasyst.com> wrote in message
news:elyhOYdmFHA.3828@.TK2MSFTNGP12.phx.gbl...
> yes what you said is correct:
> heres a fix for that.. this should work try...
>
> set nocount on
> declare @.str Varchar(255)
> declare @.id char(15)
> declare @.strRev Varchar(255)
> declare @.word Varchar(255)
> declare @.newWord Varchar(255)
> declare @.len int
> declare @.pos int
>
> Declare Cur_Rev Cursor
> For select id, name from TestRev
>
> open Cur_Rev
> Fetch next from Cur_Rev into @.id, @.str
> while @.@.fetch_status = 0
> begin
> set @.str = @.str + ' '
> set @.strRev = ''
> set @.len = LEN(@.str)
> while (@.len > 1)
> begin
> set @.pos = PATINDEX('% %',@.str)
> set @.word = ltrim(rtrim(SUBSTRING(@.str, 1, @.pos)))
> if IsNumeric(@.word) <> 1
> Begin
> Select @.newword = reverse(@.word)
> set @.strRev = @.strRev + ' ' + @.newWord
> End
> if IsNumeric(@.word) = 1
> set @.strRev = @.strRev + ' ' + @.word
> set @.str = substring(@.str, @.pos+1, @.len)
> set @.len = len(@.str)
> if @.pos = 0
> break
> end
> select ltrim(rtrim(@.strRev)) 'Reversed'
> fetch next from Cur_Rev into @.id, @.str
> set @.strRev = @.str
> end
> Close Cur_Rev
> Deallocate Cur_Rev
> go
> Thank,
> Prad
> "Damien" <Damien_The_Unbeliever@.hotmail.com> wrote in message
> news:1123253615.736504.302120@.g43g2000cwa.googlegroups.com...
>

reverse engineering MS SQL server 2k5

Hi Alan
I can certainly import a database and foreign keys in SQL 2005, for instance
if I choose the northwind database and go through the wizard making sure that
FKs are imported and I choose the option to add the tables later to a
diagram. I can then sekect the customers tables from the tables and views box
and drag it onto the diagram. Then right clicking show related tables will
bring in the orders and customercustomerdemo tables with their foreign keys.
John
"Alain R." wrote:

> Hi,
> I got a DB with more than 100 tables.
> i tried to get it structure by Visio and its reverse engineering method
> but Visio does not draw "links" among tables representing PK and FK
> connections.
> So do you know any other software which could do the job ?
> maybe a simple freeware is enough, it is just to have an overview on
> this database and its objects.
> thanks a lot,
> A.
>
I had that issue also, I installed SP3 for Visio 2003 and it started working
after that . Thanks!
Mohit K. Gupta
B.Sc. CS, Minor Japanese
MCTS: SQL Server 2005
"John Bell" wrote:
[vbcol=seagreen]
> Hi Alan
> I can certainly import a database and foreign keys in SQL 2005, for instance
> if I choose the northwind database and go through the wizard making sure that
> FKs are imported and I choose the option to add the tables later to a
> diagram. I can then sekect the customers tables from the tables and views box
> and drag it onto the diagram. Then right clicking show related tables will
> bring in the orders and customercustomerdemo tables with their foreign keys.
> John
> "Alain R." wrote:
|||Hi
I was using Microsoft Office Visio for Enterprise Architects (11.7218.8132)
SP2
John
"Mohit K. Gupta" wrote:
[vbcol=seagreen]
> I had that issue also, I installed SP3 for Visio 2003 and it started working
> after that . Thanks!
> --
> Mohit K. Gupta
> B.Sc. CS, Minor Japanese
> MCTS: SQL Server 2005
>
> "John Bell" wrote:

Returning fields that have changed since last import

I have a few tables that update everyday with fresh data.
The contents of the archive table are truncated before the import starts, the current data is then copied to the archive table and then truncated ready for the fresh data.

so I have two tables, one with current data, one with yesterdays data. I need to write queries to show if any data has changed between the two.

CREATE TABLE [TblBond] (
[issuer] [varchar] (50) NOT NULL ,
[maturity_date] [datetime] NOT NULL ,
[coupon] [numeric](30, 10) NOT NULL ,
[currency] [char] (3) NOT NULL ,
[bond_type_code] [varchar] (12) NOT NULL ,
[sec_id] [int] NOT NULL ,
[otr] [int] NOT NULL ,
[identification_str] [varchar] (60) NULL ,
[description] [varchar] (30) NULL ,
[settlement_date] [datetime] NULL ,
[buy_sell] [varchar] (4) NOT NULL ,
[trade_amount] [numeric](38, 10) NOT NULL ,
[accrued_interest] [numeric](38, 10) NOT NULL ,
[remark] [varchar] (255) NOT NULL ,
[counterparty] [varchar] (20) NOT NULL ,
[booking_entity] [varchar] (20) NOT NULL ,
[keyword] [varchar] (255) NOT NULL ,
[trans_id] [int] NOT NULL ,
[trade_id] [int] NOT NULL ,
[trade_status] [varchar] (12) NULL ,
CONSTRAINT [PK_TblBondData] PRIMARY KEY CLUSTERED
(
[trade_id]
) WITH FILLFACTOR = 90 ON [PRIMARY]
) ON [PRIMARY]
GO

Trade_id is the PK, the archive table is the same structure but called TblBond_archive

I have got a query that works, but is very lengthy and will need amending if the columns change at all (and as im dealing with traders, im sure they will add/remove things!)

query is like dis :

SELECT
TblBond.trade_id,
'Issuer' AS Field_Changed,
cast(TblBond_Archive.Issuer as varchar) AS Old_Value,
cast(TblBond.Issuer as varchar) AS New_Value
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.Issuer<>[tblbond_archive].[Issuer]

UNION

...... and goes onto next field. for another 19 columns!!

There must be a more eloquent way of coding this? anyone any ideas? im using SQL server 2000, so i do have some schema tables i can call on if needed.

Any ideas would be great as I have about 10 tables using similiar principals and tis a pain to code it all!!

RegardsThe by far easiest and fastest way for doing this would be the use of a timestamp column in your live table and an appropriate datetime in the archive table. It gets updated automatically on every update. You can get the updated records with a simple:

SELECT blablabla
FROM TblBond
INNER JOIN TblBond_Archive ON
TblBond.trade_id = TblBond_Archive.trade_id
WHERE TblBond.updated_timestamp > TblBond_Archive.updated_datetime|||Thanks Apel, in an ideal world this is what i would have done in the first place, but alas, this data is actually sourced from a rather orrible sybase system, which I can't make any DDL changes to, as all the sybase dev team where let go a few months back!!

so no go on the timestamp column! all I can do is import the tables from sybase into my sql server for the purposes of reporting.

Also that method wouldn't enable to keep an audit trail of what changes have happened to each trade. the way i described allows me to run that big mother query everytime data is imported and copy results to an audit table. Which stores the PK, fieldname, old value , new value , and the datetime the change was detected. I also tacked on 2 other union select queries to pick up new or deleted trades.

I ended up writing a wee bit of vba code (which i ran in MS access) to help me create the SQL code for the big query! worked nicely. but still think there must be a more eloquent way to code this.