Best solution, iterate over millions records and call extended

In fact, the functionarity needs to be available on the server. So I will
created a program, maybe C# or C++ program to do the looping and calculate
and put the executible on the server so it can be launched via xp_cmdshell..
.
It should be better than big TSQL cursor?
"JT" wrote:

> If you have a situation that calls for looping through a cursor, then it's
> better to implement the cursor on the client side than on the server. Open
a
> read-only, forward only ADO recordset and Command.Execute the stored
> procedure for each row.
> "nick" <nick@.discussions.microsoft.com> wrote in message
> news:75FAE6C1-EEC1-4D8B-A3A2-073F10CD859E@.microsoft.com...
>
>In fact, my question is
Transact-SQL script "declare cursor" (fast forward) vs. Client side ADO code
with fast forward server cursor
Which one is better for very large rows?
"nick" wrote:
> In fact, the functionarity needs to be available on the server. So I will
> created a program, maybe C# or C++ program to do the looping and calculate
> and put the executible on the server so it can be launched via xp_cmdshell
..
> It should be better than big TSQL cursor?
> "JT" wrote:
>

best provider for connection to SQL ?

Hi ,
any one can suggest me wich is the best provider connection I can use
for my program to connect to an MSSQL 2000, currently I use the
"Microsoft ADO OLEDB for SQL" .
Thanks,
Paolol
OLEDB will give you more functionality than ODBC so that's a
good choice. You want to look at the latest (or at least one
of the later ones) version of MDAC to have more stability.
-Sue
On Fri, 21 Oct 2005 16:18:00 +0200, paolol
<paolol@._nospamma_salsan.net> wrote:

>Hi ,
>any one can suggest me wich is the best provider connection I can use
>for my program to connect to an MSSQL 2000, currently I use the
>"Microsoft ADO OLEDB for SQL" .
>Thanks,
>Paolol

best provider for connection to SQL ?

Hi ,
any one can suggest me wich is the best provider connection I can use
for my program to connect to an MSSQL 2000, currently I use the
"Microsoft ADO OLEDB for SQL" .
Thanks,
PaololOLEDB will give you more functionality than ODBC so that's a
good choice. You want to look at the latest (or at least one
of the later ones) version of MDAC to have more stability.
-Sue
On Fri, 21 Oct 2005 16:18:00 +0200, paolol
<paolol@._nospamma_salsan.net> wrote:

>Hi ,
>any one can suggest me wich is the best provider connection I can use
>for my program to connect to an MSSQL 2000, currently I use the
>"Microsoft ADO OLEDB for SQL" .
>Thanks,
>Paololsql

Best program for SQL database manipulation

Hello All

I am a relative beginner to SQL databases & new to this forum, so please bear with me if my query is too basic and advise if this question belongs somewhere else

I began working at a company that uses a program that stores data in an SQL database running off a Firebird engine

The program itself doesnt come with database management/administration module, so I'll need to use an external program for manipulating data in the tables that the database contains.

I have relatively little knowledge in SQL programming, which is why I would like to know which is the most powerful program for SQL database updating / manipulation?

This database has tables that has an infinite number of joins with other tables - Even MS Access wasnt able to open a few tables in this database because of the number of joins. I have tried Access & Lotus Approach, Approach manages to do a better job than Access, it open the tables & seems like it will let me import external data directly into the SQL table, but takes forever & usually just bums out giving an error after a very long wait..

My question is - Apart from Access & Approach, are there any more powerful, yet user friendly programs out there that can help me update data directly into SQL tables? What options do I have - the tasks I need to perform are pretty simple updating & cleaning of data already in there

Please, I will hugely appreciate any pointers that you guys the experts might have for me in this regard

Thanks
AlexTry Microsoft SQL Server 2005 Express. It's Free & Downloadable From http://msdn.microsoft.com/vstudio/express/sql/sql

Best Practice for MSDE User permissions

