Showing posts with label insert. Show all posts
Showing posts with label insert. Show all posts

Thursday, March 29, 2012

export DBCC CHECKDB into table

Is there anyway to export DBCC CHECKDB results into a table?
The only example I was able to find on Books Online is:
INSERT INTO #tracestatus
EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
Please helpBelow work fine on 2005:
CREATE TABLE a
(c1 int, c2 int, c3 int, c4 varchar(4000), c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int,
c12 int, c23 int, c14 int, c15 int, c16 int, c17 int, c18 int)
insert into a
EXEC('DBCC CHECKDB(pubs) WITH TABLERESULTS')
SELECT * FROM a
Of course, create the table with a bit more care and try to match the data types carefully instead
of just guessing like I did. :-)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
news:D82B695F-110A-492B-82C6-33C592981F8F@.microsoft.com...
> Is there anyway to export DBCC CHECKDB results into a table?
> The only example I was able to find on Books Online is:
> INSERT INTO #tracestatus
> EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
> Please help|||It works!
Thanks a million, Tibor!
"Tibor Karaszi" wrote:
> Below work fine on 2005:
> CREATE TABLE a
> (c1 int, c2 int, c3 int, c4 varchar(4000), c5 int, c6 int, c7 int, c8 int, c9 int, c10 int, c11 int,
> c12 int, c23 int, c14 int, c15 int, c16 int, c17 int, c18 int)
> insert into a
> EXEC('DBCC CHECKDB(pubs) WITH TABLERESULTS')
> SELECT * FROM a
>
> Of course, create the table with a bit more care and try to match the data types carefully instead
> of just guessing like I did. :-)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Leon Shargorodsky" <LeonShargorodsky@.discussions.microsoft.com> wrote in message
> news:D82B695F-110A-492B-82C6-33C592981F8F@.microsoft.com...
> > Is there anyway to export DBCC CHECKDB results into a table?
> >
> > The only example I was able to find on Books Online is:
> > INSERT INTO #tracestatus
> > EXEC ('DBCC TRACESTATUS (-1) WITH NO_INFOMSGS')
> >
> > Please help
>

Tuesday, March 27, 2012

Export data to SQL Insert statements?

Is it possible to export data from tables to SQL insert statements? Got SQL 2005 developer, visual studio 2005, and Visio enterprise architect, if that makes any difference. I already foundthis tool but it costs money.

HI

DaFT is an open source frontend to any database that can be connected via ODBC and it can Create INSERT Scripts for you.

You can download here:http://sourceforge.net/project/showfiles.php?group_id=102868

Click help ,choose Contents and type "Create INSERT Scripts " to search and you can get the help on how to generate insert scripts.

Hope it helps.

Monday, March 26, 2012

export data into insert statements

is there any tools to do so? export everything into a lot of insert statements. i mean those free tools or scripts:)What are you trying to do ?|||it is for my web-based application, which checks database version to determine whether it is valid. if not valid, just execute all pre-defined sql scripts. can help customers to install the web-based application.|||This link may help you:
http://www.red-gate.com/SQL_Compare.htm
But it's not free stuff...|||Thanks guys. Just found a tool from msdn web site: sqlwebadmin. a bit similar to phpmyadmin. can export everything into scripts. but it got problem with large database. but at least can use:)|||Did you mean you wanted to script your database - creating the database, tables, constraints ... If so, then you can script it through Enterprise Manager.|||Em doesn't script data.|||For those of you who want to download this tool:

tool (http://www.microsoft.com/downloads/details.aspx?familyid=f0d03472-5e6c-459e-a6d8-6745a729c3c9&languageid=f49e8428-7071-4979-8a67-3cffcb0c2524&displaylang=en)

Export data into a script as "insert into"s

Hello,

I am using a Web based front end to a SQL Server db installed at my host that only lets me import data into tables either manually (in a 'datasheet' view) or with a SQL query.

Does anybody know if I can export data from my local db as a script such as

insert into TestTable(Id, Name) values ("1", "Test1")
insert into TestTable(Id, Name) values ("2", "Test2")
.
.
.

... so that I can run this script on the Web front end and insert data into the db at my host?

Many thanks in advance.Here's a link to some stored procedures you might find useful. I think the DataAs InsCommand may be what you are looking for.

http://www.mssqlcity.com/Scripts/scrImpExp.htm|||Thanks for your help Fred.

That stored procedure mostly does the job but looks like it needs a few slight mods because some data types aren't implemented (such as text which is written out as 'None Yet')...

Export data from table as SQL Inserts

Hi,
I have data in an old database I would like to capture for my newsystem but I dont have the original insert scripts. Is there atool (in SQL Server 2000 or thirdparty) that will help me export thedata as SQL inserts?
Thanks
jr.
Use DTS. Are they both SQL Server databases? If so, you canimport data from one database to another. Use the Import Datawizard. If the old database is not SQL Server, export the file toCSV, and the use SQL Server's Import Data wizard to import from the CSVfile.

Export data as bulk insert statement

Hi all,
Is there a way to export data from a particular table as a bulk insert
statements? I can export to all sort of database or even Excel spreadsheet
but seems like there is no option to export it as SQL Insert statements.
Anyone has any idea to achieve this?
Victor Hadianto
Check this: http://www.codeproject.com/dotnet/ScriptDatabase.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Victor Hadianto" <synop@.nospam.nospam> wrote in message
news:D4DA5860-6345-45CE-839B-59507B8AC7FB@.microsoft.com...
> Hi all,
> Is there a way to export data from a particular table as a bulk insert
> statements? I can export to all sort of database or even Excel spreadsheet
> but seems like there is no option to export it as SQL Insert statements.
> Anyone has any idea to achieve this?
> --
> Victor Hadianto
>
|||This proc will script data as INSERT statements:
http://vyaskn.tripod.com/code.htm#inserts
A BULK INSERT statement just loads data from an external delimited file.
It's easy to create such a file from Query Analyzer for example.
David Portas
SQL Server MVP
|||Hi Victor,
I think the articles MVP Dejan Sarka and MVP David Portas provided was very
helpful, I just want to post a quick note to see if you would like
additional assistance or information regarding this particular issue. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Export data as bulk insert statement

Hi all,
Is there a way to export data from a particular table as a bulk insert
statements? I can export to all sort of database or even Excel spreadsheet
but seems like there is no option to export it as SQL Insert statements.
Anyone has any idea to achieve this?
Victor HadiantoCheck this: http://www.codeproject.com/dotnet/ScriptDatabase.asp.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Victor Hadianto" <synop@.nospam.nospam> wrote in message
news:D4DA5860-6345-45CE-839B-59507B8AC7FB@.microsoft.com...
> Hi all,
> Is there a way to export data from a particular table as a bulk insert
> statements? I can export to all sort of database or even Excel spreadsheet
> but seems like there is no option to export it as SQL Insert statements.
> Anyone has any idea to achieve this?
> --
> Victor Hadianto
>|||This proc will script data as INSERT statements:
http://vyaskn.tripod.com/code.htm#inserts
A BULK INSERT statement just loads data from an external delimited file.
It's easy to create such a file from Query Analyzer for example.
David Portas
SQL Server MVP
--|||Hi Victor,
I think the articles MVP Dejan Sarka and MVP David Portas provided was very
helpful, I just want to post a quick note to see if you would like
additional assistance or information regarding this particular issue. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Export data as bulk insert statement

Hi all,
Is there a way to export data from a particular table as a bulk insert
statements? I can export to all sort of database or even Excel spreadsheet
but seems like there is no option to export it as SQL Insert statements.
Anyone has any idea to achieve this?
--
Victor HadiantoCheck this: http://www.codeproject.com/dotnet/ScriptDatabase.asp.
--
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com
"Victor Hadianto" <synop@.nospam.nospam> wrote in message
news:D4DA5860-6345-45CE-839B-59507B8AC7FB@.microsoft.com...
> Hi all,
> Is there a way to export data from a particular table as a bulk insert
> statements? I can export to all sort of database or even Excel spreadsheet
> but seems like there is no option to export it as SQL Insert statements.
> Anyone has any idea to achieve this?
> --
> Victor Hadianto
>|||This proc will script data as INSERT statements:
http://vyaskn.tripod.com/code.htm#inserts
A BULK INSERT statement just loads data from an external delimited file.
It's easy to create such a file from Query Analyzer for example.
--
David Portas
SQL Server MVP
--|||Hi Victor,
I think the articles MVP Dejan Sarka and MVP David Portas provided was very
helpful, I just want to post a quick note to see if you would like
additional assistance or information regarding this particular issue. We
appreciate your patience and look forward to hearing from you!
Sincerely yours,
Michael Cheng
Online Partner Support Specialist
Partner Support Group
Microsoft Global Technical Support Center
---
Get Secure! - http://www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!

Thursday, March 22, 2012

Export a table data in INSERT statement

Can I export a table data into a script file contains INSERT statements in
EM ?
Man
Take a look at http://vyaskn.tripod.com/code/generate_inserts.txt
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eBJf9RU3FHA.1600@.TK2MSFTNGP12.phx.gbl...
> Can I export a table data into a script file contains INSERT statements in
> EM ?
>

Export a table data in INSERT statement

Can I export a table data into a script file contains INSERT statements in
EM ?Man
Take a look at http://vyaskn.tripod.com/code/generate_inserts.txt
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eBJf9RU3FHA.1600@.TK2MSFTNGP12.phx.gbl...
> Can I export a table data into a script file contains INSERT statements in
> EM ?
>sql

Wednesday, March 21, 2012

Export a table data in INSERT statement

Can I export a table data into a script file contains INSERT statements in
EM ?Man
Take a look at http://vyaskn.tripod.com/code/generate_inserts.txt
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eBJf9RU3FHA.1600@.TK2MSFTNGP12.phx.gbl...
> Can I export a table data into a script file contains INSERT statements in
> EM ?
>

Export a table data in a script file

How can I export a table data as a script file as INSERT statement from
Enterprise Manager or Query Analyser ?Try this
http://vyaskn.tripod.com/code/generate_inserts.txt
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:eQbNxGs2FHA.3092@.TK2MSFTNGP10.phx.gbl...
> How can I export a table data as a script file as INSERT statement from
> Enterprise Manager or Query Analyser ?
>
>

Monday, March 12, 2012

EXPERT: Stored procedure operand type clash

Im trying to insert a new record into tblMessages...the unique ID generated for MessageID is the value I want to use as a value for the MessageID field when inserting a new record in tblUsersAndMessages

I have the following sp:

-- ================================================

-- Template generated from Template Explorer using:

-- Create Procedure (New Menu).SQL

--

-- Use the Specify Values for Template Parameters

-- command (Ctrl-Shift-M) to fill in the parameter

-- values below.

--

-- This block of comments will not be included in

-- the definition of the procedure.

-- ================================================

SET

ANSI_NULLSON

GO

SET

QUOTED_IDENTIFIERON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE

PROCEDURE spNewMessage

@.UserIDSender

nvarchar(256),

@.MessageTitle

nvarchar(50),

@.MessageContent

text,

@.MessageType

int,

@.MessageID

uniqueidentifier,

@.UserID

uniqueidentifier

AS

Begin

SetNoCountonDECLARE @.WhateverIDuniqueidentifier

INSERT

INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)

