Use Bcp To Output Data To A Compressed (zipped) Folder
Jan 25, 2007
Hi all,
I am trying to use bcp to output data to a compressed (zipped) folder.
The bcp command is called from a step in scheduled job in SQL 2005 (T-SQL) similar to:
SET@chvCommand =
'bcp [working_t] out D:EprojectsEdataCdata200701.dat'
+ ' -c -STPISQL -T'
EXECUTE master.dbo.xp_cmdshell @chvCommand, NO_OUTPUT
.... where Cdata is a compressed (zipped) folder.
The scheduled job seems to work without errors, but afterwards there is nothing in the compressed folder.
If Cdata is a regular folder everything works fine.
Thanks for you insight.
View 8 Replies
ADVERTISEMENT
Nov 23, 2005
One way to save storage space is to put the SQL data files into a compressedfile. Has anyone got any idea how this will affect the query speed?
View 2 Replies
View Related
Mar 5, 2007
Where do I go in my computer to correct this problem so that I can complete the installation?
View 3 Replies
View Related
Jan 31, 2007
I want to zip up some HTML and store it a field. 2 questions:
1: Are there any sample scripts available for this, I haven't found any?
2: What Type should the storage field be for that?
Thanks,
--PhB
--PhB
View 6 Replies
View Related
Jul 19, 2007
Noticed weird activity today with pull merge.
When your snapshot is set to be delivered via ftp and compressed in a cab file. If you add a new article to your publication and re-run the snapshot the agent will be unable to pull the snapshot down as it for some reason doesnt think its compressed. It is failing to find the scripts it needs inside the cab file despite the cab file existing in the correct location.
Here is the error.
2007-07-19 09:57:29.855 Snapshot files will be downloaded via ftp
2007-07-19 09:57:29.886 Connecting to ftp site 'SQL3'
2007-07-19 09:57:29.933 The schema script 'empActive_127.sch' could not be propagated to the subscriber.
2007-07-19 09:57:29.933 Category:NULL
Source: Merge Replication Provider
Number: -2147201001
Message: The schema script 'empActive_127.sch' could not be propagated to the subscriber.
2007-07-19 09:57:29.933 Category:AGENT
Source: SQL2SQL2005
Number: 20033
Message: The process could not retrieve file 'SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch' from the FTP site 'SQL3'.
2007-07-19 09:57:29.949 CategoryS
Source:
Number: 12003
Message: 200 Type set to I.
200 PORT command successful.
550 SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch: The system cannot find the file specified.
550 SQL3_CCUSA_ATLAS_SYSTEM TABLES/20070719055712/empActive_127.sch: The system cannot find
View 1 Replies
View Related
Apr 27, 2007
Hello,
I have a table of compressed data and am looking for an efficient way to expand the data for reporting purposes.
The table is used to store the number of hours a given contractor works and is stored in the following fashion:
cntHours
58
20
58
20
The first row represents the number of sequential days where an employee worked the same # of hours. Once the # of hours changes, a new record is created. In this simple example, the first row shows an employee working Monday-Friday (5) for a total of 8 hours each day. The second row represents the weekend (2 days) where the employee worked 0 hours.
What I need to do is explode this out to show 1 record per day. Ideally I'd like to write a function to do this as I would be linking to another table which has the start and end date for the contractor and would allow me to apply individual dates to each record based on the contractor start date through to the end date.
Thanks for any help you can provide!
View 2 Replies
View Related
Apr 14, 2014
Source System : Oracle
Target System : Ms Sql Server 2012
ETL Tool Used : SSIS
My source data is present in XML File which is stored in CLOB column Of Oracle. CLOB column is compressed.I need to Migrate data by Uncompressing XML to SQL 2012 .
Do I need to define XML column in SQL Server 2012 for storing Uncompressed CLOB values ?
How to uncompress the clob and extract the required data from XML using SSIS .
View 2 Replies
View Related
Mar 14, 2007
Hi,
I need to zipped files using SSIS, but not sure how to do that.
Zip application: 7-zip
Any help?
thanks
View 1 Replies
View Related
May 15, 2015
I am asked to create 100 procedures to a database. Any best way to create them in a database one by one by calling the files and saving the execution output files in a folder?
View 9 Replies
View Related
Sep 19, 2007
I dont alot about sql server 2005(Express edition). For debugging purposes i want to copy the whole app_data folder(.mdf & .log files) on the production server to another folder on the same machine(or sometimes to a network folder). So when i copy and try to paste this App_data folder to a new location, i get this error message
"cannot copy ASPNETDB: it is being used by another person or program. close any programs that might be using the file and try again."
After reading the above message, i close visual web developer, stop the website in IIS and stop the SQLExpress service on the server and try again but still get the same message.
So how can i make sure that all the programs accessing these database files are closed such that i'm able able to copy them to a different location.
View 2 Replies
View Related
Jul 2, 2015
I know a WMI event watcher can be used to watch for a new file being added to a folder. However, I need to check for new folders being added to an existing folder. I haven't been able to find a post on doing this. Is there a way in WQL to check for a new folder being added instead of a new file? I've used SQL for years, but am new to SSIS.
View 2 Replies
View Related
Oct 31, 2007
Ok,
I have a network folder called A
I have a SharePoint (2007) Document Library site called B. Web Client is enabled on the server and B is mapped as a Drive (let's call it Y for this discussion)
I want to move documents in A to B. Easy enough, right? Not so....
I first started by creating a batch file that issues a COPY \A \Y /Y at the command prompt. Viola! Worked Great!
I then moved that command to a SQL Agent job as a CMDExec statement (exact same statement) and attempted to run it.....CRASH! It found the files in A but then said "The system cannot find the path specified"
Ok, so I tried it in SSIS. CRASH! Checked the error log. Same thing...
So I then checked the account under which the SQl Agent was running (special domain account for all our SQL Servers). Thinking it might mater I changed it to run under my name (I'm in Domain admin). I also ensured I had permissions to the SPS 2007 library as well. (I did).
Ran again! CRASH! Same error....
So, I created a batch file , placed thec ommand in the batch file and ran that from the command prompt! Viola! Worked Great.
So, I was thinking of how ingenious I was as I pasted my C:RootCopy.bat into my SQL Agent job. With a big grin on my face I right clicked and picked "Start Job at step".......CRASH! Same error.
Does anyone have any ideas on this ???????????????
Thanks,
Stephen
View 3 Replies
View Related
Sep 4, 2006
I have a set of folders with the following structure:
MainFolder
1999
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
2000
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
etc.
Is there a way that I can use the foreach loop container to loop the sub folders? My plan was to get the folder name and path into a variable, use this to build the connection string for each file in this folder, carry out the upload for that folder, then move onto the next folder and do the same thing, but I cannot see a way to do this.
Thanks,
Sam
View 2 Replies
View Related
Feb 2, 2007
I am using SQL2005 merge replication and have pull subscribers on a low bandwidth link
I am compressing the snapshot into an alternate folder. Files are not put into the default folder
When I start a synchronization, I would expect the cab file to be copied to the subscriber and then the files to be extracted locally at the subscriber in order to apply the snapshot
However, what appears to be happening is that the files are being extracted from the cab file on the publisher (in a UNC specified directory) and then copied in their uncompressed form to the subscriber - resulting in an extremely slow snapshot application.
Any ideas what I am doing wrong? I have read about the options for using FTP to transfer snapshot files, but I am not clear whether I have to use FTP in order to transmit a compressed snapshot. I don't want to use FTP unless I need to.
Any suggestions would be much appreciated
aero1
View 8 Replies
View Related
Oct 10, 2001
hi, I am curious to know if I can do the following from within a store procedure.
If I have a folder under c drive c:clientand would like to check if there is any files inside the client folder, how can I write such code in sql into a store procedure. I want to do some actions like expoft table data (bcp) into the client folder if there is no text files..... I do appreciate your help
Ali
View 1 Replies
View Related
Jun 11, 2015
I just completed a copy-Only compressed backup of a DB (with a FULL Recovery Model ) on SQL Server 2012 and the resulting backup (the bak file) is 1/100th the size of the data & log file. Is the compression in SQL Server 2012 just that good or did something else happen that I did not catch? Below is the T-SQL to re-create the backup. The size of the data file is 750MB and the log file is 75GB and is %95 used according to the SQLPERF command.
Does the compression in SQL Server 2012 simply that good
BACKUP DATABASE [MYBIGOLEDB] TO DISK = N'Z:Microsoft SQL ServerMSSQL11.MSSQLSERVERMSSQLBackupMYBIGOLEDB_20150611.bak' WITH COPY_ONLY, NOFORMAT, INIT, NAME = N'MYBIGOLEDB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
GO
View 1 Replies
View Related
Oct 16, 2007
Hi,
I have a server in our central location which is a compressed snapshot publisher. I have 2 push subscribers in remote locations on very slow WAN links. I would like the snapshot cabinet file to be uncompressed at the subscribers location rather than the publisher location. Is this possible with push subscribers? I want to manage the pushing of data to the remote subscribers from the publisher location.
I understand the default with push subscriptions is to uncompress the cabinet file at the publisher location.
Thanks,
Mark.
View 3 Replies
View Related
Apr 24, 2007
Hi everyone,
already tried this in other SQL forums, but maybe i have some luck here.
I need mainly to restore database backups from customers. They arrive in all kind of formats (zip, rar, gz). I'd like to be able to restore those directly from the compressed file, because i'm talking up to 7GB rar files which take a while to uncompress in a separate step.
I'm working for 6 years in R&D environments, but mostly on Linux/Oracle where this is an easy task using pipes, but i haven't found a sinlge web page, post or even script to do this with MSSQL. The VDI is not really what i'm looking for, so aren't backup software like SQLBackup, Litespeed etc. because i can't force the customer to use those.
Anybody any idea or even the same problem maybe with solution?
Thx.
View 10 Replies
View Related
Jan 31, 2006
I am trying to read a 36 byte files that contains compressed data. I create my Flat File data source and SSIS reads it fine UNTIL it hits a x00 in the file. Then it stops reading and I can't get any data after it. There is data after the x00. Here the entire hex string: C7 C7 CF 6A 00 00 05 02 3D 03 21 01 E0 02 00 00 00 00 00 00 00 00 3D 3C 1E FD 02 C8 00 00 00 AE 41 E3 28 7C
To test, I changed the two x00 in bytes 5 and 6 to x01 and SSIS read until the next x00.
Anyone have any ideas?
Thanks,
Jack Lavender
View 3 Replies
View Related
May 15, 2008
Hi
I need to use .txt files as data source, those files are in same folder. Now I connect to them with ODBC, and allow client select which file they want to view by input the name of file as a parameter.
The problem is all those file are tabdelimited and the default of ODBC driver is CSV delimited. So once new file is added in the folder, we must refresh the ODBC otherwise the drive will deal all the data as one column.
Are there any better solution for this case?
View 6 Replies
View Related
Nov 2, 2015
I have this bcp
EXEC xp_cmdshell 'bcp "SELECT top 10 * FROM [db].dbo.[u_activity]" queryout "C:bcpCustomers.csv" -c -b 10000 -t~ -S 10.20.8.149 -U user-P password'
which throws the data into a csv, but the issue is I also need to compress the file down in a .rar file,
can this be done in a SP while still executing the above bcp ?
View 2 Replies
View Related
Sep 22, 2006
It seems SQL Server 2005 can store data in both C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData and C:Program FilesMicrosoft SQL ServerMSSQL.2MSSQLData. But what's the difference of the 2 folders? I accidentally found identical mdf files in the two folders and got confused. Thanks.
View 2 Replies
View Related
Jun 3, 2008
I am having a problem with a client's MS SQL Server 2005. When doing a restore, SQL will not allow me to restore to the database mdf and ldf files outside the SQL root folder (C:Program FilesMicrosoft SQL Server). I want the data and log files to be on a separate disk with plenty of disk space. When I created the database, SQL allowed me to create the mdf and ldf files in the E:Data SQL ServerData folder. But when I perform the restore SQL reverts to the C:Program FilesMicrosoft SQL ServerMSSQL.1MSSQLData folder. I am specifically redirecting the mdf and ldf files to the custom folder by clicking on the button (...) to change the folder.
I have tried creating a new SQL Server instance and uninstalling and reinstalling SQL Server to no avail.
How can I force the restore task to restore my database to the custom data folder location?
Thanks
View 10 Replies
View Related
Aug 2, 2007
How do you determine the data folder during the SQL2005 Express install?
No matter what folder I start with it gets appended with MSSQL.1 or MSSQL.2
If another vendor has installed a different instance of SQL2005 how do I know whether mine is .1 or .2 ?
Basically I need to know the full folder in order to copy our MDF/LDF files into so we can attach them.
Or is there another way?
Thanks.
Graham
View 7 Replies
View Related
Sep 14, 2012
I have created a new report.what i do in application is i compress the image and save it in database.now i need to retrieve the compressed image and display in the report. I have used the following code to decompress the binary data save in the image.
I dont know after that what should i do. How to show the picture in SSRS Report. I need to show picture in many reports.one of my doubt is how to call this function in SSRS Report. The function accepts input as byte but in database the column in varbinary.
Should I convert the input type of function to varbinary instead of byte array?
Public Function Decompress(ByVal arr As Byte()) As Byte()
Dim notCompressed As Boolean
notCompressed = False
Dim MS As MemoryStream
MS = New MemoryStream()
[Code] ....
View 4 Replies
View Related
Nov 2, 2005
Hey everyone, trying to set up a SQL Query to run on a pre-determinedbatch cycle that will automatically dump out specific table data to a..csv file so that users can load the file into Excel right away, withno manipulation or modification.We are running SQL Server 2000. I am a novice SQL user. Anysuggestions?
View 2 Replies
View Related
Feb 23, 2006
hi everyone,
can any one suggest me how to clean the 'REPLDATA' folder created during snapshot replication. i want to automate the process.
can anyone suggest any undocumented sp like
'sp_MSDelete_publisherdb_trans'
thanks
View 9 Replies
View Related
Nov 5, 2007
What is the purpose of the .cer file found in MSSQL.1MSSQLData? Something to do with encryption, but what?
Why do I see as many as three .cer files on a server?
Thanks, Dave
View 1 Replies
View Related
Nov 1, 2007
HI All,
I have created around 150 reports using SSRS. I just made a new data drive subscription(file sharing) using report manager. Now I'm wondering is there any option to make subscription at the higher level? That means instead of subscribing one report by one report , can I do it at the folder lever using report manager without writting a programm?
Thanks
View 2 Replies
View Related
Feb 4, 2008
Hi All:
Using SQL Express 2005.
I want to attach some *.mdf files that are located in a folder other than the SQL Server Data folder (for example, D:MyData). If I do so, then I get an error message that the files can not be updated because they are read only.
If I move the files to the SQL ServerData folder then they attach without errors and work fine.
Is there some way I can tell SQL Server to allow the attachments in my other folder as well?
View 7 Replies
View Related
Feb 15, 2007
Hi, I am trying to lean ASP.NET. So I was trying to install SQL Server too. But, I could not install. The aleart box popped up:
"SQL Server set up cannot install files to the compressed or encrypted folder: C:Program FilesMicrosoft SQL Server"
Before this, I also got a warning that the hardware of my computer does not meet the hardware requirement.
I would like to know how I can install SQL Server into my computer. Or, shouldn't I do that? To learn ASP.NET I want to have a database ready. If I can not use SQL Server, I may try Access or something smaller...
Thanks in advance.
yyokota2
View 1 Replies
View Related
Oct 19, 2007
I have inherited a SSRS setup and am trying to do some clean up. Problem is I cannot see the Data Sources Folder. I am setup as SA and SU on both the RS Server and SQL Server. When I go check the catalog in SQL db it does truly exist as well as a number of data sources in the folder that reports are currently using. How can I see this folder?
View 7 Replies
View Related
Jun 30, 2015
When creating a new integration project, the data folder to create a new data source does not load.
View 5 Replies
View Related
