SQL 2012 :: BCP Extract Files Error

Feb 24, 2015

I run this code on several environments successfully but when on run it on a different environment it fails.

Its very strange as the user has database SA and OS administrator permissions.

Code...

declare @Command varchar (250)
set @Command = 'bcp "SELECT * FROM QAProcess.dbo.ReadMe_Table" queryout "THOMSONS-SQL01TempReadMe.txt" -c -T'
EXEC master..xp_cmdshell @Command

Error....

SQLState = 28000, NativeError = 18456
Error = [Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user 'T-BXSQLServerAccount'.
NULL

View 2 Replies


ADVERTISEMENT

Extract Data From Two Excel Files

Apr 28, 2008



Hi,
I have just started learning SSIS. Could someone please tell me if where can I find step by step instructions on how to simply extract data from two excel files and populate the relevant table. What I simply want to do is:


Excel File 1 (With Columns FirstName, DateJoined)
+
Excel File 2 (with column Summary)
|
|
->Add these three columns to a new table called CustSummary


Any thoughts and suggestions will be really appreciated.

Thanks

View 1 Replies View Related

How To Extract The Data From MSSQL05 DB To XML Files?

Apr 10, 2008

Dear all,
I have a table containing some information that needed to generate a XML file periodically.
Do anyone has idea how to achieve it in SSIS?
Thanks a lot!

lokman LM

View 6 Replies View Related

SQL 2012 :: SSIS FTP Task Error Handling - Files Not Found

Nov 2, 2015

When my ForEach Loop runs, when a file does not exist on the server, I am getting a File does not exist error.I would prefer to write a mesaage to my log and then move on to the next step successfully.When I got to Event Handlers and select OnTaskFailed, what do I want to do from here?

View 0 Replies View Related

T-SQL (SS2K8) :: How To Extract Strings Saved In Column Into HTML Files

Oct 12, 2015

I am working in an environment where i inherited bad design. I have a column in a table. This col contains huge HTML files. We are mostly reading these files and very little updates.

I am changing this whole architecture and going to using Azure Blob storage. I am stuck right now. I need to extract these html strings and save them into separate files in year/month/day/filename.html format. I have another column in the table which has create date saved in it.

I am planning to import all these files into BLOB storage and save link in the table.

1) How can i extract these strings from the table and save them in the year/month/day/filename.html directory/sub-directory format

2) How to import these files into BLOB storage.

View 1 Replies View Related

Writing A DTXS Package To Extract 50 Tables To 50 Text Files?

Nov 26, 2007

Is there a quick way to extract a full dump of 50 tables to 50 corresponding text files?


i.e.

table_a has to be extracted to table_a.txt

table_b has to be extracted to table_b.txt

table_c has to be extracted to table_c.txt
etc.

I don't want to have to add each one separately by hand in the DTSX package designer.
I can't see any way to do it in a loop (because you have to do the field mapping).
I can't seem to get the DTS Wizard to help - it only seems to be able to handle one table-to-text extract at any one time.
And I've tried editing the DTXS file directly (in XML) but it looks like it's going to be rather complex, even if I only do it to define the connection managers.
Feel free to suggest any better way to do this, though the specification has already been agreed, so I'm unlikely to be able to change it.
Thanks

View 3 Replies View Related

SQL Server 2012 :: Extract Old Record Value

Jun 3, 2014

I have a 2table like

create table test1
(col1 identity(1,1),
col2 varchar(20)
)

create table test2
(old int,
new int)

Existing record in test1 table
(1,'test')

Now I want to insert a new record into test1 and then capture the old and new identity columsn in test 2 table

i.e. after insert test1 table will be
1,test
2,test

and test2 table will be like
1,2 -- old and new identity columns are captured during insert and stored in test2 table.

If the repeat the process again test2 table will be like
1, test
2,test
3, test

and test2 table will be
2,3 --- test2 table will always have only one record. old records will be deleted.

Using output clause i can get the new identity value.. but how can i get the old identity value and insert that into test2 table..

I am not willing to do a self join after the record is inserted into test2. I wanted to get the old ident value during insert of new ident value.

View 2 Replies View Related

SQL 2012 :: Extract Data From Given Formula

Jun 30, 2015

I need to extract the information from the following Formula:

prj(10517)(a*w/100)+prj(10742)(a*w/100)+prj(10804)(a*w/100)+prj(10808)(a*w/100)+prj(10809)(a*w/100)+prj(10810)(a*w/100)