VALUES

(@.UserIDSender,@.MessageTitle,@.MessageContent,@.MessageType)Select @.WhateverID=@.@.Identity

INSERT

INTO tblUsersAndMessages(MessageID,UserID)

VALUES

(@.WhateverID,@.UserID)

End

GO

And here are my tables:
tblUsersAndMessages
allow nulls
MessageID uniqueidentifier false
UserID uniqueidentifier false
New bit false *default set to ((1))


tblMessages
allow nulls
MessageID uniqueidentifier false *PK default set to (newid())
UserIDSender uniqueidentifier false
MessageTitle nvarchar(50) true
MessageContent text true
SentDateTime datetime false * default set to (getdate())
MessageType int false

Now when I try to add my SP I get this error:
Msg 206, Level 16, State 2, Procedure spNewMessage, Line 22
Operand type clash: numeric is incompatible with uniqueidentifier

What can this be?!?

Try usingSELECT SCOPE_IDENTITY() instead of @.@.IDENTITY. Seehere for why. It might not solve the problem but it's best practice, anyway.|||I've read the site you provided, but I cant seem to find out HOW I can use the value returned by select SCOPE_IDENTITY()...
Could you provide me with an example?

Thanks!|||Hi. You can us it as follows:

SET @.WhateverID = SCOPE_IDENTITY()

It may not solve your problem, but it's something you should do anyway. Good luck!|||

That's because SCOPE_IDENTITY is used to the lastIDENTITY value inserted into an IDENTITY column in the same scope, so it will return a numberic value, rather than uniqueidentifier. So we have to retrive the UNIQUEIDENTIFIER of the last inserted row from the table, for example:

ALTER PROCEDURE spNewMessage @.UserIDSenderuniqueidentifier, @.MessageTitle nvarchar(50),
@.MessageContent text, @.MessageType int, @.MessageID uniqueidentifier,
@.UserID uniqueidentifier

AS

Begin

Set NoCount on

DECLARE @.WhateverID uniqueidentifier
INSERT

INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)
VALUES

(@.UserIDSender,@.MessageTitle,@.MessageContent,@.MessageType)

Select @.WhateverID=MessageID FROM tblMessages ORDER BY SentDateTime ASC
INSERTINTO tblUsersAndMessages(MessageID,UserID)
VALUES(@.WhateverID,@.UserID)
End

GO

Friday, March 9, 2012

Experiencing time-outs with INSERT into Large SQL 2000 table

We have a very large SQL 2000 table here (150 million rows) and normally INSERTs happen with no issue. Occasionally, however, the performance is terrible for INSERTs and actually times out. Is there something going on with new the extents allocated for a very large table (i.e., is the number of extents allocated proportional to the size of the table?) .

Consulted offline with SQL Server MVP Kalen Delaney - her response as follows:

"As a file grows, the growth of the files is proportional to the size of the files. However, extents are always allocated to a table one at a time. The slow inserts might be due to page splits requiring a new extent, and there is no free space in any existing extents, and no free extents, so a file has to grow. If your growth amount is large, I have seen this time out."

|||This is correct, the database file was set to autogrow by 10% and is already at 15 GB, so adding 1.5 GB to the file timed out this INSERT. I was able to confirm by looking at the file modification date - it matched exactly with the time that the timeout occurred.

Expensive logical reads on INSERT - SQL 2005

Hi, I have an insert that is taking about 500ms to execute.
This is on a table with about 1,000,000 rows. The table has a number of
indexes, a PK, one default value but no relationships.
The profile displays logical reads=31 writes=6.
The statement is SQL in an app rather than a stored procedure.
Would anyone know why I have so many writes on a simple insert statement?
Many thanks,
Robert
Each index requires a write, as well as the table entry.
How many indexes?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:154CA998-B62F-4871-B739-FE830AE0114F@.microsoft.com...
> Hi, I have an insert that is taking about 500ms to execute.
> This is on a table with about 1,000,000 rows. The table has a number of
> indexes, a PK, one default value but no relationships.
> The profile displays logical reads=31 writes=6.
> The statement is SQL in an app rather than a stored procedure.
> Would anyone know why I have so many writes on a simple insert statement?
> Many thanks,
> Robert
>
|||Hi Arnie, thank you for the answer.
I pretty much understand the writes - will be the data and the overhead of
updating the indexes. The table has a primary key index and a few
non-clustered indexes spanning one or more columns.
I do not understand the reads though. Are these to do with the indexes too?
Other insert/updates do not seem to have this overhead.
Regards,
Robert
"Arnie Rowland" wrote:

> Each index requires a write, as well as the table entry.
> How many indexes?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:154CA998-B62F-4871-B739-FE830AE0114F@.microsoft.com...
>
>

Expensive logical reads on INSERT - SQL 2005

Hi, I have an insert that is taking about 500ms to execute.
This is on a table with about 1,000,000 rows. The table has a number of
indexes, a PK, one default value but no relationships.
The profile displays logical reads=31 writes=6.
The statement is SQL in an app rather than a stored procedure.
Would anyone know why I have so many writes on a simple insert statement?
Many thanks,
RobertEach index requires a write, as well as the table entry.
How many indexes?
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:154CA998-B62F-4871-B739-FE830AE0114F@.microsoft.com...
> Hi, I have an insert that is taking about 500ms to execute.
> This is on a table with about 1,000,000 rows. The table has a number of
> indexes, a PK, one default value but no relationships.
> The profile displays logical reads=31 writes=6.
> The statement is SQL in an app rather than a stored procedure.
> Would anyone know why I have so many writes on a simple insert statement?
> Many thanks,
> Robert
>|||Hi Arnie, thank you for the answer.
I pretty much understand the writes - will be the data and the overhead of
updating the indexes. The table has a primary key index and a few
non-clustered indexes spanning one or more columns.
I do not understand the reads though. Are these to do with the indexes too?
Other insert/updates do not seem to have this overhead.
Regards,
Robert
"Arnie Rowland" wrote:
> Each index requires a write, as well as the table entry.
> How many indexes?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to the
> top yourself.
> - H. Norman Schwarzkopf
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:154CA998-B62F-4871-B739-FE830AE0114F@.microsoft.com...
> > Hi, I have an insert that is taking about 500ms to execute.
> > This is on a table with about 1,000,000 rows. The table has a number of
> > indexes, a PK, one default value but no relationships.
> > The profile displays logical reads=31 writes=6.
> > The statement is SQL in an app rather than a stored procedure.
> > Would anyone know why I have so many writes on a simple insert statement?
> > Many thanks,
> >
> > Robert
> >
>
>|||I believe that Profiler includes information such as reading system catalogs (like sysobjects and
syscolumns, syspermissions). This might explain some of those reads. Also, look ups for
check-constraints. Just a guess, though...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DC5309E8-85FC-4D46-9C73-9DB90F0A7347@.microsoft.com...
> Hi Arnie, thank you for the answer.
> I pretty much understand the writes - will be the data and the overhead of
> updating the indexes. The table has a primary key index and a few
> non-clustered indexes spanning one or more columns.
> I do not understand the reads though. Are these to do with the indexes too?
> Other insert/updates do not seem to have this overhead.
> Regards,
> Robert
> "Arnie Rowland" wrote:
>> Each index requires a write, as well as the table entry.
>> How many indexes?
>> --
>> Arnie Rowland, Ph.D.
>> Westwood Consulting, Inc
>> Most good judgment comes from experience.
>> Most experience comes from bad judgment.
>> - Anonymous
>> You can't help someone get up a hill without getting a little closer to the
>> top yourself.
>> - H. Norman Schwarzkopf
>>
>> "Robert" <Robert@.discussions.microsoft.com> wrote in message
>> news:154CA998-B62F-4871-B739-FE830AE0114F@.microsoft.com...
>> > Hi, I have an insert that is taking about 500ms to execute.
>> > This is on a table with about 1,000,000 rows. The table has a number of
>> > indexes, a PK, one default value but no relationships.
>> > The profile displays logical reads=31 writes=6.
>> > The statement is SQL in an app rather than a stored procedure.
>> > Would anyone know why I have so many writes on a simple insert statement?
>> > Many thanks,
>> >
>> > Robert
>> >
>>|||On Thu, 2 Nov 2006 11:50:02 -0800, Robert wrote:
>Hi Arnie, thank you for the answer.
>I pretty much understand the writes - will be the data and the overhead of
>updating the indexes. The table has a primary key index and a few
>non-clustered indexes spanning one or more columns.
>I do not understand the reads though. Are these to do with the indexes too?
>Other insert/updates do not seem to have this overhead.
>Regards,
>Robert
Hi Robert,
In addition to what Tibor writes, the DB will also have to read root and
intermediate level pages of the index in order to find the correct leaf
page to insert the new row.
--
Hugo Kornelis, SQL Server MVP

