Search Results

Search found 60 results on 3 pages for 'bcp'.

Page 2/3 | < Previous Page | 1 2 3  | Next Page >

• How to export SQL Server data from corrupted database (with disk write error)

  - by damitamit

  IT realised there was a disk write error on our production SQL Server 2005 and hence was causing the backups to fail. By the time they had realised this the nightly backup was old, so were not able to just restore the backup on another server. The database is still running and being used constantly. However DBCC CheckDB fails. Also the SQL Server backup task fails, Copy Database fails, Export Data Wizard fails. However it seems all the data can be read from the tables (i.e using bcp etc) Another observation I have made is that the Transaction Log is nearly double the size of the Database. (Does that mean all the changes arent being written to the MDF?) What would be the best plan of attack to get the database to a state where backups are working and the data is safe? Take the database offline and use the MDF/LDF to somehow create the database on another sql server? Export the data from the database using bcp. Create the database (use the Generate Scripts function on the corrupt db to create the schema on the new db) on another sql server and use bcp again to import the data. Some other option that is the right course of action in this situation? The IT manager says the data is safe as if the server fails, the data can be restored from the mdf/ldf. I'm not sure so insisted that we start exporting the data each night as a failsafe (using bcp for example). IT are also having issues on the hardware side of things as supposedly the disk error in on a virtualized disk and can't be rebuilt like a normal raid array (or something like that). Please excuse my use of incorrect terminology and incorrect assumptions on how Sql Server operates. I'm the application developer and have been called to help (as it seems IT know less about SQL Server than I do). Many Thanks, Amit Results of DBBC CheckDB: Msg 1823, Level 16, State 2, Line 1 A database snapshot cannot be created because it failed to start. Msg 7928, Level 16, State 1, Line 1 The database snapshot for online checks could not be created. Either the reason is given in a previous error or one of the underlying volumes does not support sparse files or alternate streams. Attempting to get exclusive access to run checks offline. Msg 5030, Level 16, State 12, Line 1 The database could not be exclusively locked to perform the operation. Msg 7926, Level 16, State 1, Line 1 Check statement aborted. The database could not be checked as a database snapshot could not be created and the database or table could not be locked. See Books Online for details of when this behavior is expected and what workarounds exist. Also see previous errors for more details. Msg 823, Level 24, State 3, Line 1 The operating system returned error 1(error not found) to SQL Server during a write at offset 0x00000674706000 in file 'G:\AX40_Dynamics_Live.mdf'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.

  Read the article

• Back up a single table in SQL Server

  - by BuckWoody

  SQL Server doesn’t have an easy way to take a table backup, so I often use the bcp (Bulk Copy Program) to accomplish the same goal. I’ve mentioned this before, and someone told me when they tried it they couldn’t restore the table – ah the dangers of telling people half the information! I should have mentioned that you need to have a “format file” ready if the table does not exist at the destination. In my case I already had the table, in this person’s case they did not. The format file can be used to rebuild that table structure before the data is bcp’d in, and you can read more about it here: http://msdn.microsoft.com/en-us/library/ms191516.aspx There’s another way to back up a table, and that’s to create a Filegroup and place the table there. Then you can take a Filegroup backup to back up a single table. Of course, there are other methods of moving a single table’s data in an out, including SQL Server Integration Services and even the older Data Transformation Services, or simply by using hte SQLCMD or PowerShell utilities to run a query and just save the output to a file. In fact, these days I’m using a PowerShell script to build INSERT statements from that query. That could also easily be modified to create the table structure (or modify one if needed) quite easily. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• How to properly mix generics and inheritance to get the desired result?

  - by yamsha

  My question is not easy to explain using words, fortunately it's not too difficult to demonstrate. So, bear with me: public interface Command<R> { public R execute();//parameter R is the type of object that will be returned as the result of the execution of this command } public abstract class BasicCommand<R> { } public interface CommandProcessor<C extends Command<?>> { public <R> R process(C<R> command);//this is my question... it's illegal to do, but you understand the idea behind it, right? } //constrain BasicCommandProcessor to commands that subclass BasicCommand public class BasicCommandProcessor implements CommandProcessor<C extends BasicCommand<?>> { //here, only subclasses of BasicCommand should be allowed as arguments but these //BasicCommand object should be parameterized by R, like so: BasicCommand<R> //so the method signature should really be // public <R> R process(BasicCommand<R> command) //which would break the inheritance if the interface's method signature was instead: // public <R> R process(Command<R> command); //I really hope this fully illustrates my conundrum public <R> R process(C<R> command) { return command.execute(); } } public class CommandContext { public static void main(String... args) { BasicCommandProcessor bcp = new BasicCommandProcessor(); String textResult = bcp.execute(new BasicCommand<String>() { public String execute() { return "result"; } }); Long numericResult = bcp.execute(new BasicCommand<Long>() { public Long execute() { return 123L; } }); } } Basically, I want the generic "process" method to dictate the type of generic parameter of the Command object. The goal is to be able to restrict different implementations of CommandProcessor to certain classes that implement Command interface and at the same time to able to call the process method of any class that implements the CommandProcessor interface and have it return the object of type specified by the parametarized Command object. I'm not sure if my explanation is clear enough, so please let me know if further explanation is needed. I guess, the question is "Would this be possible to do, at all?" If the answer is "No" what would be the best work-around (I thought of a couple on my own, but I'd like some fresh ideas)

  Read the article

