Data throughput

I am trying to understand what things affect the
throughput of data when queries are executed to SQL
Server. I'm trying to determine if there are
settings,pramiters or times that can improve the speed of
a recordset being returned to the application.
What I have noticed in our environment is that a
recordset of 1,000 rows is returned very quickly where as
a recordset of a 1,000,000,000 rows can take over an hour.
I understand that the bandwidth will have a large effect
on the time but there are times when the bandwidth is
wide open and it still takes a significant amount of
time. What things can I do/set to optimixe this as much
as possible.
What can you do? Don't return 1 billion row resultsets, quite simple. No
human is able to digest that amount of information, so you should filter or
summarize it at the server using WHERE, SUM etc, and then return a more
limited number of rows to the client.
Jacco Schalkwijk
SQL Server MVP
"Jim Abel" <jim.abel@.lmco> wrote in message
news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
> I am trying to understand what things affect the
> throughput of data when queries are executed to SQL
> Server. I'm trying to determine if there are
> settings,pramiters or times that can improve the speed of
> a recordset being returned to the application.
> What I have noticed in our environment is that a
> recordset of 1,000 rows is returned very quickly where as
> a recordset of a 1,000,000,000 rows can take over an hour.
> I understand that the bandwidth will have a large effect
> on the time but there are times when the bandwidth is
> wide open and it still takes a significant amount of
> time. What things can I do/set to optimixe this as much
> as possible.
|||The query is used to fill a CSV file that a third party
application uses to generate charts driven by the
ultimate Users. The reason I'm asking the question is to
see if there are options to speed the flow of data from
the Database to the CSV file to shorten the time it
currently takes. I can't modify the 3rd parties software
and I can't query less data so the ODBC seems like the
place to look. Your answer is logical for different
circumstances but doesn't get to the original question,
ARE there items that can be tweaked in ODBC that can
increase throughput?

>--Original Message--
>What can you do? Don't return 1 billion row resultsets,
quite simple. No
>human is able to digest that amount of information, so
you should filter or
>summarize it at the server using WHERE, SUM etc, and
then return a more[vbcol=seagreen]
>limited number of rows to the client.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jim Abel" <jim.abel@.lmco> wrote in message
>news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
of[vbcol=seagreen]
as[vbcol=seagreen]
hour.[vbcol=seagreen]
effect[vbcol=seagreen]
much
>
>.
>
|||What do you use to generate the csv file? bcp will be the quickest.
I--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:70ca01c42fae$ac9de360$a101280a@.phx.gbl...[vbcol=seagreen]
> The query is used to fill a CSV file that a third party
> application uses to generate charts driven by the
> ultimate Users. The reason I'm asking the question is to
> see if there are options to speed the flow of data from
> the Database to the CSV file to shorten the time it
> currently takes. I can't modify the 3rd parties software
> and I can't query less data so the ODBC seems like the
> place to look. Your answer is logical for different
> circumstances but doesn't get to the original question,
> ARE there items that can be tweaked in ODBC that can
> increase throughput?
>
> quite simple. No
> you should filter or
> then return a more
> of
> as
> hour.
> effect
> much

Data throughput

I am trying to understand what things affect the
throughput of data when queries are executed to SQL
Server. I'm trying to determine if there are
settings,pramiters or times that can improve the speed of
a recordset being returned to the application.
What I have noticed in our environment is that a
recordset of 1,000 rows is returned very quickly where as
a recordset of a 1,000,000,000 rows can take over an hour.
I understand that the bandwidth will have a large effect
on the time but there are times when the bandwidth is
wide open and it still takes a significant amount of
time. What things can I do/set to optimixe this as much
as possible.What can you do? Don't return 1 billion row resultsets, quite simple. No
human is able to digest that amount of information, so you should filter or
summarize it at the server using WHERE, SUM etc, and then return a more
limited number of rows to the client.
Jacco Schalkwijk
SQL Server MVP
"Jim Abel" <jim.abel@.lmco> wrote in message
news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
> I am trying to understand what things affect the
> throughput of data when queries are executed to SQL
> Server. I'm trying to determine if there are
> settings,pramiters or times that can improve the speed of
> a recordset being returned to the application.
> What I have noticed in our environment is that a
> recordset of 1,000 rows is returned very quickly where as
> a recordset of a 1,000,000,000 rows can take over an hour.
> I understand that the bandwidth will have a large effect
> on the time but there are times when the bandwidth is
> wide open and it still takes a significant amount of
> time. What things can I do/set to optimixe this as much
> as possible.|||The query is used to fill a CSV file that a third party
application uses to generate charts driven by the
ultimate Users. The reason I'm asking the question is to
see if there are options to speed the flow of data from
the Database to the CSV file to shorten the time it
currently takes. I can't modify the 3rd parties software
and I can't query less data so the ODBC seems like the
place to look. Your answer is logical for different
circumstances but doesn't get to the original question,
ARE there items that can be tweaked in ODBC that can
increase throughput?

