Access databases using ADO.Net

Hi
I am using Visual Studio.Net 2003 and SQL 2000
Web applications run using the ASPNET user account.

I have to set up this account and grant it permissions before my Web application will have access to a SQL database.
How do I grant permissions ?.

There are two permissions in SQL Server the Database permissions in the database and the Server permissions under security in Enterprise manager. If you need more help post again. Hope this helps.

Access database change to SQL Server

I designed a database that works well in Access and I want to get it to run in SQL Server. How do I learn how and what to do to change over to SQL Server?First thing I'd recommend is to go to http://www.microsoft.com/sql and download the most recent copy of SQL Server Books Online. From there you should have enough information to plan your migration.

Access data update problem

I have developed an Access 2002 fornt end for an SQL
database. When an form is run, it opens a limited set of
data that is updatable - the sql table is updated directly
from the access table, query or form. This works fine on
our intranet. When accessed from the internet, however, I
am only able to to update data in the table; when I run
the query or the form, the keyboard beeps when I try to
update any value. I have run the analyzer on the query,
and it shows that the dataupdatable value is false (inside
our intranet, the value shows true), but I cannot figure
out how to change this.
I have simplified the query to only draw information from
one table, but cannot update the data in the query
datasheet view or on a resulting form, only on the
original data table.Access is not designed for or intended to be used as the front end for
an Internet application. I'd look at using ASP.NET or some other more
suitable technology for the Internet.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 6 Feb 2004 14:40:42 -0800, "Don" <don@.ameritest.net> wrote:

>I have developed an Access 2002 fornt end for an SQL
>database. When an form is run, it opens a limited set of
>data that is updatable - the sql table is updated directly
>from the access table, query or form. This works fine on
>our intranet. When accessed from the internet, however, I
>am only able to to update data in the table; when I run
>the query or the form, the keyboard beeps when I try to
>update any value. I have run the analyzer on the query,
>and it shows that the dataupdatable value is false (inside
>our intranet, the value shows true), but I cannot figure
>out how to change this.
>I have simplified the query to only draw information from
>one table, but cannot update the data in the query
>datasheet view or on a resulting form, only on the
>original data table.|||Hi Don,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
From the information you provided, you have an Access 2002 front end and a
SQL Server backend. I assume it is a ADP. You found every thing is fine on
intranet but when you run it in a internet environment, you found you could
only able to update data in the table but cannot update from query or form,
right?
Well, could you please provide some informaiton, which is helpful for our
analysis:
As you mentioned that you cannot change the value from the query and the
form, beside the keyboard beep, is there any message you got? You mentioned
that you could update the the record in table, you could please provide the
information of how you modified the record and could you confirm that the
modification is made on the SQL Server side? As a ADP, could provide the
information of the connection of it? You could get the information by
choose from the menu, 'File'->'Connection', then get the information of
Server Name, the authentication mode and related logins you use and the the
database you connect to? This information is much helpful to us.
I am standing by to you reply and thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

ACCESS between serveral SQL-servers

I'v set up a new SQL6.5 server. The idea is to relieve our main server.
The problem is that I can't run a SP on the main server from the new server.

Ex: *fake data*

Insert into newserver exec mainserver.database..sp

this will generate:
Msg 18484, Level 14, State 1
Unable to connect to site 'newserver' because login failed

I've managed to add the newserver in mainserver.master..sysservers and the mainserver in newserver.master..sysservers

The way I see it I've got a problem with the users...??
This is all new to me so please help me!

Thanks:
BixHi, It's me again.
I solved my problem, and it was easy...
I guess that it was so easy that you guys didn't bother *s*

If anybody else get my problem, the answere lays in "Remote Servers" under the "Server" menu.
Register a remote server and map the logins...
and everything works like a dream!

Thanks!

Access ADP - SQL server diagram conflicts!

I've run into an annoyance, if I create a new Access project from an
existing SQL database and open up diagrams created earlier with th
enterprise manager Access XP crashes. Creating new diagrams through Access
seems to work just fine. Anyone else run into this? Thanks all.
JonSounds like an Access bug. I'd ask on those newsgroups. It may be
fixed in newer versions of Access.
--Mary
On Thu, 24 Jun 2004 08:25:04 -0400, "Jon" <jonremovemewest@.msn.com>
wrote:

>I've run into an annoyance, if I create a new Access project from an
>existing SQL database and open up diagrams created earlier with th
>enterprise manager Access XP crashes. Creating new diagrams through Access
>seems to work just fine. Anyone else run into this? Thanks all.
>Jon
>