• Backup a Single Table in SQL Server using SSMS

  - by Greg Low

  Our buddy Buck Woody made an interesting post about a common question: "How do I back up a single table in SQL Server?" That got me thinking about what a backup of a table really is. BCP is often used to get the data but you want the schema as well. For reasonable-sized tables, the easiest way to do this now is to create a script using SQL Server Management Studio. To do this, you: 1. Right-click the database (note not the table) 2. Choose Tasks > Generate Scripts 3. In the Choose Objects pane,...(read more)

  Read the article

• Specify rowterminator when creating a file with SQLCMD

  - by MadMax

  How do you specify the the row terminator when outputting query results using sqlcmd? bcp is not an option. sqlcmd -E -s" " -Q "Select * From SomeTable" -o C:\Output.txt -W What is the default row terminator?

  Read the article

• Is there any boost-independent version of boost/tr1 shared_ptr

  - by Artyom

  I'm looking for independent implementation of boost/tr1 shared_ptr, weak_ptr and enable_shared_from_this. I need: Boost independent very small implementation of these features. I need support of only modern compilers like GCC-4.x, MSVC-2008, Intel not things like MSVC6 or gcc-3.3 I need it to be licensed under non-copyleft LGPL compatible license like Boost/Mit/3-clause BSD. So I can include it in my library. Note - it is quite hard to extract shared_ptr from boost, at least BCP gives about 324 files...

  Read the article

• Securing smtp with login

  - by Paul Peelen

  I have a ispconfig server, and it seems that someone is using it to send spam. I got about 130 "Mail Delivery System" email about declined send email. This spammer uses my email address as sent from adress, so I get all these email adresses to my mail. I am using Postfix and Courier. I installed my server according to this guide: http://www.howtoforge.com/perfect-server-debian-lenny-ispconfig3-p3 I did this a few months ago. My question: Can I secure my server to require login to be able to send email, and if so... how? Thanks! EDIT Some data from mail.log, these kind of error show up constantly: Jun 15 17:58:16 bolt postfix/qmgr[10712]: CC7DA1242AE: from=<paul@*****.se>, size=3782, nrcpt=1 (queue active) Jun 15 17:58:16 bolt postfix/smtp[11337]: CC7DA1242AE: to=<[email protected]>, relay=none, delay=4641, delays=4640/0.01/0.32/0, dsn=4.4.3, status=deferred (Host or domain name not found. Name service error for name=cmlisboa.pt type=MX: Host not found, try again) Jun 15 17:58:19 bolt postfix/smtpd[10836]: connect from static-200-105-220-154.acelerate.net[200.105.220.154] Jun 15 17:58:20 bolt postfix/smtpd[10836]: NOQUEUE: reject: RCPT from static-200-105-220-154.acelerate.net[200.105.220.154]: 550 5.1.1 <advertising@*****.com>: Recipient address rejected: User unknown in virtual mailbox table; from=<[email protected]> to=<advertising@*****.com> proto=ESMTP helo=<static-200-105-220-154.acelerate.net> Jun 15 17:58:20 bolt postfix/smtpd[10836]: lost connection after DATA (0 bytes) from static-200-105-220-154.acelerate.net[200.105.220.154] Jun 15 17:58:20 bolt postfix/smtpd[10836]: disconnect from static-200-105-220-154.acelerate.net[200.105.220.154] Jun 15 17:58:29 bolt postfix/smtpd[10834]: connect from unknown[62.176.172.226] Jun 15 17:58:32 bolt postfix/smtpd[10834]: 386791241F9: client=unknown[62.176.172.226] Jun 15 17:58:34 bolt postfix/cleanup[10975]: 386791241F9: message-id=<[email protected]> Jun 15 17:58:34 bolt postfix/qmgr[10712]: 386791241F9: from=<[email protected]>, size=867, nrcpt=1 (queue active) Jun 15 17:58:35 bolt postfix/smtpd[10834]: disconnect from unknown[62.176.172.226] Jun 15 17:58:35 bolt amavis[11084]: (11084-17) Blocked SPAM, [62.176.172.226] [62.176.172.226] <[email protected]> -> <*****@*****>, Message-ID: <[email protected]>, mail_id: XczovKoMBYNr, Hits: 18.471, size: 867, 833 ms Jun 15 17:58:35 bolt postfix/smtp[10732]: 386791241F9: to=<*****@*****>, relay=127.0.0.1[127.0.0.1]:10024, delay=3.5, delays=2.7/0/0/0.83, dsn=2.7.0, status=sent (250 2.7.0 Ok, discarded, id=11084-17 - SPAM) Jun 15 17:58:35 bolt postfix/qmgr[10712]: 386791241F9: removed Jun 15 17:58:43 bolt postfix/smtpd[10836]: warning: 178.121.154.194: address not listed for hostname mm-194-154-121-178.dynamic.pppoe.mgts.by Jun 15 17:58:43 bolt postfix/smtpd[10836]: connect from unknown[178.121.154.194] Jun 15 17:58:45 bolt postfix/smtpd[10727]: connect from unknown[180.134.223.86] EDIT #2 Got some more info from the logs, this is a send request: mail.info.1:Jun 15 16:41:57 bolt amavis[5399]: (05399-06) Passed CLEAN, [110.139.48.64] [110.139.48.64] <paul@*****.se> -> <[email protected]>, Message-ID: <CHILKAT-MID-7c54ebcf-5501-de9b-f0b1-4f0234290d8d@HP-IRISH>, mail_id: 35l56Ramx6Nc, Hits: -2.941, size: 3329, queued_as: 2485770086, 136 ms mail.info.1:Jun 15 16:41:57 bolt postfix/smtp[4743]: 375C570082: to=<[email protected]>, relay=127.0.0.1[127.0.0.1]:10024, delay=4.8, delays=4.7/0/0/0.14, dsn=2.0.0, status=sent (250 2.0.0 Ok, id=05399-06, from MTA([127.0.0.1]:10025): 250 2.0.0 Ok: queued as 2485770086) Which apparently got thrue. Any ideas how to restrict this?

  Read the article