Hi All
I am new to MSDE/SQL Server and need some guidance on best practices for
user permissions.
I have a VB6 program running in a bakery factory
The computer network is a peer to peer 3 computer network running WIndowsXP
MSDE runs on computer A and the data entry person runs my program on this
machine to enter daily orders for their customers
A manager needs to access the MSDE data from another computer for reporting
tasks and is not allowed to enter or modify data
I am sure I can't use Windows authentication as it is only a peer network.
Is this correct?
Should I create a new Login and set individual permissions on each table or
is it OK to use the sa account etc?
Any ideas appreciated
Regards
Steve> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
Windows authentication is problematic when you have multiple computers
without a domain. It is possible by mapping a drive on the client to the
SQL Server using a local server account but this is a kluge.

> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
I suggest you use SQL authentication and assign permissions to roles. You
can prompt for the user's SQL login and password during application at
startup. Never use the 'sa' login for routine application access.
USE MyDatabase
--setup role-based security
EXEC sp_addrole 'Manager'
EXEC sp_addrole 'Clerk'
GRANT SELECT ON MyTable TO Manager
GRANT SELECT, INSERT, UPDATE, DELETE ON MyOtherTable TO Manager
GRANT SELECT ON MyOtherTable TO Clerk
--create login for managers
EXEC sp_addlogin 'SomeManager', 'SomeManagerPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeManager'
EXEC sp_addrolemember 'Manager', 'SomeManager'
--create login for clerks
EXEC sp_addlogin 'SomeClerk', 'SomeClerkPassword', 'MyDatabase'
EXEC sp_grantdbaccess 'SomeClerk'
EXEC sp_addrolemember 'Clerk', 'SomeClerk'
Hope this helps.
Dan Guzman
SQL Server MVP
"Steve" <Steve@.discussions.microsoft.com> wrote in message
news:6DE28FBB-20B4-4637-BC82-7BDDD9FDA62B@.microsoft.com...
> Hi All
> I am new to MSDE/SQL Server and need some guidance on best practices for
> user permissions.
> I have a VB6 program running in a bakery factory
> The computer network is a peer to peer 3 computer network running
> WIndowsXP
> MSDE runs on computer A and the data entry person runs my program on this
> machine to enter daily orders for their customers
> A manager needs to access the MSDE data from another computer for
> reporting
> tasks and is not allowed to enter or modify data
> I am sure I can't use Windows authentication as it is only a peer network.
> Is this correct?
> Should I create a new Login and set individual permissions on each table
> or
> is it OK to use the sa account etc?
> Any ideas appreciated
> --
> Regards
> Steve

Best license program

I have a small company and i make online ERP software in ASP.NET for small
companies. Companies with one server en a maximum of 10 concurrent users.
What SQL server license should i advice for these companies? And how much
does it costs?
I've tried to understand the documents on the microsoft site but i did not
find an satisfying answer.
Thanks in advance.
RonaldIf your database won't be bigger than 2 GB, you can use MSDE which is free.
Otherwise the best is to use Standard Edition with per seat licensing if
your client don't have more than 25 users (not concurrent users, you need a
license for everyone who uses the server), or Standard Edition with a
processor license if there are more than 25 users.
--
Jacco Schalkwijk
SQL Server MVP
"Sandeman" <ilighters@.zeelandnet.nl> wrote in message
news:OAiA2bYlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I have a small company and i make online ERP software in ASP.NET for small
> companies. Companies with one server en a maximum of 10 concurrent users.
> What SQL server license should i advice for these companies? And how much
> does it costs?
> I've tried to understand the documents on the microsoft site but i did not
> find an satisfying answer.
> Thanks in advance.
> Ronald
>

Best license program