Access ADP - SQL server diagram conflicts!

I've run into an annoyance, if I create a new Access project from an
existing SQL database and open up diagrams created earlier with th
enterprise manager Access XP crashes. Creating new diagrams through Access
seems to work just fine. Anyone else run into this? Thanks all.
JonSounds like an Access bug. I'd ask on those newsgroups. It may be
fixed in newer versions of Access.
--Mary
On Thu, 24 Jun 2004 08:25:04 -0400, "Jon" <jonremovemewest@.msn.com>
wrote:
>I've run into an annoyance, if I create a new Access project from an
>existing SQL database and open up diagrams created earlier with th
>enterprise manager Access XP crashes. Creating new diagrams through Access
>seems to work just fine. Anyone else run into this? Thanks all.
>Jon
>

Access ADP - SQL server diagram conflicts!

I've run into an annoyance, if I create a new Access project from an
existing SQL database and open up diagrams created earlier with th
enterprise manager Access XP crashes. Creating new diagrams through Access
seems to work just fine. Anyone else run into this? Thanks all.
Jon
Sounds like an Access bug. I'd ask on those newsgroups. It may be
fixed in newer versions of Access.
--Mary
On Thu, 24 Jun 2004 08:25:04 -0400, "Jon" <jonremovemewest@.msn.com>
wrote:

>I've run into an annoyance, if I create a new Access project from an
>existing SQL database and open up diagrams created earlier with th
>enterprise manager Access XP crashes. Creating new diagrams through Access
>seems to work just fine. Anyone else run into this? Thanks all.
>Jon
>
sql

Access 2000 upsizing trouble

I am upgrading an Access 2000 database to SQL Server 7.0 and 2000. When I
run the upsizing wizard it works great, but when I view the tables within
SQL only the first two tables appear to have data. I deleted the SQL
database and tried again. I tried upsizing one table (has 3500 records) and
it worked.
I have many tables of varying record size but the entire database is around
6MB. I don't find any information about why it works when I do one table at
a time and doesn't work when I try to upsize all the tables. Any help is
greatly appreciated.
WB"WB" <none> wrote:
>I am upgrading an Access 2000 database to SQL Server 7.0 and 2000. When I
>run the upsizing wizard it works great, but when I view the tables within
>SQL only the first two tables appear to have data. I deleted the SQL
>database and tried again. I tried upsizing one table (has 3500 records) and
>it worked.
Dates outside the range which SQL Servers default date formats can
cause problems. For more details and other items see the Random
Thoughts on SQL Server Upsizing from Microsoft Access Tips page at my
website.
Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm|||Hi WB,
This problem is wired. I would like you to provide more information so that
I can narrow down it?
1. Do you mean that there is not any error message?
2. Does this problem occur on SQL Server 7.0 or SQL Server 2000? What is
the Service Pack of the SQL Server?
3. I would like you to try to upsize all the tables of the Access 2000
database to SQL Server separately. Does the same problem occur again?
4. Please try to transform data from Access to SQL Server using DTS. For
additional information regarding DTS, please refer to the following article
on SQL Server Books Online:
Topic:" DTS Basics".
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.|||Hi Michael,
During this upsize I did have some errors. The problem (errors) weren't
specifically with the tables that didn't upsize the data.
I have experienced this with both versions.
Yes, after I posted the message I went back and upsized each table, fixed
the errors and everything upsized correctly.
I will look into the DTS.
Thanks for your response and interest.
I am unsure as to why the data isn't converting on tables that don't have
errors?
WB
"Michael Shao [MSFT]" <v-yshao@.online.microsoft.com> wrote in message
news:HN61VeSfDHA.1544@.cpmsftngxa06.phx.gbl...
> Hi WB,
> This problem is wired. I would like you to provide more information so
that
> I can narrow down it?
> 1. Do you mean that there is not any error message?
> 2. Does this problem occur on SQL Server 7.0 or SQL Server 2000? What is
> the Service Pack of the SQL Server?
> 3. I would like you to try to upsize all the tables of the Access 2000
> database to SQL Server separately. Does the same problem occur again?
> 4. Please try to transform data from Access to SQL Server using DTS. For
> additional information regarding DTS, please refer to the following
article
> on SQL Server Books Online:
> Topic:" DTS Basics".
> Thanks for using MSDN newsgroup.
> Regards,
> Michael Shao
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
>|||Hi WB,
Thanks for your feedback. I glad to here that everything upsized correctly
now. Based on the information you provided, I am also unsure why the data
hasn't converted. To help me find out why, I would like you to provide more
information.
1. Do you mean the same problem occurs on both versions of SQL Server? If
so, what is the version of your Service Pack on both SQL Server (SQL Server
7.0 or SQL Server 2000)?
2. As I understand, the all structures of the tables have been upsized.
Only two of the tables have data and the other tables do not have any data.
If I have misunderstood, please feel free to let me know.
3. You wrote: "During this upsize I did have some errors. The problem
(errors) weren't specifically with the tables that didn't upsize the data."
Why do you think these errors are not related to this issue? Could you
please provide me with more details about the error message? If it is
possible, please provide me with a screenshot. You can send it to me
directly at v-yshao@.microsoft.com as an attachment.
4. I am not sure if this problem is machine specific. If it is possible,
could you upsize the MDB database from another machine? Can this problem be
reproduced on another machine? If this problem cannot be reproduced on my
side, it will be hard to find the cause.
Also, I found some useful articles regarding the issues with Upsizing
Wizard, could you check to see if your database has the same problem?
225993 ACC2000: Data Not Upsized in Table with Index
http://support.microsoft.com/?id=225993
325019 ACC2000: Issues with the Access 2000 Upsizing Wizard
http://support.microsoft.com/?id=325019
Thanks for using MSDN newsgroup.
Regards,
Michael Shao
Microsoft Online Partner Support
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.