>--Original Message--
>What can you do? Don't return 1 billion row resultsets,
quite simple. No
>human is able to digest that amount of information, so
you should filter or
>summarize it at the server using WHERE, SUM etc, and
then return a more
>limited number of rows to the client.
>--
>Jacco Schalkwijk
>SQL Server MVP
>
>"Jim Abel" <jim.abel@.lmco> wrote in message
>news:5c7301c42d7f$a97fd480$a101280a@.phx.gbl...
of[vbcol=seagreen]
as[vbcol=seagreen]
hour.[vbcol=seagreen]
effect[vbcol=seagreen]
much[vbcol=seagreen]
>
>.
>|||What do you use to generate the csv file? bcp will be the quickest.
I--
Jacco Schalkwijk
SQL Server MVP
<anonymous@.discussions.microsoft.com> wrote in message
news:70ca01c42fae$ac9de360$a101280a@.phx.gbl...[vbcol=seagreen]
> The query is used to fill a CSV file that a third party
> application uses to generate charts driven by the
> ultimate Users. The reason I'm asking the question is to
> see if there are options to speed the flow of data from
> the Database to the CSV file to shorten the time it
> currently takes. I can't modify the 3rd parties software
> and I can't query less data so the ODBC seems like the
> place to look. Your answer is logical for different
> circumstances but doesn't get to the original question,
> ARE there items that can be tweaked in ODBC that can
> increase throughput?
>
>
> quite simple. No
> you should filter or
> then return a more
> of
> as
> hour.
> effect
> much

Data tarsfer from one table to another with different schema on sqlserver 2000

Hi all
I am working on SqlServer 2000,
When row will be inserted/updated in one table that data neds to be
inserted/updated into the another DataBase on the same machine. Both
table has different schema, what is the Better solution? I know we can
do it with Trigger, But Is there any better way to do it?
Thanks
ABCL
> do it with Trigger, But Is there any better way to do it?
How do you insert/update the data ? By using SP? If so insert/update into
another table within the same SP.
I hope you don't have permmisions problems when you update/insert the
data into differnt schem/user owner object
"ABCL" <ohmp05@.hotmail.com> wrote in message
news:64f2096c-1e83-4e6a-8cec-2f0f818284bd@.n77g2000hse.googlegroups.com...
> Hi all
> I am working on SqlServer 2000,
> When row will be inserted/updated in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
|||On Mar 16, 8:01Xam, ABCL <ohm...@.hotmail.com> wrote:
> Hi all
> X I am working on SqlServer 2000,
> XWhen row will be inserted/updated Xin one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
Actually we want to SynchroniZe the Data from one DataBase to another
and vise versa on Insert/update and delete.
How Can we Perform Data Synchronization on both the DataBase with
different schema.
If I write trigger, I think There will be endless loop If both Tables
hava trigger on inster/update and Delete.
Do u have any solution for that? I am new to perform this kind of task
Is it possible with DTS( scheduled job)?
Is there any Performance Issue?
sql

Data tarsfer from one table to another with different schema on sql

Hi all
I am working on SqlServer 2000,
When row will be inserted/updated in one table that data neds to be
inserted/updated into the another DataBase on the same machine. Both
table has different schema, what is the Better solution? I know we can
do it with Trigger, But Is there any better way to do it?
ThanksABCL
> do it with Trigger, But Is there any better way to do it?
How do you insert/update the data ? By using SP? If so insert/update into
another table within the same SP.
I hope you don't have permmisions problems when you update/insert the
data into differnt schem/user owner object
"ABCL" <ohmp05@.hotmail.com> wrote in message
news:64f2096c-1e83-4e6a-8cec-2f0f818284bd@.n77g2000hse.googlegroups.com...
> Hi all
> I am working on SqlServer 2000,
> When row will be inserted/updated in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks|||On Mar 16, 8:01=A0am, ABCL <ohm...@.hotmail.com> wrote:
> Hi all
> =A0 I am working on SqlServer 2000,
> =A0When row will be inserted/updated =A0in one table that data neds to be
> inserted/updated into the another DataBase on the same machine. Both
> table has different schema, what is the Better solution? I know we can
> do it with Trigger, But Is there any better way to do it?
> Thanks
Actually we want to SynchroniZe the Data from one DataBase to another
and vise versa on Insert/update and delete.
How Can we Perform Data Synchronization on both the DataBase with
different schema.
If I write trigger, I think There will be endless loop If both Tables
hava trigger on inster/update and Delete.
Do u have any solution for that? I am new to perform this kind of task
Is it possible with DTS( scheduled job)?
Is there any Performance Issue?

Data source name not found

Hello, I am trying to retrieve data from an Informix UniData database to SQL
Server. I am doing all the setup and work from the SQL Server. I have
created the package in SSIS and the Connection Manager item (which is setup
as a System DSN on the server) tests ok when manually tested. However, when
the package is executed, it seems it can't make the connection to the
Informix database. This is what is reported:
SSIS package "Test1.dtsx" starting.
Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
beginning.
Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcException:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and
no default driver specified at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
pTransaction) at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
transaction) at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90 wrapper, Object transaction)
Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
validation and returned error code 0x80131937.
Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component failed
validation.
Error: 0xC0024107 at Sample Data: There were errors during task validation.
SSIS package "Test1.dtsx" finished: Failure.
The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
2005 are 64 Bit installs.
Any suggestions on why everything is ok and SSIS can connect to the Informix
database while configuring the column mappings etc, but fails upon execution?
Scott
I've found the issue. I needed to set the Run64BitRuntime to False in the
Properties of the Project. The default is for the project to use 64 Bit
drivers and my Informix driver is 32 Bit only.
"Scott" wrote:

> Hello, I am trying to retrieve data from an Informix UniData database to SQL
> Server. I am doing all the setup and work from the SQL Server. I have
> created the package in SSIS and the Connection Manager item (which is setup
> as a System DSN on the server) tests ok when manually tested. However, when
> the package is executed, it seems it can't make the connection to the
> Informix database. This is what is reported:
> SSIS package "Test1.dtsx" starting.
> Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
> beginning.
> Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcException:
> ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name not found and
> no default driver specified at
> Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnec tionManager90.AcquireConnection(Object
> pTransaction) at
> Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceA dapter.AcquireConnections(Object
> transaction) at
> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentH ost.HostAcquireConnections(IDTSManagedComponentWra pper90 wrapper, Object transaction)
> Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
> validation and returned error code 0x80131937.
> Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component failed
> validation.
> Error: 0xC0024107 at Sample Data: There were errors during task validation.
> SSIS package "Test1.dtsx" finished: Failure.
> The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
> The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
> 2005 are 64 Bit installs.
> Any suggestions on why everything is ok and SSIS can connect to the Informix
> database while configuring the column mappings etc, but fails upon execution?
> Scott
>

Data source name not found

Hello, I am trying to retrieve data from an Informix UniData database to SQL
Server. I am doing all the setup and work from the SQL Server. I have
created the package in SSIS and the Connection Manager item (which is setup
as a System DSN on the server) tests ok when manually tested. However, when
the package is executed, it seems it can't make the connection to the
Informix database. This is what is reported:
SSIS package "Test1.dtsx" starting.
Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
beginning.
Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcExceptio
n:
ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source name
not found and
no default driver specified at
Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireConne
ction(Object
pTransaction) at
Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnections(
Object
transaction) at
Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnections
(IDTSManagedComponentWrapper90 wrapper, Object transaction)
Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
validation and returned error code 0x80131937.
Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component failed
validation.
Error: 0xC0024107 at Sample Data: There were errors during task validation.
SSIS package "Test1.dtsx" finished: Failure.
The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
2005 are 64 Bit installs.
Any suggestions on why everything is ok and SSIS can connect to the Informix
database while configuring the column mappings etc, but fails upon execution
?
ScottI've found the issue. I needed to set the Run64BitRuntime to False in the
Properties of the Project. The default is for the project to use 64 Bit
drivers and my Informix driver is 32 Bit only.
"Scott" wrote:

> Hello, I am trying to retrieve data from an Informix UniData database to S
QL
> Server. I am doing all the setup and work from the SQL Server. I have
> created the package in SSIS and the Connection Manager item (which is setu
p
> as a System DSN on the server) tests ok when manually tested. However, wh
en
> the package is executed, it seems it can't make the connection to the
> Informix database. This is what is reported:
> SSIS package "Test1.dtsx" starting.
> Information: 0x4004300A at Sample Data, DTS.Pipeline: Validation phase is
> beginning.
> Error: 0xC0047062 at Sample Data, M2K [1]: System.Data.Odbc.OdbcExcept
ion:
> ERROR [IM002] [Microsoft][ODBC Driver Manager] Data source nam
e not found and
> no default driver specified at
> Microsoft.SqlServer.Dts.Runtime.Wrapper.IDTSConnectionManager90.AcquireCon
nection(Object
> pTransaction) at
> Microsoft.SqlServer.Dts.Pipeline.DataReaderSourceAdapter.AcquireConnection
s(Object
> transaction) at
> Microsoft.SqlServer.Dts.Pipeline.ManagedComponentHost.HostAcquireConnectio
ns(IDTSManagedComponentWrapper90 wrapper, Object transaction)
> Error: 0xC0047017 at Sample Data, DTS.Pipeline: component "M2K" (1) failed
> validation and returned error code 0x80131937.
> Error: 0xC004700C at Sample Data, DTS.Pipeline: One or more component fail
ed
> validation.
> Error: 0xC0024107 at Sample Data: There were errors during task validation
.
> SSIS package "Test1.dtsx" finished: Failure.
> The program '[3896] Test1.dtsx: DTS' has exited with code 0 (0x0).
> The Informix ODBC driver is 32 Bit and Windows Server 2003 and SQL Server
> 2005 are 64 Bit installs.
> Any suggestions on why everything is ok and SSIS can connect to the Inform
ix
> database while configuring the column mappings etc, but fails upon executi
on?
> Scott
>

Data Security

Hello,
Is there a way to provide the security to the data present in the SQL
Server, like encrypting the entire data?
Thanks,
Sheshadrinath RNo, not natively. SQL Server 2005 to be released 7 November 2005 supports th
is.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Sheshadrinath R" wrote:

> Hello,
> Is there a way to provide the security to the data present in the SQL
> Server, like encrypting the entire data?
> Thanks,
> Sheshadrinath R

Data Replication performance.

I'm seearching for information regarding database replication
performance. We need to compare the performance of replication for SQL
Server and Oracle and it is urgent! Anyone who can describe the
performance bottlenecks for each database when performing replication,
or can point me to a white paper or webpage.Hi Tobias

please have a look at the following link,

http://www.akadia.com/services/sqls...l#_Toc513865493

thanks
Shehzad

toby_one_canoby@.hotmail.com (Tobias Johansson) wrote in message news:<a64d0b53.0410010417.386d1fb6@.posting.google.com>...
> I'm seearching for information regarding database replication
> performance. We need to compare the performance of replication for SQL
> Server and Oracle and it is urgent! Anyone who can describe the
> performance bottlenecks for each database when performing replication,
> or can point me to a white paper or webpage.

data replication

To all,
I am totally new to database programming and SQLServer. I need to find out
how to "copy" the data in database 1 to the database 2. How, in general one
does this type of "copy" or "migration". Is there a SQL standard way to do
this?
Thanks in advance.
RezaReza,
there are numerous options depending on your requirements eg is this for
high-availability, reporting, load balancing etc.
Anyway, please have a look in BOL at Log Shipping, Replication and Database
Mirroring.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||This ultimately is to generate reports from the data in the database. Just
curious, what is BOL at Log Shipping?
Reza
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ukK6DswYGHA.3972@.TK2MSFTNGP04.phx.gbl...
> Reza,
> there are numerous options depending on your requirements eg is this for
> high-availability, reporting, load balancing etc.
> Anyway, please have a look in BOL at Log Shipping, Replication and
> Database Mirroring.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||BOL = Books On Line
Log Shipping is what you need to look up in the index.
"Reza Razavipour" <rrazavipour@.stbernard.com> wrote in message
news:eeP5PvwYGHA.3424@.TK2MSFTNGP02.phx.gbl...
> This ultimately is to generate reports from the data in the database. Just
> curious, what is BOL at Log Shipping?
> Reza
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:ukK6DswYGHA.3972@.TK2MSFTNGP04.phx.gbl...
>

Data recovery service!

I've been asked to find a data recovery service who can help restore a sql
server database that has become corrupt or is on a hard drive that has
become non-functional. Does anyone have suggestions of a company who
specializes in this sort of work?
Since most drives (disk arrays) are multiple spindles in a RAID configuration
could you be a bit more specific? Do you mean, for example, an internal set
of drives?
--DatabaseAdmins.com
Remote DBA Services
"Beth In Alaska" wrote:

> I've been asked to find a data recovery service who can help restore a sql
> server database that has become corrupt or is on a hard drive that has
> become non-functional. Does anyone have suggestions of a company who
> specializes in this sort of work?
>
>
|||Hello,
If you hard drive is totally unusable, only option is restore from a good
backup and restore transaction log backups.
Take a look into various hugh availability options using:-
1. Replication
2. Logshipping
3. Database mirroring
or
Clustering
Thanks
Hari
"Beth In Alaska" <bethinak@.spamfreeclearwire.net> wrote in message
news:12up0hbf4hgf94b@.corp.supernews.com...
> I've been asked to find a data recovery service who can help restore a sql
> server database that has become corrupt or is on a hard drive that has
> become non-functional. Does anyone have suggestions of a company who
> specializes in this sort of work?
>
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MgK8%235XHHA.2640@.TK2MSFTNGP06.phx.gbl...
> Hello,
> If you hard drive is totally unusable, only option is restore from a good
> backup and restore transaction log backups.
>
In the meantime, see what a company like Ontrack.com can do.
And there's one or two software packages that claim to be able to help
recover data from a corrupt db.
Take a look at those.

> Take a look into various hugh availability options using:-
> 1. Replication
> 2. Logshipping
> 3. Database mirroring
> or
> Clustering
> Thanks
> Hari
> "Beth In Alaska" <bethinak@.spamfreeclearwire.net> wrote in message
> news:12up0hbf4hgf94b@.corp.supernews.com...
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Data recovery service!

I've been asked to find a data recovery service who can help restore a sql
server database that has become corrupt or is on a hard drive that has
become non-functional. Does anyone have suggestions of a company who
specializes in this sort of work?Since most drives (disk arrays) are multiple spindles in a RAID configuratio
n
could you be a bit more specific? Do you mean, for example, an internal set
of drives?
--DatabaseAdmins.com
Remote DBA Services
"Beth In Alaska" wrote:

> I've been asked to find a data recovery service who can help restore a sql
> server database that has become corrupt or is on a hard drive that has
> become non-functional. Does anyone have suggestions of a company who
> specializes in this sort of work?
>
>|||Hello,
If you hard drive is totally unusable, only option is restore from a good
backup and restore transaction log backups.
Take a look into various hugh availability options using:-
1. Replication
2. Logshipping
3. Database mirroring
or
Clustering
Thanks
Hari
"Beth In Alaska" <bethinak@.spamfreeclearwire.net> wrote in message
news:12up0hbf4hgf94b@.corp.supernews.com...
> I've been asked to find a data recovery service who can help restore a sql
> server database that has become corrupt or is on a hard drive that has
> become non-functional. Does anyone have suggestions of a company who
> specializes in this sort of work?
>|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:%23MgK8%235XHHA.2640@.TK2MSFTNGP06.phx.gbl...
> Hello,
> If you hard drive is totally unusable, only option is restore from a good
> backup and restore transaction log backups.
>
In the meantime, see what a company like Ontrack.com can do.
And there's one or two software packages that claim to be able to help
recover data from a corrupt db.
Take a look at those.

> Take a look into various hugh availability options using:-
> 1. Replication
> 2. Logshipping
> 3. Database mirroring
> or
> Clustering
> Thanks
> Hari
> "Beth In Alaska" <bethinak@.spamfreeclearwire.net> wrote in message
> news:12up0hbf4hgf94b@.corp.supernews.com...
>
Greg Moore
SQL Server DBA Consulting
sql (at) greenms.com http://www.greenms.com

Data not posting to SQL Database

I have a asp.net page that is supposed to post data to a sqlserver 2000 database. The page opens fine and let's you enter the data and submit it, but the data never appears in the database. I've set up exception handling to catch duplicate records, invalid date/time format and then general sql exceptions and general system exceptions. Either something is going wrong before the try...catch or it's not throwing an exception and I don't get any runtime or designtime errors>>>
'This code adds values to the fields to be submitted
SqlConnection1.Open()
SqlInsertCommand1.Parameters("@.Site").Value() = Server.HtmlEncode(ddlSite.SelectedValue)
SqlInsertCommand1.Parameters("@.RepMonth").Value() = Server.HtmlEncode(ddlMonth.SelectedValue)
SqlInsertCommand1.Parameters("@.SerDate").Value() = Server.HtmlEncode(txtDate.Text)
SqlInsertCommand1.Parameters("@.VolName").Value() = Server.HtmlEncode(txtName.Text)
SqlInsertCommand1.Parameters("@.SSNum").Value() = Server.HtmlEncode(txtSSNum.Text)
SqlInsertCommand1.Parameters("@.ArtCraftHrs").Value = Server.HtmlEncode(Val(txtArt.Text))
SqlInsertCommand1.Parameters("@.TransHrs").Value = Server.HtmlEncode(Val(txtTrans.Text))
SqlInsertCommand1.Parameters("@.EscortHrs").Value = Server.HtmlEncode(Val(txtEscort.Text))
SqlInsertCommand1.Parameters("@.InfoRefHrs").Value = Server.HtmlEncode(Val(txtInfo.Text))
SqlInsertCommand1.Parameters("@.ProgAsstHrs").Value = Server.HtmlEncode(Val(txtProgAsst.Text))
SqlInsertCommand1.Parameters("@.SHopAsstHrs").Value = Server.HtmlEncode(Val(txtShopAsst.Text))
SqlInsertCommand1.Parameters("@.FoodAidHrs").Value = Server.HtmlEncode(Val(txtFoodAid.Text))
SqlInsertCommand1.Parameters("@.HomeDelHrs").Value = Server.HtmlEncode(Val(txtDelAid.Text))
SqlInsertCommand1.Parameters("@.SubmitDate").Value = Server.HtmlEncode(Now().ToShortDateString)

'This code creates a sqlcommand to return data to the datagird

Dim sqlstr As String
sqlstr = "declare @.Date as char(12)set @.Date=(select rtrim(cast(datepart(mm,getdate()) as c" & _
"har(2)))+'/'+cast(datepart(dd,getdate()) as char(2))+'/'+cast(datepart(yy,getdat" & _
"e()) as char(4)))SELECT Site, RepMonth, SerDate, VolName, SSNum, ArtCraftHrs, Tr" & _
"ansHrs, EscortHrs, InfoRefHrs, ProgAsstHrs, ShopAsstHrs, FoodAidHrs, HomeDelHrs," & _
" SubmitDate FROM dbo.VolDetailReport where submitdate = @.Date and Site = '" + ddlSite.SelectedItem.Text + "'"
Me.SqlSelectCommand1.CommandText = sqlstr
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Try

