Thursday, March 29, 2012
export DBCC CHECKDB into 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?
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
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
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
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
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
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
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
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
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
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
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
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_NULLSONGO
SET
QUOTED_IDENTIFIERONGO
-- =============================================
-- 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
uniqueidentifierAS
Begin
SetNoCountonDECLARE @.WhateverIDuniqueidentifierINSERT
INTO tblMessages(UserIDSender,MessageTitle,MessageContent,MessageType)VALUES
(@.UserIDSender,@.MessageTitle,@.MessageContent,@.MessageType)Select @.WhateverID=@.@.IdentityINSERT
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?!?
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
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
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
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
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....
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.TextsqlPar = 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
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