Accesing the SQL Server 2005

Hi,

I have a asp.net 2.0 web applicaiton which is connecting to the remote SQL server 2005. When I run the Web app, then Iam connecting to the DB and its displaying the records on the page.

When I browse the same page from the IIS, then iam getting the following error:

"System.Data.SqlClient.SqlException: Login failed for user 'mydomain\user' "

Connection string :

connectionString="Data Source=MySystem;Initial Catalog=DBLive;Integrated Security=SSPI;" providerName="System.Data.SqlClient"/>

Please let me know if find accross a solution for this...

Thanks

Vaskam

Verify the 'user' assigned to the Web Servers' AppDomain.

Somewhere, you seem to have assigned the security to 'mydomain\user' -I hope that your domain is NOT called 'mydomain' and that you do NOT have a user named 'user'.

accept all directory in sql query analizer

Well I try to find why I can not run dtsrun with xp_cmdschell
but I try to put all the directory c:\program files\microsoft SQL\...
and the procedure response that he dont find c:\program command
is there a instruccion for the large names on directories that make
the correct functionality of the path?

Regards. Mirtha.I use double-quoted names in other places, maybe it works here... also, as a turn around you can use the ~ short names form.
Hope it helps,
Csar|||There is a convention for shortening a long name to an 8.3 convention but I find it hard to remember.

You can just put your pathname and executable in double quotes, i.e. "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe"|||Originally posted by Cesar Fraustro
I use double-quoted names in other places, maybe it works here... also, as a turn around you can use the ~ short names form.
Hope it helps,
Csar
Yes , I do but it does not work. thanks.|||Originally posted by Paul Young
There is a convention for shortening a long name to an 8.3 convention but I find it hard to remember.

You can just put your pathname and executable in double quotes, i.e. "C:\Program Files\Microsoft SQL Server\80\Tools\Binn\bcp.exe"

Yes it works, thanks and regards.

accent insensitive search and SPS

What about this?
http://www.cfgigolo.com/unsorted/fts..._sensitive.txt
I don't need to run SPS and SQL 2K on the same machine so it would be no
problem to overwrite MSSearch
Is it safe to do it?
Thanks
Matias
The binaries for the version of MSSearch which ships Sharepoint 2003 are
radically different from the ones which ship with SQL 2000 FTS. I would not
advise you to do this.
Hilary Cotter
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
"Matias Woloski" <woloski@.NOSPAMsion.com> wrote in message
news:u%235VxECVFHA.3760@.TK2MSFTNGP15.phx.gbl...
> What about this?
> http://www.cfgigolo.com/unsorted/fts..._sensitive.txt
> I don't need to run SPS and SQL 2K on the same machine so it would be no
> problem to overwrite MSSearch
> Is it safe to do it?
> Thanks
> Matias
>

Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause

Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause

Absolute Beginner in SQL 2005 EE, needs help.

Hi everyone.

Using C# 2005 EE with SQL 2005 EE

I am really new to SQL, got qns here.