SqlInsertCommand1.ExecuteNonQuery()
SqlDataAdapter1.Fill(DataSet11, "VolDetailReport")
dgDisplay.DataBind()
SqlConnection1.Close()
Dim volSubmitDate As String
volSubmitDate = DatePart(DateInterval.Month, Now()) & " / " & DatePart(DateInterval.Day, Now()) & " / " & DatePart(DateInterval.Year, Now())
Response.Cookies("VolSubmitted").Value = volSubmitDate
Response.Cookies("VolSubmitted").Expires = DateTime.MaxValue
Catch ex As SqlClient.SqlException When ex.Number = 242
'Checks for improper format of the data fields
lblSqlError.Visible = "True"
lblSqlError.Text = "The Date is not in the form m/d/yyyy"
Catch ex As SqlClient.SqlException When ex.Number = 2627
'Checks for Primary Key violation ie(Already existing record)
lblSqlError.Text = "This Record has already been submitted"
Catch ex As Exception
lblSqlError.Text = ex.Message
End Try

Reset_Page()Can you run SQL Profiler and see what is actually being passed to SQL Server? I have found this to be very helpful.|||I don't have sysadmin rights to create a trace in SQL Profiler. What I did was do a step into debug and setup a break in the button click procedure. It didn't find a problem and when I checked the database the data was there!!!! I don't know what the problem was, but it seems to have fixed itself.

Thanks for the info

Data Modeling

What's the best Microsoft tool for data modeling of a data warehouse on SQL
Server 2000?
That's just about like asking, "What's the best car?" It depends on
what you will be doing with it. Do you need to model both the logical
and physical? Do you need it to do schema generation for you? Reverse
engineering? Does it need to link into SourceSafe?
The big three that I see used the most are ERwin, Power Designer, and
Microsoft Visio w/ Enterprise Architect. I would suggest that you start
with these, check out the websites, download the free trials, and
compare their functionality and ease of use with what you need.
HTH,
-Tom.
|||ER-Studio is also a very big player
Greg Jackson
PDX, Oregon

Data Modeling

What's the best Microsoft tool for data modeling of a data warehouse on SQL
Server 2000?That's just about like asking, "What's the best car?" It depends on
what you will be doing with it. Do you need to model both the logical
and physical? Do you need it to do schema generation for you? Reverse
engineering? Does it need to link into SourceSafe?
The big three that I see used the most are ERwin, Power Designer, and
Microsoft visio w/ Enterprise Architect. I would suggest that you start
with these, check out the websites, download the free trials, and
compare their functionality and ease of use with what you need.
HTH,
-Tom.|||ER-Studio is also a very big player
Greg Jackson
PDX, Oregon

data migration from sybase 8.0 to sqlserver 2005

Hi all,

Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.

JSR2005 wrote:

Hi all,

Here i had a task to migrate sybase 8.0 database to mssqlserver 2005 .how can i migrate this using INTEGRATED SERVICES (SSIS) or any other options. .Please try to provide some basical info because i am new to sybase versions.

data in sqlserver is not case sensitive

I am facing problems as the data in sqlserver is not case sensitive. The data in parent key may be capital/lower case and the same data in the child table may be lower/capital case. While migrating the data from sqlserver database to other databases(like oracle) its giving error as the data not found in parent key, though the data found in parent table . This is just because the case sensitive in oracle. But according to my knowledge its better if the sqlserver also supports data case sensitive.

Take a small example
Need a table to store all the alphabets in a table
the table structure is

CREATE TABLE [dbo].[ALPHABET] (
[Alphabet] [varchar] (1) NOT NULL ,
[Description] [varchar] (50) NOT NULL
)
GO

The data is

Alphabet

Description

a

Small a

b

Small b

C

Capital C

A

Capital A

sqlserver wont allow to insert data 'A' and gives error "voilation of primary key", though a & A are different according to this table.

I tried with NVARCHAR datatype also. The same problem here also. Sqlserver atleast should support data case sensitivity for NVARCHAR datatype as this can store different languages. May be in other languages the entire meaning may be differ with case differences. Even in english language some words meaning will differ with case differences. For reference can refer english dictionary

change the collation if the field you want to be case-sencetive into SQL_Latin1_General_CP1_CS_AS

Data from Excel into SQLserver 2005

Hi Folks,
I'm a novice user of sqlserver. I'm planned to run webqueries in excel. And
finally the fresh data should enter sqlserver als well.
Can one of you advise me how to handle this with some clear and stepwise
directions?
Hi
SQL Server 2005 (Dev)
The file contains two fields (Lname,FName)
CREATE TABLE dbo.Users
(
LName VARCHAR(50) NOT NULL,
FName VARCHAR(50) NOT NULL
)
INSERT INTO dbo.Users
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\TEST.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM dbo.Users
"JerrelA" <JerrelA@.discussions.microsoft.com> wrote in message
news:159A8952-2B70-4442-A035-2165EA70F3F4@.microsoft.com...
> Hi Folks,
> I'm a novice user of sqlserver. I'm planned to run webqueries in excel.
> And
> finally the fresh data should enter sqlserver als well.
> Can one of you advise me how to handle this with some clear and stepwise
> directions?

Data from Excel into SQLserver 2005

Hi Folks,
I'm a novice user of sqlserver. I'm planned to run webqueries in excel. And
finally the fresh data should enter sqlserver als well.
Can one of you advise me how to handle this with some clear and stepwise
directions?Hi
SQL Server 2005 (Dev)
The file contains two fields (Lname,FName)
CREATE TABLE dbo.Users
(
LName VARCHAR(50) NOT NULL,
FName VARCHAR(50) NOT NULL
)
INSERT INTO dbo.Users
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\TEST.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM dbo.Users
"JerrelA" <JerrelA@.discussions.microsoft.com> wrote in message
news:159A8952-2B70-4442-A035-2165EA70F3F4@.microsoft.com...
> Hi Folks,
> I'm a novice user of sqlserver. I'm planned to run webqueries in excel.
> And
> finally the fresh data should enter sqlserver als well.
> Can one of you advise me how to handle this with some clear and stepwise
> directions?

Data from Excel into SQLserver 2005

Hi Folks,
I'm a novice user of sqlserver. I'm planned to run webqueries in excel. And
finally the fresh data should enter sqlserver als well.
Can one of you advise me how to handle this with some clear and stepwise
directions?Hi
SQL Server 2005 (Dev)
The file contains two fields (Lname,FName)
CREATE TABLE dbo.Users
(
LName VARCHAR(50) NOT NULL,
FName VARCHAR(50) NOT NULL
)
INSERT INTO dbo.Users
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\TEST.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM dbo.Users
"JerrelA" <JerrelA@.discussions.microsoft.com> wrote in message
news:159A8952-2B70-4442-A035-2165EA70F3F4@.microsoft.com...
> Hi Folks,
> I'm a novice user of sqlserver. I'm planned to run webqueries in excel.
> And
> finally the fresh data should enter sqlserver als well.
> Can one of you advise me how to handle this with some clear and stepwise
> directions?

Data for source column is too large for the specified buffer size...

Hello there,

I have and small excel file, which when I try to import into SQl
Server will give an error "Data for source column 4 is too large for
the specified buffer size"

I have four columns in the excel file, one of the column contains a
large chunk of data so I created a table in SQL Server and changed the
type of the field to text so I could accomodate this field but still
no luck.

Any suggestions as to how to go about this.

Thanks in advance,
Srikanth paispai@.immunetolerance.org (Pai) wrote in message news:<5332f465.0401101239.441dc621@.posting.google.com>...
> Hello there,
> I have and small excel file, which when I try to import into SQl
> Server will give an error "Data for source column 4 is too large for
> the specified buffer size"
> I have four columns in the excel file, one of the column contains a
> large chunk of data so I created a table in SQL Server and changed the
> type of the field to text so I could accomodate this field but still
> no luck.
> Any suggestions as to how to go about this.
> Thanks in advance,
> Srikanth pai

How are you importing? bcp, dts or what?
We are currently using bcp to import text fields without any problems.
I have used also used dts for importing text fields in the past.|||PromisedOyster@.hotmail.com (Mystery Man) wrote in message news:<87c81238.0401111336.1216eb23@.posting.google.com>...
> spai@.immunetolerance.org (Pai) wrote in message news:<5332f465.0401101239.441dc621@.posting.google.com>...
> > Hello there,
> > I have and small excel file, which when I try to import into SQl
> > Server will give an error "Data for source column 4 is too large for
> > the specified buffer size"
> > I have four columns in the excel file, one of the column contains a
> > large chunk of data so I created a table in SQL Server and changed the
> > type of the field to text so I could accomodate this field but still
> > no luck.
> > Any suggestions as to how to go about this.
> > Thanks in advance,
> > Srikanth pai
> How are you importing? bcp, dts or what?
> We are currently using bcp to import text fields without any problems.
> I have used also used dts for importing text fields in the past.

Hello there,

I am using DTS to import the excel file...

Thanks,
Srikanth Pai|||spai@.immunetolerance.org (Pai) wrote in message news:<5332f465.0401120742.d8bf3a4@.posting.google.com>...
> PromisedOyster@.hotmail.com (Mystery Man) wrote in message news:<87c81238.0401111336.1216eb23@.posting.google.com>...
> > spai@.immunetolerance.org (Pai) wrote in message news:<5332f465.0401101239.441dc621@.posting.google.com>...
> > > Hello there,
> > > > I have and small excel file, which when I try to import into SQl
> > > Server will give an error "Data for source column 4 is too large for
> > > the specified buffer size"
> > > > I have four columns in the excel file, one of the column contains a
> > > large chunk of data so I created a table in SQL Server and changed the
> > > type of the field to text so I could accomodate this field but still
> > > no luck.
> > > > Any suggestions as to how to go about this.
> > > > Thanks in advance,
> > > Srikanth pai
> > How are you importing? bcp, dts or what?
> > We are currently using bcp to import text fields without any problems.
> > I have used also used dts for importing text fields in the past.
> Hello there,
> I am using DTS to import the excel file...
> Thanks,
> Srikanth Pai

Pai,

Some suggestions for you.

(1) Try using bcp.
(2) Try with a manually created file and see if you can work out the
differences between your sample file and the prblem file. It maybe
some data, eg quotes or control chars that are
(3) Send me email containing your schema and a sample data file
(cutdown to the 2meg hotmail limit) and I'll have a look.|||PromisedOyster@.hotmail.com (Mystery Man) wrote in message news:<87c81238.0401121321.7bae3a6e@.posting.google.com>...
> spai@.immunetolerance.org (Pai) wrote in message news:<5332f465.0401120742.d8bf3a4@.posting.google.com>...
> > PromisedOyster@.hotmail.com (Mystery Man) wrote in message news:<87c81238.0401111336.1216eb23@.posting.google.com>...
> > > spai@.immunetolerance.org (Pai) wrote in message news:<5332f465.0401101239.441dc621@.posting.google.com>...
> > > > Hello there,
> > > > > > I have and small excel file, which when I try to import into SQl
> > > > Server will give an error "Data for source column 4 is too large for
> > > > the specified buffer size"
> > > > > > I have four columns in the excel file, one of the column contains a
> > > > large chunk of data so I created a table in SQL Server and changed the
> > > > type of the field to text so I could accomodate this field but still
> > > > no luck.
> > > > > > Any suggestions as to how to go about this.
> > > > > > Thanks in advance,
> > > > Srikanth pai
> > > > How are you importing? bcp, dts or what?
> > > We are currently using bcp to import text fields without any problems.
> > > I have used also used dts for importing text fields in the past.
> > Hello there,
> > I am using DTS to import the excel file...
> > Thanks,
> > Srikanth Pai
>
> Pai,
> Some suggestions for you.
> (1) Try using bcp.
> (2) Try with a manually created file and see if you can work out the
> differences between your sample file and the prblem file. It maybe
> some data, eg quotes or control chars that are
> (3) Send me email containing your schema and a sample data file
> (cutdown to the 2meg hotmail limit) and I'll have a look.

Thanks promised one for everything, what I did here was saved the
excel file as a .txt file and then imported the file sucessfully, I
have never tried bcp?

Thnaks,
Srikanth|||Pai wrote:
> PromisedOyster@.hotmail.com (Mystery Man) wrote in message
news:<87c81238.0401121321.7bae3a6e@.posting.google.com>...
> > spai@.immunetolerance.org (Pai) wrote in message
news:<5332f465.0401120742.d8bf3a4@.posting.google.com>...
> > > PromisedOyster@.hotmail.com (Mystery Man) wrote in message
news:<87c81238.0401111336.1216eb23@.posting.google.com>...
> > > > spai@.immunetolerance.org (Pai) wrote in message
news:<5332f465.0401101239.441dc621@.posting.google.com>...
> > > > > Hello there,
> > > > > > > > I have and small excel file, which when I try to import into
SQl
> > > > > Server will give an error "Data for source column 4 is too
large for
> > > > > the specified buffer size"
> > > > > > > > I have four columns in the excel file, one of the column
contains a
> > > > > large chunk of data so I created a table in SQL Server and
changed the
> > > > > type of the field to text so I could accomodate this field
but still
> > > > > no luck.
> > > > > > > > Any suggestions as to how to go about this.
> > > > > > > > Thanks in advance,
> > > > > Srikanth pai
> > > > > > How are you importing? bcp, dts or what?
> > > > We are currently using bcp to import text fields without any
problems.
> > > > I have used also used dts for importing text fields in the
past.
> > > > Hello there,
> > > > I am using DTS to import the excel file...
> > > > Thanks,
> > > Srikanth Pai
> > Pai,
> > Some suggestions for you.
> > (1) Try using bcp.
> > (2) Try with a manually created file and see if you can work out
the
> > differences between your sample file and the prblem file. It maybe
> > some data, eg quotes or control chars that are
> > (3) Send me email containing your schema and a sample data file
> > (cutdown to the 2meg hotmail limit) and I'll have a look.
>
> Thanks promised one for everything, what I did here was saved the
> excel file as a .txt file and then imported the file sucessfully, I
> have never tried bcp?
> Thnaks,
> Srikanth

FYI all, I had problems exporting to SQL from Access. So by exporting
to text (from access) and then to SQL using DTS...it worked!