• SQL SERVER – Extending SQL Azure with Azure worker role – Guest Post by Paras Doshi

  - by pinaldave

  This is guest post by Paras Doshi. Paras Doshi is a research Intern at SolidQ.com and a Microsoft student partner. He is currently working in the domain of SQL Azure. SQL Azure is nothing but a SQL server in the cloud. SQL Azure provides benefits such as on demand rapid provisioning, cost-effective scalability, high availability and reduced management overhead. To see an introduction on SQL Azure, check out the post by Pinal here In this article, we are going to discuss how to extend SQL Azure with the Azure worker role. In other words, we will attempt to write a custom code and host it in the Azure worker role; the aim is to add some features that are not available with SQL Azure currently or features that need to be customized for flexibility. This way we extend the SQL Azure capability by building some solutions that run on Azure as worker roles. To understand Azure worker role, think of it as a windows service in cloud. Azure worker role can perform background processes, and to handle processes such as synchronization and backup, it becomes our ideal tool. First, we will focus on writing a worker role code that synchronizes SQL Azure databases. Before we do so, let’s see some scenarios in which synchronization between SQL Azure databases is beneficial: scaling out access over multiple databases enables us to handle workload efficiently As of now, SQL Azure database can be hosted in one of any six datacenters. By synchronizing databases located in different data centers, one can extend the data by enabling access to geographically distributed data Let us see some scenarios in which SQL server to SQL Azure database synchronization is beneficial To backup SQL Azure database on local infrastructure Rather than investing in local infrastructure for increased workloads, such workloads could be handled by cloud Ability to extend data to different datacenters located across the world to enable efficient data access from remote locations Now, let us develop cloud-based app that synchronizes SQL Azure databases. For an Introduction to developing cloud based apps, click here Now, in this article, I aim to provide a bird’s eye view of how a code that synchronizes SQL Azure databases look like and then list resources that can help you develop the solution from scratch. Now, if you newly add a worker role to the cloud-based project, this is how the code will look like. (Note: I have added comments to the skeleton code to point out the modifications that will be required in the code to carry out the SQL Azure synchronization. Note the placement of Setup() and Sync() function.) Click here (http://parasdoshi1989.files.wordpress.com/2011/06/code-snippet-1-for-extending-sql-azure-with-azure-worker-role1.pdf ) Enabling SQL Azure databases synchronization through sync framework is a two-step process. In the first step, the database is provisioned and sync framework creates tracking tables, stored procedures, triggers, and tables to store metadata to enable synchronization. This is one time step. The code for the same is put in the setup() function which is called once when the worker role starts. Now, the second step is continuous (or on demand) synchronization of SQL Azure databases by propagating changes between databases. This is done on a continuous basis by calling the sync() function in the while loop. The code logic to synchronize changes between SQL Azure databases should be put in the sync() function. Discussing the coding part step by step is out of the scope of this article. Therefore, let me suggest you a resource, which is given here. Also, note that before you start developing the code, you will need to install SYNC framework 2.1 SDK (download here). Further, you will reference some libraries before you start coding. Details regarding the same are available in the article that I just pointed to. You will be charged for data transfers if the databases are not in the same datacenter. For pricing information, go here Currently, a tool named DATA SYNC, which is built on top of sync framework, is available in CTP that allows SQL Azure <-> SQL server and SQL Azure <-> SQL Azure synchronization (without writing single line of code); however, in some cases, the custom code shown in this blogpost provides flexibility that is not available with Data SYNC. For instance, filtering is not supported in the SQL Azure DATA SYNC CTP2; if you wish to have such a functionality now, then you have the option of developing a custom code using SYNC Framework. Now, this code can be easily extended to synchronize at some schedule. Let us say we want the databases to get synchronized every day at 10:00 pm. This is what the code will look like now: (http://parasdoshi1989.files.wordpress.com/2011/06/code-snippet-2-for-extending-sql-azure-with-azure-worker-role.pdf) Don’t you think that by writing such a code, we are imitating the functionality provided by the SQL server agent for a SQL server? Think about it. We are scheduling our administrative task by writing custom code – in other words, we have developed a “Light weight SQL server agent for SQL Azure!” Since the SQL server agent is not currently available in cloud, we have developed a solution that enables us to schedule tasks, and thus we have extended SQL Azure with the Azure worker role! Now if you wish to track jobs, you can do so by storing this data in SQL Azure (or Azure tables). The reason is that Windows Azure is a stateless platform, and we will need to store the state of the job ourselves and the choice that you have is SQL Azure or Azure tables. Note that this solution requires custom code and also it is not UI driven; however, for now, it can act as a temporary solution until SQL server agent is made available in the cloud. Moreover, this solution does not encompass functionalities that a SQL server agent provides, but it does open up an interesting avenue to schedule some of the tasks such as backup and synchronization of SQL Azure databases by writing some custom code in the Azure worker role. Now, let us see one more possibility – i.e., running BCP through a worker role in Azure-hosted services and then uploading the backup files either locally or on blobs. If you upload it locally, then consider the data transfer cost. If you upload it to blobs residing in the same datacenter, then no transfer cost applies but the cost on blob size applies. So, before choosing the option, you need to evaluate your preferences keeping the cost associated with each option in mind. In this article, I have shown that Azure worker role solution could be developed to synchronize SQL Azure databases. Moreover, a light-weight SQL server agent for SQL Azure can be developed. Also we discussed the possibility of running BCP through a worker role in Azure-hosted services for backing up our precious SQL Azure data. Thus, we can extend SQL Azure with the Azure worker role. But remember: you will be charged for running Azure worker roles. So at the end of the day, you need to ask – am I willing to build a custom code and pay money to achieve this functionality? I hope you found this blog post interesting. If you have any questions/feedback, you can comment below or you can mail me at Paras[at]student-partners[dot]com Reference: Pinal Dave (http://blog.SQLAuthority.com) Filed under: Pinal Dave, PostADay, SQL, SQL Authority, SQL Azure, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology

  Read the article

• The new SSIS in SQL2005/SQL2008 are oversized

  - by Ice

  I studied the new MERGE Statement and there is a nice example for importing a flatfile. INSERT <Table> SELECT * FROM OPENROWSET BULK <Import-Flat-File>, <Format-File>... seems to be a good replacment for such a simple job and avoids to build a SSIS-Package. EXEC XP_CMDSHELL bcp <Table or View> out <Flat-File> ... is almost simpler than building an SSIS, isn't it? (I know that the MERGE-Statement doesn't run on a SQL2005)

  Read the article

• MySQL – Export the Resultset to CSV file

  - by Pinal Dave

  In SQL Server, you can use BCP command to export the result set to a csv file. In MySQL too, You can export data from a table or result set as a csv file in many methods. Here are two methods. Method 1 : Make use of Work Bench If you are using Work Bench as a querying tool, you can make use of it’s Export option in the result window. Run the following code in Work Bench SELECT db_names FROM mysql_testing; The result will be shown in the result windows. There is an option called “File”. Click on it and it will prompt you a window to save the result set (Screen shot attached to show how file option can be used). Choose the directory and type out the name of the file. Method 2 : Make use of OUTFILE command You can do the export using a query with OUTFILE command as shown below SELECT db_names FROM mysql_testing INTO OUTFILE 'C:/testing.csv' FIELDS ENCLOSED BY '"' TERMINATED BY ';' ESCAPED BY '"' LINES TERMINATED BY '\r\n'; After the execution of the above code, you can find a file named testing.csv in C drive of the server. Reference: Pinal Dave (http://blog.sqlauthority.com)Filed under: MySQL, PostADay, SQL, SQL Authority, SQL Query, SQL Tips and Tricks, T SQL Tagged: CSV

  Read the article

• With Choice Comes Complexity

  - by BuckWoody

  "Complex" may be defined as "Having many steps, details or parts." Many of Microsoft's products, including SQL Server, can be complex. I'm stating what most data professionals already know - there's usually multiple ways to do things in SQL Server. For instance, to import some data into a table you can use graphical tools, SQLCMD, bcp, SQL Server Integration Services, BULK INSERT, even PowerShell, just to name a few tools at your disposal. That's really not the issue, though. The bigger issue is that there are normally multiple thought-processes, or methods, that you have available for a task. That's both a strength and a weakness. If things were more simple, you would have fewer choices. Sometimes that's a good thing. Just tell me what I need to do and I'll do it. However, your particular situation may not fit that tool or process, so having more options increases your ability to get your job done the way you need to do it. On the other hand, that's more for you to learn, which is harder. There's another side of this benefit/difficulty that you need to be aware of. Even if you're quite good at what you do, keep in mind that the way you know how to do something may not be the only way to do it. Keep your mind open to new possibilities, and most importantly - to new knowledge. SQL Server professionals teach me something new every day. So embrace the complexity - on balance, it's a good thing! Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• Data Movement and the Decision Matrix

  - by BuckWoody

  Maybe it’s my military background, or maybe I’ve always had this predilection, but I like to use two devices when I need to make a complex decision: A checklist and a decision matrix. I like to use a checklist because it ensures that I remember the big bits of what I need to do, and brings up questions or areas that I didn’t think about when evaluating options for the decision. And the decision matrix – that’s the thing I use to actually lay out those options. It’s simply a spreadsheet-like grid (I use Excel, but paper and pencil works as well) that lays out the requirements or advantages for the decision across the top, and the options I have on the left-hand side. Then in the “cells” I put whether or not that option on the left will meet the requirement in that column. I then simply “weight” each cell to organize the choices by best-fit. The right answer (or answers) will float right to the top. I was asked yesterday about options for moving data in SQL Server to another system. There are just dozens of ways to do this, from bcp to Replication, each with certain advantages and costs. But asking the questions for the top row first helped me show the person that it isn’t a particular technology that is important, it’s laying out those requirements and thinking about which elements are more important than the other. For instance, is it more important to have the data moved all the time, or is it OK if that happens once in a while? Does the data have to move in two directions or just one? All of these will help that answer jump right out. Try it sometime – it’s a great learning exercise, since it will force you to focus on filling out the matrix. The answer is out there, Neo. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• Automated backups for Windows Azure SQL Database

  - by Greg Low

  One of the questions that I've often been asked is about how you can backup databases in Windows Azure SQL Database. What we have had access to was the ability to export a database to a BACPAC. A BACPAC is basically just a zip file that contains a bunch of metadata along with a set of bcp files for each of the tables in the database. Each table in the database is exported one after the other, so this does not produce a transactionally-consistent backup at a specific point in time. To get a transactionally-consistent copy, you need a database that isn't in use.The easiest way to get a database that isn't in use is to use CREATE DATABASE AS COPY OF. This creates a new database as a transactionally-consistent copy of the database that you are copying. You can then use the export options to get a consistent BACPAC created.Previously, I've had to automate this process by myself. Given there was also no SQL Agent in Azure, I used a job in my on-premises SQL Server to do this, using a linked server configuration.Now there's a much simpler way. Windows Azure SQL Database now supports an automated export function. On the Configuration tab for the database, you need to enable the Automated Export function. You can configure how often the operation is performed for you, and which storage account will be used for the backups.It's important to consider the cost impacts of this as well. You are charged for how ever many databases are on your server on a given day. So if you enable a daily backup, you will double your database costs. Do not schedule the backups just before midnight UTC, as that could cause you to have three databases each day instead of one.This is a much needed addition to the capabilities. Scott Guthrie also posted about some other notable changes today, including a preview of a new premium offering for SQL Database. In addition to the Web and Business editions, there will now be a Premium edition that has reserved (rather than shared) resources. You can read about it all in Scott's post here: http://weblogs.asp.net/scottgu/archive/2013/07/23/windows-azure-july-updates-sql-database-traffic-manager-autoscale-virtual-machines.aspx

  Read the article

• How to export more than 1MB in XML format using sqlcmd and without an input file?

  - by jon

  Hello, In SQL Server 2008, I want to export the result of a stored procedure to a file using sqlcmd utility. Now the end of my stored procedure is a select statement with a "for xml path.." clause at the end. I read on BOL that if I don't want my output truncated when reaching 1MB file size, I have to use this :XML ON command, but it should be placed on its own line, before calling the stored procedure. Does any of you experts know if it is possible to do that without specifying an input file for sqlcmd? (I'm calling sqlcmd like this: exec master..xp_cmdshell 'sqlcmd -Q"exec storedProcedureName @param1=value1, @param2=value2" -o c:\exportResults.xml -h-1 -E', but "storedProcedureName" and its parameters can change, which would mean 1 input file per passed parameters to sqlcmd) Also, it seems that I can't use bcp instead of sqlcmd because my stored procedure is creating a temporary table and performing DML statements on it? Thanks a lot

  Read the article