Can my program with SQL database run correctly on another com using win xp home edition, with only C# 2005 EE installed?

Is it compulsory for every table in a database to have a primary key?

Can I extract a data from a specific cell in a database table(click button get data)? How do I go about doing it (the simplest n straight forward method)?

Many Thanks in advance.

Regards,

dragoncells.

Hi !

"Can my program with SQL database run correctly on another com using win xp home edition, with only C# 2005 EE installed?"

YOu compiled application (The actual exe you produce with compiling) will work on every computer which has the requisites installed that you need in your application (at leat the .NET Framework). If you using an installer instead of Xcopy deployment you can also package the prerequisites ensuring that the client will fullfill the requirements.

"Is it compulsory for every table in a database to have a primary key? "

Thats all about database design, primary keys will help you to keep unique integrity throught he database and integrity with the other tables using PK/FK relations. They help you (and behind the scenes the SQL Server) to create and retrieve data by a unique identitifer.

"Can I extract a data from a specific cell in a database table(click button get data)? How do I go about doing it (the simplest n straight forward method)?"

I am not quite sure what you mean, but from my understanding you want to retrieve an specific attribute (your cell) from a row in a table. Therefore you will have to create a SQL query like the following one:

SELECT YourAttributeName_Column FROM YourTable Where <filter the row out by specifying the condition like SOmeCOlumn = 'SomeValue'>

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||

Additionally info about me: I am also Absolute Beginner in C# 2005 EE.

when you wrote "YOu compiled application (The actual exe you produce with compiling) will work on every computer which has the requisites installed that you need in your application (at leat the .NET Framework). If you using an installer instead of Xcopy deployment you can also package the prerequisites ensuring that the client will fullfill the requirements."

I assume you are telling me that if my application has a SQL database than I must have SQL server 2005 EE installed on that computer for it to work.

And what do you mean by Xcopy deployment?

Installer? I thought when I publish the application it comes with the installer?

**Note**
I replied to Jens post(1:27pm) but it does show properly.

Edited: Backed to normal.

|||

dragoncells wrote:

I assume you are telling me that if my application has a SQL database than I must have SQL server 2005 EE installed on that computer for it to work.

You don′t need to have a SQL Server on the actual machine if you have another server in the network you want to connect to. Assuming that another server in the network act as a SQL Server you don′t have to install the SQL Server Express on the client machine.

"And what do you mean by Xcopy deployment?"
Xcopy deployment is possible because if the .NET framework is installed on the client machine no other things have to be registered like in the old days. YOu can just copy the application over (and also overwrite it with a newer version and it will work.

"Installer? I thought when I publish the application it comes with the installer?"
I don′t know which project type you are using. You will need an installer if you use components which do not easily install by just copying the files over, or if they have to be "installed" or copied in a different way.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

|||

That means Xcopy deployment is obsolete nowadays.

Using C# 2005 EE, working on a windows application project. When I publish my application it does come with a installer automatically?

|||No and no as the answer :-)

"That means Xcopy deployment is obsolete nowadays."
Seems that my description was too fuzzy. It is absolutely nice that you can do Xcopy deployment. Assuming that you want to update your application or an assembly within the application you just have to copy it over. Thats often used instead of uninstalling the old version and installing a new one.

A Windows application does not come automatically with a installer project. You will have to add one and will have to add the output files of your project solution to the installer project.

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de|||

Than what about the Publish properties, isn't it suppose to add a installer after I publish my application?

Does the forum have a suggestion area or something, so that microsoft can collect useful suggestions and try to improve the forums?

|||As being a moderator you can direct that directly to me. The thing with the quotes was fixed by me as it seems that the syntax I used is not supported by the forum.

-Jens.|||

It slipped my mind that you are the moderator, lol.

Suggestions:

Images: Currently cannot post picture, a picture tells a thousand words.|||

nice suggestions, thanks. as we are currently working on additional features for the forums, we will take these under consideration and prioritize as appropriate. should you have additional suggestions, please be sure to post them in our suggestions forum.

best,
brian -- forums product manager

|||

Ok.

=)

Absolut Noob here.

