|
Hi All,
I have a File System Watcher, which is monitoring file drops and renaming etc, which keeps the file information into a table, I have another application which executes the file related SSIS packages, and these two applications are Asynchronous. The problem is the Excel Connection Manager in the SSIS Package, when its reading the files, its creating another temporary file which is unnecessarily creating a record in the table. Can anybody please help me in how to overcome this scenario, I did a little bit by keeping a file exists check in the SSIS package that if the file exists or file already ran I am not going to run the package again on the File but still a record entry would be there on the monitors table which comes because of the Excel connection manager opening and reading the File.
Any help would be very helpful, a code snippet, a link or suggestion anything helps, thanks in advance 
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
|
I did but the problem is the temp files are going to be created with $ prefix but the extension is same and another thing is that the temp files is going to be renamed back to the original file I think that's the reason why, it was hard for me to catch it and fix it. I tried it in hard way but only got 90% success, the way is to not insert any record into the monitoring table if same file name and location file comes within 15 minutes of previous drop then don't insert it into the monitoring table, but still for some SSIS package may take more that 15 minutes to read, then still those files are showing up in the monitoring table.
If there is any easy and proven approach that would be very helpful, thanks for the help buddy
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
hi,
I am having doubt in logical schema of database.
A logical schema
a)is the entire database
b)is a standard way of organizing information into a accessible part
c)describe how data is actually stored on disk
d)is design-centric database structure built to meet business requirements
My answer is "a" that is the entire database.Is it correct?
Please help............
|
|
|
|
|
|
Answer: none of the above, although d is maybe closest.
|
|
|
|
|
Thanks for confirming that 
Bastard Programmer from Hell 
If you can't read my code, try converting it here[^]
|
|
|
|
|
Actually looks more like the output of one of the online buzzword generators. 
|
|
|
|
|
I have exhausted my resources. My application works fine with Access 2007. I have a windows form built with VS 2010 using a MySql database. Following is what I thought was the proper coding for MySql. Even if I run a simple query with a WHERE colmx = "string" no rows are returned. Only the SELECT clause is working. Can someone point me to a resource for a solution?
MySql 5.7.17 MySql database on remote server. (and local)
Windows C# Form VS 2010 .NET 3.5
private void txtSearchName_TextChanged(object sender, EventArgs e)
{
string srch = "Strain"; //txtSearchName.Text + "%";
this.contactsTableAdapter.FillByName(this.strain_family_databaseDataSet.contacts, srch);
}
VS 2010 DataSet Query
SELECT ID, Company, Last Name, First Name, 'etc' FROM contacts
WHERE 'Last Name' LIKE @Param1 OR 'First Name' LIKE @Param1
ORDER BY 'Last Name'
|
|
|
|
|
Errors = field names with spaces - this will drive you nuts as you now need to bracket the field names with []
try
WHERE [Last Name] like @param1 etc..
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Brackets is only on SQL Server, on MySQL you need to use the backtick `
Both supports the use of double quote " as that is the ANSI standard. You may need to SET sql_mode = 'ANSI_QUOTES' though.
|
|
|
|
|
Ah thanks, have not got used to the variations in MySQL yet and I would never find that wrinkle - spaces in field names are NEVER acceptable.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The differences in the SQL dialects of MS Access and MySQL can be terrible. It is best to avoid field / table names with blanks. If you insist on them, you must escape the name correctly, Access uses [name with blank] while MySQL `name with blank`.
Next, a comparison with LIKE expects a wild card. Strain does not contain a wild card, use Strain% instead - and make sure that you have data starting with that in one of the columns...
|
|
|
|
|
Bernhard,
Thank you. I don't know how I missed the backtick. I kept looking for something serious. I discovered it about an hour after posting. It became obvious when I executed queries with Workbench. That is a great tool! Well, I corrected that. As to the wildcard, I had that done properly in the query I was interested in, appending the % to the string before passing to the query. The web is filled with advice on wildcards, most is incorrect. I always get answers quickly here.
|
|
|
|
|
The same project again. Yes I know it's old, and I'm not the author of it, but it's quick cash.
I have this dbf file that seems to be locked or something. In some other dbf files, I can delete a record. But in this one, even using another program called DBF Manager, I can't delete rows.
So I tried 2 methods, using 2 different drivers.
The dbf file has about say 20 rows for invoice number 28794, and in the 2nd example returns 1 in ExecuteNonQuery
The first method I tried was using Jet, which is what I used through out the program.
But I get a 0 back from ExecuteNonQuery
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path
Const queryString1 As String = _
" DELETE FROM ARTRS01H.dbf h " & _
" WHERE h.FINVNO = @FINVNO "
Const queryString2 As String = _
" DELETE FROM ARTRS01.dbf v " & _
" WHERE v.FINVNO = @FINVNO "
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Using command As New System.Data.OleDb.OleDbCommand(queryString1, connection)
Dim paramFINVNO As System.Data.OleDb.OleDbParameter
paramFINVNO = New System.Data.OleDb.OleDbParameter("@FINVNO", OleDbType.VarChar)
paramFINVNO.Value = p.Trim
command.Parameters.Add(paramFINVNO)
Try
connection.Open()
Dim x As Integer = command.ExecuteNonQuery()
command.CommandText = queryString2
x += command.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
The 2nd method I tried was using the Visual Fox Pro, in which I get a 1 back from ExecuteNonQuery
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & ";"
Dim queryString1 As String = _
" SET EXCLUSIVE ON" & _
" DELETE FROM ARTRS01H.dbf " & _
" WHERE FINVNO = '" & p & "' " & _
" PACK "
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Using command As New System.Data.OleDb.OleDbCommand(queryString1, connection)
Try
connection.Open()
command.CommandType = CommandType.StoredProcedure
command.CommandText = "ExecScript"
command.Parameters.Add("script1", OleDbType.Char).Value = queryString1
Dim x As Integer = command.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
I'd thought and see if any folks out there may flash back 20 years on this.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
This really is digging out at the very back of my memory, but I vaguely remember a similar problem. I think there was another file created - either same name as database with a different suffix, or logon id, or random numbers+letters - I don't recall which and I've seen all three of these over the years.
Any how - this file contained details of locks on the dbf and didn't always get cleared down properly - leaving things locked.
If you're using WIN7 upwards then you might be hitting issues with virtual storage depending on where the DBF file is stored - came across something similar a couple of years ago.
Good luck!
|
|
|
|
|
Thats a start for me at least, Thanks!
Strange, I deleted some funky looking row data at the bottom of the file and now I can delete records using DBF Manager.
Guess I'll give my vb code another try again now.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
When I use DBF Manager and do a search by invoice number, it comes back search string not found.
Think it's because it a partial string or something?
This technology is before I got into programming, and I never really used back in 1997.
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & "; DELETED = FALSE;"
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Dim c1 As New System.Data.OleDb.OleDbCommand("SET EXCLUSIVE ON", connection)
Dim c2 As New System.Data.OleDb.OleDbCommand("SET DELETED ON", connection)
Dim c3 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01H WHERE FCUSTNO = ? AND FINVNO = ?", connection)
Dim c4 As New System.Data.OleDb.OleDbCommand("PACK", connection)
Dim paramFCUSTNO As System.Data.OleDb.OleDbParameter
paramFCUSTNO = New System.Data.OleDb.OleDbParameter("@FCUSTNO", OleDbType.VarChar)
paramFCUSTNO.Value = pCustNo
c3.Parameters.Add(paramFCUSTNO)
Dim paramFINVNO As System.Data.OleDb.OleDbParameter
paramFINVNO = New System.Data.OleDb.OleDbParameter("@FINVNO", OleDbType.VarChar)
paramFINVNO.Value = pInvNo
c3.Parameters.Add(paramFINVNO)
Try
connection.Open()
c1.ExecuteNonQuery()
c2.ExecuteNonQuery()
Dim x As Integer = c3.ExecuteNonQuery()
c4.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
End Using
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
Maybe I just fixed the index file and now it works. Tired of working on this, not fun!
Dim dwXCode As Integer = registry_shared.read_HKCU_dataPath(m_path)
Dim connString As String = "Provider=VFPOLEDB; Data Source=" & m_path & "; DELETED = FALSE;"
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Dim c1 As New System.Data.OleDb.OleDbCommand("SET EXCLUSIVE ON", connection)
Dim c2 As New System.Data.OleDb.OleDbCommand("SET DELETED ON", connection)
Dim c3 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01H WHERE FCUSTNO = ? AND FINVNO = ?", connection)
Dim c4 As New System.Data.OleDb.OleDbCommand("DELETE FROM ARTRS01 WHERE FCUSTNO = ? AND FINVNO = ?", connection)
Dim c5 As New System.Data.OleDb.OleDbCommand("PACK", connection)
c3.Parameters.Add("@FCUSTNO", OleDbType.VarChar).Value = pCustNo
c3.Parameters.Add("@FINVNO", OleDbType.VarChar).Value = pInvNo
c4.Parameters.Add("@FCUSTNO", OleDbType.VarChar).Value = pCustNo
c4.Parameters.Add("@FINVNO", OleDbType.VarChar).Value = pInvNo
Try
connection.Open()
c1.ExecuteNonQuery()
c2.ExecuteNonQuery()
Dim x As Integer = c3.ExecuteNonQuery()
x += c4.ExecuteNonQuery()
c5.ExecuteNonQuery()
pValue.Result = If(x > 0, True, False)
pValue.Message = x & " record(s) were deleted successfully"
Catch sqlEx As SqlClient.SqlException
pValue.Result = False
pValue.Message = sqlEx.Message.ToString
Catch exdb As OleDb.OleDbException
pValue.Result = False
pValue.Message = exdb.Message.ToString
Catch ex As Exception
pValue.Result = False
pValue.Message = ex.Message.ToString
Finally
connection.Close()
End Try
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
This is the first I'm trying to update a row in a FoxPro DBF file.
My code runs without error, but the row just won't update. I get a 0 back from ExecuteNonQuery
I tried hard coding the customer number but same thing.
Tried just updating 1 column FCOMPANY, same thing.
I must be missing something here, like perhaps a cursor or something.
The SELECT line was a feeble attempt to try something.
Dim m_path As String = Nothing
Dim dwXCode As Integer = registry_shared.read_HKCU_dataPath(m_path)
Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & m_path & "; Extended Properties=dBASE IV"
Const queryString As String = _
"SELECT * FROM ARCUS01.dbf WHERE FCUSTNO = @FCUSTNO " & _
" UPDATE ARCUS01.dbf " & _
" SET " & _
" FCOMPANY = 'Wiped Customer' " & _
", FADDR1 = @Empty " & _
", FADDR2 = @Empty " & _
", FCITY = @Empty " & _
", FSTATE = @Empty " & _
", FZIP = @Empty " & _
", FSADDR1 = @Empty " & _
", FSADDR2 = @Empty " & _
", FSCITY = @Empty " & _
", FSSTATE = @Empty " & _
", FSZIP = @Empty " & _
", FPHONE1 = @Empty " & _
", FFAX = @Empty " & _
", FFNAME = @Empty " & _
", FLNAME = @Empty " & _
", FTITLE = @Empty " & _
", FSALESPN = @Empty " & _
", FTERR = @Empty " & _
", FRESALENO = @Empty " & _
", FLPDATE = @Empty " & _
", FLPAMT = 0 " & _
", FATDSAMT = 0 " & _
", FYTDSAMT = 0 " & _
" WHERE FCUSTNO = @FCUSTNO "
Using connection As New System.Data.OleDb.OleDbConnection(connString)
Using command As New System.Data.OleDb.OleDbCommand(queryString, connection)
Dim paramWipe As System.Data.OleDb.OleDbParameter
paramWipe = New System.Data.OleDb.OleDbParameter("@Empty", OleDbType.VarChar)
paramWipe.Value = String.Empty
command.Parameters.Add(paramWipe)
Dim paramZero As System.Data.OleDb.OleDbParameter
paramZero = New System.Data.OleDb.OleDbParameter("@Zero", OleDbType.Decimal)
paramZero.Value = 0.0
command.Parameters.Add(paramZero)
Dim paramFCUSTNO As System.Data.OleDb.OleDbParameter
paramFCUSTNO = New System.Data.OleDb.OleDbParameter("@FCUSTNO", OleDbType.VarChar)
paramFCUSTNO.Value = p
command.Parameters.Add(paramFCUSTNO)
Try
connection.Open()
Dim x As Integer = command.ExecuteNonQuery()
pValue = If(x > 0, True, False)
Catch sqlEx As SqlClient.SqlException
pValue = False
Catch exdb As OleDb.OleDbException
pValue = False
Catch ex As Exception
pValue = False
Finally
connection.Close()
End Try
End Using
End Using
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
It's my parameter @FCUSTNO
I think I can figure it out now, got a 1 back
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
jkirkerx wrote: in a FoxPro DBF file.
Presumably as a learning experience.
I would suggests finding some other database to use if possible. I doubt knowing FoxPro is going to open any doors unless you are targeting some very specific organization. That is because it is no longer a commercial product (not even clear if there is any non-commercial use.)
|
|
|
|
|
I don't use Fox Pro, it's just an app that a customer of mine has and it's quick money, but got it working last Friday.
21st Century Globalism has become Socialism on a planetary scale, in which the unequal treaties of the past have come back into play.
|
|
|
|
|
I'm starting a new script. This is local on my machine and I'm the only user:
-- IF THE DATABASE EXISTS, DROP AND RE-CREATE IT
IF EXISTS (SELECT Name FROM master.dbo.sysdatabases WHERE name = N'Dashboard')
BEGIN
ALTER DATABASE Dashboard SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DROP DATABASE Dashboard
END
CREATE DATABASE Dashboard
-- CREATE THE TABLES
GO
USE Dashboard
CREATE TABLE DashboardInfo
(Id INT PRIMARY KEY NOT NULL,
SiteId INT NOT NULL,
InstrumentId INT NOT NULL,
TowerLocation INT NOT NULL)
The first time I run it all works fine. Any time I run it after that I get
Msg 3702, Level 16, State 3, Line 5
Cannot drop database "Dashboard" because it is currently in use.
Msg 1801, Level 16, State 3, Line 7
Database 'Dashboard' already exists. Choose a different database name.
Msg 2714, Level 16, State 6, Line 12
There is already an object named 'DashboardInfo' in the database.
If I close and re-open SSMS then it works ok again.
WTF is wrong here??????????
If it's not broken, fix it until it is.
Everything makes sense in someone's mind.
Ya can't fix stupid.
|
|
|
|
|
Try putting USE [Master] at the top of the script
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
