Automating the creation of a database

Hi;
We have a program (ASP.NET) that requires a database as it's back end. We
are trying to create an install that requires as little expertise as
possible. In other words, no DBA required.
For creating the database itself we are at that point. We use the registry
to find the location of osql.exe and use that to run the schema that creates
the database. I'ld prefer an API we could call so we can more cleanly handle
errors but this works fine 98% of the time.
The remaining problem is ownership of the created database.
1) Is there a way (in .NET 2.0) to query if the database is mixed mode
authentication.
2) And if it is a way to both get a list of all users
3) And to create a user?
We can already enum all domain users. So with the above we could give them a
list of all users they can choose from as the owner and also let then create
a new one - without the user ever having to run any SqlServer tool or even
have any installed.
thanks - dave
david_at_windward_dot_net
http://www.windwardreports.com
Cubicle Wars - http://www.windwardreports.com/film.htm> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
It is SERVER property not a databases
SELECT SERVERPROPERTY('IsIntegratedSecurityOnly
') AS
[IsIntegratedSecurityOnly]

> 2) And if it is a way to both get a list of all users
EXEC northwind..sp_helpuser

> 3) And to create a user?
Create Database mydb
go
use mydb
go
sp_addlogin 'mydbuser','monitor','mydb'
go
sp_adduser 'mydbuser'
go
sp_Addrolemember 'db_datawriter','mydbuser'
go
sp_Addrolemember 'db_datareader','mydbuser'
go
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi David,
I am afraid that the database may have some synchronous problem now. Our
yesterday's replies cannot be seen from Web and we also cannot see your
replies.
So I post it again from Outlook Express and hope you could see it now. Sorry
for bringing you any inconvenience.
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
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.
========================================
==============
"David Thielen" <thielen@.nospam.nospam> wrote in message
news:25882AC6-F4EF-422A-8B68-63ABE75AACF3@.microsoft.com...
> Hi;
> We have a program (ASP.NET) that requires a database as it's back end. We
> are trying to create an install that requires as little expertise as
> possible. In other words, no DBA required.
> For creating the database itself we are at that point. We use the registry
> to find the location of osql.exe and use that to run the schema that
> creates
> the database. I'ld prefer an API we could call so we can more cleanly
> handle
> errors but this works fine 98% of the time.
> The remaining problem is ownership of the created database.
> 1) Is there a way (in .NET 2.0) to query if the database is mixed mode
> authentication.
> 2) And if it is a way to both get a list of all users
> 3) And to create a user?
> We can already enum all domain users. So with the above we could give them
> a
> list of all users they can choose from as the owner and also let then
> create
> a new one - without the user ever having to run any SqlServer tool or even
> have any installed.
> --
> thanks - dave
> david_at_windward_dot_net
> http://www.windwardreports.com
> Cubicle Wars - http://www.windwardreports.com/film.htm
>|||Hi Dave,
I understand that your application used osql.exe to create the database and
you have three questions on the ownership of the created database now:
1. How to query (in .NET 2.0) if the database is with mixed authentication
mode?
2. How to get a list of all users?
3. How to create a user?
If I have misunderstood, please let me know.
For your three questions and even your creating database function, you can
fully resolve the questions by using the SQL Server SMO component for .NET
2.0.
1. You can just create a Server object like this:
//Server Name
string strConn = "(local)";
//Instantiate SMO Server Object
Server svr = new Server(strConn);
Console.Writeline(svr.Settings.LoginMode.ToString());
2. To get the list of all users, you can use:
Database db = server.Databases["your_db_name"];
UserCollection users = db.Users;
3. To create a user, you can use:
//Instantiate SMO Login object
Login l = new Login(svr, loginName);
//If Login doesn't already exist
if (!svr.Logins.Contains(loginName))
{
//Login should be of type Sql Login
l.LoginType = LoginType.SqlLogin;
//Create the Login on the SQL Server with password: pa$$w0rd
l.Create("pa$$w0rd");
//Add the login to the sysadmin role
l.AddToRole("sysadmin");
}
//Instantiate a new database object
Database db = new Database(svr, "Fizoo2");
//Make SQL Server create the database
db.Create();
//Instantiate a new User object
User u = new User(db, "SQL_Login_user");
//associated it with the login "SQL_Login"
u.Login = loginName;
//Make SQL Server create the user
u.Create();
For more information, you can refer to the following references:
User Privileges View & Create User Tool
http://forums.microsoft.com/MSDN/Sh...840637&SiteID=1
How to: Create a Visual C# SMO Project in Visual Studio .NET
http://msdn2.microsoft.com/it-it/library/ms162129.aspx
How to: Modify SQL Server Settings in Visual Basic .NET
http://msdn2.microsoft.com/en-us/library/ms162131.aspx
If you have any other questions or concerns, please feel free to let me
know. It is my pleasure to be of assistance.
Sincerely yours,
Charles Wang
Microsoft Online Community Support
========================================
==============
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.
========================================
==============

