Newbie Question

Hi

I'm working with SQL Express, and this is my first experience with DB, so I'm still learning. I made a rather stupid mistake, and I'm not quite sure how to get out of it. In the process of trying to fix a problem, I changed the default DB in my account to one that it can no longer connect to - for a reason unknown to me. (once I get this cleared up, I'll ask about that.) Now I can't login properly to SQL Express, b/c it can't connect properly.

please help

thanx

Jeffery

hi,

yiu should be able to connect via a system administrator login, bot WinNT trusted authenticated (login in in Windows as a local administrator) or standard SQL Server authenticated (if you enabled mixed security) providing sa login's credentials (userid and password)...

once you connect to SQLExpress, you can modify (via SSMSX) the default database property of the faling login object in the general tab or, executing the

ALTER LOGIN login_name
WITH DEFAULT_DATABASE = new_default_db;

Transact-SQL statement..

regards

|||Thanx...

Duh! - to me - I should have thought of creating another local Admin account.

Thanx - it worked.

Newbie Question

Hi
I simply want to automate the backup of the database every hour using the
Enterprise Manager
Thank you,
Shmuel Shulman
Use "Database Maintenance Plan Wizard".
"SQL Server 2000 Administrator's Pocket Consultant: Database Automation and
Maintenance"
http://www.microsoft.com/technet/pro.../c12ppcsq.mspx
"Automate database upkeep with the SQL Server Maintenance Plan Wizard"
http://techrepublic.com.com/5100-6268_11-1043672.html
Cristian Lefter, SQL Server MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%234sWEVsOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>
|||Steps:-
Hi,
Use the maintenance Plan wizard from enterprise manager to scdule a database
backup every one hour.
1. Open Enterprise manager and connect to server
2. Go to Managaement option
3. Select Database maintenance plans
4. Right click and select the new maintenenace plan
5. Select the database
6. Go further by clicking next
7. You couild see the Backup option, retension days...
8. schedule the maintenance plan and give a suitable name
Note:
You can also scdule by writing a backup script using Backup database command
and scheule using SQL Agent... Jobs..
Thanks
Hari
SQL Server MVP
"S Shulman" wrote:

> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>
>

Newbie Question

Hi
I simply want to automate the backup of the database every hour using the
Enterprise Manager
Thank you,
Shmuel ShulmanUse "Database Maintenance Plan Wizard".
"SQL Server 2000 Administrator's Pocket Consultant: Database Automation and
Maintenance"
http://www.microsoft.com/technet/pr...s/c12ppcsq.mspx
"Automate database upkeep with the SQL Server Maintenance Plan Wizard"
http://techrepublic.com.com/5100-6268_11-1043672.html
Cristian Lefter, SQL Server MVP
"S Shulman" <smshulman@.hotmail.com> wrote in message
news:%234sWEVsOFHA.1176@.TK2MSFTNGP12.phx.gbl...
> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>|||Steps:-
Hi,
Use the maintenance Plan wizard from enterprise manager to scdule a database
backup every one hour.
1. Open Enterprise manager and connect to server
2. Go to Managaement option
3. Select Database maintenance plans
4. Right click and select the new maintenenace plan
5. Select the database
6. Go further by clicking next
7. You couild see the Backup option, retension days...
8. schedule the maintenance plan and give a suitable name
Note:
You can also scdule by writing a backup script using Backup database command
and scheule using SQL Agent... Jobs..
Thanks
Hari
SQL Server MVP
"S Shulman" wrote:

> Hi
> I simply want to automate the backup of the database every hour using the
> Enterprise Manager
> Thank you,
> Shmuel Shulman
>
>

Newbie Merge replication question

Hi!

I'm setting up a merge replication for sql ce. Ten guys with windows mobile 5 pocket pc works as service technicians and need to synchronize data in the field.

The backend database is huge and I just want each person to have their own data on the pda and synchronized, they have unique userid's in the database. Is it possible to filter data for each person or do all have to have all data that's in the backend database?

/Magnus

