how to insert a huge data from mdb to sql 2000 ?

I want to migrate my access data to sql 2000 server, but
the prolems are :
1. my access data is very huge (hundreds thousands rows),
can I use bcp or bulk insert to sql 2000 from ms access ?
2. Can we performs inserting data without recorded in
transaction log ?
thanks.Hundreds of thousands of rows is not an insignificant amount but likewise is
is not really an amount that should caue you concern.
A number of methods to move the data
1. Export to text file from Access and import using either BULK INSERT or
bcp
2. DTS
3. Access upsizing wizard.
4. Linked server to Access
All things are recorded in the log but to varying amounts. Look up RECOVERY
MODELs in BOL.
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:041c01c37058$981b8840$a501280a@.phx.gbl...
> I want to migrate my access data to sql 2000 server, but
> the prolems are :
> 1. my access data is very huge (hundreds thousands rows),
> can I use bcp or bulk insert to sql 2000 from ms access ?
> 2. Can we performs inserting data without recorded in
> transaction log ?
> thanks.|||KRESNA
For now days hundreds thousands rows i would not called very huge table.
> 2. Can we performs inserting data without recorded in
> transaction log ?
You cannot , but you set recovery mode of the database to SIMPLE.
Also ,consider create linked server to the .mdb and perform
SELECT * FROM OPENQUERY( test_Access1, 'select * from table1')
For more details please refer to BOL.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:041c01c37058$981b8840$a501280a@.phx.gbl...
> I want to migrate my access data to sql 2000 server, but
> the prolems are :
> 1. my access data is very huge (hundreds thousands rows),
> can I use bcp or bulk insert to sql 2000 from ms access ?
> 2. Can we performs inserting data without recorded in
> transaction log ?
> thanks.|||Dear friends,
I already try to use dts to import data from access to sql
2000 , performance is slow, it takes more than 5 menits,
because this is part of reporting system, I am afraid user
will complain, I try to use linkserver to retrieve data
from ms access but peformance is not good too.
My friends tell me, using dts with active X scripting is
slow, is it true ?
By the way, thans for your sugestions, I will try to put
temporary table in differents db and use simple mode
recovery, I hope performance is better.
Thanks.
>--Original Message--
>KRESNA
>For now days hundreds thousands rows i would not called
very huge table.
>> 2. Can we performs inserting data without recorded in
>> transaction log ?
>You cannot , but you set recovery mode of the database to
SIMPLE.
>Also ,consider create linked server to the .mdb and
perform
>SELECT * FROM OPENQUERY( test_Access1, 'select * from
table1')
>For more details please refer to BOL.
>
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> I want to migrate my access data to sql 2000 server, but
>> the prolems are :
>> 1. my access data is very huge (hundreds thousands
rows),
>> can I use bcp or bulk insert to sql 2000 from ms
access ?
>> 2. Can we performs inserting data without recorded in
>> transaction log ?
>> thanks.
>
>.
>|||Kresna
Just a guess.
Try remove all indexes on .mdb database.
Also perhaps you need to transfer data when all users went home.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> Dear friends,
> I already try to use dts to import data from access to sql
> 2000 , performance is slow, it takes more than 5 menits,
> because this is part of reporting system, I am afraid user
> will complain, I try to use linkserver to retrieve data
> from ms access but peformance is not good too.
> My friends tell me, using dts with active X scripting is
> slow, is it true ?
> By the way, thans for your sugestions, I will try to put
> temporary table in differents db and use simple mode
> recovery, I hope performance is better.
> Thanks.
>
>
> >--Original Message--
> >KRESNA
> >For now days hundreds thousands rows i would not called
> very huge table.
> >> 2. Can we performs inserting data without recorded in
> >> transaction log ?
> >You cannot , but you set recovery mode of the database to
> SIMPLE.
> >Also ,consider create linked server to the .mdb and
> perform
> >SELECT * FROM OPENQUERY( test_Access1, 'select * from
> table1')
> >
> >For more details please refer to BOL.
> >
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> I want to migrate my access data to sql 2000 server, but
> >> the prolems are :
> >> 1. my access data is very huge (hundreds thousands
> rows),
> >> can I use bcp or bulk insert to sql 2000 from ms
> access ?
> >>
> >> 2. Can we performs inserting data without recorded in
> >> transaction log ?
> >>
> >> thanks.
> >
> >
> >.
> >|||I have exactly near 750.000 rows, I gathered from joining
5 tables in Ms Access before insert them to sql 2000
server, I think you are right, I must delete indexes in
destination and may created them back after inserted, is
it better ? , but sorry I cannot remove indexes in Ms
Access.
>--Original Message--
>How many rows do you have exactly ?
>What is the structure of the table?
>Removing the following from the destination should see an
increase in
>performance
>1. Indexes
>2. Triggers
>Also setting the recovery model to Simple will help.
>You will need to reapply afterwards.
>
>Exporting to text file then reimporting will I dare say
be no quicker for
>you. This as has been suggested would ideally be carried
out out of hours
>
>--
>--
>Allan Mitchell (Microsoft SQL Server MVP)
>MCSE,MCDBA
>www.SQLDTS.com
>I support PASS - the definitive, global community
>for SQL Server professionals - http://www.sqlpass.org
>
>"Uri Dimant" <urid@.iscar.co.il> wrote in message
>news:ujaZJ8FcDHA.1744@.TK2MSFTNGP12.phx.gbl...
>> Kresna
>> Just a guess.
>> Try remove all indexes on .mdb database.
>> Also perhaps you need to transfer data when all users
went home.
>> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>> news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
>> > Dear friends,
>> >
>> > I already try to use dts to import data from access
to sql
>> > 2000 , performance is slow, it takes more than 5
menits,
>> > because this is part of reporting system, I am afraid
user
>> > will complain, I try to use linkserver to retrieve
data
>> > from ms access but peformance is not good too.
>> >
>> > My friends tell me, using dts with active X scripting
is
>> > slow, is it true ?
>> >
>> > By the way, thans for your sugestions, I will try to
put
>> > temporary table in differents db and use simple mode
>> > recovery, I hope performance is better.
>> >
>> > Thanks.
>> >
>> >
>> >
>> >
>> > >--Original Message--
>> > >KRESNA
>> > >For now days hundreds thousands rows i would not
called
>> > very huge table.
>> > >> 2. Can we performs inserting data without recorded
in
>> > >> transaction log ?
>> > >You cannot , but you set recovery mode of the
database to
>> > SIMPLE.
>> > >Also ,consider create linked server to the .mdb and
>> > perform
>> > >SELECT * FROM OPENQUERY( test_Access1, 'select * from
>> > table1')
>> > >
>> > >For more details please refer to BOL.
>> > >
>> > >
>> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
>> > message
>> > >news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> > >> I want to migrate my access data to sql 2000
server, but
>> > >> the prolems are :
>> > >> 1. my access data is very huge (hundreds thousands
>> > rows),
>> > >> can I use bcp or bulk insert to sql 2000 from ms
>> > access ?
>> > >>
>> > >> 2. Can we performs inserting data without recorded
in
>> > >> transaction log ?
>> > >>
>> > >> thanks.
>> > >
>> > >
>> > >.
>> > >
>>
>
>.
>|||Uri, why should I remove indexes from ms access ? , I
think it is not relevan, I agree with suggestions from
Allan and you to remove indexes in destinations (SQL 2000)
because t-sql insert will update index too.
Thanks.
>--Original Message--
>Kresna
>Just a guess.
>Try remove all indexes on .mdb database.
>Also perhaps you need to transfer data when all users
went home.
>"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
message
>news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
>> Dear friends,
>> I already try to use dts to import data from access to
sql
>> 2000 , performance is slow, it takes more than 5 menits,
>> because this is part of reporting system, I am afraid
user
>> will complain, I try to use linkserver to retrieve data
>> from ms access but peformance is not good too.
>> My friends tell me, using dts with active X scripting is
>> slow, is it true ?
>> By the way, thans for your sugestions, I will try to put
>> temporary table in differents db and use simple mode
>> recovery, I hope performance is better.
>> Thanks.
>>
>>
>> >--Original Message--
>> >KRESNA
>> >For now days hundreds thousands rows i would not called
>> very huge table.
>> >> 2. Can we performs inserting data without recorded in
>> >> transaction log ?
>> >You cannot , but you set recovery mode of the database
to
>> SIMPLE.
>> >Also ,consider create linked server to the .mdb and
>> perform
>> >SELECT * FROM OPENQUERY( test_Access1, 'select * from
>> table1')
>> >
>> >For more details please refer to BOL.
>> >
>> >
>> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
>> message
>> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
>> >> I want to migrate my access data to sql 2000 server,
but
>> >> the prolems are :
>> >> 1. my access data is very huge (hundreds thousands
>> rows),
>> >> can I use bcp or bulk insert to sql 2000 from ms
>> access ?
>> >>
>> >> 2. Can we performs inserting data without recorded in
>> >> transaction log ?
>> >>
>> >> thanks.
>> >
>> >
>> >.
>> >
>
>.
>|||OK I see now.
Indexes on the Source will help you get to the rows you want (Providing they
are well placed).
Is it a complicated SELECT ?
Have you tried importing all the tables to SQL Server and then use a view
over them to do the inserts ?
How long does it take to Export using Access your Access data to Text File ?
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:05e401c37062$8dd4df30$a301280a@.phx.gbl...
> I have exactly near 750.000 rows, I gathered from joining
> 5 tables in Ms Access before insert them to sql 2000
> server, I think you are right, I must delete indexes in
> destination and may created them back after inserted, is
> it better ? , but sorry I cannot remove indexes in Ms
> Access.
> >--Original Message--
> >How many rows do you have exactly ?
> >What is the structure of the table?
> >
> >Removing the following from the destination should see an
> increase in
> >performance
> >
> >1. Indexes
> >2. Triggers
> >
> >Also setting the recovery model to Simple will help.
> >
> >You will need to reapply afterwards.
> >
> >
> >Exporting to text file then reimporting will I dare say
> be no quicker for
> >you. This as has been suggested would ideally be carried
> out out of hours
> >
> >
> >
> >--
> >
> >--
> >Allan Mitchell (Microsoft SQL Server MVP)
> >MCSE,MCDBA
> >www.SQLDTS.com
> >I support PASS - the definitive, global community
> >for SQL Server professionals - http://www.sqlpass.org
> >
> >
> >
> >"Uri Dimant" <urid@.iscar.co.il> wrote in message
> >news:ujaZJ8FcDHA.1744@.TK2MSFTNGP12.phx.gbl...
> >> Kresna
> >> Just a guess.
> >> Try remove all indexes on .mdb database.
> >> Also perhaps you need to transfer data when all users
> went home.
> >>
> >> "kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >> news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> >> > Dear friends,
> >> >
> >> > I already try to use dts to import data from access
> to sql
> >> > 2000 , performance is slow, it takes more than 5
> menits,
> >> > because this is part of reporting system, I am afraid
> user
> >> > will complain, I try to use linkserver to retrieve
> data
> >> > from ms access but peformance is not good too.
> >> >
> >> > My friends tell me, using dts with active X scripting
> is
> >> > slow, is it true ?
> >> >
> >> > By the way, thans for your sugestions, I will try to
> put
> >> > temporary table in differents db and use simple mode
> >> > recovery, I hope performance is better.
> >> >
> >> > Thanks.
> >> >
> >> >
> >> >
> >> >
> >> > >--Original Message--
> >> > >KRESNA
> >> > >For now days hundreds thousands rows i would not
> called
> >> > very huge table.
> >> > >> 2. Can we performs inserting data without recorded
> in
> >> > >> transaction log ?
> >> > >You cannot , but you set recovery mode of the
> database to
> >> > SIMPLE.
> >> > >Also ,consider create linked server to the .mdb and
> >> > perform
> >> > >SELECT * FROM OPENQUERY( test_Access1, 'select * from
> >> > table1')
> >> > >
> >> > >For more details please refer to BOL.
> >> > >
> >> > >
> >> > >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> >> > message
> >> > >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> > >> I want to migrate my access data to sql 2000
> server, but
> >> > >> the prolems are :
> >> > >> 1. my access data is very huge (hundreds thousands
> >> > rows),
> >> > >> can I use bcp or bulk insert to sql 2000 from ms
> >> > access ?
> >> > >>
> >> > >> 2. Can we performs inserting data without recorded
> in
> >> > >> transaction log ?
> >> > >>
> >> > >> thanks.
> >> > >
> >> > >
> >> > >.
> >> > >
> >>
> >>
> >
> >
> >.
> >|||ok
I did not think you have already database on SQL Server. I thought you are
plane phase yet.
So , I agree you need to remove all indexes and triggres on SQL Server
database.
"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in message
news:04da01c37063$5f92df40$a401280a@.phx.gbl...
> Uri, why should I remove indexes from ms access ? , I
> think it is not relevan, I agree with suggestions from
> Allan and you to remove indexes in destinations (SQL 2000)
> because t-sql insert will update index too.
> Thanks.
> >--Original Message--
> >Kresna
> >Just a guess.
> >Try remove all indexes on .mdb database.
> >Also perhaps you need to transfer data when all users
> went home.
> >
> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> message
> >news:049001c3705e$1e8d4f80$a401280a@.phx.gbl...
> >> Dear friends,
> >>
> >> I already try to use dts to import data from access to
> sql
> >> 2000 , performance is slow, it takes more than 5 menits,
> >> because this is part of reporting system, I am afraid
> user
> >> will complain, I try to use linkserver to retrieve data
> >> from ms access but peformance is not good too.
> >>
> >> My friends tell me, using dts with active X scripting is
> >> slow, is it true ?
> >>
> >> By the way, thans for your sugestions, I will try to put
> >> temporary table in differents db and use simple mode
> >> recovery, I hope performance is better.
> >>
> >> Thanks.
> >>
> >>
> >>
> >>
> >> >--Original Message--
> >> >KRESNA
> >> >For now days hundreds thousands rows i would not called
> >> very huge table.
> >> >> 2. Can we performs inserting data without recorded in
> >> >> transaction log ?
> >> >You cannot , but you set recovery mode of the database
> to
> >> SIMPLE.
> >> >Also ,consider create linked server to the .mdb and
> >> perform
> >> >SELECT * FROM OPENQUERY( test_Access1, 'select * from
> >> table1')
> >> >
> >> >For more details please refer to BOL.
> >> >
> >> >
> >> >"kresna rudy kurniawan" <kresnark@.yahoo.com> wrote in
> >> message
> >> >news:041c01c37058$981b8840$a501280a@.phx.gbl...
> >> >> I want to migrate my access data to sql 2000 server,
> but
> >> >> the prolems are :
> >> >> 1. my access data is very huge (hundreds thousands
> >> rows),
> >> >> can I use bcp or bulk insert to sql 2000 from ms
> >> access ?
> >> >>
> >> >> 2. Can we performs inserting data without recorded in
> >> >> transaction log ?
> >> >>
> >> >> thanks.
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >

How to insert a german ole objet (bmg-Image) to a RS Report

Hi all,
how can i get an image from a db (blob) field that was inseret into the DB
with Access (OLE-Object) into a Report?!
On Internet (Microsoft KB) i found the following Source
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
But on my side this source does not work. I have researched that there are
different length on the ole header. How do i get the ole header length for an
german ole bmp object?!
Thanks
Danieli have found a solution
=System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!Picture.Value),97))
"Daniel Doerfel" wrote:
> Hi all,
> how can i get an image from a db (blob) field that was inseret into the DB
> with Access (OLE-Object) into a Report?!
> On Internet (Microsoft KB) i found the following Source
> =System.Convert.FromBase64String(Mid(System.Convert.ToBase64String(Fields!VolvoProductExtLogo.Value),105))
> But on my side this source does not work. I have researched that there are
> different length on the ole header. How do i get the ole header length for an
> german ole bmp object?!
> Thanks
> Daniel
>sql

How to inquire on column headings?

Hello!
I'd like to be able to find out, programmatically, what field names are there in a particular table. I use JScript and an MS Access database.
Also, if possible, if there is a particular table in the database.
And to do it so that no error message is generated.
Is there a way in SQL? I spent considerable time looking for an SQL syntax for that query, without any success.There is a MS Access forum on this site. The below should help you out:

http://www.dbforums.com/showthread.php?threadid=755288&highlight=column+names

Originally posted by masha
Hello!
I'd like to be able to find out, programmatically, what field names are there in a particular table. I use JScript and an MS Access database.
Also, if possible, if there is a particular table in the database.
And to do it so that no error message is generated.
Is there a way in SQL? I spent considerable time looking for an SQL syntax for that query, without any success.|||Originally posted by dmmac
There is a MS Access forum on this site. The below should help you out:

http://www.dbforums.com/showthread.php?threadid=755288&highlight=column+names

Thank you very much! That solves it indeed.
Sorry for posting in this forum. I did it because I hoped that it were possible using pure SQL.
Thanks again!sql

How to improve this SQL statement?

I have a query in MS Access that will select the only record from a table DECISION, given three input parameters: pMonth, pLoansize and pLTV. The record should have the maximal possible value for MONTH, LOAN_SIZE and PERCENTAGE, in this order.

My query is working, but a bit slow since it seems joining four tables. I'd appreicate for any idea about how to improve this query, which is as
follow. Thanks.

------------------------
SELECT distinct C.DECISION_ID, C.DECISION_NAME_ID, C.MONTH, C.LOAN_SIZE, C.percentage, C.ADMIN_FEE_HL_ID, C.PNTY_ID, C.OFFER_RATE_ID
FROM DECISION AS A, DECISION AS B, DECISION AS C, DECISION AS D
WHERE
(D.MONTH = (SELECT max(MONTH) FROM DECISION D WHERE pLoansize>=D.LOAN_SIZEand pMONTH >= D.MONTH AND pLTV >= D.percentage))
AND
(B.LOAN_SIZE= (SELECT max(loan_size) FROM DECISION B WHERE B.MONTH = D.MONTH AND pLoansize>=B.LOAN_SIZEAND pLTV >= B.percentage))
AND
(A.percentage = (SELECT max(percentage) FROM DECISION A WHERE A.MONTH = D.MONTH and A.loan_size=B.LOAN_SIZE
AND pLTV >= A.percentage))
AND C.MONTH = D.MONTH
AND C.LOAN_SIZE= B.loan_size
AND C.percentage = A.percentagetotal shot in the dark, please let me know if this works:SELECT DECISION_ID
, DECISION_NAME_ID
, MONTH
, LOAN_SIZE
, percentage
, ADMIN_FEE_HL_ID
, PNTY_ID
, OFFER_RATE_ID
FROM DECISION AS A
WHERE MONTH = (
SELECT max(MONTH)
FROM DECISION
)
AND LOAN_SIZE = (
SELECT max(loan_size)
FROM DECISION
WHERE MONTH = A.MONTH
)
AND percentage = (
SELECT max(percentage)
FROM DECISION
WHERE MONTH = A.MONTH
and loan_size = a.LOAN_SIZE
)
rudy
http://r937.com/|||Hi r937,

Thanks for the try. But I am afraid that it is not working. Your SQL may end up with no record selected. For example, a simplified DECISION table:

Month, Loan_size, Percentage
8, 100, 70
4, 200, 50
6, 150, 80|||did you try it?

i tried it on your sample of 3 and it gave the row with

8, 100, 70

if you just wanted the max of all three colulmns, that's a totally different query (and a lot simpler, too)

i thought you wanted row integrity

for example, suppose there are 80 rows in the table, of which 20 belong to the highest month

then out of those 20 rows which have the highest month, 6 of those rows have the highest loan_size for that month

then out of those 6 rows which have the highest loan_size for the highest month, one of them has the highest percentage

my query will always return a row, as long as there is at least one row in the table

perhaps you did not explain your problem correctly?

i tried to see what your query was attempting to do, but it's seriously messed up

or maybe i totally misunderstood you

rudy|||Hi Rudy,

Sorry it was my mistake. The query is working now since I missed
out something. Thank you very much.

How to import relations with DTSWizard?

Hello,

I have installed the recent version of MSSQLExpress 2005 with the DTSWizard. I was able to import my Access Database successfully into MSSQL. However the keys and relations are not copied over. Is there a way to do that? or Do I have to set everything manually?

Thanks
KaveCheck this KBA http://support.microsoft.com/kb/237980 about upsizing the access database and that will talk about your need. Review http://support.microsoft.com/kb/285829 too.|||Many thanks that helped me out.

Regards
Kave

How to import MS Access database into SQL Server 2005

Hello,

I have a database MS access: test.mdb.

Is it possible to attach to SQL SERVER 2005 (or 2000 than to 2005), or any other solution ?

I need to transfer all the application:

structure and data.

How to do it?

Microsoft has a tool called the Access Upsizing Wizard. There are different versions for different versions of Access.

http://support.microsoft.com/kb/325017

|||

I googled a bit, no screentshot I can find

Anyway, In SSMS -> DB -> right-click, choose "Task" -> "Import"

pick "Microsoft Access" from the Data Source dropdown, and it asks for "File Name", "User name:", "Password"

You might have to do it table by table though

As above said, found this through SQL 2005 Books Online (Upsizing Wizard)

http://www.sqljunkies.com/howto/bd909d1e-3ea1-4323-889e-5e3a5415b977.scuk

|||

Thank to everybody,

http://www.microsoft.com/sql/solutions/migration/access/default.mspx

here the migration tool.

How To Import Access to SQLServer with Parameter from SQLServer, Help Pls!

Hello Expert!

I have 2 Database – Access & SQLServer(ver 7)

I need to Import Data TblShift from Access to SQLServer – using DTS I’ve done this successfully!

Now I want to use parameter so I only importing record within range (e.g. ShiftDate BETWEEN 05-24-2006 AND 06-23-2006)

In SQLServer, I have created table to store the date range as following:

TblParameter
DateFrom: 04/24/2006
DateTo: 05/23/2006

How do I use the date range from TblParameter(SQLServer) to import record from TblShift(Access) using DTS?

Is this possible or any better solution for this?

TIA

Regards,

have you tried using the ole db source component? If you use a parameterized query, you can map variables to parameters to specify the values for individual parameters in the SQL statements.|||

Hi Douglas,

I newbie to sqlserver dts,

I don't know how to do as u have suggested

Can u explain step by step?

Or can u guide me to the place where I can see some sample

TIA

Regards

|||

Try doing this:

http://blogs.conchango.com/jamiethomson/archive/2005/12/09/2480.aspx

-Jamie

How to Import access table into SQL express 2005 database?

Can anyone help me import a ms access (97) table into a sql 2005 express database? The sql database already contains other tables but I would like to add some of my older access tables to this database without having to re-enter all the data. Please help! Thanks!SQL Server Management Studio for SQL Server 2005 (or Enterprise Manager for 2000) has a lovely Data Transformation Service (DTS) package builder.

If you set up a table in your SQL database you can just map the data from access straight to the data source.

DTS package -> look it up :)|||Do you know if this DTS package is present with management studio express? Because I am running sql 2005 express? I'm not having much luck finding it, sorry I'm pretty new with databases.|||maybe this will help: http://www.microsoft.com/sql/solutions/migration/access/default.mspx|||Thanks ! I will give that a try. :)

How to implement a DropDownList w/o a Table

I am using Visual Studio 2005 & SQL Server. How do i implement a DDL for users to select which value to input. like i can with Access. i do not need a table i think. if not the table would have only ID & Value.?

If you want a static set of items in the dropdown list you can define them in the property panel under the property Items you will find a collection, click the "..." and add items with value and text to appear in the dropdown list.

Best regards,
Per Salmi

|||

If you're trying to get the values from the database, you can do something like this:

Sub Page_Load(ByVal senderAs Object,ByVal eAs EventArgs)
If Not Page.IsPostBackThen GetData()End If
 End Sub
 Sub GetData()
Dim connStringAs String
 Dim conAs SqlConnection
Try connString = System.Web.Configuration.WebConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString
 con =New SqlConnection(connString)
Dim cmdAs SqlCommand =New SqlCommand()
Dim readerAs SqlDataReader
 cmd.Connection = con
 cmd.CommandText ="EXECUTE dbo.GetDepartmentCode" reader = cmd.ExecuteReaderIf reader.HasRowsThen
 While (reader.Read)
Dim newItemAs New ListItem
 newItem.Text = (reader.Item("DeptCode")).ToString
 newItem.Value = (reader.Item("DeptCode")).ToString
 deptCodeDDL.Items.Add(newItem)
End While
 End If reader.Close()Catch exAs Exception
 Response.Write(ex)
Finally con.Close() con.Dispose()End Try
 Catch exAs ApplicationException
 Response.Write("Could not load the database")
End Try
 End Sub

The ASP.Net side would like this:

<asp:DropDownList ID="deptCodeDDL" runat="server"></asp:DropDownList>

A few things to note though.

In the code I've posted, I use a stored procedure (sproc) to retrieve the data from the database. If you don't what sprocs are, you can read about themhere. Using sprocs is generally considered a better way of accessing the db.

Also, you will of course, have to replace DeptCode with the column name from where you will be getting the values.

Finally, I use a ConnectionString, which is being retrieved from the Web.config file. You should have that in you application folder.

If you don't, given below is a sample:

<!--
 Note: As an alternative to hand editing this file you can use the
 web admin tool to configure settings for your application. Use
 the Website->Asp.Net Configuration option in Visual Studio.
 A full list of settings and comments can be found in
 machine.config.comments usually located in
 \Windows\Microsoft.Net\Framework\v2.x\Config
--><configuration>
<appSettings/>
<connectionStrings>
<add name="ConnectionString1" connectionString="Data Source=SERVERNAME\SQLSERVER;Initial Catalog=DBNAME;Integrated Security=True" providerName="System.Data.SqlClient"/>
</connectionStrings>
<system.web>
<!--
 Set compilation debug="true" to insert debugging
 symbols into the compiled page. Because this
 affects performance, set this value to true only
 during development.
 --><compilation debug="false"
</compilation>
<!--
 The <authentication> section enables configuration
 of the security authentication mode used by
 ASP.NET to identify an incoming user.
 --><authentication mode="Windows"/>
<!--
 The <customErrors> section enables configuration
 of what to do if/when an unhandled error occurs
 during the execution of a request. Specifically,
 it enables developers to configure html error pages
 to be displayed in place of a error stack trace.
 <customErrors mode="RemoteOnly" defaultRedirect="GenericErrorPage.htm">
 <error statusCode="403" redirect="NoAccess.htm" />
 <error statusCode="404" redirect="FileNotFound.htm" />
 </customErrors>
 --></system.web>
</configuration>

You will have to replace SERVERNAME with you server name and DBNAME with your db name.

How to impersonate known user to AS 2000

I am developing an application in C# 2.0 that will access a AS 2000 cube. I want the application to access the cube with a known account. I do not want to use XMLA.

My ConnectionString looks like this:

Data Source=MyServer; Catalog=GL; User ID=ReportUser; Password=mypass

My code looks like this:

AdomdConnection Connection = new AdomdConnection(ConnectionString);
Connection.Open();

ReportUser is a local account on the server. The cube has a single role where ReportUser is a member.

When trying to open the connection I get an exception that the connection was actively refused by the server.

Help?

Thanks

Sorry - you can't do that unless you set up http authentication and use basic auth for your virtual directory. (please use https)

And that takes Enterprise edition in 2000 (but standard in 2005!).

How to i hide my database schema from...

everybody except 1 login which is non-sa in sql server 2000.
Even sa should not be able to access the schema.
Also are there anyways encrypt data in sql server 2000w
Anyone who has access to the database has access to examine, but not
necessarily modify, the schema.
If you Google for encryption and SQL Server you should find a couple of
hits. I assume that you want the data encrypted in the database.
If you mean the files themselves, an alternative is to use NTFS encryption
on the files.
Russell Fields
"w" <wilcorning@.hotmail.com> wrote in message
news:6a24cdf7.0402261134.be0b6ca@.posting.google.com...
> everybody except 1 login which is non-sa in sql server 2000.
> Even sa should not be able to access the schema.
> Also are there anyways encrypt data in sql server 2000

How to i hide my database schema from...

everybody except 1 login which is non-sa in sql server 2000.
Even sa should not be able to access the schema.
Also are there anyways encrypt data in sql server 2000w
Anyone who has access to the database has access to examine, but not
necessarily modify, the schema.
If you Google for encryption and SQL Server you should find a couple of
hits. I assume that you want the data encrypted in the database.
If you mean the files themselves, an alternative is to use NTFS encryption
on the files.
Russell Fields
"w" <wilcorning@.hotmail.com> wrote in message
news:6a24cdf7.0402261134.be0b6ca@.posting.google.com...
> everybody except 1 login which is non-sa in sql server 2000.
> Even sa should not be able to access the schema.
> Also are there anyways encrypt data in sql server 2000

How to hide unauthorized databases with SQL 2005?

Hi,
I have configure permission for userA and he can access only one database.
When user estabilish the connection via management studio, though he cannot
access other databases, he can see them. Is it possible to hide other
databases for userA?
Appreciate all your reply.
ShaneVIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>|||Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one databas
e while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:%2
300soIBaGHA.3304@.TK2MSFTNGP04.phx.gbl...
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>|||SL
Well, this unwanted user must be connected via SSMS (am I right?) and if you
have not added him/her to the database , he/she will see the database's nam
e but cannot access to
"SL Coder" <sl_coder@.hotmail.com> wrote in message news:eV8jH9BaGHA.4116@.TK2
MSFTNGP05.phx.gbl...
Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one databas
e while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message news:%2
300soIBaGHA.3304@.TK2MSFTNGP04.phx.gbl...
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>|||After VIEW ANY DATABASE is denied, only master, tempdb, and databases that
the login owns are visible. Other databases that the user can access are
not enumerated but can still be accessed directly by setting the database
context (e.g. USE). Unfortunately, SSMS Object Explorer functionality is
limited to visible databases.
The reason for this behavior is that it is necessary to open each database
on the server to determine whether or not a non-privileged login has
database access. This caused performance issues on servers with a lot
(100's) of databases.
If this feature is important to you, make a suggestion (or vote on the
importance if already submitted) at the product feedback center:
http://lab.msdn.microsoft.com/produ...ck/default.aspx
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:eV8jH9BaGHA.4116@.TK2MSFTNGP05.phx.gbl...
Thanks for the reply Dan. But the problem is, this statement applies for all
databases that is not what I want. I need to allow userA to see one database
while denying other databases. Is it possible. Have I missed anything?
Shane
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:%2300soIBaGHA.3304@.TK2MSFTNGP04.phx.gbl...
VIEW ANY DATABASE is granted to public by default. If you want to remove
this permission from userA:
USE master
DENY VIEW ANY DATABASE TO userA
Although the user still has VIEW ANY DATABASE via public role membership,
the DENY takes precedence.
You could also REVOKE VIEW ANY DATABASE from public and then selectively
grant that permission to users as you see fit.
Hope this helps.
Dan Guzman
SQL Server MVP
"SL Coder" <sl_coder@.hotmail.com> wrote in message
news:e50gEfAaGHA.3704@.TK2MSFTNGP03.phx.gbl...
> Hi,
> I have configure permission for userA and he can access only one
database.
> When user estabilish the connection via management studio, though he
> cannot access other databases, he can see them. Is it possible to hide
> other databases for userA?
> Appreciate all your reply.
> Shane
>

how to hide system tables?

I just upsized from access to a new dbase and now I have all of these system
tables etc. I believe I was told that I could hide those some how?
I'm using Enterprise Manager
ThanksIn enterprise manager, right-click the server name and choose edit sql
server registration properties, then you'll see an option that reads show
system databases and system objects.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Robert Blackwell" <robbie@.NOspamwowcentral.com> wrote in message
news:O3FGz9tUDHA.392@.TK2MSFTNGP11.phx.gbl...
> I just upsized from access to a new dbase and now I have all of these
system
> tables etc. I believe I was told that I could hide those some how?
> I'm using Enterprise Manager
> Thanks
>|||http://www.aspfaq.com/2010
"Robert Blackwell" <robbie@.NOspamwowcentral.com> wrote in message
news:O3FGz9tUDHA.392@.TK2MSFTNGP11.phx.gbl...
> I just upsized from access to a new dbase and now I have all of these
system
> tables etc. I believe I was told that I could hide those some how?
> I'm using Enterprise Manager
> Thanks
>|||Thanks guys :o)