Automating Daily Database Inserts.

Hi can anybody please provide me information on how i can automate a vb.net script to insert records in to a SQL Server database everyday. I know that the Scheduled Tasks tool in Control Panel must be used but i am not sure about the VB.net code. Opening up the DB and inserting records is no problem its just geting the script to run using the Scheduled Tasks tool. Any other type of script that opens up a DB or updates it even a vb.net script that executes using the Scheduled Tasks tool would be helpful.

Thanks in advance for the help.

ImranDoes it have to be run via DotNet? It would be simpler if you could have the Sql Server Agent run some SQL or execute a sproc. Alternatively you could have DTS do it and Sql Server Agent would trigger the DTS package.

If you need to create the scheudle from DotNet you would need to create a Service that manages when things run and then at the appropriate time trigger your code. If you need to go this route I can post some code to get you started but it will be in C# so you'll have to translate.|||The service is the best idea.

Simply, yet very bad and chessy, but effective, is to build an aspx page that performs the work, and place the following command in a batch file:

"C:\Program Files\Internet Explorer\IEXPLORE.EXE" http://MyScriptURL"

You could then use the built in Scheduler (AT.exe from a command prompt) within 2000 or NT4, or any other scheduler, to run the batch file nightly.

Of course, the great risk here is anyone can fire that URL at anytime and then it runs more than once. If it's a critical, must be bullet-proof task, go with the service. If you want something quick and dirty, that's temporary, give the above a shot.

Brian|||McMurdoStation

i was going to post a question in the forum about this and i saw this thread.
can you give some advice on how to get started...link to some tutorial, i am writing a vb.net app tht will call some SP's ( the Sp's will add transactions to customer transactions table). i need to create an html file (i can already do this). basically i have the whole prog working. i can run it manually. i just need to schedule it so it runs automatically every night ( at the specified time).

thanks.|||This article should get you started for creating a DotNet scheduler service.|||Hi McMurdoStation,
yes could you post the code please.

Thanks for the help.|||Hi Brian,
is the command that you specified above the only command that needs to go in to the batch file or do some other commands need to be put in there. In other words do i need to create a file called for example updatedb.bat and put the following bit of code in to it in the following way.

C:\Program Files\Internet Explorer\IEXPLORE.EXE http://localhost/metrics/updatedb.aspx

or do some other bits of code need to go in there aswell? Thanks for your help its much appreciated.|||All you should need is the above one line of code. You can test it by running it from a cmd prompt yourself. All it does is fire IE with the address that follows, and of course, the page renders.

If you can schedule that command, w/o having to use a batch file, then that will work as well. It's just nice to keep the comand in a batch file so it can be updated w/o having to touch a scheduler.

I, again, do recommend the service. My idea is just a temp or short-term workaround.

Brian|||ASPNester,

The link posted above describes how to create a scheduler service with VB.Net. It's probably easier to work from that rather than try to translate my C# code.|||thanks McMurdoStation...will spend some tiem trying to go through the article and understanding it.
thanks.|||Brian thanks, the code worked.

However as you mention its not really an ideal method. Do you know of any web sites that have tutorials for creating "Windows?" Service, or any books that have instructions on creating window services, the URL that McDurmock gave uses Visual Studio so its not really much use for me.

Thanks for all you r help anyway.|||Wrox has a good book called "Visual Basic .NET Windows Services Handbook". It's just under 200 pages, and meant to get you going fast. However, it assumes you have VS.NET. Of course, you don't need VS.NET. It shows most code, so you should be able to get by.

I've only written one service, but was up to speed in just a few days with the book

ISBN 1-86100-772-8, ~$30.

Brian|||ok mate thanks for all your help.

Automatically Generating PDFs With Reports Requiring Parameters

Hi,
I'm working on many reports that are generated using SQL Server
Reporting Services SP 1 from an ASP.NET 1.1 web application. One of the
things the users would like to do is have the report automatically
generate a PDF when they click on the View Report button after entering
the parameters for the report. Parameters could be a date, a city, a
state, etcetera. However, they would like to avoid having to choose the
format and click the Export link. Is there a way to manipulate the
functionality of the View Report button such that it does this
automatically? Thank you for any insights you can provide.
JabooHow are you accessing the reports?
1) Report Manager
2) URL parameters
3) Web Service
Kulgan.sql