What would be the simplest and fastest way to do this...

I did write a fetch statement but i want to do something simpler as it is a bit hectic...

Basically i require the information from prj(10517) etc.

View 2 Replies View Related

SQL 2012 :: FOR FILES Command To Delete Old Backup Files On Remote Server?

Feb 24, 2015

I have the need to delete old backup files via TSQL job. Found this solution online:

PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD

It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?

View 6 Replies View Related

SQL Server 2012 :: Extract Text From Numbers

Oct 20, 2014

I have a field which contains something like prj(5616) .

I have been assigned to display the actual name and not the text with the number.

Example: if prj(8616) is called Soccer , then I want display Soccer instead of prj(8616).

View 9 Replies View Related

SQL Server 2012 :: Extract Ispac File From SSISDB?

Jul 14, 2014

I need to extract the .ispac file from the SSISDB. I can retrieve the stream with catalog.get_project sp. However, the file I end up with cannot be unzipped, giving an error message. My guess is that it is meta-data on the zip/ispac file that has a problem, because I can actually unzip it with Winrar, but not with any of those I (programmatically) need to unzip it with.

Below is the code for my stored procedure - My own suspicion is in the BCP usage to turn the stream into a file.

USE [SSISDB]
GO
ALTER PROCEDURE [dbo].[spGetIspacFile]
@project VARCHAR(255) ,
@environmentFolder VARCHAR(50) ,
@ispacTempFolder VARCHAR(100) ,
@ispacFilePath VARCHAR(200) OUTPUT

[code]....

View 5 Replies View Related

SQL Server 2012 :: Query If Extract Failed And / Or Succeed The Same Day

Nov 19, 2014

We extract 10k tables every night and I have a table that keeps track of ETL tables that fail or succeed. I would like to know if a table fails during the night and nobody kicks off another job to fix it during the day.

The table structure looks like this:

| Table_Name | Time_Start | Status | Duration | Time_End |

Table_Name = varchar(20)
Time_Start = DateTime
Status varchar(7) = Success or Error
Duration = Number
Time_End = DateTime

Select Table_Name into #MyTempTable
From ETL.STATS_Table
Where Status = 'Error'
AND Cast(Time_Start as Date) = GetDate()

How do I take the table names from #MyTempTable and find out if they where successful for the same date? Duration time and Time_End fields aren't needed.

View 5 Replies View Related

SQL Server 2012 :: How To Extract One More Piece Of Data From The Column

Dec 17, 2014

I have a database table that was designed by someone else, and I'm trying to see if I can normalize the pattern. Here's the dummy table:

CREATE TABLE [dbo].[BadTox](
[PatientID] [int] NULL,
[Cycle] [tinyint] NULL,
[ALOPECIA] [tinyint] NULL,
[Causality1] [tinyint] NULL,

[Code] ....

All the column names in upper case are actually symptom names, and in those columns are values {NULL, 1, 2, 3, 4, 5} and they belong in a column, so the normalized structure should be like this:

CREATE TABLE Symptom (
PatientID INT NOT NULL,
Cycle TINYINT NOT NULL,
SymptomName VARCHAR(20) NOT NULL, -- from the source column *name*
Grade TINYINT NOT NULL -- from the value in the column with the name in uppercase
PRIMARY KEY (PatientID, Cycle, SymptomName));

I can untwist the repeating groups with the code I borrowed from Kenneth Fisher's article [ here ], but the part I'm having a harder time with is grabbing the information that's still left in the column name and integrating it into the solution...

I can retrieve all the column names that are in uppercase using this:

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'SCRIDB');
SET @object_id = OBJECT_ID(N'SCRIDB.dbo.BadTox');
SELECT name AS column_name
, column_id AS col_order
FROM sys.all_columns
WHERE name = UPPER(name) COLLATE SQL_Latin1_General_CP1_CS_AS
AND object_id = @object_id;

but I can't figure out how to work it into this (that I built by mimicking Kenneth Fisher's article...):

ALTER PROC [dbo].[UnpivotMaxGradeUsingCrossApply]
AS
SELECT PatientID
, Toxicity
, MAX(Grade) AS MaxGrade

[code]....

The problem is that I need to extract the column names (where ToxicityName[n] would be). I can do that by querying the sys.all_columns view, but I can't figure out how to integrate the two pieces. About the only thing I have even dreamed up is to build the VALUES(...) statements dynamically from the values returned by the system view.