• Reading correctly alphanumeric fields into R

  - by gd047

  A tab-delimited text file, which is actually an export (using bcp) of a database table, is of that form: 102 1 01 e113c 3224.96 12 102 1 01 e185 101127.25 12 102 2 01 e185 176417.90 12 102A 3 01 e185 26261.03 12 I tried to import it in R with a command like data <- read.delim("C:\\test.txt", header = FALSE, sep = "\t") The problem is that the 3rd column which is actually a varchar field (alphanumeric) is mistakenly read as integer (as there are no letters in the entire column) and the leading zeros disappeared. The same thing happened when I imported the data directly from the database, using odbcConnect. Again that column was read as integer. str(data) $ code: int 1 1 1 1 1 1 6 1 1 8 ... How can I import such a dataset in R correctly, so as to be able to safely populate that db table again, after doing some data manipulations?

  Read the article

• SSMS Results to Grid - CRLF not preserved in copy/paste - any better techniques?

  - by Cade Roux

  When I have a result set in the grid like: SELECT 'line 1 line 2 line 3' or SELECT 'line 1' + CHAR(13) + CHAR(10) + 'line 2' + CHAR(13) + CHAR(10) + 'line 3' With embedded CRLF, the display in the grid appears to replace them with spaces (I guess so that they will display all the data). The problem is that if I am code-generating a script, I cannot simply cut and paste this. I have to convert the code to open a cursor and print the relevant columns so that I can copy and paste them from the text results. Is there any simpler workaround to preserve the CRLF in a copy/paste operation from the results grid? The reason that the grid is helpful is that I am currently generating a number of scripts for the same object in different columns - a bcp out in one column, an xml format file in another, a table create script in another, etc...

  Read the article

• Exporting query results to a file on the fly

  - by ercan

  Hi all, I need to export the results of a query to a csv file in an FTP folder. Is it possible to achieve this within a stored procedure? If yes, comes yet another constraint: can I achieve this without sysadmin privileges, aka without using xp_cmdshell + BCP utility? If no to 2., does the caller have to have sysadmin privileges or would it suffice if the SP owner has sysadmin privileges? Here are some more details to the problem: The SP must export and transfer the file on the fly and raise error if something went wrong. The caller must get a response immediately, i.e. in case of no error, he can assume that the results are successfully transferred to the folder. Therefore, a DTS/SSIS job that runs every N minutes is not an option. I know the problem smells like I will have to do this at application level, but I would be more than happy if all those stuff could be done from T-SQL.

  Read the article

• Does anyone have a backup strategy for SQL Azure databases?

  - by Pete

  I'm using SQL Azure on a project and it works great. The problem is that the usual backup features do not exist. I have exported the database a couple of times using SQLAzureMW ( http://sqlazuremw.codeplex.com/ ) but this tool is now choking trying to download the database data with bcp. In any case, it's not as nice a solution as SQL Server backups. Is anyone aware of a commercial or open source tool, or other technique, for making reliable backups of SQL Azure databases? This is really a showstopper.

  Read the article

• sql server bulk copy out/postgres copy from infile

  - by Chris Curvey

  I'm starting a conversion of a system from MS SQL Server to Postgres. I have the table structures converted, and I use "bcp" to get the data out of SQL Server. ERROR: invalid byte sequence for encoding "UTF8": 0x80 HINT: This error can also happen if the byte sequence does not match the encoding expected by the server, which is controlled by "client_encoding". CONTEXT: COPY cm_outgoing, line 200: "200 c:\temp\200.xml 2009-10-10 01:50:44.000 1900-01-01 00:00:00.000" I've already used "sed" to get rid of the NUL (0x00) entries in the file, and I can't find any instances of 0x80 in the file that I'm trying to import. Any thoughts? Is there an easier way?

  Read the article

• How do I push my initial snapshot to a subscriber server in SQL Server 2000?

  - by Kev

  I'm configuring Transactional Replication using the Push model. The scenario is: The SQL Servers: SQL01 (publisher) and SQL02 (subscriber) - both running SQL 2000 SP4. Both servers are standalone (i.e. not domain members) Both servers have their FQDN and NETBIOS names in their HOSTS files I've managed to configure SQL01 to publish my database and configured a Push subscription for SQL02 using the Push New Subscription wizard and set the Distribution Agent to update the subscription continuously. On the Push Subscription wizard "Initialise Subscription" page I've selected "Yes, initialise the schema and data" and ticked the "Start the Snapshot Agent to begin the initialisation process immediately" option. All the required services are running (SQL Agent). When I complete the wizard and browse the Replication - Publications folder I can see my publication (blue book with arrow). The publication shows the Push subscription and its status is Pending. If I look in the c:\Program Files\Microsoft SQL Server\Mssql\Repldata folder I see a number of T-SQL scripts for each table e.g. Products.bcp, Products.sch, Products.idx. What should happen now? Should my replicated database now (magically) appear on the subscription server?

  Read the article

• Cloud Backup: Getting the Users' Backs Up

  - by Tony Davis

  On Wednesday last week, Microsoft announced that as of July 1, all data transfers into its Microsoft Azure cloud will be free (though you have to pay for transferring data out). On Thursday last week, SQL Azure in Western Europe went down. It was a relatively short outage, but since SQL Azure currently provides no easy way to take a standard backup of a database and store it locally, many people had no recourse but to wait patiently for their cloud-based app to resume. It seems that Microsoft are very keen encourage developers to move their data onto their cloud, but are developers ready to do it, given that such basic backup capabilities are lacking? Recently on Simple-Talk, Mike Mooney described a perfect use case for the Microsoft Cloud. They had a simple web-based application with a SQL Server backend; they could move the application to Windows Azure, and the data into SQL Azure and in the process free themselves from much of the hassle surrounding management and scaling of the hardware, network and so on. It was a great fit and yet it nearly didn't happen; lack of support for the BACKUP command almost proved a show-stopper. Of course, backups of Azure databases are always and have always been taken automatically, for disaster recovery purposes, but these are strictly on-cloud copies and as of now it is not possible to use them to them to restore a database to a particular point in time. It seems that none of those clever Microsoft people managed to predict the need to perform basic backups of Azure databases so that copies could be stored locally, outside the Azure universe. At the very least, as Mike points out, performing a local backup before a new deployment is more or less mandatory. Microsoft did at least note the sound of gnashing teeth and, as a stop-gap measure, offered SQL Azure Database Copy which basically allows you to create an online clone of your database, but this doesn't allow for storing local archives of the data. To that end MS has provided SQL Azure Import/Export, to package up and export a database and its data, using BACPACs. These BACPACs do not guarantee transactional consistency; for example, if a child table is modified after the parent is copied, then the copied database will be in inconsistent state (meaning, to add to the fun, BACPACs need to be created from a database copy). In any event, widespread problems with BACPAC's evil cousin, the DACPAC have been well-documented, and it seems likely that many will also give BACPAC the bum's rush. Finally, in a TechEd 2011 presentation tagged "SQL Azure Advanced Administration", it was announced that "backup and restore" were coming in the next SQL Azure CTP. And yet this still doesn't mean that we'll get simple backups as DBAs know and love them. What it does mean, at least, is the ability to restore any given database to a point in time within a 2-week window. For the time being, if you want a local copy of your data and don't want to brave the BACPAC, one is left with SSIS or BCP, creative use of schema and data comparison tools, or use of SQL Azure Backup (currently in beta) in order to perform this simple but vital task. Cheers, Tony.

  Read the article