automatically copy database to another pc

When I create a setup program for my vb.net 1.0 app which has embedded sql express database, it successfully copies the database to the new machine alone with the upgraded app.

However, if I just copy the vb.exe app to the other pc and also copy the mdf, ldf files, I get an error opening the sql database.

I am presuming that the setup program does some kind of backup restore or detach, attach to copy in the .mdf.

If the user cannot do this from the management studio (or if they do not have the management studio installed), is there any way I can create some method to have this copying done automatically via some code by the user?

Thanks

SM Haig

Yes, you can run a restore script using the sqlcmd program. You can read more about the restore command here:

http://msdn2.microsoft.com/en-us/library/ms186858.aspx

Buck Woody

Automatic sync between SQLce and Access

Hi,

I am converting an old eVB application to CF.NET 2. It's a very simple setup.
1) An Access database sits on the host and has occasional edits applied to it.
2) An application on the PDA with a mirror copy of the Access database (filtered data) and also has occasional edits applied
3) When the user puts the PDA in the cradle the databases get automatically synchronized with each other.

Probably couldn't get much simpler than this. This used to be very easy to do with PocketAccess and ActiveSync. Unfortunately this is no longer supported. I decided to use the new SQLce database instead when porting to CF.NET. It's working like a charm except that I haven't found an easy way to synchronize with the desktop Access application.

Merge replication seems to be exactly what I need but it doesn't work with Access or SQL Server Express. I don't need (or want) a full version of SQL Server. The dataset is very small.

ADS seems to point in the right direction except it doesn't support automatic synchronization. It also doesn't support Vista in combination with PPC2003SE.

Sync Services seems to have the same problems as ADS.

I have seen some third party tools around but they all require the user to manually kick off synchronization which I'm desperatly trying to avoid.

I've been searching for days now and I'm not getting any closer to a solution. Any help would be greatly appreciated!

Cheers,
Thomas

Hi Thomas,

You can kick of a process on the PC (from the desktop PC) (your own program), whenever ActiveSync is connected, and then use RAPI to launch a process on the device. You can even add command line parameters to the PDA process.

The activesync registry key is:

[HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Windows CE Services\AutoStartOnConnect]

and opennetcf.org has a .NET RAPI wrapper: http://www.opennetcf.com/library/communication/OpenNETCF.Desktop.Communication.RAPI.CreateProcess.html

|||

Thanks for the reply. This is indeed a step in the right direction.

I guess the idea then would be to use ADS on top of it. So cradling the PDA would create 2 processes, one to start the ADS service and one to start some custom synchronization application on the PDA which in turn would connect to the ADS desktop service using RDA.

Sounds very clunky to me, but it could work. I guess I was hoping there would be an easy solution. I mean, i can't imagine a much simpler scenario and it's not supported out of the box (anymore). Why does Microsoft not support this?

|||

ADS is the replacement technology for CEDB/ADOCE - MS Access sync - as described here: http://blogs.msdn.com/sqlservercompact/archive/2007/01/13/sync-with-access.aspx

It looks like ADS does do automatic sync on desktop connection:

Synchronization happens when there is an active ActiveSync connection between the device and the desktop.