I have a small company and i make online ERP software in ASP.NET for small
companies. Companies with one server en a maximum of 10 concurrent users.
What SQL server license should i advice for these companies? And how much
does it costs?
I've tried to understand the documents on the microsoft site but i did not
find an satisfying answer.
Thanks in advance.
Ronald
If your database won't be bigger than 2 GB, you can use MSDE which is free.
Otherwise the best is to use Standard Edition with per seat licensing if
your client don't have more than 25 users (not concurrent users, you need a
license for everyone who uses the server), or Standard Edition with a
processor license if there are more than 25 users.
Jacco Schalkwijk
SQL Server MVP
"Sandeman" <ilighters@.zeelandnet.nl> wrote in message
news:OAiA2bYlEHA.3452@.TK2MSFTNGP15.phx.gbl...
>I have a small company and i make online ERP software in ASP.NET for small
> companies. Companies with one server en a maximum of 10 concurrent users.
> What SQL server license should i advice for these companies? And how much
> does it costs?
> I've tried to understand the documents on the microsoft site but i did not
> find an satisfying answer.
> Thanks in advance.
> Ronald
>

being able to stop parallelism in a C++ Program calling SQL Server

Hi All,

I have written ETL software that runs on SQL Server. We are running it for the first time on a 4cpu (2 x dual core) machine on sql server 2005.

One of the things this software does is perform a 'select * from tablename' to validate that the tables passed to it as parameters exist. This has worked fine on previous releases and on single cpu machines because what the optimiser decides to do is to return just the first page of data and then fetch more. I guess it even works in 2005 standard edition.

However, 2005 enterprise edition allows parallelism. And what the optimiser is deciding to do with such a query is to parallelise it and fetch all rows and then give the result back to the program. So, instead of seeing a fraction of a second to return the first page of data we are seeing up to 90 seconds and the database goes and fetches 15M rows in parallel.

Obviously, what we would like to do is to somehow tell the optimiser that this set of programs should not perform any parallel queries. Or, we would like to turn parallelism off on the specific tables we are dealing with for the period of running these ETL programs....they have no need of parallel processing at the database level for virtually all the calls that are performed.

Would someone please be so kind as to advise us if we can do something like pass a parameter to ODBC to stop parallelism or if we can issue commands against specific tables to stop parallelism for a period and then turn it back on?

Thanks in Advance.

Peter Nolan

www.peternolan.com

select * from tablename OPTION (MAXDOP 1)

That should do it. MAXDOP stands for Max Degree of Parallelism, and allows you to limit the number of parallel execution threads for a query. Setting it to 1 essentially disables parallelism for that query.
|||

Hi David,

thanks very much for that....I did not know the syntax of how to do this at query level....

We turned off parallelism at the server level and it turns out the optimiser will still perform a table scan....we have many logical tables in on physical table and even when we query the underlying table with a select * from with a where clause it is scanning the table and taking about 40 seconds to do so....even when we say 'top 1' to make it get the first row and put an index on the field that logically partitions the table.

It looks like we will have to introduce partitioning so that the optimiser can know to scan just the partition in question. We also had a feature we planned to introduce which was to turn off table validation....if an etl subsystem is in production there is little need to validate tables....we never put it in...and now there seems like a need to do so.

Again, thanks for the tip. Much appreciated.

Peter

www.peternolan.com

|||

Hi All,

one of our guys here came up with a better idea....

What the tool is doing is validating the existence of the taget table/views passed to the program....so in the case of a typo a specific message saying the table/view not found is issued.

However the optimiser is thinking the program really wants all the rows.

One of the guys here suggested if the constraint where 1 = 0 is added to the select * then all optimisers will be smart enough to know no rows will be returned yet the database will still check that the table/view exists.

So we will also implement this test and that will make sure all programs test for existence of a table quickly.

If we feel industrious one day we can actually ask ODBC if the table exists.

One reason why I like the select * from tablename as a test for existence of a table over just asking ODBC is that it forces the preparation of the plan and if the view has been invalidated then it will also be caught at this stage of processing where ODBC will return a positive answer to does the view exist...

Again, thanks for your responses...it is much appreciated...

Just by the way....we installed the new performance dashboards just released and it was these that managed to show us the statement that was in a wait state and how we detected the problem...so if you have not installed the new performance dashboards yet....you might want to do so...