Merge replication permits the agents to have subsets of the data based on how the Publication is programmed. One alternate approach would be to publish individual agent tables, but this gets messy as agents are added and removed.|||Could I use a "translator" table to translate suser_name()'s to applications userid's? Then in the filter query against this table to identify the user? One solution could be to have one Publication for each employee? Yes it would become messy to administer. But my problem can't be unique. How do you all query out data for each employee?|||You could have each user identifying themselves when using the application, and setting their userid as HostName on your replication object. Then you can reference this in your publication filter with HOST_NAME().

Newbie Merge replication question

Hi!

I'm setting up a merge replication for sql ce. Ten guys with windows mobile 5 pocket pc works as service technicians and need to synchronize data in the field.

The backend database is huge and I just want each person to have their own data on the pda and synchronized, they have unique userid's in the database. Is it possible to filter data for each person or do all have to have all data that's in the backend database?

/Magnus

Merge replication permits the agents to have subsets of the data based on how the Publication is programmed. One alternate approach would be to publish individual agent tables, but this gets messy as agents are added and removed.|||Could I use a "translator" table to translate suser_name()'s to applications userid's? Then in the filter query against this table to identify the user? One solution could be to have one Publication for each employee? Yes it would become messy to administer. But my problem can't be unique. How do you all query out data for each employee?|||You could have each user identifying themselves when using the application, and setting their userid as HostName on your replication object. Then you can reference this in your publication filter with HOST_NAME().

Newbie advise

Hi
I am going to begin my first sql server app with vb.net front end. Is there
somewhere I can read on good practices on developing sql server apps, or is
there a sample app that I can see as an example?
Thanks
Regards
I'd check the "How Do I" Videos from MSDN if you're just starting out.
This link is specific to Visual Basic.NET
http://msdn2.microsoft.com/en-us/vbasic/bb466226.aspx
"John" <info@.nospam.infovis.co.uk> wrote in message
news:eHk$nX8iIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Hi
> I am going to begin my first sql server app with vb.net front end. Is
> there somewhere I can read on good practices on developing sql server
> apps, or is there a sample app that I can see as an example?
> Thanks
> Regards
>
|||Here are the samples and starter kits for SQL Server 2005 Express with have
plenty of examples in VB.NET:
http://msdn2.microsoft.com/en-us/express/bb403187.aspx
http://msdn2.microsoft.com/en-us/express/aa718396.aspx
HTH,
Plamen Ratchev
http://www.SQLStudio.com
|||My latest book is a good match for you. See www.betav.com for details or
just visit
http://www.amazon.com/Hitchhikers-Guide-Visual-Studio-Server/dp/0321243625.
It walks you through how SQL Server works in terms anyone can understand and
uses VB.NET for all of the examples from simple to far more sophisticated
approaches.
__________________________________________________ ________________________
William R. Vaughn
President and Founder Beta V Corporation
Author, Mentor, Dad, Grandpa
Microsoft MVP
(425) 556-9205 (Pacific time)
Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
__________________________________________________ __________________________________________
"John" <info@.nospam.infovis.co.uk> wrote in message
news:eHk$nX8iIHA.5280@.TK2MSFTNGP02.phx.gbl...
> Hi
> I am going to begin my first sql server app with vb.net front end. Is
> there somewhere I can read on good practices on developing sql server
> apps, or is there a sample app that I can see as an example?
> Thanks
> Regards
>
|||On Mar 21, 10:39Xpm, "John" <i...@.nospam.infovis.co.uk> wrote:
> Hi
> I am going to begin my first sql server app with vb.net front end. Is there
> somewhere I can read on good practices on developing sql server apps, or is
> there a sample app that I can see as an example?
>
While it's actually a Visual Studio.Net reference, it has a lot of
material that covers data access in your .Net applications:
http://www.learnvisualstudio.net
Good luck!
Richard Carpenter

Newbie advise

