Database

Re: SqlHierarchyId insert [resolved]

10-Sep-09 18:37

What is the syntax for using a sQLParameter to insert a SqlHierarchyId. I'm stuck with the datatype to apply to the parameter object as I can't seem to get anything to support the SqlHierarchyId type. And my google mojo has completely let me down.

List<SqlParameter> lParams = new List<SqlParameter>();
SqlParameter oParam = new SqlParameter();
oParam.ParameterName = "@NodeKey";
oParam.Value = this.NodeKey;
oParam.SqlValue = this.NodeKey;
oParam.DbType = SqlHierarchyId;

Mycroft Holmes10-Sep-09 19:49

10-Sep-09 19:49

By using a varchar datatype for the insert parameter. It seems there is an implicit conversion from varchar to hierarchyid in SQL Server 2008

oParam = new SqlParameter();
oParam.ParameterName = "@NodeKey";
oParam.DbType.Equals(SqlDbType.VarChar);
oParam.Value = this.NodeKey.ToString();
oCmd.Parameters.Add(oParam);

Mass Insert

zeusToronto10-Sep-09 17:04

zeusToronto

10-Sep-09 17:04

Hi,
I am trying to help my IT department by developing a simple vb.net app where I am going through EventLog on XP and Vista Boxes and uploading all EventEntries to a db hosted on MSSQL-Express.

The executable sits that sits on a client PC and is compiled as a console app that gets fired up through scheduled task.

My app attempts to insert new records for every record found in EventLog on each PC on the network to db at around noon (noon is the time when scheduled task fires up my app.). I have about 50PCs on the network and I use Procedure to insert new records.

My question is, how many simultaneous connections MSSQL-EXPRESS can handle and what happens when I attempt to insert into the same table from more than 1 client.

What would you recommend if I needed to increase number of workstations that upload to DB.

Appretiate your help and advise.

Mycroft Holmes10-Sep-09 18:31

10-Sep-09 18:31

I don't know the format for the log files but would assume either fixed length or hopefully delimited.

I would use Bulkcopy (I assume express supports it) if the files are delimited.