Hi all,
I've been working with SQL Server 2000 for most of the development I do but
now we're looking to build a small database that can run locally on the
client machine along with the application with which it will be distributed.
The following are my questions regarding this.
What do I need to do to properly create a database that will be accessible
by MSDE on a client machine?What is the best way to access it once it's
there (ie creating an ODBC System DSN)?
Can I do all of the database design work in SQL Server 2000?
Thanks in advance for the help.
Regards,
Daniel Hobert
hi Daniel,
Daniel Hobert wrote:
> Hi all,
> I've been working with SQL Server 2000 for most of the development I
> do but now we're looking to build a small database that can run
> locally on the client machine along with the application with which
> it will be distributed.
as you are used to SQL Server, MSDE is the same engine (with some
limitations) and all you know about SQL Server is valid for MSDE as long as
you do not hit it's limitations...

> The following are my questions regarding this.
> What do I need to do to properly create a database that will be
> accessible by MSDE on a client machine?
you have all the SQL Server possibilities available, from detach-attach,
backup/restore and script/exec...
I am an estimator of the latter, where you get the DDL code from your source
control system to create the user's database(s) your application need, where
you inherit from destination model database all the available settings as
long as included objects/users...
just for your note, the best article I read about such a kind of
installation, including database schema support is
http://msdn.microsoft.com/msdnmag/is...einstaller/...

>What is the best way to access
> it once it's there (ie creating an ODBC System DSN)?
it really depends on your coding standard... personally I always use
DSN-less connections as ADO/Ado.Net support them very well...

> Can I do all of the database design work in SQL Server 2000?
yes and you should... MSDE is intended as a distribution platform and not as
a development paltform, with all of your dev tools available, like EM, Query
Analyzer, Index Tuning, Profiler and the like..
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Hi Andrea,
Thanks a ton for the quick response and the link to the article .
Cheers,
Daniel
"Andrea Montanari" <andrea.sqlDMO@.virgilio.it> wrote in message
news:3h65jcFf4sp8U1@.individual.net...
> hi Daniel,
> Daniel Hobert wrote:
>
> as you are used to SQL Server, MSDE is the same engine (with some
> limitations) and all you know about SQL Server is valid for MSDE as long
> as you do not hit it's limitations...
>
> you have all the SQL Server possibilities available, from detach-attach,
> backup/restore and script/exec...
> I am an estimator of the latter, where you get the DDL code from your
> source control system to create the user's database(s) your application
> need, where you inherit from destination model database all the available
> settings as long as included objects/users...
> just for your note, the best article I read about such a kind of
> installation, including database schema support is
> http://msdn.microsoft.com/msdnmag/is...einstaller/...
>
> it really depends on your coding standard... personally I always use
> DSN-less connections as ADO/Ado.Net support them very well...
>
> yes and you should... MSDE is intended as a distribution platform and not
> as a development paltform, with all of your dev tools available, like EM,
> Query Analyzer, Index Tuning, Profiler and the like..
> --
> Andrea Montanari (Microsoft MVP - SQL Server)
> http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
> DbaMgr2k ver 0.12.0 - DbaMgr ver 0.58.0
> (my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
> interface)
> -- remove DMO to reply
>

About trigger

is it possible to run a trigger forever... whose work is to archive data from a table. or how can i do the operation to archive automatically ?

Regards,

Kamrul Hassan

Well... triggers cannot be used for schedule tasking, there are certain some ways to do this.
1st) if you are running on dedicated server then you can use windows service to update records periodically
2nd) Use Sql Server jobs. to know more about sql server jobs http://msdn2.microsoft.com/en-us/library/ms187880.aspx

cheers..

About sys.fn_indexinfo in SQL2005 MOC 2733A

Hi guys,
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the product. My guess is that
the function you want is sys.dm_db_index_physical_stats.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" function in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>From: <io.com@.newsgroup.nospam>
>>References: <O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl>
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>
>>Subject: Re: About sys.fn_indexinfo in SQL2005 MOC 2733A
>>Date: Wed, 30 Aug 2006 16:50:21 +0200
>>Lines: 36
>>X-Priority: 3
>>X-MSMail-Priority: Normal
>>X-Newsreader: Microsoft Outlook Express 6.00.2900.2869
>>X-RFC2646: Format=Flowed; Response
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962
>>Message-ID: <#09RgOEzGHA.1536@.TK2MSFTNGP02.phx.gbl>
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: 62.241.4.149
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP02.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:443733
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>Yes it's right.
>>Found!
>>bye.
>>"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
in
>>message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
>> You might have an version of the course that was based on a beta of the
>> product. My guess is that the function you want is
>> sys.dm_db_index_physical_stats.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> <io.com@.newsgroup.nospam> wrote in message
>> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>> Hi guys,
>> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
>> function in a module 5 but when i run a sys.fn_indexinfo how indicated
a
>> error showed
>> Seems that not exist ... it's possible ? It was replaced ?
>> If so how i can view a index defrag status ?
>> Thanks in advance.
>>
>>
>>