So how do I get both the value from the ToxicityName[n] column and the column name into my final data query?

View 5 Replies View Related

SQL Server 2012 :: Extract Locking Information In Database

Jul 16, 2015

I am using following sql to extract locking information in database. It only work on current selected database, how can I tune to work on all databases and not only currently selected?

SELECT DISTINCT
ES.login_name AS LoginName,
L.request_session_id AS BlockedBy_SPID,
DATEDIFF(second,At.Transaction_begin_time, GETDATE()) AS Duration_Sec,
DB_NAME(L.resource_database_id) AS DatabaseName,

[Code] ....

View 3 Replies View Related

SQL Server 2012 :: Derived Table To Extract Value Of Account For Comparison

Nov 2, 2014

In my TSQL code i use a derived table to extract the value of account 321 to compare if they are the same that the SUM of my line invoice cost multiply by quantity line : Sum(fi.ecusto*qtt)

This is my script:

SELECT ft.ndoc [Doctype],ft.fno [Docnr] , Sum(fi.ecusto*qtt) [totalcostof my Invoiceline], xctb.conta [accountancy account],
sum(Case when ft.tipodoc = 1 then Xctb.ecre else Xctb.edeb end) as [Value of Cost of invoice in accountancy],
[DIF] = Sum(fi.ecusto*qtt) - Sum(Case when ft.tipodoc = 1 then xctb.ecre else xctb.edeb end)

[Code] ....

My problem is if i have more than on line on my invoice, for example 2 lines, the value of column [Value of Cost of invoice in accountancy] are duplicated, for 3 line invoice the value are multiply by 3.

View 7 Replies View Related

When I Use SSIS For Extract From OLAP Database, Then The Error Random Occurred,Error Code = 0x80040E05

May 10, 2007



I have tired for this!

When I use SSIS for extract data from ssas, that means,I use mdx query.

then random error occured.



Hope some one can understand my poor English....



And the Error Info show below.






Code Snippet

Error: 0xC0202009 at Data Flow Task - For Individual User Tech Points, OLE DB Source 1 1 [31]: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040E05.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for Analysis Services 2005" Hresult: 0x00000001 Description: "Error Code = 0x80040E05, External Code = 0x00000000:.".
Error: 0xC004701A at Data Flow Task - For Individual User Tech Points, DTS.Pipeline: component "OLE DB Source 1 1" (31) failed the pre-execute phase and returned error code 0xC0202009.

View 6 Replies View Related

SQL Server 2012 :: Patindex Function - Cannot Retrieve Or Extract Single Numeric Value

Jul 17, 2015

I would like solving the following issue using the Patindex function i cannot retrieve or extract the single numeric value as an example in the the values below i would like retrieve the Value 2, but in my result set the value 22 also appears or it is completely omitted.

"2 8 7"
"2 8"
"2"
"22"
"3 2 8"

I have tried the following