Best Regards

Peter

Beginning PL/SQL question

I'm writing a pl/sql program to insert the month number and number of days in that month into a table called Month_Days. I only want to use the Insert Into command once, and I also don't want to use a nested If statement. i can get the month number into month column using a loop and a counter going up to 13, but i don't know how to get the number of days into the days column. I'm assuming Jan has 31 days, Feb has 29, March has 31, Apr has 30, etc.
Here's what i have so far:

Create Table Month_Days(
Month Number(2)
Days Number(2));

Declare
LoopX Binary_Integer;
Begin
LoopX:=0;
Loop
LoopX:=LoopX+1;
If LoopX=13 Then
Exit;
End If;
Insert Into Month_Days Values (LoopX);
End Loop;
End;

Thanks in advance for any help!Hello,

the easiest way to get the lastnumber of a months is:

cDate VARCHAR2(20);
cLastDay VARCHAR2(2);

-- Build date
cDate := TO_CHAR(loopx) || '01' || '2003'

SELECT TO_CHAR(LAST_DAY(TO_DATE(cDate, 'MMDDYYYY')), 'DD')
INTO cLastDay FROM dual;

Hope that helps ?

If you want to use a PL/SQL editor try our product AlligatorSQL. It is very helpful ...

Manfred Peter
(Alligator Company GmbH)
http://www.alligatorsql.com

Begin some web programming