Hi
I have an ms access background. After hearing all the good stuff about
vb2005/sql2005 I am aiming to switch over. My problem is that I am not very
familiar with features of sql server that are not found in access such as
triggers, stored procedures etc and am not sure when these should be used. I
need to know things like;
- Is it recommended to keep all/most queries as stored procedures and pass
them parameters from vb.net client or is it ok to have the queries in the vb
app?
- Are views essential or just a mater of convenience?
- Triggers, how important are these?
- What about new features of vb2005/sql2005, when should they be used?
...and so on.
Is there a place where I can get some more information, to have a better
idea of how to take the best advantage of these features?
Thanks
Regards
Hi
As most of your questions are generic then reading the SQL 2000 books online
will give you a good idea of where you should be heading. See
http://msdn.microsoft.com/library/de...ortal_7ap1.asp
In general using stored procedures is the best way to manage code and
optimize it's execution. Having Views is not essential, the need for them is
something that will be determined as part of your design process. Similarly
with triggers, your design may require them, but quite often there are
alternatives such as check constraints provide the same functionality and you
may find them easier to manage.
If you have specific issues then you may want to post a new message just
relating to that specific issue.
HTH
John
"John" wrote:

> Hi
> I have an ms access background. After hearing all the good stuff about
> vb2005/sql2005 I am aiming to switch over. My problem is that I am not very
> familiar with features of sql server that are not found in access such as
> triggers, stored procedures etc and am not sure when these should be used. I
> need to know things like;
> - Is it recommended to keep all/most queries as stored procedures and pass
> them parameters from vb.net client or is it ok to have the queries in the vb
> app?
> - Are views essential or just a mater of convenience?
> - Triggers, how important are these?
> - What about new features of vb2005/sql2005, when should they be used?
> ...and so on.
> Is there a place where I can get some more information, to have a better
> idea of how to take the best advantage of these features?
> Thanks
> Regards
>
>
|||What in the wide world of sports is MS SQL and why is it important on some of
the new programs?
Chuck
"John" wrote:

> Hi
> I have an ms access background. After hearing all the good stuff about
> vb2005/sql2005 I am aiming to switch over. My problem is that I am not very
> familiar with features of sql server that are not found in access such as
> triggers, stored procedures etc and am not sure when these should be used. I
> need to know things like;
> - Is it recommended to keep all/most queries as stored procedures and pass
> them parameters from vb.net client or is it ok to have the queries in the vb
> app?
> - Are views essential or just a mater of convenience?
> - Triggers, how important are these?
> - What about new features of vb2005/sql2005, when should they be used?
> ...and so on.
> Is there a place where I can get some more information, to have a better
> idea of how to take the best advantage of these features?
> Thanks
> Regards
>
>

Newbie advise

Hi
I have an ms access background. After hearing all the good stuff about
vb2005/sql2005 I am aiming to switch over. My problem is that I am not very
familiar with features of sql server that are not found in access such as
triggers, stored procedures etc and am not sure when these should be used. I
need to know things like;
- Is it recommended to keep all/most queries as stored procedures and pass
them parameters from vb.net client or is it ok to have the queries in the vb
app?
- Are views essential or just a mater of convenience?
- Triggers, how important are these?
- What about new features of vb2005/sql2005, when should they be used?
...and so on.
Is there a place where I can get some more information, to have a better
idea of how to take the best advantage of these features?
Thanks
RegardsHi
As most of your questions are generic then reading the SQL 2000 books online
will give you a good idea of where you should be heading. See
http://msdn.microsoft.com/library/d...
ap1.asp
In general using stored procedures is the best way to manage code and
optimize it's execution. Having Views is not essential, the need for them is
something that will be determined as part of your design process. Similarly
with triggers, your design may require them, but quite often there are
alternatives such as check constraints provide the same functionality and yo
u
may find them easier to manage.
If you have specific issues then you may want to post a new message just
relating to that specific issue.
HTH
John
"John" wrote:

> Hi
> I have an ms access background. After hearing all the good stuff about
> vb2005/sql2005 I am aiming to switch over. My problem is that I am not ver
y
> familiar with features of sql server that are not found in access such as
> triggers, stored procedures etc and am not sure when these should be used.
I
> need to know things like;
> - Is it recommended to keep all/most queries as stored procedures and pass
> them parameters from vb.net client or is it ok to have the queries in the
vb
> app?
> - Are views essential or just a mater of convenience?
> - Triggers, how important are these?
> - What about new features of vb2005/sql2005, when should they be used?
> ...and so on.
> Is there a place where I can get some more information, to have a better
> idea of how to take the best advantage of these features?
> Thanks
> Regards
>
>|||What in the wide world of sports is MS SQL and why is it important on some o
f
the new programs'
--
Chuck
"John" wrote:

> Hi
> I have an ms access background. After hearing all the good stuff about
> vb2005/sql2005 I am aiming to switch over. My problem is that I am not ver
y
> familiar with features of sql server that are not found in access such as
> triggers, stored procedures etc and am not sure when these should be used.
I
> need to know things like;
> - Is it recommended to keep all/most queries as stored procedures and pass
> them parameters from vb.net client or is it ok to have the queries in the
vb
> app?
> - Are views essential or just a mater of convenience?
> - Triggers, how important are these?
> - What about new features of vb2005/sql2005, when should they be used?
> ...and so on.
> Is there a place where I can get some more information, to have a better
> idea of how to take the best advantage of these features?
> Thanks
> Regards
>
>

Newbie advise

Hi
I have an ms access background. After hearing all the good stuff about
vb2005/sql2005 I am aiming to switch over. My problem is that I am not very
familiar with features of sql server that are not found in access such as
triggers, stored procedures etc and am not sure when these should be used. I
need to know things like;
- Is it recommended to keep all/most queries as stored procedures and pass
them parameters from vb.net client or is it ok to have the queries in the vb
app?
- Are views essential or just a mater of convenience?
- Triggers, how important are these?
- What about new features of vb2005/sql2005, when should they be used?
...and so on.
Is there a place where I can get some more information, to have a better
idea of how to take the best advantage of these features?
Thanks
RegardsHi
As most of your questions are generic then reading the SQL 2000 books online
will give you a good idea of where you should be heading. See
ap1.asp" target="_blank">http://msdn.microsoft.com/library/d...
ap1.asp
In general using stored procedures is the best way to manage code and
optimize it's execution. Having Views is not essential, the need for them is
something that will be determined as part of your design process. Similarly
with triggers, your design may require them, but quite often there are
alternatives such as check constraints provide the same functionality and yo
u
may find them easier to manage.
If you have specific issues then you may want to post a new message just
relating to that specific issue.
HTH
John
"John" wrote:

> Hi
> I have an ms access background. After hearing all the good stuff about
> vb2005/sql2005 I am aiming to switch over. My problem is that I am not ver
y
> familiar with features of sql server that are not found in access such as
> triggers, stored procedures etc and am not sure when these should be used.
I
> need to know things like;
> - Is it recommended to keep all/most queries as stored procedures and pass
> them parameters from vb.net client or is it ok to have the queries in the
vb
> app?
> - Are views essential or just a mater of convenience?
> - Triggers, how important are these?
> - What about new features of vb2005/sql2005, when should they be used?
> ...and so on.
> Is there a place where I can get some more information, to have a better
> idea of how to take the best advantage of these features?
> Thanks
> Regards
>
>|||What in the wide world of sports is MS SQL and why is it important on some o
f
the new programs'
--
Chuck
"John" wrote:

> Hi
> I have an ms access background. After hearing all the good stuff about
> vb2005/sql2005 I am aiming to switch over. My problem is that I am not ver
y
> familiar with features of sql server that are not found in access such as
> triggers, stored procedures etc and am not sure when these should be used.
I
> need to know things like;
> - Is it recommended to keep all/most queries as stored procedures and pass
> them parameters from vb.net client or is it ok to have the queries in the
vb
> app?
> - Are views essential or just a mater of convenience?
> - Triggers, how important are these?
> - What about new features of vb2005/sql2005, when should they be used?
> ...and so on.
> Is there a place where I can get some more information, to have a better
> idea of how to take the best advantage of these features?
> Thanks
> Regards
>
>

Newbie - Filling Tables with Data

Hi

I am writing SQL scripts to define my tables and also fill some of them with
data that are essentially look up tables. One of the tables contains
hundreds of stock items which need to be filled with information.

Is it better to write my INSERT INTO... statements to fill this large table
or better to manually insert the data??