• PowerShell: Read Excel to Create Inserts

  - by BuckWoody

  I’m writing a series of articles on how to migrate “departmental” data into SQL Server. I also hold workshops on the entire process – from discovering that the data exists to the modeling process and then how to design the Extract, Transform and Load (ETL) process. Finally I write about (and teach) a few methods on actually moving the data. One of those options is to use PowerShell. There are a lot of ways even with that choice, but the one I show is to read two columns from the spreadsheet and output statements that would insert the data using a stored procedure. Of course, you could re-write this as INSERT statements, out to a text file for bcp, or even use a database connection in the script to move the data directly from Excel into SQL Server. This snippet won’t run on your system, of course – it assumes a Microsoft Office Excel 2007 spreadsheet located at c:\temp called VendorList.xlsx. It looks for a tab in that spreadsheet called Vendors. The statement that does the writing just uses one column: Vendor Code. Here’s the breakdown of what I’m doing: In the first block, I connect to Microsoft Office Excel. That connection string is specific to Excel 2007, so if you need a different version you’ll need to look that up. In the second block I set up a selection from the entire spreadsheet based on that tab. Note that if you’re only after certain data you shouldn’t get the whole spreadsheet – that’s just good practice. In the next block I create the text I want, inserting the Vendor Code field as I go. Finally I close the connection. Enjoy! $ExcelConnection= New-Object -com "ADODB.Connection" $ExcelFile="c:\temp\VendorList.xlsx" $ExcelConnection.Open("Provider=Microsoft.ACE.OLEDB.12.0;` Data Source=$ExcelFile;Extended Properties=Excel 12.0;") $strQuery="Select * from [Vendors$]" $ExcelRecordSet=$ExcelConnection.Execute($strQuery) do { Write-Host "EXEC sp_InsertVendors '" $ExcelRecordSet.Fields.Item("Vendor Code").Value "'" $ExcelRecordSet.MoveNext()} Until ($ExcelRecordSet.EOF) $ExcelConnection.Close() Script Disclaimer, for people who need to be told this sort of thing: Never trust any script, including those that you find here, until you understand exactly what it does and how it will act on your systems. Always check the script on a test system or Virtual Machine, not a production system. All scripts on this site are performed by a professional stunt driver on a closed course. Your mileage may vary. Void where prohibited. Offer good for a limited time only. Keep out of reach of small children. Do not operate heavy machinery while using this script. If you experience blurry vision, indigestion or diarrhea during the operation of this script, see a physician immediately. Share this post: email it! | bookmark it! | digg it! | reddit! | kick it! | live it!

  Read the article