Expensive logical reads on INSERT - SQL 2005

Hi, I have an insert that is taking about 500ms to execute.
This is on a table with about 1,000,000 rows. The table has a number of
indexes, a PK, one default value but no relationships.
The profile displays logical reads=31 writes=6.
The statement is SQL in an app rather than a stored procedure.
Would anyone know why I have so many writes on a simple insert statement?
Many thanks,
RobertEach index requires a write, as well as the table entry.
How many indexes?
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
You can't help someone get up a hill without getting a little closer to the
top yourself.
- H. Norman Schwarzkopf
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:154CA998-B62F-4871-B739-FE830AE0114F@.microsoft.com...
> Hi, I have an insert that is taking about 500ms to execute.
> This is on a table with about 1,000,000 rows. The table has a number of
> indexes, a PK, one default value but no relationships.
> The profile displays logical reads=31 writes=6.
> The statement is SQL in an app rather than a stored procedure.
> Would anyone know why I have so many writes on a simple insert statement?
> Many thanks,
> Robert
>|||Hi Arnie, thank you for the answer.
I pretty much understand the writes - will be the data and the overhead of
updating the indexes. The table has a primary key index and a few
non-clustered indexes spanning one or more columns.
I do not understand the reads though. Are these to do with the indexes too?
Other insert/updates do not seem to have this overhead.
Regards,
Robert
"Arnie Rowland" wrote:

> Each index requires a write, as well as the table entry.
> How many indexes?
> --
> Arnie Rowland, Ph.D.
> Westwood Consulting, Inc
> Most good judgment comes from experience.
> Most experience comes from bad judgment.
> - Anonymous
> You can't help someone get up a hill without getting a little closer to th
e
> top yourself.
> - H. Norman Schwarzkopf
>
> "Robert" <Robert@.discussions.microsoft.com> wrote in message
> news:154CA998-B62F-4871-B739-FE830AE0114F@.microsoft.com...
>
>|||I believe that Profiler includes information such as reading system catalogs
(like sysobjects and
syscolumns, syspermissions). This might explain some of those reads. Also, l
ook ups for
check-constraints. Just a guess, though...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Robert" <Robert@.discussions.microsoft.com> wrote in message
news:DC5309E8-85FC-4D46-9C73-9DB90F0A7347@.microsoft.com...[vbcol=seagreen]
> Hi Arnie, thank you for the answer.
> I pretty much understand the writes - will be the data and the overhead of
> updating the indexes. The table has a primary key index and a few
> non-clustered indexes spanning one or more columns.
> I do not understand the reads though. Are these to do with the indexes too
?
> Other insert/updates do not seem to have this overhead.
> Regards,
> Robert
> "Arnie Rowland" wrote:
>|||On Thu, 2 Nov 2006 11:50:02 -0800, Robert wrote:

>Hi Arnie, thank you for the answer.
>I pretty much understand the writes - will be the data and the overhead of
>updating the indexes. The table has a primary key index and a few
>non-clustered indexes spanning one or more columns.
>I do not understand the reads though. Are these to do with the indexes too?
>Other insert/updates do not seem to have this overhead.
>Regards,
>Robert
Hi Robert,
In addition to what Tibor writes, the DB will also have to read root and
intermediate level pages of the index in order to find the correct leaf
page to insert the new row.
Hugo Kornelis, SQL Server MVP

Expects parameter which was not supplied....need help....

I wanted to add new record into two different tables. By using one web form...
Not so sure I coded it correctly. And I got this error trying to insert new record

" Procedure 'sp_insert' expects parameter '@.mid', which was not supplied. "

Isn't it i have include @.mid in my code ...??

<STORED PROCEDURES>
sp_insert

(
@.mid char(10),
@.status char(10),
@.name char(20),
@.dj char(20),
@.rank char(20),
@.nric char(14),
@.dob char(20),
@.age char(10),
@.add char(80),
@.school char(50),
@.hp char(10),
@.mp char(10),
@.email char(30),
@.pgname char(20),
@.relationship char(20),
@.contact char(10)

)
AS
insert into Table1(mid,status,name,date_joined,rank,nric,dob,age,address,school,house_phone,Mobile_phone,email,[P/G_name],[P/G_relationship],[P/G_contacts])
values (@.mid,@.status,@.name,@.dj,@.rank,@.nric,@.dob,@.age,@.add,@.school,@.hp,@.mp,@.email,@.pgname,@.relationship,@.contact)

insert into Table2 (name) values (@.name)

RETURN

<WEB FORM>
mycommand = New SqlCommand("sp_insert", mycon)
mycommand.CommandType = CommandType.StoredProcedure

Dim midpar As New SqlParameter("@.mid", SqlDbType.Char, 10)
midpar.Direction = ParameterDirection.Input
midpar.Value = Tb1.Text

Dim statuspar As New SqlParameter("@.status", SqlDbType.Char, 10)
statuspar.Direction = ParameterDirection.Input
statuspar.Value = ddl1.SelectedItem.Text

---
---
---Your webform code is wrong, needs to be something like this:

Dim sqlPar As SqlParameter= myCommand.Parameter.Add("@.mid", SqlDbType.Char, 10)
sqlPar.Direction = ParameterDirection.Input
sqlPar.Value = Tb1.Text

sqlPar = myCommand.Parameter.Add("@.status", SqlDbType.Char, 10)
sqlPar.Direction = ParameterDirection.Input
sqlPar.Value = ddl1.SelectedItem.Text


etc.
etc.
etc.

Expanson on oprevious T-SQL help

I have been trying to expand on a query given to me to insert records from
one SQL2000 server table to a SQL2005 Server table. The query works like a
dream, however i would like to expand it slightly to become an UPDATE query
Orig Query
INSERT INTO
BossData.dbo.BacsHdrYearly
select * from
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SQL2000;User ID=@.USERNAME;Password=@.Password'
).BossData.dbo.BacsHdrYearly A
where not exists( select * from BossData.dbo.BacsHdrYearly
B where a.BHYear_LedgerKey =
b.BHYear_LedgerKey)
these are the fields in the table that need to be updated
BHYear_SubmissionType,
BHYear_FileStatus ,
BHYear_BACSTransmitted,
InvoiceNumber,
SubIndex
any pointers would be most helpful
Thank's in advancePeter
Let me understand your requiremnt. Could issue an UPDATE statement on
BacsHdrYearly now that INSERT INTO?
Do you want to UPDATE "directly" via OPENDATASOURCE command?
Can you create a linked server to SQL Server 2005 Server and use
UPDATE Table SET col=SERVER2000.dbname.dbo.tablename.col1............
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:36F3A117-2DC7-4CB2-BA77-6D9D3C80EA86@.microsoft.com...
>I have been trying to expand on a query given to me to insert records from
> one SQL2000 server table to a SQL2005 Server table. The query works like
> a
> dream, however i would like to expand it slightly to become an UPDATE
> query
>
> Orig Query
> INSERT INTO
> BossData.dbo.BacsHdrYearly
> select * from
> OPENDATASOURCE(
> 'SQLOLEDB',
> 'Data Source=SQL2000;User ID=@.USERNAME;Password=@.Password'
> ).BossData.dbo.BacsHdrYearly A
> where not exists( select * from BossData.dbo.BacsHdrYearly
> B where a.BHYear_LedgerKey =
> b.BHYear_LedgerKey)
> these are the fields in the table that need to be updated
> BHYear_SubmissionType,
> BHYear_FileStatus ,
> BHYear_BACSTransmitted,
> InvoiceNumber,
> SubIndex
> any pointers would be most helpful
> Thank's in advance|||rei,
i was given the INsetr statement which works fine to insert the data into my
dev sysetm, what i want to do is keep it updated. I have not created a linke
d
server, perfer to use opendatasource
"Uri Dimant" wrote:

> Peter
> Let me understand your requiremnt. Could issue an UPDATE statement on
> BacsHdrYearly now that INSERT INTO?
> Do you want to UPDATE "directly" via OPENDATASOURCE command?
> Can you create a linked server to SQL Server 2005 Server and use
> UPDATE Table SET col=SERVER2000.dbname.dbo.tablename.col1............
> "Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
> news:36F3A117-2DC7-4CB2-BA77-6D9D3C80EA86@.microsoft.com...
>
>|||Ok, so what is wrong with linked server?
"Peter Newman" <PeterNewman@.discussions.microsoft.com> wrote in message
news:4B14FBAF-D3AC-4C80-9554-5A1F4D9FDFD4@.microsoft.com...
> rei,
> i was given the INsetr statement which works fine to insert the data into
> my
> dev sysetm, what i want to do is keep it updated. I have not created a
> linked
> server, perfer to use opendatasource
> "Uri Dimant" wrote:
>|||Did you try this. did it work. what is the error its giving?
update B set col1 = A.col1, col2 = a.col2
from BossData.dbo.BacsHdrYearly B,
OPENDATASOURCE(
'SQLOLEDB',
'Data Source=SQL2000;User ID=@.USERNAME;Password=@.Password'
).BossData.dbo.BacsHdrYearly A
where a.pkcol = b.pkcol

Friday, February 24, 2012

Execution TIme Problem

I am trying to run a SQL Server procedure from a program in ASP.Net 2005. This procedure is to insert around 500 records(can exceed every month) in a table with 4 columns and is also containing another small procedure also. When this procedure is executed from online server, it shows timeout message as:

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

But when the same procedure is run from SQL Query Anayser it excute within seconds. How can i solve this problem , i need this solution urgently too.

Hope to get ur response soon.

Hello romita

Do you mind sharing some code. It would be helpful to try and figure out the problem

Also are you able to run SQL Profiler to check that statments that are hitting the databse from the aps.net application.

regards

G

|||

Hi Gonzo11,

Regarding the same problem posted yesterday, am enclosing the code of procedure below for ur reference. Please check n let me know if theres any solution.

CREATE PROCEDURE proc_userroyalty
@.month int, @.year int, @.joins int output, @.royalties int output, @.royaltyamt decimal output, @.netamt decimal output

as

declare @.joinamt bigint, @.user varchar(50), @.left int, @.middle int, @.right int, @.royalty decimal, @.regdate datetime , @.totalpairs int, @.lsdate varchar(50), @.bdate datetime, @.maxamt decimal, @.nxtmon int, @.totalroyal decimal, @.nxtdt datetime, @.nxtyr int

if exists(select * from db_bonusdetails where bd_purpose='R' and month(bd_date)=@.month and year(bd_date)=@.year and @.month<month(getdate()) and @.year<=year(getdate()) )
begin
select @.joins = count(*) from db_users where month(us_regdate)=@.month and yeaR(us_regdate)=@.year
select @.royalties=count(*) from db_tempbonus where temp_month<=@.month andtemp_year=@.year
if @.month<8 and @.year <=2007
begin
select @.joinamt=@.joins * 100
end
else
begin
select @.joinamt=@.joins * 120
end

select @.royaltyamt = floor(@.joinamt / @.royalties)
select @.netamt=sum(bd_amount) from db_bonusdetails where bd_purpose='R' and month(bd_date)=@.month and year(bd_date)=@.year group by bd_amount

end
else
begin
delete from db_bonusdetails where bd_purpose='R' and datepart(mm,bd_date)=@.month and datepart(yyyy,bd_date)=@.year

select @.joins=count(*) from db_users where datepart(month,us_regdate) = @.month and datepart(year,us_regdate) = @.year and us_status='Y'

exec proc_royal @.month, @.year
select @.royalties=count(*) from db_tempbonus where temp_month<=@.month andtemp_year=@.year

select @.joinamt=@.joins * 120

if @.royalties > 0
begin

select @.royaltyamt = floor(@.joinamt / @.royalties)
select @.lsdate=str(@.month) + '-' + '28' + '-' + str(@.year)
select @.bdate=convert(datetime,@.lsdate)


declare cur_bonus cursor
for select temp_user,temp_regdt from db_tempbonus where temp_month<=@.month andtemp_year=@.year

open cur_bonus
fetch next from cur_bonus into @.user,@.regdate

while @.@.fetch_status = 0
begin
select @.nxtdt=dateadd(mm,3,@.regdate)

if datediff(dd,@.nxtdt,getdate()) > 0
begin
select @.nxtmon=month(@.nxtdt)
select @.nxtyr=year(@.nxtdt)

SELECT @.totalpairs= SUM(mp_lcount) + SUM(mp_mcount) + SUM(mp_rcount)
FROM db_monthlypairs
WHERE (mp_month<=@.nxtmon ) and (mp_year <=@.nxtyr) AND (mp_user = @.user)

if @.totalpairs< 30
begin
select @.maxamt=25000
end
else
begin
select @.maxamt=50000
end
end
else
begin
select @.maxamt=50000
end

select @.totalroyal=sum(bd_amount) from db_bonusdetails wherebd_user=@.user and bd_purpose='R'

if @.totalroyal <@.maxamt or @.totalroyal is null
begin
insert into db_bonusdetails values(@.bdate,@.user,'R',@.royaltyamt)
end

fetch next from cur_bonus into @.user,@.regdate
end

close cur_bonus
deallocate cur_bonus

select @.netamt=sum(bd_amount) from db_bonusdetails where bd_purpose='R' and datepart(mm,bd_date)=@.month and datepart(yyyy,bd_date)=@.year

end
else
begin
select @.royalties=0
select @.royaltyamt=0
select @.netamt=0
end
end
GO

PROCEUDRE 2 - proc_royal to be executed within above one.

CREATE PROCEDURE proc_royal
@.month int , @.year int
AS


declare @.user varchar(50), @.regdt datetime, @.lcount int , @.mcount int, @.rcount int, @.uspos char(1), @.rows int, @.mainuser varchar(50), @.regdate datetime

declare cur1 cursor
for select mp_user, us_regdate from db_users,db_monthlypairs where mp_user=us_login and mp_month<=@.month and mp_year<=@.year and mp_user not in(select temp_user from db_tempbonus where temp_month<=@.month and temp_year<=@.year)
group by mp_user, us_regdate having sum(mp_lcount)>=1 and sum(mp_mcount) >=1 and sum(mp_rcount)>=1
open cur1
fetch cur1 into @.mainuser, @.regdate
while @.@.fetch_status=0
begin
declare cur2 cursor

for select us_login, us_regdate, us_position from db_users whereus_reference=@.mainuser and month(us_regdate) <= @.month and year(us_regdate)<=@.year
open cur2
fetch cur2 into @.user, @.regdt, @.uspos
while @.@.fetch_status=0
begin
if @.uspos ='L'
begin
select @.lcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.lcount = @.lcount + 1
end
else
begin
if @.uspos ='M'
begin
select @.mcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.mcount = @.mcount + 1
end
else
begin
select @.rcount=count(*) from db_users whereus_reference=@.user and month(us_regdate) <= @.month and year(us_regdate)<=@.year
select @.rcount = @.rcount + 1
end

end
fetch cur2 into @.user, @.regdt, @.uspos
end

close cur2
deallocate cur2

if @.lcount>=4 and @.mcount>=4 and @.rcount >=4
begin
insert into db_tempbonus values(@.mainuser, @.regdate, @.month, @.year)
end
fetch cur1 into @.mainuser, @.regdate
end

close cur1
deallocate cur1
GO

proc_royal is to add new members satisfying the condtitions to a table eah month. In the main procedure based on all records in this table that many records will be inserted in another one. This part came as updation in the project, so its very risky to change other codes as it might affect existing program.

Hope u got the idea of the procedure.

If theres any simple way with less execution time please let me know. By each month records can increase too, so solution has to be one for furture user too.

Awaiting ur response,

Regards,

Romita


|||

Basedon your post, the error occurs when the execution time beyond TimeoutProperty. When you are trying to connect or access to a Database tablewhich is having large volume of data, query execution time will bemore. There are two main Timeout property in ADO.NET.

1.Connection Timeout for Connection. It could be solved by settingConnectionTimeout property of Connection object in Connection String.
2. Timeout for Data access ( Command Object ). You can setCommandTimeoutproperty to Command object.I recommend you set CommandTimeOut propertyto bigger one value. Try this.

Please let me know whether this canhandle this problem or not.If you have any further questions, please feel free to let me know.

|||

Hello Romita,

Thank you for you reply. I will need some time to go over you SP.

In the meantime are you able to run the SQL Profiler and check what is actually hitting the DB and what is taking the longest time.

regards,

G

|||

Hi!

I have tried setting command timeout and connection timeout properties, but still this Time Out Expired exists. Just setting the Timeout property of each object is enough or some other changes are also reqd.

Actually i had tried executing the procedure without calling another procedure within, it didn;t solve my problem. Time is taking to insert 400-500 records at a time, but it cannot be programmed in other way too.

Pls let me know, if theres more to do in Timeout settings or if theres another solution.

Regards,

Romita

|||

Hello Romita,

I have looked at the code that you have sent but i fyou say that it is inserting 400-500 rows that generaly should not take a very long time.

You have to see which parts of the SP are taking up most of the time and address them first.

So, you would have to run SQL Server Profiler, that will help you identify what is hitting the database and what is taking the most time.
Once you identify that you can try to address that issue. For example, very often creating an index for a table ususaly helps.

Hope this helps

regards,
G