Read all the log into a datatable in the client, bulkcopy the datatable to the database, run the process. I would expect the ETL to take less than 1 minute so would schedule the systems to fire at i minute intervals (this will be a PITA so have the client app use an identifier to decide the order and have the client wait #n minutes).

Another option, have your clients send the log files to a centralised folder. Another app (SSIS maybe) reads all files from the folder and loads then. This allows you to use BCP (even faster and supports format files for fixed length) and there is no possibility of a clash caused by multiple loaders.

Oh, SSIS is probably not in SQLExpress and BCP may not be supported.

PIEBALDconsult14-Sep-09 7:19

PIEBALDconsult

14-Sep-09 7:19

I have Sql Server 2008 Express with Advanced Services[^] installed on this system and it does have BCP. I've never used SSIS so I don't know.

David Skelly10-Sep-09 22:52

David Skelly

10-Sep-09 22:52

A comparison of the various editions of SQL Server is given here:

http://www.microsoft.com/Sqlserver/2005/en/us/compare-features.aspx[^]

There is no limit to the number of connections for SQL Express, other than the limits imposed by the restriction to a single CPU and 1GB of RAM.

As far as inserting into the same table from more than 1 client goes, SQL Express is no different from any other database. Depending on what your application is doing (insert only or are any reads or updates involved?), the transaction scope you use (do you insert each row in its own transaction, or do you block the whole lot into one big transaction?) and what isolation level you are using (the default - read committed - sounds as if it should be OK for your application if it's insert only), you may or may not see blocking occurring between the clients.

What do you do if SQL Express tops out and can't cope with the load? Well, one answer is: put your hand in your pocket and buy a full version of SQL Server. If you don't have the budget for that, you could either look to use one of the free databases that are available (MySQL, Firebird, PostgreSQL, SAPDB, there are loads of them around) or you could think laterally. Don't run all the scheduled jobs at noon, stagger them through the day. Or don't write directly into the main database, write each clients' log into its own staging area then have a single central job which comes round and collates it all together later. Think of it as a chance to get creative and flex your architectural design skills.

Henry Minute11-Sep-09 10:32

Henry Minute

11-Sep-09 10:32

You might5 find this[^] article on BatchSize useful.

help writing query

michaelgr110-Sep-09 9:08

michaelgr1

10-Sep-09 9:08

Hello,
I have for example the following table

NICKNAME JOBNAME EQUIPNAME STARTTIME
SNB_A0_01_0719 051_A0_marbles_stress_AB_tie2vss post -FIB testing 2009-09-10 11:33:46.880
SNB_A0_01_0719 051_A0_marbles_stress_AB_tie2vss Post-FIB Testing Cabinet0 IS 2009-09-10 11:33:19.203
SNB_A0_01_0719 051_A0_marbles_stress_AB_tie2vss TESTING Cabinet 2009-09-10 11:34:13.233
SNB_A0_01_0720 051_A0_marbles_stress_AB_tie2vss post -FIB testing 2009-09-10 12:11:24.790
SNB_A0_01_0720 051_A0_marbles_stress_AB_tie2vss Post-FIB Testing Cabinet0 IS 2009-09-10 12:10:39.340
SNB_A0_01_0720 051_A0_marbles_stress_AB_tie2vss Pre-FIB Testing Cabinet0 IS 2009-09-10 07:58:20.937
SNB_A0_01_0720 051_A0_marbles_stress_AB_tie2vss TESTING Cabinet 2009-09-10 12:11:43.603
SNB_A0_01_0721 052_A0_marbles_stress_A_tie2vss Pre FIB Testing 2009-09-10 11:13:59.960
SNB_A0_01_0721 052_A0_marbles_stress_A_tie2vss Pre-FIB Testing Cabinet0 IS 2009-09-10 09:28:10.480
SNB_A0_01_0722 053_A0_marbles_stress_b_tie2vss Pre FIB Testing 2009-09-10 10:33:12.183
SNB_A0_01_0722 053_A0_marbles_stress_b_tie2vss Pre-FIB Testing Cabinet0 IS 2009-09-10 10:23:06.260
SNB_A1_00_0718 050_A1_DDR_Rx_Delay_pp Pre-FIB Testing Cabinet0 IS 2009-09-10 06:34:43.330

I need to choose distinct nicknames,jobnames and equipnames where the starttime is the biggest
for example for nickname SNB_A0_01_0719 i need only the 3rd row.

How can i do it?

Re: help writing query

Mycroft Holmes10-Sep-09 14:45

Re: help writing query [modified]

10-Sep-09 14:45

Use a CTE with a Row_Number() partitioned over nickname, ordered by starttime desc with the appropriate where filter. From that select the record with row_number() = 3

Johan Hakkesteegt10-Sep-09 23:30

Johan Hakkesteegt

10-Sep-09 23:30

Or:

SELECT * FROM TEST_TABLE
WHERE STARTTIME IN (SELECT MAX(STARTTIME)
FROM TEST_TABLE
GROUP BY NICKNAME, JOBNAME)

My advice is free, and you may get what you paid for.

modified on Friday, September 11, 2009 8:04 AM

getting date less than 3 yr.s old

dotnetcsharpdev10-Sep-09 0:22

dotnetcsharpdev

10-Sep-09 0:22

select myDate
from myTable
where myDate > datediff(yyyy,now,myDate)=> 3)

I want to select date which is greater that now - 3 years(less than 3 yr.s old)
above query gives me following error:-

Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '=' Sigh | :sigh:

Re: getting date less than 3 yr.s old

dotnetcsharpdev10-Sep-09 0:57

dotnetcsharpdev

10-Sep-09 0:57

got it

Posting it here if it might help anyone in future !!..

select myDate
from myTable
where datediff (yyyy,myDate ,getdate()) <= 3

Re: getting date less than 3 yr.s old

zeusToronto10-Sep-09 17:14

zeusToronto

10-Sep-09 17:14

select * from yourtablename
where
DateDiff(year, myDate, getdate()) >= 3

Database Serive stops responding on SQL Express 2005.

Aman786Singh9-Sep-09 22:35

Aman786Singh

9-Sep-09 22:35

Hi,

I have installed a SQL Server Express 2005 instance with default name SQLExpress on a machine running Windows 2003 Server.
I have two databases on this instance with size 2GB and 1 GB(approximately) on this insance. These databases are accessed by a(Web based) .Net Application which is used by a maximum of 50 concurrent users.The application is a CRM for ticketing with Reports on the data punched by users.
My problem is that some times the SQL Server Express stops giving any connections. If I go and check the Database Engine service status in SQL Server Surface Area Configuration tool it shows the serive as running. As a hack I stop the service and start it again and SQL Server starts givgig connections again.
I have similar setup running in another location and that never gives such kind of problem.

Please help me with this problem as any downtime on Database is questioned from us.

thanks & regards,
Aman

Re: Database Serive stops responding on SQL Express 2005.

Abhishek Sur10-Sep-09 11:53

Abhishek Sur

10-Sep-09 11:53

I think this might be the cause of Unclosed connections. Always use
using block and also Before you leaving the block use connection.close(). Wink | ;)