As this solution will be for a small jewellery store which has many
locations and the users cannot be assumed to be advanced-level users, are
there any advantages to writing scripts to fill the tables? (Will be using
MSDE when shipped)

TIA
MichelleI suppose it depends on the user interface you would use to input the data.
Use whichever is quickest and easiest. If you later need to create a script
of INSERT INTO statements you can use this code:
http://vyaskn.tripod.com/code.htm#inserts

Also, consider using DTS to import the data if you have some alternative
electronic source of the data.

--
David Portas
SQL Server MVP
--|||Thank you David for your reply and for the great link also.

No doubt it won't be the last post I put on here.

Michelle

"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:76mdnaugYLHpzS3d4p2dnA@.giganews.com...
> I suppose it depends on the user interface you would use to input the
data.
> Use whichever is quickest and easiest. If you later need to create a
script
> of INSERT INTO statements you can use this code:
> http://vyaskn.tripod.com/code.htm#inserts
> Also, consider using DTS to import the data if you have some alternative
> electronic source of the data.
> --
> David Portas
> SQL Server MVP
> --

newbe question: calling function inside select

Hi!

I have a scalar function that returns integer:
xview (int)

Now, I'm trying to build a procedure that has the following select
inside:

select atr1, xview(atr2)
from tablename

But, I get the 'Invalid name' error when I try to execute that
procedure.

If I got it right, I must use user.fn_name() syntax, but I cannot use
dbo.xview() inside my procedure since it means xview will always be
executed as dbo, which is unaccaptable.

I'm a bit confused, so any hint is very welcomed.

Thanks!

Mario.Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> I have a scalar function that returns integer:
> xview (int)
> Now, I'm trying to build a procedure that has the following select
> inside:
> select atr1, xview(atr2)
> from tablename
> But, I get the 'Invalid name' error when I try to execute that
> procedure.
> If I got it right, I must use user.fn_name() syntax, but I cannot use
> dbo.xview() inside my procedure since it means xview will always be
> executed as dbo, which is unaccaptable.

But those are the rules. You must refer to a scalar function with a
two-part name.

I don't really see why this is unacceptable. Do you plan to have other
xview functions owned by other users?

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Mon, 13 Oct 2003 22:13:03 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>But those are the rules. You must refer to a scalar function with a
>two-part name.
>I don't really see why this is unacceptable. Do you plan to have other
>xview functions owned by other users?

Ok, let's put is this way.
I have 'xview' function.

I'm connected to sql server as userX.
Now, when I (as userX) call dbo.xview(), do I execute it as userX or
dbo?

It is vital, because xview() contains code that uses msqql USER sistem
variable, and it should be noted that user userX executed that
function.

Mario.|||Mario Pranjic (keeper@.fly.srk.fer.hr) writes:
> Ok, let's put is this way.
> I have 'xview' function.
> I'm connected to sql server as userX.
> Now, when I (as userX) call dbo.xview(), do I execute it as userX or
> dbo?
> It is vital, because xview() contains code that uses msqql USER sistem
> variable, and it should be noted that user userX executed that
> function.

USER will return userX.

The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
does not change when you call a user-defined function or stored procedure.

The point with calling a stored procedure owned by another user, is
that you can get controlled access to objects that you don't have direct
access to. For instance, in many databases, users does not have direct
access to any tables. Instead they only have access to stored procedures
and user-defined functions that make sure that the users can only access
data they have a right to see, and their updates conforms to the rule
of the database.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||On Tue, 14 Oct 2003 22:00:21 +0000 (UTC), Erland Sommarskog
<sommar@.algonet.se> wrote:

>USER will return userX.
>The "dbo." in "dbo.xview()" has nothing to do with impersonation. The
>return values of funtions like USER, SYSTEM_USER, suser_snmae() etc
>does not change when you call a user-defined function or stored procedure.
>The point with calling a stored procedure owned by another user, is
>that you can get controlled access to objects that you don't have direct
>access to. For instance, in many databases, users does not have direct
>access to any tables. Instead they only have access to stored procedures
>and user-defined functions that make sure that the users can only access
>data they have a right to see, and their updates conforms to the rule
>of the database.

Aha. That is very good. Thank you for the information!

Mario.