(from http://blogs.msdn.com/sqlservercompact/archive/2007/02/19/microsoft-sql-server-2005-compact-edition-access-database-synchronizer-ads-rtw.aspx)

|||

That's what I thought too. Unfortunately I haven't found any evidence that it actually does so. ADS documentation is skimpy at the moment, but from what I gather you have to create a PDA application that uses RDA to sync yourself. And the only thing it does automatic is start the desktop "listen" service. There is no "client agent". This is not "simple and easy" to do since you have to push and pull data manually (using RDA) and provide your own conflict handling! ADS as it is now is definitely NOT a replacement.

|||

*bump*

Don't tell me that's it?

|||

This MS blog post addresses your question:
http://blogs.msdn.com/sqlservercompact/archive/2007/02/22/ads-automatically-starting-the-synchronization-between-device-and-desktop.aspx

sql

Automatic printing from ReportViewer control

Hi,
I've got a WinForm app written in .NET 2.0 (C#). We have a series of SQL
Reporting Services reports which are on a remote server - not RDLCs. I've
got a ReportViewer control on one of the forms, and am able to render the
reports in the ReportViewer just fine. However, I would like to be able to
print these reports, preferably without displaying any UI at all, through
this app. Does anybody have any suggestions? I realize that RDLCs would
work in this case, but that's not an option, unfortunately.
Thanks, JimOn Jan 4, 6:34 pm, "LiveCycle" <livecy...@.sandbox.etech.adobe.com>
wrote:
> Hi,
> I've got a WinForm app written in .NET 2.0 (C#). We have a series of SQL
> Reporting Services reports which are on a remote server - not RDLCs. I've
> got a ReportViewer control on one of the forms, and am able to render the
> reports in the ReportViewer just fine. However, I would like to be able to
> print these reports, preferably without displaying any UI at all, through
> this app. Does anybody have any suggestions? I realize that RDLCs would
> work in this case, but that's not an option, unfortunately.
> Thanks, Jim
This link should be helpful.
http://forums.asp.net/p/516455/533549.aspx
Regards,
Enrique Martinez
Sr. Software Consultant

Automatic Database Backup

Hi all,

I have written an application in C# .NET which will run on another PC. The database required for the application will be created during the installation. How can I provide that the application backups the database periodically? Can I generate an SQL script which will run at installation and then backup the database automatically?

I really need advices.
Thank you...
BurcuIn Enterprise Manager you can set up automated maintenance procedures for backing up, re indexing, and repairing databases.|||

Quote:

Originally Posted by Motoma

In Enterprise Manager you can set up automated maintenance procedures for backing up, re indexing, and repairing databases.

Thank you. But, what I want to do is to realize it programmatically. I don't want that the user has to set up anything in Enterprise Manager. All the back up procedure must be organized during the installation of my application program.

An idea is to define a scheduled job on server agent by using SQL-DMO library. So, the job will back up the database periodically. But I am not sure if it is the best choice.
Any other ideas?|||

Quote:

Originally Posted by eflatunn

Thank you. But, what I want to do is to realize it programmatically. I don't want that the user has to set up anything in Enterprise Manager. All the back up procedure must be organized during the installation of my application program.

An idea is to define a scheduled job on server agent by using SQL-DMO library. So, the job will back up the database periodically. But I am not sure if it is the best choice.
Any other ideas?

I am sure there is a way to set up SQL Server maintenance plans without the Enterprise Manager IDE. I am sorry, but I do not know how exactly to do this. Your best bet, if you wanted to pursue this, would be to check out the MSDN and look through all of the system stored procedures. After that, you could try the setting up a Schedule with the SQL Profiler running to see if you could find out what the IDE is calling on the server.|||I just found something while digging through the help files. There are four stored procedures that you may be able to use: sp_add_jobschedule sp_delete_jobshedule sp_help_jobschedule and sp_update_jobschedule.|||hai
I also want to know how can i get the backup of a database programmatically
If any one know this please help me|||I included SQL-DMO library in my application. Using SQL-DMO objects, I create a job in SQL Server Agent and assign a schedule so that back up can be done periodically.|||Hope the following helps

backupDir = Directory.GetCurrentDirectory() + "\\DBBackup";
if (!Directory.Exists(backupDir))
{
Directory.CreateDirectory(backupDir);
}
datePart = DateTime.Now.ToString(dateFormat);

backupFileName = backupDir + "\\DBName_" + datePart + "_" + "backup.log";

backupQuery = "use master; if exists ( select 1 from sysdevices where name = 'DBName') exec sp_dropdevice 'DBName'; " +
"exec sp_addumpdevice 'disk', 'DBName', '" + backupFileName + "' ; backup database DBName to DBName";

try
{
//gets osql and runs osql tool to execute the DB scripts
ProcessStartInfo procInfo = new ProcessStartInfo("osql.exe");
// specifies the window style
procInfo.WindowStyle = ProcessWindowStyle.Hidden;
//specifies the arguments for the process
procInfo.Arguments = Common.GetCommonProcessArguments(backupQuery);
//starts the process
Process osql = Process.Start(procInfo);

//waits for all the dbscripts to run.
osql.WaitForExit();
osql.Dispose();
}

private static string GetCommonProcessArguments(string fileName)
{
// string to be passed to osql tool
string result = " -S " + Environment.MachineName + @." -E "+ "-n" + " -Q " + Char.ToString('"') +
fileName + Char.ToString('"') + " -o " + Char.ToString('"') + CurrentPath + "\\DBScriptsLog.txt";

return result;

}

Automatic Data Fill-up

Hello guys!

I am relavtively new to ASP.NET programming ang was just starting out on my first project. I am using ASP.NET2.0 technology by using Visual Web Developer 2005 Express Edition and of course with SQL 2005 Express Edition.

I would like to develop a database for our IP addresses, so one field of my table in a SQL data is the field for IP addresses.

I would like to write a program wherein after clicking the button, that field will be automatically filled up with IP addresses (e.g, from 192.168.0.0 to 192.168.0.255).

How do I accomplish this kind of dynamic filling up of fields? Thanks a lot!

When the client clicks the button ,you can get client ip using the following code:

Dim strClientIPAs StringstrClientIP = Request.UserHostAddress()

Then you can save the strClientIP into your table:

 
Dim connAs New SqlConnection("Data Source=.\yourserver;Database=pubs;Integrated Security=SSPI;")Dim cmdAs New SqlCommand("INSERT INTO yourtable(clientip) SELECT @.clientIP", conn) conn.Open() cmd.Parameters.AddWithValue("@.clientIP",strClientIP)Dim iAs Int32 i = cmd.ExecuteNonQuery() Response.Write(i.ToString() +" row(s) has been written to clientIP") conn.Dispose()
 

Automatic client redirection during the hot failover in .NET 1.1 applications and SQL 2005

Hi,
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.repla ce("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
([url]http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover +.net+1.1&rnum=10&hl=en#cc09960826a31a94)[/url].
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
Michael
Hi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
================================================== ====
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.
================================================== ====

Automatic client redirection during the hot failover in .NET 1.1 applications and SQL 2005

Hi,
We would like to use SQL Server 2005 with our .NET 1.1 web applications.
We would like to leverage the new mirroring enhancements to achieve higher
server availability.
However, I know that .NET 1.1 doesn't support automatic client redirection
during the hot failover
and the only option is to handle the redirection manually in the code. By
saying that I mean the following:
string connectionString="Trusted_Connection=Yes;Data Source=SERVER1;Initial
Catalog=MirrorTest;Integrated Security=SSPI;";
SqlConnection connection=new SqlConnection(connectionString);
try
{
connection.Open();
}
catch
{
connection.ConnectionString=connectionString.replace("Data
Source=SERVER1","Data Source=SERVER2");
connection.Open();
}
I saw that someone has suggested to use the .NET OdbcClient or OleDB client
and have them access the new SQL Native Client library which is
mirroring-aware
(http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_thread/thread/90517df320d22f1/cc09960826a31a94?lnk=st&q=sql+server+2005+Failover+.net+1.1&rnum=10&hl=en#cc09960826a31a94).
So my question is what is the better way to use and how the second way can
be implemented? Does it mean that we need to download SQL Native Client from
SQL
2005 feature pack?
Thank you very much,
Best Regards
MichaelHi Michael,
My understanding of your issue is that:
You would like to know how to use the OLEDB provider, ODBC.NET or ADO with
SQL Native Client so that you can directly configure the connection string
for the database mirroring.
If I have misunderstood, please let me know.
Yes, you need to install SQL Native Client first on your computer before
further actions. SQL Native Client combines the SQL OLD DB provider and the
SQL ODBC driver into one native DLL while also providing new functionality
above and beyond that supplied by the MDAC, so it is easy to be integrated
to old applications.
You may refer to:
SQL Native Client Programming
http://msdn2.microsoft.com/en-us/library/ms130892.aspx
The connection string for database mirroring is "server=Partner_A; failover
partner=Partner_B; database=AdventureWorks". You may refer to:
Making the Initial Connection to a Database Mirroring Session
http://msdn2.microsoft.com/en-us/library/ms366348.aspx
Using Connection String Keywords with SQL Native Client
http://msdn2.microsoft.com/en-us/library/ms130822.aspx
Hope this helps. Please feel free to let me know if you need further
assistance.
Charles Wang
Microsoft Online Community Support
======================================================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.
======================================================

Automated Dataimport

Hi everyone,
i'm new to ado.net and need some help. My problem:
got one DB with real bad tablestructure
and one new DB (structured!)
i need do do an automated import from the old to the new DB, with check if the data row is new or updated.
I try to do it this way-> 2 datasets merged together ->Problem: new entries in destination dataset have row state unchanged
other way i tryed add rows from source to destination with add funktion -> problem no check if data exist and maybe is changed
So how can i get the merge working or how can i implemt an effective funktion to solve this problem.
Thanks in advance!

Is this something you need to do just once, or on an ongoing basis?
|||

I have to do it aprox. once per week.
My idea is to write my own merge function. But if there is a faster and simplier way please show me!
Thx!

|||I'd do this completely in SQL Server, with a stored procedure, and use SQL Server Agent to schedule it to run once/week.
The query in the stored procedure would look something like this:
IF NOT EXISTS(SELECT * FROM NewTable WHERE NewTable.Key = OldTable.Key)
BEGIN
INSERT INTO
NewTable
(
Column1,
Column2,
Column3
)
SELECT
Column1,
Column2,
Column3
FROM
OldTable
END
ELSE
UPDATE
NewTable
SET
NewTable.Column1 = OldTable.Column1,
NewTable.Column2 = OldTable.Column2,
NewTable.Column3 = OldTable.Column3,
NewTable.DateModified = GETDATE()
FROM
NewTable
INNER JOIN
OldTable ON NewTable.Key = OldTable.Key
WHERE
NewTable.Column1 <> OldTable.Column1 OR
NewTable.Column2 <> OldTable.Column2 OR
NewTable.Column3 <> OldTable.Column3



|||Thx for your help,
i think thats the better solution. Last Question i have, what is the code to call an other Database on the same Server with Stored Procedures.
|||

Iso wrote:

what is the code to call an other Database on the same Server with Stored Procedures.

Use the 3-part qualified name: database.owner.table. For example:
yourDatabase.dbo.OldTable

Automated client report rendering

My company would like to write an app that does on-demand rendering of
certain reports. Is there a .NET client report rendering library that
is not dependent on IIS (as the SOAP/URL apis are), nor WinForms (as
the ReportViewer is)?
Thanks,
EvaAfter some research I've found one third-party product called the RDL
Project that renders RDL into HTML, PDF, and some other formats. It's
found at http://www.fyiReporting.com. I played with it, and its version
1.0.1 can't yet parse a report that I built in VS2005, although I think
it could if I removed a few unsupported things from the report and if I
studied its code a bit more (documentation seems lacking).
Hope this post helps someone else, and of course if you know of any
other options please let me know!

AUTO_CREATE_STATISTICS & AUTO_UPDATE_STATISTICS are Null

What is the real net affect of these 2 parameters set to NULL?

How can I tell if stats have been generated for all tables in a db?

I am guessing that stats don't exist for the db and this accounts for the high degree of scans I am seeing on a 1.5gb db running on a 4 gb memory machine.

Any thoughts?
MikeThe net effect is that you have to create the stats your self and also update then on a timely basis.

Personnaly would turn them both on. Some people would argue to turn on the create but do the update on a scheduled basis, the thought is that you can get by with slightly out of date stats rather than take the performace hit to auto update.|||so the bottom line is the optimizer will not have any information on the tables and therefore do scans to find the data?

Is this an accurate description?

Mike|||basically, Yes.|||and what is the most expedient mthod of generating the stats for all objects?|||BTW, once you create statistics, you should recompile all views, triggers, stored procs and re-build all indexes starting with any clustered indexes.|||turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.|||Originally posted by Paul Young
turn AUTO_CREATE_STASTISTICS & AUTO_UPDATE_STATISTICS on.

I just found out each weekend the indexes get rebuilt. Dropped and recreated. I read that whenever an index is created on a table containing data the optimizer collects stats and stores them. Does that still occur when these options are not on?

Mike|||Still recommend to turn those two options on. The major purpose of rebuilding index is not for updating statistics. Even if rebuild index will
update statistics, but the statistics may not be updated during the week.

BTW, never recreate index by drop-and-create strategy. Try to use DBCC DBREINDEX, or CREATE INDEX with DROP_EXISTING, or DBCC INDEXDEFAG.

Auto update to sql server tables

I'm changing data storage for an asp.net project from MS Access to Sql Server. I've got the web site working, but I need to update the sql server tables with data from our Oracle db daily. What is the "best" way to do that?

I've read about DTS, but have never done anything like that. Would it be worth the time and effort to study? (So far I've created a package, with the import wizard, that doesn't work & I don't have the authority to delete :-)

I know I could create a dataset with my Oracle data and use that to update sql server. But is there a way to schedule an aspx to run authomatically? Would this affect performance? The sql server db isn't very big (30-40,000 records), but the Oracle db is & I need to do quite a bit of manipulation to the data.

This is new to me & I'm don't know what I should be searching for to find help. And if there is a more appropriate place to post this question, please let me know.

Thanks.Yes. DTS is one good way to go and it is very easy to do.

Do you need to purge the data in your SQL before each load? Then the account has to have the right.

You can schedule the DTS to run at whatever time you want.

auto incrementing->Updating values in two related tables :Help!

hi there,
i am new to sql server database.i am doing small projects rightnow using asp.net and sql to create webpages (very basic webpages)
My problem is:
Problem :

i have two tables ....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course .
table1 student id is the primary key refrencing table 2 student id.
Now i delete a record in table 1 which will in turn also get deleted intable 2 . so for eg if i have three records 1 ,2 and 3 ...then idelete 2 in table 1 ...i will have 1 and 3 in both table 1 and table2...now i want 3 to become 2 in both table 1 and table 2...so that idont have empty space between two student id's 1 and 3. so this is myproblem...if any one can help me out with suggestions pleasedo.
thank you all......
ahmed_ind
if you want my advice ... this is not a good solution or methodto follow .... you should not really care about the space ...once you create ID for a record and delete it, you should notreally care about it to have stabled data not to have confliced IDs.
if your concerns about the ID number that you will reach in 1 year ..you can avoid that when you create the ID columns and assign the datatype that has the range for what you know it will enough for you...... if i were you i will not bother myself with what are youlooking for.
By the way this is my idea from my experience and what database expertsthink..... but if some one has different opinion and beleive there isa better technique ... you can advise !!
|||hi fadil.....thanx for u r suggestion.
i wanted to have automation of operation at the background ; i.e. in the database (sql ) ...so i used identity function toauto-increment values of student id by one ....then i wanted to deleteby performing auto-decrement function.....so thats how i camewith the idea, i jus wanted to learn tough things...ok any way thanxagain for ur suggestion and if u have any way of solution for myquestion please do reply..thank u..
ahmed
|||look at this case and see what i mean
ID Name
1 Ahmed
2 John
3 Ali

if you deleted 'Ali' the decrement would be ok as it is the last one on the list but if you delete 'Ahmed' you will loose the primar key integritiy for John and Ali as they will be 1 and 2.....i beleive this is not a good methodlogy to do... you might injure your data !!|||hi fadil,
i get u...i understand what umean...ok then my logic is not a good one then...may be i have to trysomething else than auto decrementing.....
thanxanyway for the suggestions : i will take them and follow them now andin my future...

............ahmed

Auto Incrementing field

I have a asp.net form which is for orders with a field for numbers in it.
What I want is when a user raises a new order the Order number field will
be populated automatically with the next order number. I want this order
number to be incremented on the database table. The order number is of the
form 05/001 and when the next order is raised it will be 05/002. 05 is the
year.
Anyone any ideas how to do this.
Thanks.
Message posted via http://www.webservertalk.comHi
I'd recommend you to create a table with the following structure
CREATE TABLE Orders
(
OrderID INT NOT NULL PRIMARY KEY,
OrderDate DATETIME
)
Now you can easily to extract the order number along its orderdate.
"macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
> I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||here OrderID can me an IDENTITY column.
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"Uri Dimant" wrote:

> Hi
> I'd recommend you to create a table with the following structure
> CREATE TABLE Orders
> (
> OrderID INT NOT NULL PRIMARY KEY,
> OrderDate DATETIME
> )
> Now you can easily to extract the order number along its orderdate.
>
> "macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
> news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
>
>|||Hi,
Look into the identity property in books online.
Thanks
Hari
SQL Server MVP
"macca via webservertalk.com" <forum@.nospam.webservertalk.com> wrote in message
news:cba14b869c534870bd3670722cefac4d@.SQ
webservertalk.com...
>I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com|||Why?
"Chandra" <chandra@.discussions.microsoft.com> wrote in message
news:F05619B5-D96F-405E-B385-466995B09A71@.microsoft.com...
> here OrderID can me an IDENTITY column.
> --
> best Regards,
> Chandra
> http://chanduas.blogspot.com/
> http://groups.msn.com/SQLResource/
> ---
>
> "Uri Dimant" wrote:
>
message
it.
will
order
the
the|||Although it has been suggested, an Identity column would not satisfy your
requirement of "YY/SSS".
I would recommend a stored procedure:
PROCEDURE [Create New Order] (@.Order_Num CHAR(5) OUT)
The procedure would generate a new Order_Num, insert a row into your Orders
table (and whatever else tables), and return the order number as an output
param.
If the Order_Num needs to be known when an order is first created, run the
procedure first, fill in your field, and set the Order_Status to incomplete.
When the submit the order, change the status.
Alex Papadimoulis
http://weblogs.asp.net/Alex_Papadimoulis
"macca via webservertalk.com" wrote:

> I have a asp.net form which is for orders with a field for numbers in it.
> What I want is when a user raises a new order the Order number field will
> be populated automatically with the next order number. I want this order
> number to be incremented on the database table. The order number is of the
> form 05/001 and when the next order is raised it will be 05/002. 05 is the
> year.
> Anyone any ideas how to do this.
> Thanks.
> --
> Message posted via http://www.webservertalk.com
>|||I think this may cause concurency issue.
You can use an IDENTITY column plus another YEAR column (varchar).
whenever you show it to user, you just need to concat these 2 columns.
However, since YY is always current year, you can choose not to store this
value unless this column can be changed in the future.
Hope this may help.
Thanks.
Leo Leong
"Alex Papadimoulis" wrote:
> Although it has been suggested, an Identity column would not satisfy your
> requirement of "YY/SSS".
> I would recommend a stored procedure:
> PROCEDURE [Create New Order] (@.Order_Num CHAR(5) OUT)
> The procedure would generate a new Order_Num, insert a row into your Order
s
> table (and whatever else tables), and return the order number as an output
> param.
> If the Order_Num needs to be known when an order is first created, run the
> procedure first, fill in your field, and set the Order_Status to incomplet
e.
> When the submit the order, change the status.
> --
> Alex Papadimoulis
> http://weblogs.asp.net/Alex_Papadimoulis
>
> "macca via webservertalk.com" wrote:
>

auto identity for each Type

Hello,

I am working on an accounting system using VB.NET and sql server 2005 as a database. the application should be used by multiple users.
i have a the following structure:
Voucher: ID (primary), Date,TypeID, ReferenceCode, ....
Type: ID, Code, Name. (the user can add new type anytime!)
(Ex: PV- payment voucher, JV - Journal Voucher ,...)

When adding a voucher the user will choose a type, according to this type (for each year) a counter will be increminted.
for example: PV1, PV2...PV233,... the other type will have its separate counter JV1, JV2 ,...JV4569,..
I am using the sqlTransaction cause i am doing other operations that should be transactional with the insertion of the Voucher.

The question is :
What is the best solution to generate a counter for each type?(With code sample)

Thanks.do you really need to have the 'PV' and 'JV' before each value? if you could use ints, then you could use identity columns. That's the standard way of doing this.

You can always tack on a JV or PV in the front end if that's the way your boss wants it to look in a report or something.

from BOL:

IDENTITY

Indicates that the new column is an identity column. When a new row is added to the table, Microsoft® SQL Server™ provides a unique, incremental value for the column. Identity columns are commonly used in conjunction with PRIMARY KEY constraints to serve as the unique row identifier for the table. The IDENTITY property can be assigned to tinyint, smallint, int, bigint, decimal(p,0), or numeric(p,0) columns. Only one identity column can be created per table. Bound defaults and DEFAULT constraints cannot be used with an identity column. You must specify both the seed and increment or neither. If neither is specified, the default is (1,1).|||if you were using mysql, this functionality (starting a new auto_increment within each type group) is built in

it's impossible to do this with an IDENTITY column

you will have to generate your own numbers, and i would recommend very strongly against it|||the counter in the question is the ReferenceCode in the Voucher table
Voucher: ID (primary), Date,TypeID, ReferenceCode.
so for each added voucher and according to the TypeID a the reference code will be generated. let say the last counter for the PV type is 230 so the referenceCode will be PV231. if the Type is JV and the last counter is 566 then the ReferenceCode will be JV567 and so on.
We don't have to forget that we are working in a multi user enviroment, and the Reference Code should be unique .|||put the JV or PV in another field and concatenate it in the front end. smart numbers are stupid and loved by the accounting types. this kind of things slow down joins and causes other kinds of pain. i have not seen smart numbers in a project for five years and that was a legacy foxpro app.

Auto generate IDs in MS SQL SERVER 2005

Hello,

I m creating forms in ASP.Net 2005 using C# language.

I'musing Microsoft SQL Server 2005 and my IDs are in A001, A002, A003...and so on. Howcan I auto generate this IDs? Like A001 +1=A002? Please help...

In SQL server 2005 which datatype i should select and how can i code in ASP.NET with C#??

On button click event the data is inserted and been shown onthe grid..

Thanks

identity columns use only a few datatypes such as the numeric operators (tinyint, int etc.), and GUID (for unique id's). AFAIK, you can't generate ID's in that form. I would recommend that you either use an integer column, and create a combined field e.g. "select 'A' + nameofidcolumn as businessid" , or else don't use identity columns and write the value for your required id into the table directly via custom code.

Cathal