And hey, you are using SQL Server Express 2005 in production environment.. OMG | :OMG:

This is weird. You need to keep in mind of the limitation of 4 GB Database size and 1 GB of RAM Usage. Sigh | :sigh:

There is as such no limitation of Concurrent Connection, but if you go on creating connections, it will soon run out of resources. Also you cant include 2 -4 GB ram because of Limitations. Frown | :(

Abhishek Sur

My Latest Articles
Create CLR objects in SQL Server 2005
C# Uncommon Keywords
Read/Write Excel using OleDB
Don't forget to click "Good Answer" if you like to.

Offsite databases - setup recommendations

Simon P Stevens8-Sep-09 22:54

Simon P Stevens

8-Sep-09 22:54

We currently have a system with a central database. (SQL server 2000).

Around the company we have several applications that read/write to the database. The company is distributed across several sites. Currently the database is at site A. There are two sites B and C that have leased lines to connect their networks to site A. From a computer point of view, it's all one big domain. Sites A, B and C are all in the same city (within about 15 miles of each other).

We are about to expand our network to a location in Dubai (Site D).

What is the best way to achieve the database connections for the application?

Would I be best to look at still having one central database and everything connecting to that (Via something like VPN or whatever), or should I be considering having a cloned/replicated database for the Dubai offices that is somehow synced up after hours? Do I need to be looking at the reliability of the internet connection in Dubai?

Can anyone with experience of this kind of thing offer any guidance?

I'm a software developer, and we have IT/network admins who will be doing the actual networking, but I need to be thinking about where/how I want my databases set-up.

Simon

J4amieC9-Sep-09 2:15

J4amieC

9-Sep-09 2:15

This is a bit of a can of worms... If I was sitting down to design the architecture for a distrubuted application like this from scratch, i'd be very tempted to expose the data as services (web/wcf/whatever) and have applications at each site use these services.

However, it seems you're doing a straight database connection from each site to the central database - which is fine, but as you move further away from the database you have network latency and connectivity issues. Which is I think what you're asking about.

In terms of a solution - your suggestion of replicated database for Dubai is one way of solving the problem. You could also accept that the connection from any site to the database may slow down/fail and make your applications resilient to this (they should already be!). Perhaps think about hosting the database at a highly reliable site, with a fast connection - such as one of the big hosted data centres.

Simon P Stevens9-Sep-09 3:26

Simon P Stevens

9-Sep-09 3:26

Thanks J4amieC,

With regards to data services yes I kind of agree here, given a choice from scratch I think I'd probably go down the same route, unfortunately this is an existing system that has grown beyond it's original intentions (as is so common with these kind of things) and I don't think a re-architecture like this is really an option - although it does have a nicely separated DAL so the option is there. We are actually building another system in a distributed style like this for exactly these kind of reasons.

Yes I am kind of asking about latency & connectivity etc. The application currently has an online and offline mode and falls back silently into offline mode if any database connection errors occur, and attempts to reconnect every 15 minutes, so the system is already fairly resilient to that kind of outage.

I'm also asking if there is anything else I should be aware of with the above approach.

Finally I'm interested if anyone has any experience with a replicated database. It's not something I've ever tried, and I'd have concerns that there would be problems, but as I understand it there are features like this that are transparently built into SQL server so I could just configure it and then carry on as if it was just a normal local database. I'd be concerned about how it would handle conflicts and locks.

Simon

Johan Hakkesteegt10-Sep-09 0:10

Johan Hakkesteegt

10-Sep-09 0:10

Hi,

Just thinking out loud, mind you, so for what it is worth:

The main question is, what sort of data would you need at each site, at the same time, that must be in absolute real time sync?

The different sites of your company have been established for a reason, and I suppose it is not because all the people just won't fit into a single building. So I am guessing each site has its own customers, or vendors, or specialized work crew, or warehouse, or something.
That means that although each site will be organized with the same application(s), each will still mostly be working with unique data.
In other words, each site will be creating its own orders, deliveries, invoices, or whatever other business objects, that are specifically related to that site.

So adding site identifiers to all business objects in your database, should allow you to have each site run its own server/database/copy of the application. You can keep a central database at a single location that is updated every night (or whenever), through synchronization, or even simpler, using xml files. This central database could send certain common master data to each site's own database in turn, every night, hour, 15 minutes, etc. depending on requirements.
Usually even things like warehouse stock levels needn't be more up to date then 5 minutes.

This approach shouldn't need any kind of drastic measures, and might even offer greater flexibility for future development.

Cheers,

Johan

My advice is free, and you may get what you paid for.