|
I have an MS Access db that needs to export to Sql Server on daily basis. The access database contains numerous tables and views associated with it. Everyday basis, this gets updated and it's ok to replace the database on daily basis. Sometimes the structure remains the same, but the data in many of the tables will be more on each day. Modifications to existing structure is also possible sometimes.
So, rather than appending data to the existing tables, I would like to replace the whole database on daily basis in Sql Server and perform operations on it.And it should be scheduled at a particular time. So I am looking out which will be the best option to do the same. Will the SSIS packages helpful? Please suggest possible ways.
|
|
|
|
|
meeram39 wrote: Please suggest possible ways. Create a linked server in SQL Server, point to your Access-database, SELECT INTO to where you need them.
Bastard Programmer from Hell
If you can't read my code, try converting it here[^]
|
|
|
|
|
Hi,
I am getting the following error,while creating identity column in sql ???
This is the code:
Create Table Myschema.Employee_Demo
(
Emp_ID int constraint pk_con Primary Key Identity(1,1),
Emp_Name varchar(55),
Emp_Sal decimal(10,2)
)
Insert Myschema.Employee_Demo(Emp_Name,Emp_Sal)values('Puja',20,000),('Tira',10,000),('Serrma',15,000),('Airrwari',12,000),('Shyyjina',13,000)
ERROR:
Msg 110, Level 15, State 1, Line 1
There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.
SO,
how to solve this error???Please help.......
Thanks...
|
|
|
|
|
Take the delimiter out of the monetary walues - these are for display only, not to be stored in the database. The SQL parser sees the comma in the figures as a column delimiter
Insert Myschema.Employee_Demo(Emp_Name,Emp_Sal)values('Puja',20000),('Tira',10000),('Serrma',15000),('Airrwari',12000),('Shyyjina',13000)
=========================================================
I'm an optoholic - my glass is always half full of vodka.
=========================================================
|
|
|
|
|
When you are inserting a record, The value for Emp_Sal should not contain a comma(,).
That is ('Puja',20,000) should be written as ('Puja',20000)
|
|
|
|
|
Hello all. I am writing a C# program. The program downloads an rdl report and then examines the SharedDataSetReference tag of the XML.
From this information, I can get the server path to the shared dataset. What I need to do is to use the shared dataset name to get the name of the stored procedure that the dataset gets it's information from. I assume that I would query the Report Server Database. The dataset uses a shared datasource as well. I have been trying to figure out how to do this for a while now, and I cannot.
I am not giving up though. If I figure it out, I will come back and post the solution, but I would appreciate any help I can get.
Thanks,
David
|
|
|
|
|
From reading this[^] the shared datasource may have nothing to do with a database but is a file on the REPORT server, its source may be anything, Excel, CSV, or a web service query.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thank you for your reply. All of our shared datasets point to stored procedures.
I think I just figured out a solution. I have done this before. I don't know why I didn't think of it before. I am going to have my program download the shared dataset as an .rds file and then look at the xml tag CommandText in the rds file.
|
|
|
|
|
Hi All,
I am executing a dtsx package from C# code, what dtsx package does is, it simply reads data from .txt file and writes into another .txt file. I have two connections "Flat File Connection Manager" and "Flat File Connection Manager 1", I am calling that package with FileWatcher application which invokes when file changes or created etc. But when I am trying to execute the package, its giving me Success message but its not able to create the destination file. But package is able to create the destination file when its executed from SSDT (BIDS) using start debugging but the same package when its called from the C# code, its not generating the destination flat file.
I am not understanding is it a permission issue or something I am missing in my Code?
Can anybody please help me I am searching in google and trying with different combinations, any kind of help would be greatly helpful - thanks in advance.
private static void OnCreated(object source, FileSystemEventArgs e)
{
string strFileExt = (Path.GetExtension(e.FullPath) ?? string.Empty).ToLower();
string directoryFullPath = Path.GetDirectoryName(e.FullPath);
if (Regex.IsMatch(strFileExt, @".txt|.csv", RegexOptions.IgnoreCase))
{
Package _package = null;
Application app = new Application();
string dtsPackPath = ConfigurationManager.AppSettings["SSISPackagePath"];
_package = app.LoadPackage(dtsPackPath, null);
_package.Connections["Flat File Connection Manager"].ConnectionString = e.FullPath;
_package.Connections["Flat File Connection Manager 1"].ConnectionString = directoryFullPath + @"\Test" + DateTime.Now.Year.ToString()
+ DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString()
+ DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString()
+ DateTime.Now.Second.ToString() + DateTime.Now.Millisecond.ToString() + ".txt";
var res = _package.Execute();
if (res == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
{
string message = "Package Executed Successfully....";
}
}
while (Console.Read() != 'q') ;
}
Now I did try it by putting OLEDB connection for destination, still when package runs from Visual Studio yields o/p but when run from the Console app, it doesn't give me any o/p neither in the o/p file nor in the Database table
Here is the code for writing into Database table
private static void OnCreated(object source, FileSystemEventArgs e)
{
string strFileExt = (Path.GetExtension(e.FullPath) ?? string.Empty).ToLower();
string directoryFullPath = Path.GetDirectoryName(e.FullPath);
if (Regex.IsMatch(strFileExt, @".txt|.csv", RegexOptions.IgnoreCase))
{
Package _package = null;
Application app = new Application();
string dtsPackPath = ConfigurationManager.AppSettings["SSISPackagePath"];
string sqlServerConnectionString = ConfigurationManager.AppSettings["SqlServerConnectionString"];
_package = app.LoadPackage(dtsPackPath, null);
_package.Connections["Flat File Connection Manager"].ConnectionString = e.FullPath;
_package.Connections["BSCWD725128.DTS_Testing"].ConnectionString = sqlServerConnectionString;
var res = _package.Execute();
if (res == Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success)
{
string message = "Package Executed Successfully....";
}
}
while (Console.Read() != 'q') ;
}
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
modified 28-Sep-16 14:15pm.
|
|
|
|
|
I got it resolved my friends, no worries right now
Thanks,
Abdul Aleem
"There is already enough hatred in the world lets spread love, compassion and affection."
|
|
|
|
|
we have been working on application which use ..mdb file to store the data ...
now if any one copies the database from c drive opens it which is password protected make changes and overwrite it back on program files ... is there any way we can find this out ... if someone has touched the db ...
file is password protected but in case person knows the password ...
like if we made some mistake in some entry and we open the db correct the entry and save it and then overwrite it in program files ... the application keeps working normally after that ...
Please let me know if there is any software or method to find that out .
|
|
|
|
|
Well for a start .mdb indicates you are using Access which is a single user tool. Why is someone able to copy the file from your machine! Oh wait you probably have it on a server and a number of clients use it - WRONG TOOL for the job. Move to SQL Server.
Password protection is supposed to PROTECT your file, you are not managing your passwords well enough!
You can make your folder readonly so they can copy but not return the file. Not sure if this will stop you writing to the database - test it.
It seem you are using the wrong tool in a badly managed environment, it is unlikely this will have a happy ending.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Mycroft Holmes wrote: You can make your folder readonly so they can copy but not return the file. Not sure if this will stop you writing to the database - test it.
It will.
Making the folder read-only would even stop you reading the database, because this is MS Access. In order to open the database, it has to create a lock file in the same folder.
"These people looked deep within my soul and assigned me a number based on the order in which I joined."
- Homer
|
|
|
|
|
It's been 15 years since I touched Access, does that represent short memory loss I wonder.
While I think Access has it's place, the moment it moves to a server or shared drive it becomes the wrong tool for the job.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
|
Hi All.
I Have a SQL Server.This server have link server to Oracle DB.
I can select anything from oracle DB through this SQL server such as.
Select * from [ORacleDBName]..[SIDName].[OracleTable]
But now I don't know how to update oracle db from SQL.
Pleas help me.
|
|
|
|
|
|
I also want to create a new table in oracle DB through SQL Server such as.
Select * from SQL Server into OracleLinkDB.
|
|
|
|
|
I would suggest you study the documentation for both Oracle and SQL.
|
|
|
|
|
If you follow the link Richard supplied and look down the left menu - 18 items down you will find some interesting reading.
You need to learn to do some thinking and research on your own!
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Select * from SQL Server into OracleLinkDB is SQL Server Syntax.
Oracles syntax is CREATE TABLE new_table AS (SELECT * FROM old_table);
I'd recommend checking out techonthenet.com[^] for comparing the syntax of different databases.
|
|
|
|
|
I Mean I Want to update Oracle DB through SQL Server Management Studio via Link Server.
|
|
|
|
|
Hello all. I have the following Stored Procedure:
ALTER PROCEDURE [EventPortion].[24Start]
AS
IF object_id('[IntegratedTest1].[EventPortion].[244Q1]') is not null
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
IF object_id('[IntegratedTest1].[EventPortion].[244Q2]') is not null
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
IF object_id('[IntegratedTest1].[EventPortion].[244Q3]') is not null
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
While (object_id('[IntegratedTest1].[EventPortion].[244Q1]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
Begin
WAITFOR DELAY '00:00:01.000';
End
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
While (object_id('[IntegratedTest1].[EventPortion].[244Q2]') is null)
Begin
WAITFOR DELAY '00:00:00.001';
End
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3
I want this code to drop the 3 tables if they exist. This works.
Then run the first Select Into.
IntegratedTest1.Event.MOE_2_4_DE_4_Q2 depends on (Selects from) 244Q1. The problem that I am having is that the second Select Into is being executed and I am getting a binding error that 244Q1 doesn't exist. So, the While Is Null Wait For loops are not working. If I just execute the Wait For 1 second delay, that does take 1 second to execute. If I just run the first select into and the 1 second delay, it takes about 1 second to execute. But if I run the first and second select into with the delays in between, there is no delay. I immediately get the binding error.
Is there anything I can do about this seemingly odd behavior?
Thanks,
David
Edit: I figured out a solution and wanted to post it.
I modified the stored procedure to:
ALTER PROCEDURE [EventPortion].[24Start]
AS
DROP TABLE [IntegratedTest1].[EventPortion].[244Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[244Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[244Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[244Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[244Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_4_Q3
DROP TABLE [IntegratedTest1].[EventPortion].[245Q1]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q2]
DROP TABLE [IntegratedTest1].[EventPortion].[245Q3]
SELECT * Into [IntegratedTest1].[EventPortion].[245Q1] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q1
SELECT * Into [IntegratedTest1].[EventPortion].[245Q2] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q2
SELECT * Into [IntegratedTest1].[EventPortion].[245Q3] FROM IntegratedTest1.Event.MOE_2_4_DE_5_Q3
I didn't mention before that this stored procedure is run from a C# front-end. I have code which extracts the SQL from the stored procedure and passes it to the following method:
private void ParseStartProcedure(string spText)
{
spText = spText.Replace("\r", " ");
spText = spText.Replace("\t", " ");
spText = spText.Trim();
string[] queryPieces = Regex.Split(spText.ToLower(), " as ");
string[] queryLines = Regex.Split(queryPieces[1], "\n");
for (int i = 0; i < queryLines.Length; i++)
{
if (queryLines[i] != "")
{
string query = queryLines[i];
SqlCommand cmd = new SqlCommand(query, m_dbSettings.sqlMetadataDbConn);
try
{
cmd.ExecuteNonQuery();
}
catch (SqlException ex)
{
string message = ex.Message;
}
}
}
}
modified 22-Sep-16 19:41pm.
|
|
|
|
|
Dear all,
Currently, I need to create a database for control Process in manufacturing.
One Item have got many LotNo,
One LotNo thought many Process(ex: Process A, Process B,Process C...)
with every Process maybe yes or maybo no use Material
How can I build database for:
Select all Lot no and Process by ItemNo
Slect all Process and status in every Process by LotNo
Input ItemNo show all information about Material used on every Process
This is my database:
Item(Id_Item,ItemNo,ItemName)
Lot(Id_Lot,Item_Id,LotNo)
Process(Id_Process, ProcessNo, ProcessName)
Lot_Process(Id,Lot_Id,Process_Id)
Material(Id,Process_Id,MaterialName, Quantity)
|
|
|
|
|
I am importing Data from excel-sheet to our sql server which comes from another system. Example In our sql server database Client name is Zee Entertainment Limited but in Another System Client Name is Zee Ent. ltd. or any other name format. In this way there are 10 thousands of records which is having data variations and We can not change our data which is connected to another business.
Kindly give me solution.
Thanks & Regards
Shankar Chaurasia
|
|
|
|
|