Monday, March 26, 2012
Duplicate inserts creating an error
Everything appears correct with synchronization and data flows in both
directions. However, I have noticed that on the SQLCE tables where I do
multiple Inserts, I'm now getting an error after the second synchronization.
For example, I do a download of the data, do Inserts on some of the tables,
then merge upload the data to the server. Now I go back for a second
download, and when I try to do an Insert, I get the error, "A duplicate
value cannot be inserted into a unique index".
I'm not trying to insert a primary key or into a primary key field, and I've
deleted all the indexes on any other fields -- except the uniqueID fields
created by the publication creation.
Not sure where I'm going astray here, so any advice would be appreciated.
Which table are you getting this error on? Is it a system table or a user
table?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
|||User tables on the PocketPC.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:opsoxx6ntqrj9kur@.hcottter-lap.ap.org...
> Which table are you getting this error on? Is it a system table or a user
> table?
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
Thursday, March 22, 2012
dup check while import from excel
a
.NET programmer by trade, so T-SQL is a bit different for me.
I have an outside list of items with ID codes that I need to import to an
existing table, but I need to have the procedure check for dups in the excel
list BEFORE inserting them, if the ID already exists in the database, then
skip and move onto the next one.
(See the OOP concept here?)
I was thinking about something along the line of an If Exist followed by a
skip record or insert, but I think I am making this too complicated.
can someone give me a down-to-earth example of how to accomplish this?
Environment:
From: Excel File named InItem
ID field: ItemID
to:
SQL Database named dbo.tblInItem field ItemID
Thank you for any assistance...
-Tobias Mazzei
Wraith SystemsI prefer adding a linked server to the excel file (look up sp_addlinkedserve
r
in Books Online) to bring the two data sources to common ground.
Building an INSERT query is pretty simple from there:
insert <SQL_table>
(
<destination_column_list>
)
select <source_column_list>
from <Excel_table>
where (not exists (
..the excluding query is
dependent on the actual data
..basically you need to list
items that already exist in the destination table
))
For better help, please post DDL and sample data.
ML|||This is ussually how I check if a record already exsists, you can change the
creteria
IF EXISTS (SELECT 'True' FROM tb_Alerts WHERE ID = @.AlertID)
BEGIN
UPDATE tb_Alerts
SET AlertName = @.AlertName
WHERE ID = @.AlertID;
SELECT @.AlertID;
END
ELSE
BEGIN
INSERT INTO tb_Alerts (AlertName)
VALUES (@.AlertName)
SELECT @.@.IDENTITY;
END
-Mark
"Wraith Systems" <WraithSystems@.discussions.microsoft.com> wrote in message
news:AD088624-7F0F-4F88-BD8C-5C0AE09F754D@.microsoft.com...
> There have been some pretty advanced stuff here I've gone through, but I
> am a
> .NET programmer by trade, so T-SQL is a bit different for me.
> I have an outside list of items with ID codes that I need to import to an
> existing table, but I need to have the procedure check for dups in the
> excel
> list BEFORE inserting them, if the ID already exists in the database, then
> skip and move onto the next one.
> (See the OOP concept here?)
> I was thinking about something along the line of an If Exist followed by a
> skip record or insert, but I think I am making this too complicated.
> can someone give me a down-to-earth example of how to accomplish this?
> Environment:
> From: Excel File named InItem
> ID field: ItemID
> to:
> SQL Database named dbo.tblInItem field ItemID
> Thank you for any assistance...
> --
> -Tobias Mazzei
> Wraith Systems
>|||I am getting closer. Thanks for the response, I tried looking up "not exists
"
in the help files, and with the lack of definition, it brought me here.
where (not exist (exceltableID=SQLtableID))
This was my initial concept, but it didn't look like SQL understood what I
was trying to tell it, the words not and exists were greyed.
So the concept was to select the items where the SQLtable did not have (or
equal to) what was the current record the datareader was at, right?
is this accurate useage? and can this be done in a stored procedure?
-Tobias Mazzei
Wraith Systems
"ML" wrote:
> I prefer adding a linked server to the excel file (look up sp_addlinkedser
ver
> in Books Online) to bring the two data sources to common ground.
> Building an INSERT query is pretty simple from there:
> insert <SQL_table>
> (
> <destination_column_list>
> )
> select <source_column_list>
> from <Excel_table>
> where (not exists (
> ...the excluding query is
> dependent on the actual data
> ...basically you need to list
> items that already exist in the destination table
> ))
>
> For better help, please post DDL and sample data.
>
> ML|||On Mon, 7 Nov 2005 15:09:02 -0800, Wraith Systems wrote:
>I am getting closer. Thanks for the response, I tried looking up "not exist
s"
>in the help files, and with the lack of definition, it brought me here.
>where (not exist (exceltableID=SQLtableID))
>This was my initial concept, but it didn't look like SQL understood what I
>was trying to tell it, the words not and exists were greyed.
>So the concept was to select the items where the SQLtable did not have (or
>equal to) what was the current record the datareader was at, right?
>is this accurate useage? and can this be done in a stored procedure?
Hi Tobias,
No, this is not a correct query. The argument to an EXISTS clause should
be a subquery. And in this case, the subquery should be correlated to
the main query.
Try adapting the code below to your tables:
INSERT INTO DestTable (KeyColumn, DataColumn1, DataColumn2)
SELECT s.KeyColumn, s.DataColumn1, s.DataColumn2
FROM SourceTable AS s
WHERE NOT EXISTS (SELECT *
FROM DestTable AS d
WHERE d.KeyColumn = s.KeyColumn)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Thank you Hugo. I think Microsoft finally answered the question as well, but
using a <not in> statement. This seems to work as well. If there are
performance factors, I have not found them yet, but I am only inserting
around 1,000 items at a time per batch.
-Tobias Mazzei
Wraith Systems
"Hugo Kornelis" wrote:
> On Mon, 7 Nov 2005 15:09:02 -0800, Wraith Systems wrote:
>
> Hi Tobias,
> No, this is not a correct query. The argument to an EXISTS clause should
> be a subquery. And in this case, the subquery should be correlated to
> the main query.
> Try adapting the code below to your tables:
> INSERT INTO DestTable (KeyColumn, DataColumn1, DataColumn2)
> SELECT s.KeyColumn, s.DataColumn1, s.DataColumn2
> FROM SourceTable AS s
> WHERE NOT EXISTS (SELECT *
> FROM DestTable AS d
> WHERE d.KeyColumn = s.KeyColumn)
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 9 Nov 2005 08:46:33 -0800, Wraith Systems wrote:
>Thank you Hugo. I think Microsoft finally answered the question as well, bu
t
>using a <not in> statement. This seems to work as well. If there are
>performance factors, I have not found them yet, but I am only inserting
>around 1,000 items at a time per batch.
Hi Tobias,
There are two things to consider when you use NOT IN with a subquery:
1. If one of the rows in the subquery returns a NULL, the NOT IN will
never evaluate to True. You have to make sure that the subquery can't
return NULLs, else your query will fail.
If you use NOT EXISTS, then you don't have this problem.
2. The NOT IN can only be used if you have a sigle-key column. For a
multi-key column, NOT IN can't be used, since SQL Server doesn't
implement the ANSI standard row constructor.
With NOT EXISTS, the number of columns in the key doesn't matter.
For these reasons, I recommend to always rewrite NOT IN with a subquery
to NOT EXISTS with a subquery. Using NOT IN with a list of constants (as
in "WHERE Status NOT IN ('Sold out', 'Discontinued')") is okay, though.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is what I am using currently, but based on your responses, I anticipate
issues.
Insert Into <server>.tblInItemAddDescr
select ItemId,AddlDescr,ts
from xclItemAddDescr...[tblInItemAddDescr$]
where itemid not in(select itemid from <server>.tblInItemAddDescr)
go
I am not sure what multi-key vs. single-key would be considered, would you
be able to give a bit more detail on that?
-Tobias Mazzei
Wraith Systems
"Hugo Kornelis" wrote:
> On Wed, 9 Nov 2005 08:46:33 -0800, Wraith Systems wrote:
>
> Hi Tobias,
> There are two things to consider when you use NOT IN with a subquery:
> 1. If one of the rows in the subquery returns a NULL, the NOT IN will
> never evaluate to True. You have to make sure that the subquery can't
> return NULLs, else your query will fail.
> If you use NOT EXISTS, then you don't have this problem.
> 2. The NOT IN can only be used if you have a sigle-key column. For a
> multi-key column, NOT IN can't be used, since SQL Server doesn't
> implement the ANSI standard row constructor.
> With NOT EXISTS, the number of columns in the key doesn't matter.
> For these reasons, I recommend to always rewrite NOT IN with a subquery
> to NOT EXISTS with a subquery. Using NOT IN with a list of constants (as
> in "WHERE Status NOT IN ('Sold out', 'Discontinued')") is okay, though.
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Wed, 9 Nov 2005 16:27:57 -0800, Wraith Systems wrote:
>Here is what I am using currently, but based on your responses, I anticipat
e
>issues.
>Insert Into <server>.tblInItemAddDescr
>select ItemId,AddlDescr,ts
>from xclItemAddDescr...[tblInItemAddDescr$]
>where itemid not in(select itemid from <server>.tblInItemAddDescr)
>go
Hi Tobias,
If the column itemid in <server>.tblInItemAddDescr can't be NULL, then
you won't have any issued with this. You couls still compare it to the
corresponding NOT EXISTS version to test for performance differences,
though.
OTOH, if the itemid column can hold NULLs, then you *will* have issues.
>I am not sure what multi-key vs. single-key would be considered, would you
>be able to give a bit more detail on that?
My bad. I messed up when typping. I meant multi-column vs single-column
keys.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
Friday, February 24, 2012
DTS: EXCEL TO SQL: Returns an empty recodset
I use Asp.Net Application to upload a Excel file and then a DTS to import data from the file to the SQL2000 and finally to display the read data on the screen.
The DTS starts with setting some variables with the help of Dynamic properties.
On Succes.
DTS rum 2 simultaneous Transform Data task importing data from excel to SQL2000.
This works fine for "most" of the time, but then there are the other times.
One of the TDT(Transform Data task) reads nothing from the excel file. but it can read the data if i upload the same file again right after.
Any kind of input is welcome
Thanx
/bs26Originally posted by bs26
Hi there,
I use Asp.Net Application to upload a Excel file and then a DTS to import data from the file to the SQL2000 and finally to display the read data on the screen.
The DTS starts with setting some variables with the help of Dynamic properties.
On Succes.
DTS rum 2 simultaneous Transform Data task importing data from excel to SQL2000.
This works fine for "most" of the time, but then there are the other times.
One of the TDT(Transform Data task) reads nothing from the excel file. but it can read the data if i upload the same file again right after.
Any kind of input is welcome
Thanx
/bs26
It sounds as if you are experiencing a locking problem with the two Transform Data tasks. I have had similar problems with importing and exporting to Access databases.
Try it with only one TDT and see if it fixes it.
If you need the additional speed of multiple TDT's, you might try splitting up or duplicating the data over several excel workbooks.
Sunday, February 19, 2012
Dts works from SqlServer but not from ASP.net
I have an asp.net page that executes a DTS. When I execute that DTS from enterprise manager it takes about 5000 rows from the as400 and insert into sql server
It works right. but when I execute it from my asp.net page I have this error.
Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1System.Exception: Error al procesar DTS TransferirDatos(ExistMP) en el paso DTSStep_DTSActiveScriptTask_1 at LibreraLentos.exec.ejecuta_SP_EXISTENCIASMP() in C:\Documents and Settings\luisvalen\Mis documentos\Visual Studio Projects\InventariosLentos\LibreraLentos\exec.vb:line 43 at InventariosLentos.generacionprocesomateriaprima.btnenviar_Click(Object sender, EventArgs e) in C:\AplicacionesWeb\InventariosLentos\generacionprocesomateriaprima.aspx.vb:line 60LibreraLentos
I have this on my ASP page
Private Sub btnenviar_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnenviar.Click
Try
objexecsp.ejecuta_SP_EXISTENCIASMP()
lblmensajes.Text = "Proceso generado satisfactoriamente"
Catch ex As Exception
lblmensajes.Text = ex.Message + ex.GetBaseException.ToString + ex.Source.ToString
End Try
End Sub
this on my Data Classs
Public Function ejecuta_SP_EXISTENCIASMP()
' call UpdatePrice using a parameter array of SqlParameter objects
Try
Dim ejecutardts As New cDTS
ejecutardts.EjecutarDTS("TransferirDatos(ExistMP)")
Catch ex As Exception
Throw ex
End Try
End Function
This is what executes the DTS
Imports System.Runtime.InteropServices
Imports System.Configuration.ConfigurationSettings
Imports DTS
Public Class cDTS
Public Sub EjecutarDTS(ByVal NombreDTS As String)
Dim pkg As New DTS.Package
Dim oStep As DTS.Step
Try
pkg = New DTS.Package
'pkg.LoadFromSQLServer(AppSettings("MED20NT"), AppSettings("user"), AppSettings("pwd"), DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", "pruebaCdr1")
pkg.LoadFromSQLServer("MED20NT", "sa", "prueva", DTSSQLServerStorageFlags.DTSSQLStgFlag_Default, "", "", "", NombreDTS, "")
pkg.AutoCommitTransaction = True
pkg.Execute()
For Each oStep In pkg.Steps
If oStep.ExecutionResult = DTSStepExecResult.DTSStepExecResult_Failure Then
Throw New Exception("Error al procesar DTS " & pkg.Name & " en el paso " & oStep.Name)
End If
Next
Catch ex As System.Runtime.InteropServices.COMException
Throw ex
Catch ex As Exception
Throw ex
Finally
pkg.UnInitialize()
pkg = Nothing
End Try
End Sub
End Class
This is the CODE of my dts
but as I told before it Works when I right Click on it in enterprise manager
Dim ConnSql
Dim ConnDb2
'* Función para Conexion a Base de Datos ASW en Med13nt
Function ConexionSql()
'On Error Resume Next
Dim strConexion
strConexion = "Provider=SQLOLEDB.1;" & _
"Persist Security Info=True;" & _
"User ID=sa;Password=xx;" & _
"Initial Catalog=asw;" & _
"Data Source=Med20nt"
Set ConnSql = CreateObject("ADODB.Connection")
ConnSql.Open strConexion
If Err.Number <> 0 then ConexionSql = False
End Function
'* Función de Conexion a ASW as400
Function ConexionDb2()
'On Error Resume Next
Dim strConexion
strConexion = "DSN=asw1;User ID=CLAUDIMON;Password=xx"
Set ConnDb2 = CreateObject("ADODB.Connection")
ConnDb2.Open strConexion
If Err.Number <> 0 Then ConexionDb2 = False
' conexion por OLEDB
' Dim strconexion
' strconexion = "Provider=IBMDA400.DataSource.1; Data source=10.2.0.10;User Id=FVENTAS;Password=xx"
' Set ConnDb2 = CreateObject("ADODB.Connection")
' ConnDb2.Open strConexion
' If Err.Number <> 0 Then ConexionDb2 = False
End Function
'* Trasfiere los datos de ASW a Med20nt
Private Function Transferir()
'On Error Resume Next
Dim strSql
Dim producto
Dim strSql2
Dim strSql3
Dim strCero
Dim strcomilla
Dim RS
Dim RS2
Dim RS3
Dim Contador
Dim StrBodega
strBodega=" "
contador=1
strcomilla="'"
strCero="0"
Set RS = CreateObject("ADODB.Recordset")
Set RS2 = CreateObject("ADODB.Recordset")
Set RS3 = CreateObject("ADODB.Recordset")
strSql3 = "SELECT distinct producto from sumvenmpf"
ConnSql.Execute (strSql3)
RS.Open strSql3, ConnSql
' ConnSql.Execute (strSql3)
Do Until RS.EOF
Set RS2 = CreateObject("ADODB.Recordset")
strSql = "SELECT srprdc, srsrom, srplan,ctname,sum(srsthq), sum(srpurq), sum(srcusq), sum(srpicq) FROM HCB453AFIH.srbsro,HCB453AFIH.srbctlsd where ctsign=srplan and srprdc="&strcomilla&RS.Fields("producto").value&strcomilla &" and (srsthq>0 or srpurq>0 or srcusq>0 or srpicq>0) group by srprdc,srsrom,srplan,ctname"
contador=0
RS2.Open strSql, ConnDb2
contador=contador+1
Do until RS2.EOF
strSql2 = "INSERT INTO ASW.dbo.Existenciasmp (srprdc,srsrom,srplan,ctname,srsthq, srpurq, srcusq, srpicq ) values ('" & RS2.Fields("srprdc").value & "','" & RS2.Fields("srsrom").value & "','" & RS2.Fields("srplan").value & "','" & RS2.Fields("ctname").value & "','" & RS2.Fields(4).value & "','" & RS2.Fields(5).value & "','" & RS2.Fields(6).value & "','" & RS2.Fields(7).value & "')"
ConnSql.Execute (strSql2)
RS2.MoveNext
Loop
RS2.Close
Set RS2 = Nothing
RS.MoveNext
Loop
RS.Close
Set RS = Nothing
End Function
'* Inicio de la Interfaz
Function Principal()
'On Error Resume Next
Call ConexionSql
Call ConexionDb2
Call Transferir()
Principal = DTSTaskExecResult_Success
End Function
The strange thing here is that it always returns 864 rows.
when executing from asp.net and 5000 from enterprise manager
I have the same issue, which I set up a DTS package in the SQL Server. When I execute package from the Enterpise manager, it ran with no error.
However, when I run it from an asp.net page, it fails. I use the below code to validate the error of the steps. What could it be wrong?
Thanks!
Terrence
DimWithEvents oPkgAs DTS.Package
oPkg =New DTS.Package()
Dim vStepAs DTS.Step
ForEach vStepIn oPkg.Steps
'test if the activescripttask type of task is in this step, and dont have to the transaction if so
'use instr (return value of the position of string location)
IfNot InStr(1,CType(vStep.Name,String), "DTSActiveScriptTask", CompareMethod.Text) > 0Then
IfNot InStr(1,CType(vStep.Description,String), "Update HTMLText Table", CompareMethod.Text) > 0Then
vStep.JoinTransactionIfPresent =True
Else
vStep.JoinTransactionIfPresent =False'if the step is the 'Update HTMLText Table' step then dont join to transaction
EndIf
Else
vStep.JoinTransactionIfPresent =False'if the step is executing a script task, then dont join the transaction
EndIf
vStep.RollbackFailure =True
Next
'modify transaction properties of the package
oPkg.UseTransaction =True
oPkg.AutoCommitTransaction =True
'set the current time to later evaluate the execution time
sTimestart = Now()
'execute the package
OnErrorResumeNext
oPkg.Execute()
OnErrorGoTo 0
'evaluate total execution time of the package
Dim sTime
sTime = DateDiff("s", sTimestart, Now())
'error logging
'default the flag to true (successful execution) and set to false if error occurs
Dim flagAsString =True
Dim sErrAsString
Dim strLogAsString
Dim oDTSStepAs DTS.Step
ForEach oDTSStepIn oPkg.Steps
If oDTSStep.ExecutionResult = oDTSStep.ExecutionResult.DTSStepExecResult_FailureThen
.....
|||
DTS is SQL Server Agent dependent, your SQL Server Agent needs a service account. Try the links below. Hope this helps.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_4jxo.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_xp_aa-sz_8sdm.asp
Wednesday, February 15, 2012
DTS to .net
sort of upgrade that will generate .net code (heaven forbid, c#)?
Second question. Does one generated this code and, assumedly, compiled
it in order to batch file or otherwise script it and is there a
performance degradation by going outside the SQL Server engine?
thx
md
*** Sent via Developersdex http://www.codecomments.com ***
> You know how the DTS wizard will allow one to save as VB...is there some
> sort of upgrade that will generate .net code (heaven forbid, c#)?
AFAIK not. VS.NET uprades VB6 code to VB.NET, but you won't like the code.
You can check this guide how to use DTS in .NET:
http://sqldev.net/dts/DotNETCookBook.htm.
> Second question. Does one generated this code and, assumedly, compiled
> it in order to batch file or otherwise script it and is there a
> performance degradation by going outside the SQL Server engine?
>
DTS uses own objects, the execution is independent of SQL engine, so it
really does not matter where you execute it.
Dejan Sarka, SQL Server MVP
Associate Mentor
www.SolidQualityLearning.com