using below script to read two column from a text file and trying to trim the spaces for file_name.but trimming is not happening
bulk insert #O_P
from 'C:UsersTKTPA.txt'
with (fieldterminator = ',',rowterminator = '')
END
INSERT INTO O_P (file_name,location)
SELECT LTRIM(RTRIM(file_name)), location From #O_P
In a table I have a coulmn of postal-codes where there is a space in the middle of the postcode. How do I do to trim them away?
View 6 Replies View Related
Hello guys,
Iam showing the query results in the asp.net grid. Iam getting a long string with more than 1 space between some words.
How i can trim the spaces of that column result.
I tried RTRIM and LTRIM but these functions need two individual strings with spaces either left or right.
Help me.
Hi All,
I have a column which has some white spaces that I suspect is tab delimeted one. So when I use a rtrim(col1) it would not trim those. So i used a scrip component and wrote this line,
Row.trimDetail = RTrim(Row.detail)
here trimdetail is an o/p column and detail is the input col with the trailing spaces.
but still I don know why the column has that spaces. Can someone help me to figure out what is the problem ?
Thanks in advance,
I am inserting the following columns from one table to another,
SalesOrder,Customer,CustomerPoNumber,ShippingInstrs,CustomerName,ShipAddress1,ShipAddress2,ShipAddress3
How do I make sure that all empty spaces are trimmed.
From SSIS I need to export data to a CSV with spaces padding the end of each field before the delimited value. For example if I have three fields that are Nvarchar(10) I need it to be this:
Testing ,Test123 ,Again {end of line}
instead of this:
Testing,Test123,Again{end of line}
It's like it can do fixed width or delimited but not both. Is this possible without having to force the spaces into the data coming back from SQL? I already have the SSIS package written to export the data to CSV which works great, just need to find some way to add the spaces to the end of each column to satisfy requirements on the system being exported to. Also the commas need to be there too.
I am importing an excel spreadsheet into a MS SQL database table. When the spreadsheet is finished importing, I am noticing that some values that were brought in resemble something like this 1.41666666666667. Other values may be shorter or only have 1 digit. The problem is another web application that pulls this data for use in online forms only allows up to 2 digits. How can I round all of the numbers like the above to 2 decimals and replace the existing values?
I know there is the rounding function that could be used like so:
SELECT ROUND ([Hrs Total 2],2)
FROM AnnualClassifiedPAFs
How do I then take that rounded value and insert it back into the records?
I understand that we shouldn't shrink data files as it might cause heavy fragmentation along with log usage, high IO/CPU etc.
In a DB in which lot of DML transaction occur, there will be empty spaces whenever deletions occur.
Will SQL Server fill that part with data when new insertions occur ?.
I have a text field with about 17,500 rows I need to edit from for example: '1.90X .90' to '1.90X 0.90'? The numbers are various but follow the basic format albiet they may vary to say '22.78X .40'
View 8 Replies View RelatedI got two columns DEATHDATE and STATUS
I have to update STATUS Column as INVALID when data in DEATHDATE column as follows
Example of INAVLID DEATH DATE : 1221, 12xt88,12#10, 1, ABC, #@ , etc
Do nothing when the DEATHDATE is VALID like
Examples of Valid/accepatable DEATHDATE: 09122012, 091212 ,******,********, 000000,00000000
How can i achieve this.
so i wrote this script and its pretty sweet (tara helped me before on this one) but now, for some STRANGE reason and its been reported by users the following:
the script for example dumps "first name, last name"
when we save the results as a CSV it looks good in notepad...
"first name, last name"
but we kept getting some kind of stange issues when we tried to dump that CSV data into another program to use... so i opened the CSV file in command prompt to see what it really looks like and it is actually "f i r s t n a m e , l a s t n a m e" with all these unnecessary spaces! why is it saving the CSV with spaces in it when my script is nothing of the sort? it was working fine last week, no changes were made to the script but now the CSV's are rendering those weird spaces.
I need to write a process to get file size in kb and record count in a file. I was planning on writing a c# console app that takes the file path and name as a param however should i use a CLR?
I cant put a script in the ssis when it's bringing the file down because it has been deemed that we only use ssis for file consumption.
I'm trying to create an output file in a specific layout. For some reason my output file is adding an extra 10 spaces between the Account Number and the Check Number in the statement below. The rest of the output file looks fine. Where the extra 10 spaces are coming from? I need 1 Filler Space between these fields.
SELECT DISTINCT
CASE p.PaymentMethodID WHEN 10 THEN 'I' WHEN 60 THEN 'V' WHEN 50 THEN 'S' ELSE 'I' END
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 20 - LEN(ba.AccountNumber))+ CONVERT(CHAR(20), ba.AccountNumber))
+ CONVERT(CHAR(1), '')
+ (REPLICATE('0', 18 - LEN(p.CheckNumber)) + CONVERT(VARCHAR(18), p.CheckNumber))
[Code] .....
I'm loading data from a sql server table into a flat file. The flat file connection manager has the following settings
GENERAL:
Format:Delimited
Text Qualifier:"
Header row delimiter: {CR}{LF}
Header rows to skip : 0
Columns:
Row Delimiter: {CR}{LF}
Column delimiter: comma(,)
Please provide me with this queries solution
"select leadsource_id from Leadsource_Master where trim(ucase(leadsource_name))='" & Address_Module.name_check(Trim(UCase(cmbLeadSource ))) & "'"
It is a query in access, I want to use the same in SQL SERVER 2000 using VB 6.0
I upgraded my 2008 R2 instance to 2012 and the Linked Server I use for aconnect to a csv file stopped working.
Create script:
EXEC sp_addlinkedserver @server ='Stats_CA',
@srvproduct='',
@provider ='Microsoft.ACE.OLEDB.12.0',
@datasrc='D:RLAPPSStatsStats_CA',
@provstr='Text'
Error message:
OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA" returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 2
Cannot initialize the data source object of OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "Stats_CA".
I uninstalled and reinstalled the AccessDatabaseEngine - no change.
The provider is displayed, so it's not that.
I verified temp and csv location folder permissions
I tried the -g512 startup option
The csv has a schema.ini as required
It was an 'in place' upgrade, so nothing else has change.
I have been trying to store binary file in a folder from the SQL Server.
Here is the code I am using to do this but, it is not working. It doesn't show any error only shows 1 row(s) affected. The folder remains empty after running this query.
I have already configured server using
EXEC master.dbo.sp_configure 'show advanced options', 1
RECONFIGURE
SP_CONFIGURE 'Ole Automation Procedures', 1
GO
RECONFIGURE
GO
DECLARE @File VARBINARY(MAX),
[Code] .....
I am receiving the below message however when going into my database properties and going into 'File' it's set as either unrestricted growth for the log files or 2097152MB limit and the log files are only taking up about 3gigs.
Could not allocate new page for database. There are no more pages available in the file group.
Database log file is full. Back up the transaction log for the database to free up some log space.
Could not allocate space for ojbect in database because the filegroup is full.
how I can resolve this and give it more space?
write a backup to local disk and then run a command to send the file to the TSM Server.This is the command I use at a Command Prompt to do an TSM incremental backup.
Command for an incremental backup of drive letter h:
C:Program FilesTivoliTSMaclientdsmc incremental h:
Command for an incremental backup of a mount point:
C:Program FilesTivoliTSMaclientdsmc incremental -domain="E:Backup"
I would like to be able to run this as the last step in my backup processes. This would allow me to send my local backup file to the TSM server to write to tape.
I am looking for either a CMDEXEC Expert that could show me the syntax to run these commands via a direct command or a batch job. The other option would be to run these commands via the Powershell type.
What I am trying to recreate is:
<value version="5" type="database">
<name>master</name>
<server>servername</server>
<integratedSecurity>True</integratedSecurity>
<connectionTimeout>15</connectionTimeout>
[Code] ....
with this query:
SELECT
'version="5" type="database"' AS 'value',
'master' AS 'name',
LTRIM(RTRIM(([Server Name]))) AS 'server',
'True' AS 'integratedSecurity',
[Code] ....
BUt my output is not correct, it is creating this:
<value>
<value>version="5" type="database"</value>
<name>master</name>
<server>ServerName</server>
<integratedSecurity>True</integratedSecurity>
[Code] .....
So my question is how to I get <value>version="5" type="database"</value> as the first 'value' node?
I've tried multiple ways, but no success.
I have accidentally taken the backup of a database twice into same .bak file. Now the file is twice the size. Will it be fine if I restore this backup? Or will I screw up any data?
View 1 Replies View RelatedI have a FileTable as part of an application that a client uses. In general the response of the SQL Server has been great. However, they have started to make use of a part of the application that handles images. The application has a FileTable set up. However, there is a Stored Procedure that retrieves the path of the requested file. It is taking the Server over two seconds to return a single file path.
USE [BMIPictureDB]
GO
/****** Object: StoredProcedure [dbo].[GetFileTablePath] Script Date: 8/31/2015 6:07:37 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetFileTablePath]
[Code] ....
i have .bak file downloaded from internet , and i also have istalled sql server 2012.my problem that i can not restore this .bak file and get this error massage :
System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLDATASRO_VT_SHARD.mdf'. (Microsoft.SqlServer.SmoExtended)
- .bak file version = 661 10 50 1600 = sql server 2008R
- my sql version = Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86)
i collected the users information without using any trim fucntion(i have implemented now)
but the data which has been already posted into my server has text with some white spaces at the beginning of data
now how to remove this white spaces in this column in online server data.
the data is something like this
mycolumn
data
data
data
data
so how to remove white spaces in the above column
After my Stored Procedure has run, I need to call [URL] ..... file. Can this be done from my Stored Procedure?
View 2 Replies View RelatedI have database tables for
Stores
StoreId, Name
Products
ProductId, Name
Transactions
TransactionId, StoreId, ProductId
I was just given an excel file with a list of 300 Stores.
I need to find out if these stores are selling our products and if they are , how many products they are selling.
One way of doing this , that I can think of right now is individually querying the Transactions table for each of the store in the excel sheet and then copy the results output back to the excel sheet.
Is there a way I can write a query against all the Store names from the excel file ? I need to get this done in the next few hours.
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]....
Is it possible to dynamically grow a data file for TempDB?
I have some TSQL that I want to execute (this is raw tsql, not something I'd typically share) when I get an alert that TempDB is growing.
SET NOCOUNT ON;
DECLARE @TempDB TABLE (FileID tinyint, name varchar(50), size bigint)
DECLARE @TotalSize decimal(19,4), @TotalFiles int, @CurrentTempDBSize bigint, @SQL varchar(1000), @DBName varchar(50)
SET @SQL = ''
INSERT INTO @TempDB
[code]....
ENDThe code runs fine, but doesn't actually increase the size of the file.
I have been tasksed to create a data table and stored procedure to extract a special formatted XML file that is an attachment with a standard XML envelope. The XML file is an attchment in a node within the XML wrapper. There are other MIME files (pdf's ) that are handle by a seperate procedure. But I need to just extract the XML file attached along with those and put it into the datable with some other PK?FK fields.
Is a blob the best datatype. How to I insert that XML file into it?
Is there a way to open EDI file in SQL Server and insert the data into tables?
View 9 Replies View RelatedI've been trying to access the file table shares exposed on a server (I can select, delete, update, etc via SQL) via untransacted access with no luck (the share just sits there doing something when I try to access it, no error message of any kind).
So I thought I'd look to see if there were any open file handles via
SELECT * FROM sys.dm_filestream_non_transacted_handles;
I found there were 10 non transacted handles open, and they've been open for 3 days for automated processes and the files are about 1-2Kb in size.
so I tried to kill them via
exec sp_kill_filestream_non_transacted_handles
that's normally not something that takes very long (in fact when I cleared two for a different database it took just a second or two).
So leaving the stored proc running, I figured I'd connect and fire up Adam Mechanic's sp_whoisactive.
I see the processes to kill the open file handles, with a wait state of FFT_NSO_FILEOBJECT
I am trying to load a fixed width text file using `Bulk Insert` and a XML format file. I have used the same process and XML file on another fixed width, except with less columns.
Error
Msg 4857, Level 16, State 1, Line 16
Line 4 in format file "PATHCaddr.xml": Attribute "type" could not be specified for this type.
SQL Server Table
[code lang="sql"]
create table [dbo].[raw_addr](
address_numbervarchar(max),
addr_linelvarchar(max),
addr_line2varchar(max),
street_novarchar(max),
[code]....
After performing a join operation on two tables i get the below resultset
pid, fname, typename, pname, pcost
1, cad, bars, product-1, 100
2, har, witte, product-2, 120
3, nes, bars, product-3, 119
Now i need to create files with the obtained resultset like
Column 'fname' is the folder name and 'typename' should be the file in the particular folder.
For example the first record should be inserted into file name 'bars.txt' in the folder 'cad' and third record should be created in file name 'bars.txt' in the folder 'nes'.