I have to make a process/stored procedure that will either send or receive from a specific location (parameters: ftp server, username, password, filename, etc). Ne direction/help in order to do this. Or if anyone has done this before please help.
thx
In the full recovery model, if i run a transaction that inserts 10MB of data into a table, then 10 MB of data is moved in the data file. Does this mean then that the log file will grow by exactly 10MB as well?
I understand that all transactions are logged to the log file to enable rollback and point in time recovery, but what is actually physically stored in the log file for this transactions record? Is it the text of the command from the transaction or the actual physical data from that transaction?
I ask because say if I have two drives, one with 5MB/s write speed for the log file and one with 10MB/s write speed for the data file, if I start trying to insert 10 MB of data per second into the table, am I going to be limited to 5MB/s by the log file drive, or is SQL server not going to try and log all 10 MB each second to the log file?
Is it posible to transfer the directory of the logfile of a database without corrupting it? if yes How?
Thanks,
Keezeg
:beer:
Hi Friends,
Is there any way to transfer a flat file from UNIX to Windows NT through an NT batch script without using FTP (and vice verssa)?
Thanks & Regards,
Raj
Hi there,
Since it's very urgent, can somebody help me on how to copy one column from one table and paste it into another table in the same database SQL server 2005.....
Thanks,
ilee1
Hi
Basicaly this is isituation: Software that our company developing (in Visual Basic 2005 .NET) is supposed to deliver files to sever and get them from it over Internet. In database those files are stored as image files. But the problem is that when Internet connection is slow or unstable the data transion seems to srew things up. Therefore file that is saved in database is corrupted and when i get it back from db it's not possible to open it.
Question is this: How to ensure safer delivery of large data (large meaning up to 4 MB). Is there any strategy that is recommended or proven to be best there is to this point.
Our first guess was to make MP5 based checksum in software and send it with data and then on serverside make a new checksum wiht MP5 and check if those two match. Since I've never done this I wanted to know (in case you dont know any better way)
*) what would be the code to use in order to make a MP5 checksum form Image and *) what the best best type of data for MP5 checksums (in tables and stuff)
Does anyone know if this is possible:
I have an FTP server that will be receiving files. The directory
and file structure will be a folder with a client name (can be called
anything) and it will have files in it (these files will have the same
filenames as all the other directories. So I will have folder
JimmyDoe with files a.txt, b.txt, c.txt and I will have JonnyDue with
files a.txt, b.txt, and c.txt.
Now I'm trying to figure out a way to get that dynamic file location to
a DTS package so I can import all the data from the text file into a
SQL server. The way the SQL server will be set up is that each
Folder from the FTP site will be a separate Database and each file will
1:1 with a table with the same name..
My biggest issue is figuring out a way to tell the DTS package the file
location to pull all those files and then importing them to the proper
database.
I'm not limiting the solution to DTS packages so if .NET can be
incorporated to make it easier then so be it. But keep in mind I
can have up to 200 folders with 12 - 20 text files ranging from
hundreds of rows of data to many thousands of rows. And the
package needs to be ran twice a day so time/performance is an
issue.
To recap: Need DTS package that uses Dynamic file source and transfers data to Dynamic database destination.
(And I'll write slow VB.NET code to handle this before I create/manage 200+ DTS packages as a solution)
Any help at all is greatly appreciated.
Firstly I am not a programmer; and this is the first time I have used this forum
I am working with some friends who have been assisting.
I am developing a product that is importing data into a sequel data base, and the data is being recorded in real time but posted at minute intervals. The data is then being exported to a local flat file again at minute intervals each row containing about 50 columns.
Currently the whole file is being imported across the internet and it all works fine. I am using analysis services to look at the data every two minutes. As the file grows this time becomes longer. What I want to do is only import the latter portion of the flat file, say the last 144 rows there are 1440 rows per day.
What is the most efficient way to do this? Duplicated records would be a disaster.
Ideally the number of rows to be collected would be variable not fixed at 1440, these rows would be added to the main table that will probably contain up to 1 years worth of data i.e. 1440 rows * 365 days with approx 50 columns.
Any comments to point me in the right direction would be appreciated.
Steve
what's the easiest way to do this. i have 5 tables within the mdb filei tried the "easiest way" shown in a website but it does not work.the way i did it was on access(2007) > database tools tab > sql server buttonit gave me an error when it ask for an login IDi do not have any password/id..please helpthanks
View 3 Replies View RelatedHi All,1. i want to transfer the .csv file data into sql server table, itried with the DTS but while creating DSN it not prompt to attechthe .csv file. give me the proper steps to perform the datatransfer...2. i want result of my query into excel or text file by using the sqlquery( like Select * from employee where emp_salary>10000 to 'c:emp.xls).i know the other way right click into query analyzer windowand select option result to file, but want the result by using SQLquery.....
Quote:
Hi All,
I am trying to pass a text file from desktop machine to PDA thro TCP-IP protocols. It follows Server (on desktop machine) and Client (on pda) architecture. File is converted to bytes and will be pushed to network stream. The client on PDA will receive the bytes and store it on queue and writes the file on PDA. The operation is accomplished through threads on both Server and client. Applications are developed on MS Visual Studio 2005 (C#).
For large files like 1024 KB or having lines about 15000, it takes around 15 minutes. Can anyone give a better idea to transmit files from desktop machine to PDA using TCP-IP protocols.
Thanks in advance
Boss
We are using log shipping and we would like to remove all transfered and applied journal (in the primary box).
We have the intentionto use a trigger like this :
CREATE TRIGGER del_log
ON log_shipping_plan_history
AFTER INSERT
as
declare
@lastfile nvarchar(256)
SELECT @lastfile=i.last_file
FROM log_shipping_plan_history e INNER JOIN inserted i ON e.sequence_id = i.sequence_id
where i.activity=1
begin
if IF (@lastfile <> NULL)
...
... remove file (using xp_cmd for example...)
...
end
but the problem is that we have only the last file transfered and applied that will be removed
(some time, more that 1 file are applied in one shot ...
see num_files column in log_shipping_plan_history).
Any solution to remove all the files generated before the last one given by the query ?
Any other solutions (sql wizard gives the possiblity to to remove file after a laps of time 1hour, 1day...).
I am looking for the table that contains all the journal files (that we can see when we try to retore a db) ?
Thanks
I am trying to transfer 200 txt files into SQL server by using query analyzer.
The command is 'Bulk insert [tableName] from 'pathfilename.txt'
However, I need to read and modifiy the txt file.
I am new to SQL server but I believe there must be some one who is a wizard can do what I want easily.
Thank you for the help in advance!
Here is the raw data layout, which is comma delimited.
BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990 BDate 1/1/1990
Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005 Edate 1/1/2005
Fq D Fq D Fq D Fq D
Date R P M E D Date R P M E D Date R P M E D Date R P M E D
1/1/90 1 2 3 4 5 1/1/90 2 3 4 5 6 1/1/90 3 4 5 6 7 1/1/90 4 5 6 7 8
2 3 4 5 6 1 2 3 4 5 3 4 5 6 7 6 7 8 9 1
1/1/05 ...... 1/1/05 .... 1/1/05 ..... 1/1/05 .....
This is the desired output after load into the table, which is tacking each repeating block on top of each other.
Date R P M E D
1/1/90 1 2 3 4 5
2 3 4 5 6
1/1/05 ......
1/1/90 2 3 4 5 6
2 3 4 5 6
1/1/05 ......
1/1/90 3 4 5 6 7
3 4 5 6 7
1/1/05 ......
1/1/90 4 5 6 7 8
6 7 8 9 1
1/1/05 ......
Executing the FTP Task - The execution starts and after 3 or more minuts the execution stops with the RED X but with no errors, and the file is not transferred.I use the same entries to the FTP connection manager as it is for the Dreamweaver...The variable that I created for file in the site is FileName1 and the site directory tree is The local path is And The File Transfer is filled up like this: After the Execution stops I get..And the file was not transfered..Also, when I try to Specify the Variable Expretion.
View 8 Replies View Related
Hi,
I have a package which is pulling data from a text file. The text file is located in different domain.
when I copy the text file manually to same domain where my sel server is located then Job is working fine.
How can I avoid this. Do we have any oher options ?
Hi all,
Currently, my (small) intranet site is storing it's data on a remote SQL server. The danger with this, as has happened several times now, is that the application is twice as vulnerable; if either the webserver or the dataserver malfunctions or is unreachable, the application won't work.
I only recently discovered the possibility to use local database files (MDF files), and this seems like a much better solution for my site. But now I want to transfer the tables that are residing on the dataserver, to the MDF file. The database only contains tables. How do I handle this? I do not have access to the dataserver, only to a few databases that are residing on it. Is this possible using Visual Studio 2008? I have read about a "Bulk Copy Program" (bcp) which is included with SQL Server, but I cannot find a download for just that application.
Or is this totally not the way to go? I've discovered MDF files are a bit more problematic with concurrent connections; having tables open in Visual Studio results in "Site offline" or "Cannot open database" error messages on the website. Problems I've never had to deal with using SQL Server, but they are only minor problems.
Thanks,
Peter
I have few issues regarding the transfer of the tables from one file group to another file group in SQL 2008 and also How can we backup and restore the particular database based on file group level.
Let’s say I have a tables stored within the different FG. such as
Tables
File group Dimension tables Primary Fact tables
FG1 ...
FG2…
zzz_tables DEFAULT_FG
dim.table1 DEFAULT_FG
dim.table2 DEFAULT_FG
… ….
Here all I want to transfer the dim.table1 ,dim.table2 from DEFAULT_FG to the Primary File group .So is there simple methods for transfer the dim.table1,2 from one FG to another .I have tried somewhat but I couldn’t get the exact way.Secondly after moving those dim.table1 ,dim.table2 from DEFAULT_FG to Primary ,All I want to backup and restore the database only containing the Primary and FG1,FG2… not a DEFAULT_FG.Is it possible or not.?
Hi All,
i have mutiple text file. let us say,a1.txtb1.txtc1.txt
i have to port this text file data into the table (SqlServer Database) which have the same file structure.(i.e)x1 (SqlServer table)y2 (SqlServer table)z3 (SqlServer table)
now i have to transfer a1.txt file data ----to--- x1b1.txt file data ----to--- y2c1.txt file data ----to--- z3
using SSIS. like that, i have to transfer more than 250 files at a time.manually binding 250 files into the package is very cumbersome and time consuming process.
so, can any one give ur valuable sugession to solve this issue.
I need to transfer dbf file to sql server 2005 express edition with some periodic interval. Can any one please recommend which is the easiest and efficient method to do?. Like polling every 5 to 10 seconds transfer data to sql server 2005 ex edition.
Is it recommend to do it visual basic program?, how to do it. pls help
I'm having problems designing a package to attempt to execute a fast load data transfer but failback to regular speed with error redirection in the event of an error.
The way I designed this was to add one data flow task to my package called "DFT FASTLOAD". The data flow copies a table SRC to another table DEST in the same SQL Server database. In the error handler for the data flow task I copied the original data flow task and changed the name to "DFT REGULARLOAD with Error redirection". In this data flow task I did not use fast load and addtionally redirected errors to a text file.
In the Data Flow Task "DFT FASTLOAD". I am copying from a varchar source field(with non-date strings) to a datetime destination field to force errors. However the Data Flow Task "DFT REGULARLOAD with Error redirection" never seems to start transferring data from source to destination. The data Flow Task "DFT REGULARLOAD with Error redirection" turns yellow (after the error occurs in "DFT FASTLOAD"), but no data is being transferred). It seems like it hangs.
Do I need to increase the MaximumError Count or something? The data flow task "DFT FASTLOAD" does not turn red when the error occurs it just remains yellow, so i assume I'm on the right track since it seems the error is caught.
I have added screenshots ... hopefully these screenshots will clarify my problem.
DESIGN:
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD1.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD2.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD3.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD4.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD5.jpg
http://i256.photobucket.com/albums/hh179/abzbank/DESIGN_FASTLOAD6.jpg
RUNTIME:
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD7.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD8.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD9.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD10.jpg
http://i256.photobucket.com/albums/hh179/abzbank/RUN_FASTLOAD11.jpg
I can provide more details if needed... but really this is just a basic test.
Any assistance would be appreciated!
I am using transfer manager in SQL 6.5 to copy a database and
all objects with data to another server. Transfer manager is not recreating all stored procedures. This even happens when I used it to another database on the same server. Any ideas??
Is it possible/advisable when transfering very large amounts of data from server to server to:
trasnfer the data to a new table first
second alter new table adding indexes, defaults, ets based on original table
if it is what flow item would be used to transfer/alter the indexes and defaults?
I'm very new to ssis so the more detail you can give the better.
Thanks
i have a old database in foxpro and it has to be converted to sql server 6.5 database . the table in the foxpro has been broken into more than 1 tables in the sql server . so how can i transfer the data from 1 foxpro table to different tables in sql server 6.5.
vineet
Hello:
I have been trying to run transfer manager to transfer all of the data
from the production database on one server to a test database on another
server(to refresh it). In order to make sure it runs on the server, I have
been scheduling it under EM to do so and I am pointing to the log on the
destination server on the EM Transfer panel.
For some reason, I am getting the following message in the destination
server error log:
99/02/16 10:24:41.42 ods Error : 17824, Severity: 10, State: 0
99/02/16 10:24:41.42 ods Unable to write to ListenOn connection
'.pipesqlquery', loginname 'sa', hostname 'TEMP09'.
99/02/16 10:24:41.42 ods OS Error : 232, The pipe is being closed.
99/02/16 10:24:41.42 spid17 Error : 1608, Severity: 21, State: 2
99/02/16 10:24:41.42 spid17 A network error was encountered while sending
results to the front end. Check the SQL Server errorlog for more
information.
I checked the event viewer error log and see no messages for today.
Can any one advise me what I need to do for this to run successfully?
Thanks. Any information furnished will be greatly appreciated.
David Spaisman
Hi,
I have the following method which transfers data SQL to SQL on the same server but when I try to change the destination server it won't transfer the data. The method runs through as expected with no exceptions or errors but with no data transfered.
Private Sub TransferSQLData(ByVal SourceDetails As Admin_upload.EnvironmentDetails, ByVal DestinationDetails As Admin_upload.EnvironmentDetails)
'Transfer database from source to destination.
Dim oPackage As New DTS.Package2
Dim oConnection As DTS.Connection2
Dim oStep As DTS.Step2
Dim oTask As DTS.Task
Dim oCustomTask As DTS.TransferObjectsTask2
Try
oStep = oPackage.Steps.New
oTask = oPackage.Tasks.New("DTSTransferObjectsTask")
oCustomTask = oTask.CustomTask
oPackage.FailOnError = False
With oStep
.Name = "Copy Database design and data"
.ExecuteInMainThread = True
End With
With oTask
.Name = "GenericPkgTask"
End With
With oCustomTask
.Name = "DTSTransferObjectsTask"
.SourceServer = "MYSERVER"
.SourceUseTrustedConnection = True
.SourceDatabase = SourceDetails.MetaDB
.SourceLogin = SourceDetails.MetaUser
.SourcePassword = SourceDetails.MetaPWD
.DestinationServer = "MYSERVER"
.DestinationUseTrustedConnection = True
.DestinationDatabase = DestinationDetails.MetaDB
.DestinationLogin = DestinationDetails.MetaUser
.DestinationPassword = DestinationDetails.MetaPWD
.CopyAllObjects = True
.IncludeDependencies = False
.IncludeLogins = False
.IncludeUsers = False
.DropDestinationObjectsFirst = True
.CopySchema = True
.CopyData = DTS.DTSTransfer_CopyDataOption.DTSTransfer_ReplaceData
End With
oStep.TaskName = oCustomTask.Name
oPackage.Steps.Add(oStep)
oPackage.Tasks.Add(oTask)
oPackage.Execute()
Catch ex As Exception
LabelUploadMeta.Text = "Failed to Upload MetaData: " & ex.Message
Throw ex
Finally
oConnection = Nothing
oCustomTask = Nothing
oTask = Nothing
oStep = Nothing
If Not (oPackage Is Nothing) Then
oPackage.UnInitialize()
End If
End Try
End Sub
This works fine but when I set the following within the method:
.DestinationServer = "ANOTHERSERVER"
It won't transfer the data.
I can access the remote server and read and write data to it.
Any ideas?
Hi ,
Can anyone suggest me to transfer a database from 65 to 70.
I trasfer a database using DTS, but i am only getting data. I need my users , views, stored procedures, permissions.
I need to upgrade three databases from 65 to another 70 server.
i also generated a script from 65, but which is showing errors.
Could u pls give any suggestions.
thank u.
--Raj
I recently transferred a db from our production machine to a test machine. After the transfer was complete I realized that all copied logins were copied with blank passwords. Is this default behavior? Can I set the transfer to include passwords?
Dave King
Hi,
stupid question but i have forgotten how to transfer a DTS package between servers, its been so long since i last had to!!!
TIA
Hi Everybody...
I have recently tried to transfer my database over from one machine to another, but failed to do so because it gets jammed on 30%. I am just wondering if there is any other ways I can take to migrate the database over.
I am currently running sql server 6.5 in concurrent with iis 4 and nt4. I am trying to seperate the iis to a new server machine and sql server to another server.
Please advise me which route to take if you have any suggestions and your solution is important to me. Thanks!
-Ken
Hi,
I want to transfer rows from one database to another.
Source My Source tables in database 'Staging'
CustomersEng
Name
Age
StCode
CityCode
InternalID
CustomersSpanish
Name
Age
StCode
CityCode
InternalID
CustomersEng and CustomersSpanish contains a replica..only difference being that 'Name' in the CustomerSpanish table is written in spanish language. The Stcode, citycode and Internalid together makes the row unique.
Code BlockSample script
Create table #CustomersEng (name nvarchar(20),age int, st_code int,
citycode int, internalid int)
Insert into #CustomersEng values('Aron',23,1,1,12);
Insert into #CustomersEng values('Cathy',28,1,1,13);
Insert into #CustomersEng values('Zarog',33,1,1,14);
Insert into #CustomersEng values('Michale',25,1,2,12);
Insert into #CustomersEng values('Linda',43,1,2,13);
Insert into #CustomersEng values('Burt',53,1,2,14);
Create table #CustomersSpanish (name nvarchar(20),age int, st_code int,
citycode int, internalid int)
Insert into #CustomersSpanish values('Arona',23,1,1,12);
Insert into #CustomersSpanish values('Cathylo',28,1,1,13);
Insert into #CustomersSpanish values('Zarogo',33,1,1,14);
Insert into #CustomersSpanish values('Michalo',25,1,2,12);
Insert into #CustomersSpanish values('Lindalo',43,1,2,13);
Insert into #CustomersSpanish values('Burto',53,1,2,14);
---------------------------------------------------------------------------------------------------------------
Destination tables in database 'CMO'
CustomerMaster
CustomerId (identity) (pk)
StCode
CityCode
InternalID
CustomerDetails [customerid and languagecode together are primary keys]
CustomerId (fk)
Name
Age
languagecode
Language
languagecode (pk)
languagename
Code Block
Create table #CustomerMaster (customerid int identity(1,1), st_code int,
citycode int, internalid int)
Create table #CustomerDetails (customerid int,name nvarchar(20),age int,
languagecode int)
Create table #CustLanguage (languagecode int, languagename varchar(10))
Insert into #CustLanguage VALUES (1,'English')
Insert into #CustLanguage VALUES (2,'Spanish')
Code BlockEXPECTED OUTPUT
CustomerMaster
1 1 1 12
2 1 1 13
3 1 1 14
4 1 2 12
5 1 2 13
6 1 2 14
CustomerDetails
1 Aron 23 1
1 Arona 23 2
2 Cathy 28 1
2 Cathylo 28 2
3 Zarog 33 1
3 Zarogo 33 2
4 Michale 25 1
4 Michalo 25 2
5 Linda 43 1
5 Lindalo 43 2
6 Burt 53 1
6 Burto 53 2
How can i write t-sql for this?
thanks
Hi All;
I know that in the SQL Server, the logins are not contained in the database (thanks to Kevin Yu) but I don't know where are they stored. For example if I want to transfer to another server I'll have to backup users too; but HOW?
In order to be more clear: When I simply take a backup from my server and restore it to my computer I can not login again with the user login information which is created on the server; However even if I make some changes; after backing the DB up from my computer and restoring to the server the logins are working (with re-mapping)
Thanks...
I had Sql Server 2005 installed on my machine, with that I am working on Visual Studio 2005. I just need to add an existing database into the app_data folder. The database is existing in the Sql server, it contains 180 tables so I cannot manually copy that tables in the app_data folder. for that is there any another way to copy all the tables into the database into the folder app_data with the data in the table.
Thanks & Regards
Girish Nehte
I recently transferred my SQL DB from one server to another, I backed the DB up, copied across and restored it into the new location, the problem is that I get the following error.
System.Data.SqlClient.SqlException: Invalid object name 'Table Name'
I have created a new user for this database and changed my code accordingly.
<%@ Import Namespace="System.Data.SqlClient" %>
<%@ Page Language="VB" Debug="true" %>
<Script Runat="Server">
Sub Page_Load
Dim conPubs As SqlConnection
Dim cmdSelectContacts As SqlCommand
Dim dtrContacts As SqlDataReader
conPubs = New SqlConnection("Server=Treebeard;UID=Intranet;Pwd=glglgl;Database=Contacts")
ConPubs.Open()
cmdSelectContacts = New SqlCommand ("Select * From Clientdata", ConPubs)
dtrContacts = cmdSelectContacts.ExecuteReader()
While dtrContacts.Read()
Response.Write( "<li>" )
Response.Write( dtrContacts( "c_forename" ))
End While
dtrContacts.Close
conPubs.Close()
End Sub
</script>
Any Ideas?