About sys.fn_indexinfo in SQL2005 MOC 2733A

Hi guys,
i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo"
function in a module 5 but when i run a sys.fn_indexinfo how indicated a
error showed
Seems that not exist ... it's possible ? It was replaced ?
If so how i can view a index defrag status ?
Thanks in advance.You might have an version of the course that was based on a beta of the prod
uct. My guess is that
the function you want is sys.dm_db_index_physical_stats.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
<io.com@.newsgroup.nospam> wrote in message news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...[vbc
ol=seagreen]
> Hi guys,
> i reading the MOC 2733A for SQL2005 and found the "sys.fn_indexinfo" funct
ion in a module 5 but
> when i run a sys.fn_indexinfo how indicated a error showed
> Seems that not exist ... it's possible ? It was replaced ?
> If so how i can view a index defrag status ?
> Thanks in advance.
>[/vbcol]|||Yes it's right.
Found!
bye.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl...
> You might have an version of the course that was based on a beta of the
> product. My guess is that the function you want is
> sys.dm_db_index_physical_stats.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> <io.com@.newsgroup.nospam> wrote in message
> news:O6mSA$DzGHA.3908@.TK2MSFTNGP05.phx.gbl...
>|||Hi IO,
So far as I know, number of information in MOC 2733 is changed since it is
based on SQL 2005 beta. If you have any other questions, feel free to post
in our newsgroup. We are glad to provide assistance.
Have a good day!
Best regards,
Vincent Xu
Microsoft Online Partner Support
========================================
==============
Get Secure! - www.microsoft.com/security
========================================
==============
When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
========================================
==============
This posting is provided "AS IS" with no warranties,and confers no rights.
========================================
==============
--[vbcol=seagreen]
<uGPTSCEzGHA.4972@.TK2MSFTNGP03.phx.gbl>[vbcol=seagreen]
in[vbcol=seagreen]
a[vbcol=seagreen]

about Store Procedure

Hi i have store procedure called runontime suppose... i want to run that
store procedure mid night from monday to friday automatically . is their any
facility available in sql server 2005 to do that like timer thanksCreate job? Have you check it out?
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||Use the SQL Server agent - that's what it's there for! :)
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
> Hi i have store procedure called runontime suppose... i want to run that
> store procedure mid night from monday to friday automatically . is their
> any
> facility available in sql server 2005 to do that like timer thanks|||hi can u direct me to any artical or help about it thanks
"Uri Dimant" wrote:

> Create job? Have you check it out?
>
>
> "amjad" <amjad@.discussions.microsoft.com> wrote in message
> news:09575A58-6AA3-4615-97DD-FA7B94D74C99@.microsoft.com...
>
>|||Ok, open BOL and put it in URL
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/079c2984-0052-4a37-a2b8-4ece
56e6b6b5.htm
"amjad" <amjad@.discussions.microsoft.com> wrote in message
news:CF8B8E49-E0E0-4585-BD03-72D423092AA1@.microsoft.com...
> hi can u direct me to any artical or help about it thanks
> "Uri Dimant" wrote:
>

about SQL Suspect status

my corporate database appear Suspect status!
and i run sp_resetstatus then stop
last i restart SQL!
but it appear following information:
Could not locate row in sysobjects for system catalog 'Sysobjects' in
database 'NVS1'. This system catalog should exist in all databases. Run DBCC
CHECKTABLE on sysobjects in this database.
what should i do ?Hi Allen,
Your database appears to be corrupted. Do you have backup?
Also you may want to do a hardware diagnostic to look for possible hardware
problem.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.|||First Thanks your reply!
But,My backup every night! one day has lost!
My God! my salary is reducing? can you help me?
can you give me a plan about how backup so huge database!?
"Yih-Yoon Lee [MS]" <yihyoonl@.online.microsoft.com> wrote in message
news:$tOtmU$YDHA.396@.cpmsftngxa06.phx.gbl...
> Hi Allen,
> Your database appears to be corrupted. Do you have backup?
> Also you may want to do a hardware diagnostic to look for possible
hardware
> problem.
> Sincerely,
> Yih-Yoon Lee [Microsoft]
> Microsoft SQL Server Support
> This posting is provided "AS IS" with no warranties, and confers no
rights.
> Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
>