• Oracle Enterprise Computing Summit??!??????/EM????????

  - by Oracle Japan Marketing

  .NewsType1107 img{border:none; vertical-align:bottom;} .NewsType1107 p{margin:0; padding:0;} .NewsType1107 td{color:#333333; line-height:1.5; font-family:"MS P????", Osaka, Hiragino Kaku Gothic Pro; font-size:12px;} .NewsType1107 table.t10 td, .small{font-size:10px;} .NewsType1107 a:link, a:visited{color:#ff0000;} .NewsType1107 a:hover, a:active{color:#ff0000; text-decoration:none;} .NewsType1107 a.l01:link, a.l01:visited, a.l01:hover, a.l01:active{color:#333333;} .NewsType1107 span.r, td.r{color:#ff0000;} .NewsType1107 table.tbl-semi td{padding:5px;} ?????BCP????????????????????????! ??????????????????????????????Oracle Enterprise Computing? ??????????????????????????????????Oracle Enterprise Computing?????????????????????? ??·?????????? >> ????????????????IT???????????????????? ???????????????????????ID??·??????????·?????3??????????????????????????????? ????????? >> ??????????????????????????????????????????????????????? Oracle EPM & BI Summit???????·?????????????????????????????????????·???????????????????????? ??·?????????? >> ???????????????????????????? ???5??????????????????????????????????????????????????????????????????????????????????? ??·?????????? >> -- ?????????????????????? ????????????????????????! ???????????????????????????????????????? ????????? >> ?????????????!??????????????? ? Sun????&?????·?????????????????????IT????????? ? ???????????·???????????????????IT???????????? ????????????? ? ?????????·????????????????????BI?? more solutions ? LIXIL ?????ERP?????????????????????????????????????????????? ? ?????????? Oracle EBS???·?????????????????????????????????????????? ? ????? Oracle EBS???/??????????????????????????????????????????????????????? more success stories IT?????????????????????????????????????·???·?????? >> ???????????????????????? ?? ???? ?? 7/6(?)10:30~18:00 ?????????? 2011 ?????????(??) 7/7(?)14:00~19:00 Java SE 7 ?????? ?????? ??????????(??) 7/13(?)13:30~16:45 ?????????????????????????? ??????????(??) 7/15(?)13:00~18:00 ?????!??????????????????????? ??????????(??) 7/20(?)9:30~17:50 ????·????????&????????2011 ?????????????(??) 7/20(?)13:30~17:00 ???????????????????????????? ??????????(??) 7/25(?)14:00~17:00 MySQL?????????????? ??????????(??) 7/26(?)13:30~17:45 Oracle Enterprise Computing Summit ???????????(??) Copyright © 2011, Oracle.All Rights Reserved. ???????????? | ???????????? | ??????????/????????

  Read the article

• ????????????????? Oracle Solaris ??? - Solaris 11 ????

  - by kazun

  ???????????????? OS ????????????????????? 20 ??????????????????? Solaris?????????????????????? OS????????????????????????Solaris ???????????????????? ??????Solaris ?????????????? 4 ????????????????????? Oracle Solaris 11 ??????????????? [????????????]???? ?(????????????? ???)???? ?(??????????????)???? ?(????????)???? ?(?????????????????)(50??) Oracle Solaris 11 ??????? ???:Oracle Solaris 11??????? Solaris ??????????????????????????????????????????????Oracle Solaris 10 ?????????????????Oracle Solaris 11 ???Oracle Solaris ???????????????????????????????????????????????????????????????????????????????????Oracle Solaris ??????????????????????????Oracle Solaris ????????????????????????????????????????? ??:??????????????????????????????????????????????????????????????????????????????????Oracle Solaris 11 ????????????????????????????????????????????????????????????????????????????????????????????????Oracle Solaris 11 ????????????? ???:Oracle Solaris 11 ????????????????????????????????????????????????????????????Oracle Solaris ??????????????????????Solaris ????????????????????????Oracle Solaris 11 ?????????????????????????????????????????????????????????????????????? Oracle Solaris 11 ??????????????? ??:???????????????????ZFS?????????????Oracle Solaris ???????????????????????????????????????IPS???????????????????????? ??:?????????????????????? OS ??????????????????????????????????????????????????????????????????? OS ????? Oracle Solaris 11 ??? ??:????????????????????????????????????????????????? Oracle Solaris 11 ???????????????????????????????????????????????????????? ??:????????????????????Solaris Trusted Extensions ? Solaris ????????????Privilege ????????????????????????????????Solaris 10 ???????????????????? ??:UNIX ?????????????????????????????????????Oracle Solaris 11 ??????????????????????????????????????????????????ZFS ??????????????????????????????????????????????????Oracle Solaris ?????????????????????????????????? ???:Oracle Solaris ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????Oracle Solaris 11 ??????????????????????????????????????????????????? ?????????????Oracle Solaris 11 ??:Oracle Solaris ?????????????????????????????????????????????????????????????????? ZFS ?????????????????????????????????????????????? ??:?????????????????????????????????????????????????????????????????????Oracle Solaris ??????????????????????? ??:DTrace ????????????????????????????????????????????????????????????? ??:???????????????????????????????????????????????????????????????????????????????????????? ???:?????????????????????????????????????????????????????????????????????????Solaris ???????????????????????????????????????????????????????????????????????????? ??:?????????????????? Solaris 10 ?????????????????????????????????????? ???:ISV ??????????????????????????????? ????????????????? T4 ??????????????? T ?????? 5 ???????????????????????????????????? CPU ?????????????????? OS ? Solaris ??????????????????????????????????????????????? ??:???????????????? OS ???? Solaris ???????????????????????????????????????????????????? Oracle Solaris ????????? ??:???????????????????????????????????????????????????????????????????????????????????????? ZFS ?????????????????????????ZFS ??????????????????????????????????????? ???:Linux ??????????????????????????Solaris 11 ?????????????????????? GNU ????????????????????????????Solaris ?????????(Oracle Solaris Studio)????????????? Oracle Solaris Studio ?Linux??????????????Linux ??????????? Solaris ????????????????Linux ? Oracle Solaris Studio ?????????????????????????? Solaris ??????????????????? ??:??????????????????????????????????????????????????????????????????????????????????????????????????????????????????BCP(??????)??????????????????? ??:???????? Solaris 11 ?????? IA Server ?????????????????????????????????????????????…???????????Windows ????????????????????? Oracle Virtual Box ????????? Oracle Solaris 11 ??????????? ??:???Oracle Solaris ????????(?)??????????????ZFS???DTrace?????????????????????????????????????Solaris ?????????????????????????????????????????????????????????????????????????????Solaris ????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????????? ??:??? 3 ??????????????????????????????????????Oracle Solaris 11 ???????????? ??:???????????????????? OS ??????????????????????????Oracle Solaris 11 ???????????????????????????????????????????????????????????????? ???:???????????? Solaris ???????????????Solaris 10 ???? 6 ??????????????????????????????????Oracle Solaris ?????????????????????????????? ?????? Oracle Solaris 10 Oracle Solaris 11

  Read the article

• SqlBulkCopy causes Deadlock on SQL Server 2000.

  - by megatoast

  I have a customized data import executable in .NET 3.5 which the SqlBulkCopy to basically do faster inserts on large amounts of data. The app basically takes an input file, massages the data and bulk uploads it into a SQL Server 2000. It was written by a consultant who was building it with a SQL 2008 database environment. Would that env difference be causing this? SQL 2000 does have the bcp utility which is what BulkCopy is based on. So, When we ran this, it triggered a Deadlock error. Error details: Transaction (Process ID 58) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction. I've tried numerous ways to try to resolve it. like temporarily setting the connection string variable MultipleActiveResultSets=true, which wasn't ideal, but it still gives a Deadlock error. I also made sure it wasn't a connection time out problem. here's the function. Any advice? /// <summary> /// Bulks the insert. /// </summary> public void BulkInsert(string destinationTableName, DataTable dataTable) { SqlBulkCopy bulkCopy; if (this.Transaction != null) { bulkCopy = new SqlBulkCopy ( this.Connection, SqlBulkCopyOptions.TableLock, this.Transaction ); } else { bulkCopy = new SqlBulkCopy ( this.Connection.ConnectionString, SqlBulkCopyOptions.TableLock | SqlBulkCopyOptions.UseInternalTransaction ); } bulkCopy.ColumnMappings.Add("FeeScheduleID", "FeeScheduleID"); bulkCopy.ColumnMappings.Add("ProcedureID", "ProcedureID"); bulkCopy.ColumnMappings.Add("AltCode", "AltCode"); bulkCopy.ColumnMappings.Add("AltDescription", "AltDescription"); bulkCopy.ColumnMappings.Add("Fee", "Fee"); bulkCopy.ColumnMappings.Add("Discount", "Discount"); bulkCopy.ColumnMappings.Add("Comment", "Comment"); bulkCopy.ColumnMappings.Add("Description", "Description"); bulkCopy.BatchSize = dataTable.Rows.Count; bulkCopy.DestinationTableName = destinationTableName; bulkCopy.WriteToServer(dataTable); bulkCopy = null; }

  Read the article

< Previous Page | 1 2 3  | Next Page >