I know there are lots of books out there.. Ive been a DBA(not an all rounder
like most of you'll are that can program as well) for a while and have now
decided to do some Web programming,,i,e, basically using SQL Server as data
source and maybe generating some reports,etc.. Can one guide me into
achieving this through some online content thats out there ?Some step by
step stuff...Using SQL 2000You'll want to pick a software product to program in first. For
ASP.NET resources, see http://msdn.microsoft.com/asp.net/
-- Mary
MCW Technologies
http://www.mcwtech.com
On Sun, 1 Feb 2004 11:43:38 -0800, "Hassan" <fatima_ja@.hotmail.com>
wrote:

quote:

---

>I know there are lots of books out there.. Ive been a DBA(not an all rounde
r
>like most of you'll are that can program as well) for a while and have now
>decided to do some Web programming,,i,e, basically using SQL Server as data
>source and maybe generating some reports,etc.. Can one guide me into
>achieving this through some online content thats out there ?Some step by
>step stuff...Using SQL 2000
>

---

|||Hi Hassan.
I found the msdn VB.Net 101's a very useful resource, so if you choose to
code in VB.Net, drop by here:
http://msdn.microsoft.com/vbasic/do...101samples.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...

quote:

---

> I know there are lots of books out there.. Ive been a DBA(not an all

---

rounder

quote:

---

> like most of you'll are that can program as well) for a while and have

---

now

quote:

---

> decided to do some Web programming,,i,e, basically using SQL Server as

---

data

quote:

---

> source and maybe generating some reports,etc.. Can one guide me into
> achieving this through some online content thats out there ?Some step by
> step stuff...Using SQL 2000
>

---

|||When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
difference ?
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...

quote:

---

> Hi Hassan.
> I found the msdn VB.Net 101's a very useful resource, so if you choose to
> code in VB.Net, drop by here:
> http://msdn.microsoft.com/vbasic/do...101samples.aspx
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> rounder
> now
> data
>

---

|||When you develop within the ASP.Net framework, you choose a .Net language to
program asp pages with.
You can program ASP.Net pages with any of the .Net languages - C#, VB.Net,
C++.
I generally use C# or VB.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ee8Dr$T6DHA.2168@.TK2MSFTNGP12.phx.gbl...

quote:

---

> When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
> difference ?
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...
to[QUOTE]
have[QUOTE]
by[QUOTE]
>

---

Begin some web programming

I know there are lots of books out there.. Ive been a DBA(not an all rounder
like most of you'll are that can program as well) for a while and have now
decided to do some Web programming,,i,e, basically using SQL Server as data
source and maybe generating some reports,etc.. Can one guide me into
achieving this through some online content thats out there ?Some step by
step stuff...Using SQL 2000You'll want to pick a software product to program in first. For
ASP.NET resources, see http://msdn.microsoft.com/asp.net/
-- Mary
MCW Technologies
http://www.mcwtech.com
On Sun, 1 Feb 2004 11:43:38 -0800, "Hassan" <fatima_ja@.hotmail.com>
wrote:
>I know there are lots of books out there.. Ive been a DBA(not an all rounder
>like most of you'll are that can program as well) for a while and have now
>decided to do some Web programming,,i,e, basically using SQL Server as data
>source and maybe generating some reports,etc.. Can one guide me into
>achieving this through some online content thats out there ?Some step by
>step stuff...Using SQL 2000
>|||Hi Hassan.
I found the msdn VB.Net 101's a very useful resource, so if you choose to
code in VB.Net, drop by here:
http://msdn.microsoft.com/vbasic/downloads/samples/101samples.aspx
HTH
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> I know there are lots of books out there.. Ive been a DBA(not an all
rounder
> like most of you'll are that can program as well) for a while and have
now
> decided to do some Web programming,,i,e, basically using SQL Server as
data
> source and maybe generating some reports,etc.. Can one guide me into
> achieving this through some online content thats out there ?Some step by
> step stuff...Using SQL 2000
>|||When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
difference ?
"Greg Linwood" <g_linwoodQhotmail.com> wrote in message
news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...
> Hi Hassan.
> I found the msdn VB.Net 101's a very useful resource, so if you choose to
> code in VB.Net, drop by here:
> http://msdn.microsoft.com/vbasic/downloads/samples/101samples.aspx
> HTH
> Regards,
> Greg Linwood
> SQL Server MVP
> "Hassan" <fatima_ja@.hotmail.com> wrote in message
> news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > I know there are lots of books out there.. Ive been a DBA(not an all
> rounder
> > like most of you'll are that can program as well) for a while and have
> now
> > decided to do some Web programming,,i,e, basically using SQL Server as
> data
> > source and maybe generating some reports,etc.. Can one guide me into
> > achieving this through some online content thats out there ?Some step by
> > step stuff...Using SQL 2000
> >
> >
>|||When you develop within the ASP.Net framework, you choose a .Net language to
program asp pages with.
You can program ASP.Net pages with any of the .Net languages - C#, VB.Net,
C++.
I generally use C# or VB.
Regards,
Greg Linwood
SQL Server MVP
"Hassan" <fatima_ja@.hotmail.com> wrote in message
news:ee8Dr$T6DHA.2168@.TK2MSFTNGP12.phx.gbl...
> When would i use ASP.net or VB.net ? Do I need to use both ? Whats the
> difference ?
> "Greg Linwood" <g_linwoodQhotmail.com> wrote in message
> news:%23%23J01eQ6DHA.1596@.TK2MSFTNGP10.phx.gbl...
> > Hi Hassan.
> >
> > I found the msdn VB.Net 101's a very useful resource, so if you choose
to
> > code in VB.Net, drop by here:
> >
> > http://msdn.microsoft.com/vbasic/downloads/samples/101samples.aspx
> >
> > HTH
> >
> > Regards,
> > Greg Linwood
> > SQL Server MVP
> >
> > "Hassan" <fatima_ja@.hotmail.com> wrote in message
> > news:OaETxuP6DHA.2300@.TK2MSFTNGP10.phx.gbl...
> > > I know there are lots of books out there.. Ive been a DBA(not an all
> > rounder
> > > like most of you'll are that can program as well) for a while and
have
> > now
> > > decided to do some Web programming,,i,e, basically using SQL Server as
> > data
> > > source and maybe generating some reports,etc.. Can one guide me into
> > > achieving this through some online content thats out there ?Some step
by
> > > step stuff...Using SQL 2000
> > >
> > >
> >
> >
>