Patindex('%[2]% [^0-9] [^1] [^3] [^4] [^5] [^6] [^7] [^8] [^9]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col3,

Patindex('[2]' ,Replace(Replace(Marketing_Special_Attributes, '"',''),'^',' ')) as Col4,

Patindex('%[2][^0-9]%',Replace(Marketing_Special_Attributes,'^',' ')),

View 5 Replies View Related

SQL 2012 :: Extract All Tables Names And Their Row Counts From Linked Server Tables

Oct 7, 2015

I am using the following select statement to get the row count from SQL linked server table.

SELECT Count(*) FROM OPENQUERY (CMSPROD, 'Select * From MHDLIB.MHSERV0P')

MHDLIB is the library name in IBM DB2 database. The above query gives me only the row count of table MHSERV0P. However, I need to get the names, rowcounts, and sizes of all tables that exist in MHDLIB librray. Is it possible at all?

View 1 Replies View Related

Extract Column Data From Flat File Error Output

Dec 10, 2007

Hi Friend,


I am stuck with a problem and need your help. As we know, all columns that go to error flow of flat file source connection are displayed as a single column e.g. FlatFileSourceErrorOutputColumn, but my requirement is to extract the first column value from this FlatFileSourceErrorOutputColumn, my data is dilimeted by "|" pipe operator. I have created a script component to deal with this. However if we take FlatFileSourceErrorOutputColumn column as input column in script component, it comes as BLOB data. I wrote below code in transformation script component to extract BLOB data from column in string form and then do a Left function search to take first column out.

When I am running this script component I am getting '??????????' question marks as a result in Row.Pname.

Can anyone please help me understand if I am doing anything wrong in this script or suggest a better way to take the data out?

I appreciate your help.

Public Overrides Sub Input0_ProcessInputRow(ByVal Row As Input0Buffer)

'

'

Dim Error_Data As String

Dim Column_1 As String

Dim Len As Integer

Dim Buffer As Byte()


Buffer = Row.FlatFileSourceErrorOutputColumn.GetBlobData(0, CInt(Row.FlatFileSourceErrorOutputColumn.Length))




Error_Data = System.Text.Encoding.Unicode.GetString(Buffer)


Len = Error_Data.IndexOf("|")


Column_1 = Left(Error_Data, Len - 1)



Row.Pname = Column_1

End Sub


Thanks,
Kul

View 5 Replies View Related

SQL Server 2005 Installation Problem - Error Not Enough Space To Extract This Package

Mar 29, 2007

Hi


I downloaded the SQL server 2005 trival version (180 days trival
peroid) from the MS website. But i got a error saying that I have
don't have enough space to extract the package on my C: drive when I
do the installation. Actually I have 13g space on my c drive, so I
may be miss something? I looked up the internet and found a solution
using SET TEMP=C:TEMP and SET TMP=C:TEMP, but it doesn't work for me.




Does anyone have a better idear? I had a SQL 2005 express edition
installed on my laptop before, but I have remove it, maybe I did not
remove it completely and cause this problem?




Thanks in advance




Li

View 1 Replies View Related

SQL 2012 :: Zero Out Backup Files?

Jan 28, 2015

Looking at the documentation, it would suggest that as well as data files, when a backup file's created it will also be zeroed out unless the service a/c's been given Perform Volume Maintenance.

We take our backups to dedicated backup servers, meaning backup performance should improve significantly if instant file initialization's given to the Service account logins for the source boxes if I'm right.

View 4 Replies View Related

SQL 2012 :: Moving Log Files In HA Groups

Feb 11, 2014

Trying to find out if this is the best way to move log files in databases that are in an availability group.

remove the DB from the AG
Run alter database commands like you would normally to take offline ,move file,bring online ,etc
drop the db from secondary node
then rejoin the DB to the AG

Is that the only option for moving them when its in an avail group? cant find any other info on moving files in mirrors or HA groups

View 2 Replies View Related

SQL 2012 :: Fusion-IO Drive And MDB Files

Nov 5, 2014

We have a cluster with two nodes and two instances of SQL Server 2012 Standard Edition running on them. Volume W: is a Fusion-IO card.

On one of these nodes a lot of database names are showing up in the resource monitor as *.mdf files (W:0MSSQL1…).

How and why SQL Server is using these files? They only show up on one of the nodes having more load.

Volume I: is the volume where the transaction log is written so we can explain these files.

View 3 Replies View Related

SQL 2012 :: TempDB Data And Log Files On One SSD?

Mar 3, 2015

I'm having an argument with our infrastructure architect who has just gone and bought lots of SSD drives to use for our tempdb data and log files, sounds great doesn't it? There is a catch though, his plan is to add the disks to the two available slots in each blade in a RAID0+1 configuration, effectively giving you one usable drive, and adding both data and log files on to one disk.

I then pointed out that SQL Server best practice is to host tempdb data and log files on two separate drive to reduce contention. The architect then basically said that because this isn't spinning disk the issue of drive, r/w contention isn't an issue I don't agree with this and wanted to get some opinions from the community, I'm still advising that two separate disks should be used but someone just went and spent £80k ($150k) on SSDs and doesn't want to back down...

View 4 Replies View Related

SQL 2012 :: Converting XML Files To PDF And Zip In Folder

Jun 16, 2015

Convert 100 xml files individually to pdf's and zip them in a folder along with the source files.

Can it be possible in SQL server BI world?

If possible make this an automated process for every 100 files.

View 3 Replies View Related

SQL 2012 :: Database Data Files Out Of Sync

Jul 21, 2014

I've stepped into a new environment and have never dealt with multiple data files on user databases only with Temp db.What would be the best way to get all my data files in sync. I have done this on databases that aren't that big in size or off in size by a lot. Here is what I have

Mdf -- 69 GB
ndf -- 3
ndf -- 3
ndf -- 3
ndf -- 3
ndf -- 4
ndf --4
ndf -- 2

View 7 Replies View Related

SQL 2012 :: Copy Backup Files From One Server To Another?

Sep 23, 2014

I've an emergency requirement to copy Source server database backup files to destination Server through xcopy command. Backup job on source server runs daily, so once this job get completes all databases backups needs to be moved to destination server. But here the main concern is "the backup files on destination server shouldn't be overwritten, they should be placed separately as Source server job runs daily".

We've a command which overwrites backups on destination server. But we need to keep backups on destination at-least for 4 weeks (means : retention should be 4 Weeks).

View 5 Replies View Related

SQL 2012 :: Parse XML Files Into Relational Table

Sep 24, 2014

We have a project to parse out an xml file into relational sql table. The xml file is complex type with multiple nesting. We are trying to resort to use XQuery to parse it out to SQL tables-because of one thing or the other - other options on the table were not viable. I know that we can use C# to do the same thing but we are sticking to TSQL with Xquery. Has anybody used the same route for processing large complex xml files?

View 1 Replies View Related

SQL 2012 :: Deleting Already Duplicated Filestream Files?

Feb 8, 2015

I have three FileStreams (FS1 on F drive, FS2 on H drive, FS3 on E drive) belonging to the same FileStream group of one particular database (DB) which is in Simple recovery mode in the SQL Server 2012.

FS1 contains huge number of files due to which F drive is completely full.

So, I am trying to move some of the extra files from one FileStream (FS1 on F drive) to another FileStreams (FS2 on H drive and FS3 on E drive) using command:

dbcc shrinkfile('FS1', emptyfile)

Then, I take the Full and Differential backup of the database and issue the CheckPoint and try to delete the already duplicated files from the Filestream FS1 to get some space in the F drive using command:

sp_filestream_force_garbage_collection @dbname = 'DB' , @filename = 'FS1'

But still no files get deleted and I receive the output as such:

file_name num_collected_items num_marked_for_collection_items num_unprocessed_items last_collected_lsn
DB_FS1 0 0 0 25000001749500000

how to delete these already duplicated files.

View 0 Replies View Related

SQL 2012 :: Loading XML Files Into Tables By Using SSIS?

Apr 6, 2015

Currently we are trying to load the xml files into sqlserver tables by using ssis 2012,We are getting xml files as a column in source table ,so we have to push these xml files into destination tables.

I'm following the below way to perform this activity

[URL]

But We have standard XSD structure for all the xml files ,and if xml file matches the XSD structure then only we have to load ,else it should skip to next xml file.

View 1 Replies View Related

SQL 2012 :: Splitting TempDB With Multiple Files?

May 21, 2015

So we have new servers that are going to be installed with SQL 2012 and I'm debating the wisdom of splitting tempdb with multiple files.

I know it's a myth that performance automatically improves if you split it into a number of files based on processors, but I'm debating the wisdom of putting a file on each of my data / log file drives.

For instance, I have a server with a C: drive (OS), D: drive (Data for system DBs and install of programs - 458 GB), an F: drive for user DB data files (767 GB), and a J: drive for log files (255 GB).

Obviously no files are going on C:. I'm debating on whether or not we should even leave system DBs on the D: drive given in our current 2k8 servers, we end up with Memory.dmp files over flowing the D: drives as well as .cabs and other install / update files that tend to collect on that drive over the years.

But if we leave the system DBs on D:, I'm wondering if adding a second tempdb file to F: and a third to J: will improve query performance or not.

View 9 Replies View Related

SQL 2012 :: Long Path Names To Files On Installation

Dec 13, 2013

I'm getting a rule check fail "Long path names to files on SQL Server Installation media failed" when installing SQL 2012 Standard edition from a network share.

View 8 Replies View Related

SQL 2012 :: Load Files In Date Order In SSIS

Apr 10, 2014

I have files which has date in file name and I want to load all files in sequential order.

Here I am giving you an example of my file name.

"NetworkActivity_869_403722_01-01-2014.log"
"NetworkActivity_869_403722_01-02-2014.log"
"NetworkActivity_869_403722_01-03-2014.log"
"NetworkActivity_869_403722_01-04-2014.log"
"NetworkActivity_869_403722_01-05-2014.log"

These are my files in that I want to load files sequentially means jan 1st, 2nd like this way.

View 5 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved