for some reason my loop stops when i = 1 :s
Statement s7 = MySql.connection.createStatement();
for(int i = 0; i < 50 ; i++) {
String test = "INSERT ignore INTO `testtable` (`name`,`Item"+i+"`,`Amount"+i+"`) values ('"+getUsername()+"','"+getItem(i)+"','"+getAmount(i)+"')";
s7.executeUpdate(test);
}
Can anyone help me please???
I use loop to insert few record into a table:But the for_Loop only loop once and throw an error:"The variable name '@res_name' has already been declared. Variable names must be unique within a query batch or stored procedure."What should i do to get this fix?Code:Protected Sub confirm_button_Click(ByVal sender As Object, ByVal e As System.EventArgs)Dim DataSources1 As New SqlDataSource()DataSources1.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()DataSources1.InsertCommandType = SqlDataSourceCommandType.TextDataSources1.InsertCommand = "INSERT INTO cust_order (res_name, my_menu) VALUES (@res_name, @my_menu)"Dim c As IntegerFor c = 0 To selectListBox.Items.Count - 1 Step +2DataSources1.InsertParameters.Add("res_name", selectListBox.Items(c).Text)DataSources1.InsertParameters.Add("my_menu", selectListBox.Items(c + 1).Text)DataSources1.Insert()Next End Sub
View 2 Replies View RelatedHello! quick question.
Say I have a table, and I select the primary key as follows:
select fID from findings;
how can I loop through that resultset and execute the following insert at each iteration:
insert into owners (fID, uID) values (@curFID, 273);
where @curFID is the current record from the resultset? I've done some playing with while loops, however the tutorials I found were fairly basic and only provided examples using static loop controls. any help is appreciated!
Hi friends, I've been stumped on this for almost a week now. Everything works in the stored procedure code below except for the 'INSERT INTO @Uppdates' block of code. I have a SQL Analyzer test driver and when the code gets to the SELECT statement below the INSERT INTO @Updates line the value of the select line is displayed on the screen and nothing gets written to @Updates. I hope I'm being clear about this. Any ideas? IF @Edit=1 AND LEN(@Changes) > 0
BEGIN
--Split and parse changes
DECLARE @curRec int, @nxtRec int, @Record varchar(8000), @TNum int, @TNam varchar(50), @PDesc varchar(512), @PChk varchar(8), @SNum varchar(12), @NScr varchar(10), @OScr varchar(10),
@curField int, @nxtField int, @curSRec int, @nxtSRec int, @subRec varchar(8000), @curSField int, @nxtSField int
DECLARE @NewProj table (
ProjectID int,
SchoolNumber varchar(20),
ArtTeacherNumber int,
TeacherNumber int,
TeacherName varchar(40),
ProjectDescription varchar(512) NULL,
[Checksum] varchar(20) NULL)
DECLARE @Updates table (
ProjectID int,
StudentNumber varchar(12),
NewScore varchar(10) NULL,
OldScore varchar(10) NULL)
SET @curRec = 1
WHILE @curRec IS NOT NULL
BEGIN
SET @nxtRec = NULLIF(CHARINDEX(CHAR(1), @Changes, @curRec), 0)
SET @Record = SUBSTRING(@Changes, @curRec, ISNULL(@nxtRec,8000)-@curRec) --Extract a class record
SET @curField = 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNum = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Number
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @TNam = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Teacher Name
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(2), @Record, @curField), 0)
SET @PDesc = SUBSTRING(@Record, @curField, ISNULL(@nxtField,1000)-@curField) -- Extract Project Description
SET @curField = @nxtField + 1
SET @nxtField = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)-- Step over existing checksum
SET @PChk = RIGHT('0000000' + dbo.int2base(Checksum(@PDesc),16),8)-- Calculate new checksum based on project description that may have been changed.
SET @curField = @nxtField + 1
INSERT INTO @NewProj (ProjectID, SchoolNumber, ArtTeacherNumber, TeacherNumber, TeacherName, ProjectDescription, [Checksum])
SELECT DISTINCT Students.ProjectID, @SchoolNumber, @ArtTeacherNumber, @TNum, @TNam, @PDesc, @PChk
FROM @Students Students
WHERE Students.SchoolNumber=@SchoolNumber AND Students.TeacherNumber=@TNum
SET @curSRec = 1
WHILE @curSRec IS NOT NULL
BEGIN
SET @nxtSRec = NULLIF(CHARINDEX(CHAR(3), @Record, @curField), 0)
SET @subRec = SUBSTRING(@Record, @curField, ISNULL(@nxtSRec,8000)-@curField) -- Extract a score sub record. Consists of Student Number, new Score, old Score.
SET @curSField = 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @SNum = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract Student Number
SET @curSField = @nxtSField + 1
SET @nxtSField = NULLIF(CHARINDEX(CHAR(4), @subRec, @curSField), 0)
SET @NScr = SUBSTRING(@subRec, @curSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract new Score
SET @curSField = @nxtSField + 1
IF @curSField > LEN(@subRec)
SET @Oscr = NULL-- If no Old Score specified
ELSE
BEGIN
SET @nxtSField = LEN(@subRec) + 1
SET @OScr = SUBSTRING(@subRec, @CurSField, ISNULL(@nxtSField, 1000)-@curSField) -- Extract old Score
END
-- Check for errors
IF ISNUMERIC(@SNum) = 0 OR @NScr IS NULL OR LEN(ISNULL(@PChk,0)) <> 8
BEGIN
SET @UpdateErr = 1
BREAK
END
-- Update the updates table and find ProjectID from existing data table
INSERT INTO @Updates (ProjectID, StudentNumber, NewScore, OldScore)
SELECT DISTINCT Students.ProjectID, @SNum, @NScr, @OScr
FROM @Students Students
WHERE Students.StudentNumber=@SNum
SET @curField = @nxtSRec + 1
SET @curSRec = @nxtSRec + 1
select * from @Updates
END
IF @UpdateErr = 1
BEGIN
BREAK
END
SET @curRec = @nxtRec + 1
END
Thanks in advance for looking at this,
I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
sqlServerCommand.ExecuteNonQuery()Next
sqlServerConn.Close()
objConn.Close()
End Sub
Thanks for eveones input in advance.
I am trying to insert each record coming from my DataTable object to sql server database. The problem that I have is that I have my stored procedures within the loop and it work only for one record, because it complaing that there are too many parameters. Is there a way i can add up my parameters before the loop and avoid this issue?
Here is the code I am using:Public Sub WriteToDB(ByVal strDBConnection As String, ByVal strFileName As String, ByVal strTable As String)
'Fill in DataTable from AccessDim objConn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strFileName)
Dim adapter As New OleDbDataAdapter()Dim command As New OleDbCommand
Dim DataTable As New DataTableDim sqlCommand = "SELECT * FROM " & strTableDim objDataTable As New DataTable
objConn.Open()
command.CommandType = CommandType.Text
command.Connection = objConn
command.CommandText = sqlCommandadapter = New OleDbDataAdapter(command)DataTable = New DataTable("NFS")
adapter.Fill(DataTable)
'Sql DB vars
'Dim dtToDBComm = "INSERT INTO NFS_Raw(Time, Exch, Status) VALUES ('test', 'test', 'test')"Dim sqlServerConn As New SqlConnection(strDBConnection)Dim sqlServerCommand As New SqlCommand
sqlServerCommand = New SqlCommand("InsertFromAccess", sqlServerConn)
sqlServerCommand.CommandType = CommandType.StoredProcedure
sqlServerConn.Open()For Each dr As DataRow In DataTable.Rows
sqlServerCommand.Parameters.Add(New SqlParameter("@Time", dr.ItemArray(0).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Exch", dr.ItemArray(1).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Status", dr.ItemArray(2).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Msg", dr.ItemArray(3).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Action", dr.ItemArray(4).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@BS", dr.ItemArray(5).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OC", dr.ItemArray(6).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CP", dr.ItemArray(7).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Qty", dr.ItemArray(8).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Product", dr.ItemArray(9).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@MMMYY", dr.ItemArray(10).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Strike", dr.ItemArray(11).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Limit", dr.ItemArray(12).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@StopPrc", dr.ItemArray(13).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Type", dr.ItemArray(14).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Rstr", dr.ItemArray(15).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TIF", dr.ItemArray(16).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@RstrQty", dr.ItemArray(17).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExecQty", dr.ItemArray(18).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@WorkQty", dr.ItemArray(19).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@CxlQty", dr.ItemArray(20).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@AccountNum", dr.ItemArray(21).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchMbrID", dr.ItemArray(22).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchGrpID", dr.ItemArray(23).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTrdID", dr.ItemArray(24).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@MemberID", dr.ItemArray(25).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@GroupID", dr.ItemArray(26).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@NTrdID", dr.ItemArray(27).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@Acct", dr.ItemArray(28).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@ExchTime", dr.ItemArray(29).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchDate", dr.ItemArray(30).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeSent", dr.ItemArray(31).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@TimeProcessed", dr.ItemArray(32).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@PA", dr.ItemArray(33).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@OrderNo", dr.ItemArray(34).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TTOrderKey", dr.ItemArray(35).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@IP", dr.ItemArray(36).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@FFT3", dr.ItemArray(37).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FFT2", dr.ItemArray(38).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitTime", dr.ItemArray(39).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SubmitDate", dr.ItemArray(40).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@TransID", dr.ItemArray(41).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@SessionID", dr.ItemArray(42).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@SeriesKey", dr.ItemArray(43).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@ExchangeOrderID", dr.ItemArray(44).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@Destination", dr.ItemArray(45).ToString()))
sqlServerCommand.Parameters.Add(New SqlParameter("@FlowDeliveryUnit", (dr.ItemArray(46))))sqlServerCommand.Parameters.Add(New SqlParameter("@TimeReceived", dr.ItemArray(47).ToString()))sqlServerCommand.Parameters.Add(New SqlParameter("@CallbackReceived", dr.ItemArray(48).ToString()))
sqlServerCommand.ExecuteNonQuery()Next
sqlServerConn.Close()
objConn.Close()
End Sub
Thanks for eveones input in advance.
I want to insert data (month&year) from 2014 till now - into temp table using 2 while loop.
drop table #loop
create table #loop
(
seq int identity(1,1),
[month] smallint,
[Year] smallint
For some reason I cant not get 2015 data .
Is there a way to insert multiple records into a database table when you're just given "count" of the number of rows you want? I want to do this in ONE insert statment, so I don't want a solution that loops round doing 100 inserts - that would be too inefficient.
For example, suppose I want to create 100 card records starting it card number '1234000012340000'. Something like this ...
declare @card_start dec(16)
set @card_start = '1234000012340000'
declare @card_count int
set @card_count = 100
drop table card_table
create table card_table (
card_number dec(16),
activated char default 'N'
)
insert into card_table
select
... ???? ....
But WITHOUT using a while-loop (or any other kind of loop). I'm looking for fast and efficient code! Thanks.
I have the following attributes in this Table A.
1) Location_ID (int)
2) Serial_Number (nvarchar(Max))
3) KeyID (nvarchar(max)
4) Reference_Address (nvarchar(max)
5) SourceTime (datetime)
6) SourceValue (nvarchar)
I am trying to create 1000000 dummy records in this this table A.How do i go about do it? I would like my data to be something like this
LOCATION_ID
1
Serial Number
SN-01
KeyID
E1210
Reference_Address
83
SourceTime
2015-05-21 00:00:00 000
SourceValue
6200
difference between FOR LOOP and FOREACH LOOP with example(if possible) in SSIS.
View 4 Replies View RelatedI have a table with RowID(identity). I need to loop though the table using RowID(not using a cursor). Please help me.
Thanks
Hi,
I'm new to this SQL thing, and I inherited a DB that has a job that runs every two hours 24/7. Normally the process takes about 5 minutes to complete, but on occasion it just doesn't finish. In those cases, it cannot run at the next two hour cycle. If this happens on a Friday evening, it doesn't get noticed until Monday morning. That is a Bad Thing (tm).
Someone told me to create a second job that would run 45 minutes behind the first and automatically stop the first job if it hadn't terminated on it's own. My problem is I cannot find anywhere how to do this. I have found how to start other jobs, but not stop them.
Thanks in advance!
- Adam
It appears that our web server (IIS 4) stopped responding after running
some BCP jobs. Anyone know why BCP would interfere with IIS.
SQL and IIS are on the same server. Anyone had problems with IIS/SQL getting along?
After the problem showed up, SQL responds fine in ISQLw, but the Web service goes out to lunch.
I scheduled jobs to take backups, but i found out that sqlserver agent is stopping by itself, what could be the solution for this problem.
Thanks in advance
I created some test data in two tables. Then I went to one of the tables and right clicked then Selected Script Table asSelect ToNew Query Editor Window. I then cleared the generated data and selectedDesign Query in Editor. I then picked both tables in the 'Add Table' window and picked 'Add'. This produced two windows and I made the links I needed and clicked OK. This generated the SQL I wanted and it works great! But when I close the SQL Express and then re-open it the SQL does not work! I open SQL Express and selectFileOpenFile and pick the SQL I had just saved. Then I pick Execute I get the following message.
Msg 208, Level 16, State 1, Line 1
Invalid object name 'dbo.FileData'
How can I save SQL that works and then not be able to use it?
Beats Me,
Miname
I have had my site running for several months now. My pages retrieve data from the SQL Server on the same machine. Today my users are are getting the message "SQL Server does not exist or access denied"
I have made sure that the credentials are correct. I can use those credentials to create a DSN on the server. So I don't know where to look for the problem. Help please!
I have created a stored procedure in SQL Server. I found it very slow, so i putted "select getDate(), 'testposition 1'" at different places, so I could see what part of the code that takes time.
The problem is: Depending on where I put the select statements, the execution of the stored procedure seems to just stop. And depending on where i put the select statements, it stops at different places.
This is how I do (example):
1. I re-create the stored procedure with some "select getDate()"-statements
2. I run the stored procedure 15:00:00
3. I cancel the stored procedure after 20 seconds and look at the resultsets. All getDate-functions show a time between 15:00:00 and 15:00:02
4. I run the stored procedure 15:01:00
5. I cancel the stored procedure after 5 seconds and look at the resultsets. The same amount of resultsets are showed, so I can make the conclusion that the execution stopped at the same place as last time. All getDate-functions show a time between 15:01:00 and 15:01:02 this time too.
6. I re-create the stored procedure with some new "select getDate()"-statements
7. Now the execution stops at an other position. Somtimes even between two "select getDate()"-statements!
I pasted the whole stored procedure here:
drop PROCEDURE spUpdateASW
go
create PROCEDURE spUpdateASW
AS
DECLARE @DataBatchID int
DECLARE @DataHeaderID int
DECLARE @ASWTableID int
DECLARE @ASWTableName varchar(25)
DECLARE @ASWFieldName varchar(25)
DECLARE @AllowASWUpdate tinyint
DECLARE @IsPrimaryKey tinyint
DECLARE @DataTypeIsNumeric tinyint
DECLARE @Data varchar(100)
DECLARE @SQL_Where as varchar(400)
DECLARE @SQL_Insert as varchar(1000)
DECLARE @SQL_InsertValues as varchar(400)
DECLARE @SQL_Update as varchar(1000)
DECLARE @updateCounter int
DECLARE @whereCounter int
DECLARE @SQL_CheckIfAlreadyExist as varchar(1000)
DECLARE @ErrorMessage varchar(500)
DECLARE @RuleWhen as varchar(50)
DECLARE @RuleWhenToExec as varchar(500)
DECLARE @tempStr as varchar(700)
DECLARE @server varchar(50)
DECLARE @shortServer varchar(50)
SET @server = 'GIBSON_A3MFGF_T1.S44E5797.A3MFGFT1'
SET @shortServer = 'GIBSON_A3MFGF_T1'
DECLARE @SQL varchar(5000)
select getdate(), 'testposition 1'
CREATE Table #tmptblUpdateASW(
ASWRowAlreadyExists int,
RuleWhenIsValid int
)
INSERT INTO #tmptblUpdateASW(ASWRowAlreadyExists, RuleWhenIsValid) Values(-1, -1)
DECLARE Batch_Cursor CURSOR LOCAL FOR
SELECT DataBatchID from tblDataBatch
where DateConverted is not null and ASWUpdateStarted = 0
and DataBatchID not IN(
select fkDataBatchID from tblDataHeader where DataHeaderID IN(
select fkDataHeaderID from tblASWData where ConversionErrorMessage is not null
)
)
OPEN Batch_Cursor
FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
WHILE @@FETCH_STATUS = 0
BEGIN
Update tblDataBatch set ASWUpdateStarted = 1 where DataBatchID = @DataBatchID
DECLARE Header_Cursor CURSOR LOCAL FOR
SELECT DataHeaderID
from tblDataHeader
inner join tblAgileFieldType on tblDataHeader.fkAgileFieldTypeID = tblAgileFieldType.AgileFieldTypeID
where fkDataBatchID = @DataBatchID and isSentToASW = 0 order by tblAgileFieldType.InsertOrder
OPEN Header_Cursor
FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE ASWTable_Cursor CURSOR LOCAL FOR
SELECT ASWTableID, ASWTableName, RuleWhen
from tblASWTable
inner join tblASWField on tblASWTable.ASWTableID = tblASWField.fkASWTableID
inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
where fkDataHeaderID = @DataHeaderID
group by ASWTableID, ASWTableName, RuleWhen, InsertOrder
order by InsertOrder
OPEN ASWTable_Cursor
FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
WHILE @@FETCH_STATUS = 0
BEGIN
exec spBuildRuleString @DataHeaderID, @RuleWhen, @RuleWhenToExec output, 0
SET @tempStr = 'IF ' + @RuleWhenToExec + ' UPDATE #tmptblUpdateASW SET RuleWhenIsValid=1 ELSE UPDATE #tmptblUpdateASW SET RuleWhenIsValid=0'
EXEC (@tempStr)
IF (SELECT RuleWhenIsValid FROM #tmptblUpdateASW) = 1
BEGIN
set @ErrorMessage = null
exec spASWDataCheck_hardCoded @DataHeaderID, @ErrorMessage output
SET @SQL_Insert = 'INSERT INTO ' + @server + '.' + @ASWTableName + '('
SET @SQL_InsertValues = 'VALUES('
SET @SQL_Update = 'UPDATE ' + @server + '.' + @ASWTableName + ' set '
SET @updateCounter = 0
SET @SQL_Where = ' WHERE '
SET @whereCounter = 0
DECLARE ASWField_Cursor CURSOR LOCAL FOR
SELECT ASWFieldName, AllowASWUpdate, IsPrimaryKey, DataTypeIsNumeric, Data
from tblASWField
inner join tblASWData on tblASWField.ASWFieldID = tblASWData.fkASWFieldID
where fkASWTableID = @ASWTableID and fkDataHeaderID = @DataHeaderID
OPEN ASWField_Cursor
FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
select getdate(), 'testposition 2'
WHILE @@FETCH_STATUS = 0
BEGIN
select getdate(), @ASWFieldName, 'testposition 3'
set @Data = replace(@Data, char(39), char(39) + char(39))
if @DataTypeIsNumeric = 0
set @Data = char(39) + @Data + char(39)
set @SQL_Insert = @SQL_Insert + @ASWFieldName + ', '
set @SQL_InsertValues = @SQL_InsertValues + @Data + ', '
IF @AllowASWUpdate = 1
BEGIN
set @SQL_Update = @SQL_Update + @ASWFieldName + ' = ' + @Data + ', '
set @updateCounter = @updateCounter + 1
END
IF @IsPrimaryKey = 1
BEGIN
set @SQL_Where = @SQL_Where + @ASWFieldName + ' = ' + @Data + ' and '
SET @whereCounter = @whereCounter + 1
END
FETCH NEXT FROM ASWField_Cursor INTO @ASWFieldName, @AllowASWUpdate, @IsPrimaryKey, @DataTypeIsNumeric, @Data
END
select getdate(), 'testposition 4'
CLOSE ASWField_Cursor
DEALLOCATE ASWField_Cursor
SET @SQL_Where = LEFT(@SQL_Where, LEN(@SQL_Where) - 4)
SET @SQL_Insert = LEFT(@SQL_Insert, LEN(@SQL_Insert) - 1) + ') ' + LEFT(@SQL_InsertValues, LEN(@SQL_InsertValues) - 1) + ')'
SET @SQL_Update = LEFT(@SQL_Update, LEN(@SQL_Update) - 1) + @SQL_Where
SET @SQL_CheckIfAlreadyExist = 'Update #tmptblUpdateASW set ASWRowAlreadyExists = ' +
'(SELECT * from OPENQUERY(' + @shortServer + ','' SELECT count(*) FROM ' + @ASWTableName + ' ' + replace(@SQL_Where,char(39), char(39) + char(39)) + ' ''))'
Exec(@SQL_CheckIfAlreadyExist)
select getdate(), 'testposition 4'
select getdate(), 'testposition 5'
select getdate(), 'testposition 6'
IF @whereCounter = 0
begin
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Inga primary keys var valda för denna tabellen!')
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) > 1
begin
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, '(allvarligt fel. Inget skickades till ASW)', 'Fel! Kombinationen av primary keys genererade följande where-sats: ' + @SQL_Where)
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 1 and @updateCounter > 0
begin
EXEC(@SQL_Update)
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Update, @ErrorMessage)
update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
end
ELSE IF (select ASWRowAlreadyExists from #tmptblUpdateASW) = 0
begin
EXEC(@SQL_Insert)
insert into tblASWUpdateLog(LogTime, fkDataHeaderID, fkASWTableID, ASWAction, ErrorMessage)
values(getDate(), @DataHeaderID, @ASWTableID, @SQL_Insert, @ErrorMessage)
update tblDataHeader set isSentToASW = 1 where DataHeaderID = @DataHeaderID
end
END
FETCH NEXT FROM ASWTable_Cursor INTO @ASWTableID, @ASWTableName, @RuleWhen
END
CLOSE ASWTable_Cursor
DEALLOCATE ASWTable_Cursor
FETCH NEXT FROM Header_Cursor INTO @DataHeaderID
END
CLOSE Header_Cursor
DEALLOCATE Header_Cursor
UPDATE tblDataBatch set DateToASW = getDate() where DataBatchID = @DataBatchID
FETCH NEXT FROM Batch_Cursor INTO @DataBatchID
END
CLOSE Batch_Cursor
DEALLOCATE Batch_Cursor
DROP Table #tmptblUpdateASW
GO
Hi there!!!
We got problem on sql server 2k,
Sql server stops unknowingly, and all user database has marked as Suspect/Offline,
and later on, after sql server stops, all user database has been detached.
what is going on????
Dear Group,I am tring to use a command that calls the server to fill an adapter, itnever seems to get to the adapter, command and the server either times outor does not respond. The timeout is set at 10 hours. I am using VisualStudio to acces MS SQL - Server.I think I have all the rights and permissions set correctly. Also, I haveused this command to fill other adapters and tables.Does anyone have a suggestion.Jeff Magouirk
View 1 Replies View RelatedI have a very weird issue in my latest package. I run it and it just randomly stops. When I watch it in debug mode or not in debugging a command prompt window will flash for an instant (too fast to read) and then the package will stop. It stops inside of a for each loop and the "on completion" is never fired for the loop. I never receive any errors - its just like someone hit the stop button. Sometimes it will process hundreds of files before stopping, other times only a few. (And its not stopping on the same file each time.. it doesn't appear to be related to the file at all)
Any ideas what could be going on? How to troubleshoot?
Have a task that has 120 tables (components) that I am running in debug mode. Just over half of the components run which takes btrieve db and converts into a sybase db. When it stops running there are a few components that are yellow, the components which completed are green and the rest are still white because they have ran yet. The problem is there is not a message to indicate as to why it stopped. I've broken up the task into two tasks and also tried making two projects. The same situation happens at the same point. Our dbas have checked the database to ensure that's fine and it is. Is there some sort of limitation in how many components can be run in debug mode?
View 5 Replies View RelatedI've been coding for a few weeks now, building an ASP.NET application. ran the aspnet_regsql.exe wizard and it created my table and procedures correctly within the live SQL 2005 server being run by my host (ASPNIX.COM). I've been able to run my app just fine locally saving to my remote SQL server. However, now that I've moved my code onto my hosted server, my app stopped working. You would think this would "jus work" since I've been using the same SQL server throughout...BUT NOoooo! I've scoured the net trying to find any hint about what might be happening. The ONLY thing I've been able to find is a story in the May issue of asp.netPRO that says "Once the scripts have been executed, grant the user account used to access the database from the Web application Execute permissions on all the new stored procedures and Selection permissions on the views that were created"This may be my problem, but I haven't figured out how to accomplish this. Within the SQl Server Management Studio Express, I can pull up the properties of each stored procedure and place a check next to my "USER" account. However it does not appear to be saved.My symptoms at this point are:It will not log me in under accounts I'd already created.
View 7 Replies View RelatedHi i have a page in which stock can be allocated, there are two boxes which have a product serial number start range and a product serial number end range, when these boxes are filled the "allocate" button is then clicked and the product will then be allocated the serial numbers.
What i want to happen is that when the start and end ranges have been entered into the text boxes it will fail if any number within the range has already been allocated previously. E.g
Start Range
End Range
So lets say in the start range text box 15 is entered and in the end range 25 is entered, however 18 has already been allocated previously, this will then bring up a message saying please select another range;
I have done the following so far;private void ValidateRange()
{
//String strSql;SqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["strConnectionString"]);
String strSql = "SELECT SqlCommand dbCommand4 = new SqlCommand(strSql, conn);
dbCommand4.Parameters.Add("@Start_RangeID", txt_Start_Range.Text);dbCommand4.Parameters.Add("@End_RangeID", txt_End_Range.Text);
dbCommand4.Connection = conn;
conn.Open();SqlDataReader myDataReader = dbCommand4.ExecuteReader();
myDataReader.Read();if (txt_Start_Range.Text == Convert.ToString(myDataReader["Serial_No"]))
{lblRange.Text = "Please enter a different range a portion of the selected values have already been allocated";
//Response.Redirect("Selectedfilm.aspx");
}
else
{Allocate(true);
}
conn.Close();
}
My problem is constructing the select statement, thanks.
We are currently trying to install SQL Server 7 Enterprise edition using MS SELECT CDs. The install wizard stops at the beginning telling us that the used CD can only be used to install SQL Server clients with this computer although we used exactly this CD-ROM to install all other SQL Servers, too.
Did anybody have the same problem and knows what the reason for the abortion is?
Thanks
Axel
I have a stored procedure that is calling a cursor to populate some variables it then uses those variable to get more information and then inserts that info into a final table. The estimated number of records that it should insert is around 2 million. The procedure stops after about 6000 records inserted. It still apears to be running but in fact is not. Can anyone help? I have also attached the code.
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE [dbo].[create_table1_tmp] AS
/* table1 variables */
declare@v_ARCKEY int
declare@v_INVKEY int
declare@v_ITEM char (15)
declare@v_BRAND char(15)
declare@v_PRICE decimal(12, 2)
declare@v_QTYORD decimal(12, 3)
declare@v_QTYSHP decimal(12, 3)
declare@v_CTCKEY int
declare@v_SOMKEY int
declare@v_ORDATE datetime
declare@v_FNAME char (15)
declare@v_CONTACT varchar (20)
declare@v_TITLE varchar (25)
declare@v_SALUT char (4)
declare@v_DEGREE char (6)
declare@v_SALESMN2char (3)
declare@v_TTL_CODEchar (4)
declare@v_SPECIALTYvarchar (30)
declare@v_COMPANYvarchar (35)
declare@v_ADDRESS1varchar (50)
declare@v_ADDRESS2varchar (50)
declare@v_CITYvarchar (20)
declare@v_STATEchar (10)
declare@v_KEYCODEchar (10)
declare@v_ZIPchar (10)
declare@v_COUNTRYchar (2)
declare@v_ARCSOURCEchar (5)
declare@v_SLSVOLchar (1)
declare@v_TYPEchar (8)
declare@v_NUMDRSdecimal(9,0)
declare@v_BEDSIZEdecimal(4,0)
declare@v_NUMLIVESdecimal(9,0)
declare@v_CODEchar (3)
declare@v_SUBKEYint
declare@v_SUBTYPEchar (1)
declare@v_STARTDATEdatetime
declare@v_TERMdecimal(2, 0)
declare@v_STATUSchar (1)
declare@v_STATDATEdatetime
declare@v_XRENEWEDdecimal(2, 0)
declare@v_EXPDATEdatetime
declare@v_SHPAMTdecimal(12, 2)
declare@v_SOMSOURCEchar (5)
declare@v_PMETHchar (1)
declare@v_EXTPRICEdecimal(12, 2)
declare@v_CTYPEchar (5)
declare@v_CTCKEY_SBSUBS int
declare@v_ACRONYMchar (8)
declare@v_PONUMvarchar (20)
declare@v_EMAILvarchar (50)
declare@v_ARCPHONEVARCHAR (20)
declare@v_ARCFAXNOVARCHAR (20)
declare@v_CMCPHONEVARCHAR (20)
declare@v_CMCFAXNOVARCHAR (20)
declare@v_DOFAXTINYINT
declare@v_DOPHONETINYINT
declare@v_DORENEWTINYINT
declare@v_DOMAILTINYINT
declare@v_DOUPDATETINYINT
declare@v_UPDONLYTINYINT
declare@v_EMAIL_INFOTINYINT
declare@v_EMAILPROMOTINYINT
declare@v_DISCdecimal(7,3)
declare @v_EUPDATETINYINT
/* processing Variables */
declare @v_tmpint
declare@v_amtdecimal(9,3)
declare@v_loopint
declare@v_arckey_tmp INT
declare@v_ctckey_tmp INT
declare@v_invkey_tmp INT
declare@v_subkey_tmp INT
declare@v_arcDORENEW TINYINT
declare@v_cmcDORENEW TINYINT
declare@v_sbsDORENEW TINYINT
declare@v_arcDOFAX TINYINT
declare@v_cmcDOFAX TINYINT
declare@v_arcDOMAIL TINYINT
declare@v_cmcDOMAIL TINYINT
declare@v_arcDOPHONE TINYINT
declare@v_cmcDOPHONE TINYINT
/* cursors for retrieving online data*/
declarev_mast cursor for
select /*+ INDEX_COMBINE(arc) */
som.arckey arckey,
sot.invkey invkey,
sot.item item,
sot.price price,
sot.qtyord qtyord,
sot.qtyshp qtyshp,
som.ctckey ctckey,
som.somkey somkey,
sot.ordate ordate,
arc.phone arcphone,
arc.faxno arcfaxno,
arc.salesmn2 salesmn2,
arc.country country,
arc.source arcsource,
arc.slsvol slsvol,
arc.type type,
arc.specialty specialty,
arc.numdrs numdrs,
arc.bedsize bedsize,
arc.numlives numlives,
arc.code code,
arc.dorenew arcdorenew,
arc.company company,
arc.address1 address1,
arc.address2 address2,
arc.city city,
arc.state state,
arc.zipzip,
sub.eupdate eupdate,
sub.doupdate doupdate,
sub.subkey subkey,
sub.subtype subtype,
sub.startdate startdate,
sub.term term,
sub.status status,
sub.statdate statdate,
sub.xrenewed xrenewed,
sub.dorenew sbsdorenew,
som.shpamt shpamt,
som.source somsource,
som.pmeth pmeth,
sub.ctckey ctckey_sbsubs,
sub.updonly updonly,
sot.disc disc,
arc.dofax arcdofax,
som.ponum,
arc.domail,
arc.dophone
from sotran sot,somast som, arcust arc,sbsubs sub
where (som.arckey != '121449' and som.arckey != '1364166')
and (som.sotype not in (
select value from table1_param
where name = 'sotype') or
som.sotype is null)
and (som.sostat not in (
select value from table1_param
where name = 'somstat') or
som.sostat is null)
and arc.arckey = som.arckey
and (arc.code not in (
select value from table1_param
where name = 'code') or
arc.code is null)
and (arc.slsvol not in (
select value from table1_param
where name = 'slsvol') or
arc.slsvol is null)
and arc.foreign_ = 0
and sot.somkey = som.somkey
and (sot.sostat not in (
select value from table1_param
where name = 'sotstat') or
sot.sostat is null)
and sot.item > '0100'
and sub.subkey =* sot.subkey
order by arc.arckey,som.ctckey,sot.invkey,sub.subkey,sot.rq date desc
-- BEGIN PROCESSING THE CURSOR DATA HERE
OPEN v_mast
--pull all somast records where custno isn't 121449 or 1364166
--and sotype filtered and sostat isnot filtered
set @v_loop = 0
fetch next from v_mast into @v_arckey, @v_invkey, @v_item, @v_price, @v_qtyord,
@v_qtyshp, @v_ctckey, @v_somkey, @v_ordate, @v_arcphone, @v_arcfaxno, @v_salesmn2, @v_country,
@v_arcsource, @v_slsvol, @v_type, @v_specialty, @v_numdrs, @v_bedsize, @v_numlives,
@v_code, @v_arcdorenew, @v_company, @v_address1, @v_address2, @v_city,
@v_state, @v_zip, @v_eupdate, @v_doupdate, @v_subkey, @v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_sbsdorenew, @v_shpamt, @v_somsource, @v_pmeth, @v_ctckey_sbsubs,
@v_updonly, @v_disc, @v_arcdofax, @v_ponum, @v_arcdomail, @v_arcdophone
while @@fetch_status = 0--for v_rec in v_mast loop
Begin
set @v_loop = @v_loop + 1
set @v_expdate = dateadd(month,@v_term,@v_startdate) - 1
set @v_keycode = ''
--select company, address1, address2, city, state, zip
--from cmcship
--where ctckey = @v_ctckey
--if (@@rowcount = 0)
--set @v_tmp = null
--else
--begin
begin tran t1
set @v_company = (select top 1 company from cmcship where ctckey = @v_ctckey)
set @v_address1 = (select top 1 address1 from cmcship where ctckey = @v_ctckey)
set @v_address2 = (select top 1 address2 from cmcship where ctckey = @v_ctckey)
set @v_city = (select top 1 city from cmcship where ctckey = @v_ctckey)
set @v_state = (select top 1 state from cmcship where ctckey = @v_ctckey)
set @v_zip = (select top 1 zip from cmcship where ctckey = @v_ctckey)
commit tran t1
--end
--select company, address1, address2, city, state, zip
--from cmcadd
--where ctckey = @v_ctckey
--if (@@rowcount = 0)
--set @v_tmp = null
--else
--begin
begin tran t2
set @v_company = (select top 1 company from cmcadd where ctckey = @v_ctckey)
set @v_address1 = (select top 1 address1 from cmcadd where ctckey = @v_ctckey)
set @v_address2 = (select top 1 address2 from cmcadd where ctckey = @v_ctckey)
set @v_city = (select top 1 city from cmcadd where ctckey = @v_ctckey)
set @v_state = (select top 1 state from cmcadd where ctckey = @v_ctckey)
set @v_zip = (select top 1 zip from cmcadd where ctckey = @v_ctckey)
commit tran t2
--end
--select fname, contact, title, salut, degree, phone, faxno, ttl_code, dorenew,
--ctype, email, dofax, domail, email_info, emailpromo, docall
--from cmctac
--where ctckey = @v_ctckey
--if (@@rowcount = 0)
--RAISERROR ('cmc not found', 16, 1)
--else
--Begin
begin tran t3
set @v_fname = (select top 1 fname from cmctac where ctckey = @v_ctckey)
set @v_contact = (select top 1 contact from cmctac where ctckey = @v_ctckey)
set @v_title = (select top 1 title from cmctac where ctckey = @v_ctckey)
set @v_salut = (select top 1 salut from cmctac where ctckey = @v_ctckey)
set @v_degree = (select top 1 degree from cmctac where ctckey = @v_ctckey)
set @v_cmcphone = (select top 1 phone from cmctac where ctckey = @v_ctckey)
set @v_cmcfaxno = (select top 1 faxno from cmctac where ctckey = @v_ctckey)
set @v_ttl_code = (select top 1 ttl_code from cmctac where ctckey = @v_ctckey)
set @v_cmcdorenew = (select top 1 dorenew from cmctac where ctckey = @v_ctckey)
set @v_ctype = (select top 1 ctype from cmctac where ctckey = @v_ctckey)
set @v_email = (select top 1 email from cmctac where ctckey = @v_ctckey)
set @v_cmcdofax = (select top 1 dofax from cmctac where ctckey = @v_ctckey)
set @v_cmcdomail = (select top 1 domail from cmctac where ctckey = @v_ctckey)
set @v_email_info = (select top 1 email_info from cmctac where ctckey = @v_ctckey)
set @v_emailpromo = (select top 1 emailpromo from cmctac where ctckey = @v_ctckey)
set @v_cmcdophone = (select top 1 docall from cmctac where ctckey = @v_ctckey)
commit tran t3
--end
--select acronym, brand from invhead
--where invkey = @v_invkey
--if (@@cursor_rows = 0)
--set @v_tmp = null
--else
--begin
begin tran t4
set @v_acronym = (select top 1 acronym from invhead where invkey = @v_invkey)
set @v_brand = (select top 1 brand from invhead where invkey = @v_invkey)
commit tran t4
--end
set @v_amt = ((@v_qtyshp+@v_qtyord)*@v_price *(100-@v_disc))*.01
if @v_amt < 0
set @v_extprice = round(@v_amt,2)
else
set @v_extprice = round(@v_amt,2)
if (@v_arcdomail = 0 or @v_cmcdomail = 0)
set @v_domail = 0
else
set @v_domail = 1
if (@v_arcdofax = 0 or @v_cmcdofax = 0)
set @v_dofax = 0
else
set @v_dofax = 1
if (@v_arcdophone = 0 or @v_cmcdophone = 0)
set @v_dophone = 0
else
set @v_dophone = 1
if ((@v_arcdorenew = 0 or @v_cmcdorenew = 0) or @v_sbsdorenew = 0)
set @v_dorenew = 0
else
set @v_dorenew = 1
-- remove all but the newest orders
if @v_arckey = @v_arckey_tmp and @v_ctckey = @v_ctckey_tmp and
@v_invkey = @v_invkey_tmp and ((@v_subkey is null and
@v_subkey_tmp is null) or (@v_subkey = @v_subkey_tmp))
set @v_tmp = null
else
Begin
begin tran t5
set @v_arckey_tmp = @v_arckey
set @v_ctckey_tmp = @v_ctckey
set @v_invkey_tmp = @v_invkey
set @v_subkey_tmp = @v_subkey
commit tran t5
begin tran t6
insert into table1_tmp
(
arckey, invkey, item, brand, price, qtyord, qtyshp, ctckey, somkey,
ordate, fname, contact, title, salut, degree, salesmn2, ttl_code,
specialty, company, address1, address2, city, state, keycode,
zip, country, arcsource, slsvol, type, numdrs, bedsize, numlives,
code, subkey, subtype, startdate, term, status,
statdate, xrenewed,expdate, shpamt, somsource,
pmeth, extprice, ctype,ctckey_sbsubs, acronym,
ponum, email, arcphone, arcfaxno, cmcphone, cmcfaxno,
dofax, dophone, dorenew, domail, doupdate, updonly,
email_info, emailpromo, disc)
values (
@v_arckey, @v_invkey, @v_item, @v_brand, @v_price, @v_qtyord, @v_qtyshp,
@v_ctckey, @v_somkey, @v_ordate, @v_fname, @v_contact, @v_title,
@v_salut, @v_degree, @v_salesmn2, @v_ttl_code, @v_specialty, @v_company,
@v_address1, @v_address2, @v_city, @v_state, @v_keycode,
@v_zip, @v_country, @v_arcsource, @v_slsvol,
@v_type, @v_numdrs, @v_bedsize, @v_numlives, @v_code,
@v_subkey,@v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_expdate, @v_shpamt, @v_somsource,
@v_pmeth, @v_extprice, @v_ctype, @v_ctckey_sbsubs, @v_acronym,
@v_ponum, @v_email, @v_arcphone, @v_arcfaxno, @v_cmcphone,
@v_cmcfaxno, @v_dofax, @v_dophone, @v_dorenew, @v_domail,
@v_doupdate, @v_updonly, @v_email_info, @v_emailpromo, @v_disc
)
commit tran t6
End
fetch next from v_mast into @v_arckey, @v_invkey, @v_item, @v_price, @v_qtyord,
@v_qtyshp, @v_ctckey, @v_somkey, @v_ordate, @v_arcphone, @v_arcfaxno, @v_salesmn2, @v_country,
@v_arcsource, @v_slsvol, @v_type, @v_specialty, @v_numdrs, @v_bedsize, @v_numlives,
@v_code, @v_arcdorenew, @v_company, @v_address1, @v_address2, @v_city,
@v_state, @v_zip, @v_eupdate, @v_doupdate, @v_subkey, @v_subtype, @v_startdate, @v_term, @v_status,
@v_statdate, @v_xrenewed, @v_sbsdorenew, @v_shpamt, @v_somsource, @v_pmeth, @v_ctckey_sbsubs,
@v_updonly, @v_disc, @v_arcdofax, @v_ponum, @v_arcdomail, @v_arcdophone
end --while loop
close v_mast
deallocate v_mast
Hello,
I have 2 servers with the same software (win2k, sql 2000 sp3) and the same data on them. The older server is working fine, but the new one that I am trying to use will run fine for a various amount of time and then stop responding. I am confused because when it does this I can pull up the task manager and see that there is plenty of memory and cpu left, and there are no error messages up on the screen. The only way that I can get the server to respond again is to restart the system. The problem never happens at the same time of day and it doesn't matter how long it has been since the last reboot, could be 5 minutes, could be 5 hours. If anyone has any ideas on what could be causing this, any replies are appreciated. The new system is a dell 2600, 2G Ram, Xeon processor, win2k.
Thanks for any ideas,
Brian
I have a stored procedure which does a simple select joining 3 tables.
This had been working fine for weeks and then just stopped returning any rows even though data existed for it to return.
After re-compiling the procedure, it worked fine as before.
Does anyone know of any reason why a procedure would need recompiling like this?
We have been doing data restores and also dropping/recreating tables during this development. Would any of this affect a procedure?
I am trying to move SQLMail from a standard SQL 6.5 server to a virtual clustered SQL Server. The exchange profile has been set up and the services are running on a domain login. Exchange is also running as a clustered service which is being used successfully from the standalone SQL Server with SQL Mail.
However, when the SQL Mail service is started on the Clustered SQL Server, the service starts for about 5 seconds and then stops. The logs report that SQL Mail started but no messages are recorded to state that why the service has stopped again.
Any clues anyone please?
Hello,
I've got a couple of jobs who have a odbc connection to a AS400 machine. But when these jobs run they won't stop anymore. I've got to stop these jobs manually so that the next day the jobs can start again as scheduled. The jobs did run all the packages succesfully. Does somebody know how this is possible? It did work fine but since a couple of weeks they just won't stop anymore. I hope you can help me! :S
Hi,
I have Oracle 9i. Oracle listener is aborting by itself after every few minutes giving TNSLSNR.exe failed. While listener is running I can do all the activity on the database.
Please let me know if there is any solution.
Thanks in Advance.
Pushpam
Hi,
I have transactional replication set up between two dedicated servers. Server A is the PDC and Server B is a BDC (they are both Win2000 boxes). Both the servers are brand new, and replaced the two that were running like clock work (replication wise) for the last 12 months. I never had this problem with the old servers....
When the servers are shut down (as the case was a couple of weeks back with a power failure) or just recently when they were move to another room. Both servers boot up at the same time. Server B (which is the server holding the db being replicated) boots quicker and as a result replication fails and is then 'sucessfully stopped'. Unless I am aware of the server being rebooted and can monitor this potential problem, within 2 days the logfile grows to large and everything comes to a crashing halt.
I just remove replication, truncate and shrink the log, reset replication and we're away.... BUT I really need to know why it is happening in the first place. I figure there must be a setting that I have forgotten about or something.
Both servers are Win2000 (SP4) and SQL2000(SP3a).
Any help would be appreciated.
Thanks
Casper
I have a few reports that suddenly stopped printing.
Nothing changed on the report server, and one minute they could print, the other they couldn't
I have one report that is 4 pages in preview and six when it prints. The users press the print control on the web page and the print dialog comes up, they select a printer and the printing messagebox comes up. Then it will print a couple of pages VERY SLOWLY... and eventually will hang at the 3rd page.
IT appears it is just spooling in the printer control viewer...
Other similiar reports that use the same query but a different parameter, work just fine.
I have rebuilt and redployed the report. The problem is happening on both my live and test environments.
I have looked through this forum but not found an answer. I did delete and re-isntall the client print control.
I have turned the client logging on. But no errrors show up anywhere.
I am running ssrs 2005 on both machines. test machine has win2003 sp1 , sql 2005 sp2 AND hotfix's
live machine has win2003 and sql 2000 latest sp.
I need help!
TIA
Daryl
Below is my batch file that calls one SSIS package. When I run it from the command prompt, it runs and runs and never stops. What am I missing?
dtexec "/FILE "\sqldevssispackagesDW_DatamartETLDEVExtract_DimItem.dtsx" /DECRYPT /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING EW "