|
Hi Mika
It's done now and it's working fine
so thanks for your help to solve this issue and teaching me a lot
![Rose | [Rose]](https://codeproject.global.ssl.fastly.net/script/Forums/Images/rose.gif)
|
|
|
|
|
You're welcome 
|
|
|
|
|
Hello everyone,
I'm using bcp.exe to export some data from an SQL Server 2005 database to a txt file that can be used in some other old unix system.
Thing is, once the export is done, exported rows look like this:
0730280001868 38 20040005232004-03-24 00:00:00.0002004-04-19 00:00:00.000
Dates are in a yyyy-MM-dd hh:mm:ss.sss format. However, I need datetime values to get exported in a dd/MM/year format.
According to documentation in MSDN, format in bcp import/export operations is done through a format file (an xml file in this case), so any change in the format should be done by modifying this file. Currently, my format file looks like this:
<?xml version="1.0"?>
<BCPFORMAT xmlns="http://schemas.microsoft.com/sqlserver/2004/bulkload/format" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<RECORD>
<FIELD ID="1" xsi:type="CharFixed" LENGTH="20" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="2" xsi:type="CharFixed" LENGTH="11" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="3" xsi:type="CharFixed" LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="4" xsi:type="CharFixed" LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
<FIELD ID="5" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="8" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
</RECORD>
<ROW>
<COLUMN SOURCE="1" NAME="POL_NumPoliza" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="2" NAME="CIA_Id" xsi:type="SQLINT"/>
<COLUMN SOURCE="3" NAME="SIN_Alias" xsi:type="SQLVARYCHAR"/>
<COLUMN SOURCE="4" NAME="SIN_FechaSiniestro" xsi:type="SQLVARYCHAR" LENGTH="8"/>
<COLUMN SOURCE="5" NAME="SIN_FechaParte" xsi:type="SQLVARYCHAR" LENGTH="8"/>
</ROW>
</BCPFORMAT>
Considering I've changed almost everything that can be changed in that file [datatypes, collations, lengths etc], my question is, does anyone have an idea on how to force bcp to write datetime values in a particular format (just like using FORMAT!) when writing the text file?
Thanks in advance !
Kazz
"Users are there to click on things, not think. Let the archs do the damn thinking."
|
|
|
|
|
Hi,
you didn't include the bcp command to the post, but I believe that you are exporting a table directly (like: bcp tablename...).
Instead of that, try exporting based on a query where you convert the datetime colun to varchar in desired format and use that query in bcp. For example:
bcp "SELECT CONVERT(varchar(50), DatetimeColumnName, 103), ..." queryout, ...
Mika
|
|
|
|
|
I'm creating a query to transform one DB to another, but it's getting uncontrollably big! I know, I know I should use SSIS packages, but they just don't do it for me. Most of the time it's faster to just write the code than model it in SSIS. Truth be told, I REALLY miss SQL200's DTS packages! Maybe I'm just to stubborn to accept change.
But anyway...
Is there no way that I can make my query simpler by moving some code out and simply referencing it in my main query? (And I don't think SPROCS is what I want here)
In Oracle I used to be able to write statements which execute text files... does SQL2005 have such functionality?
modified on Thursday, August 28, 2008 5:25 AM
|
|
|
|
|
If you want to break logic into several pieces you can use for example sqlcmd command line utility to run a file (a bit like in Oracle SQL*Plus). However you cannot break a single statement, but I guess that is not your intention.
Mika
|
|
|
|
|
You might want to go after the SSIS package approach. It might take longer to create, but it is a lot easier to maintain with larger projects. You might need to ask yourself will you be saving the time in the future by using SSIS?
|
|
|
|
|
i have a string data in a column in an msqsql database table and the values are separated by commas (,)eg 01,02,M,1,045,12,1948 i want to remove the commas in that column so that the data looks like 0102M1045148.
can any one help me .
thanks members 
phokojoe
|
|
|
|
|
select replace(Columnname,',','') from TableName
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
thanks Blue_Boy, i am going to try it, but by the look of the syntax, it will work.
thanks once more
phokojoe
|
|
|
|
|
I have tested that query and it has worked for me, and sure will work for you too.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
i have just tested it, but the problem is, i have so many entries in that column. when i write a column name, it does not replace the commas, instead it returns the column name not even the entries in that column. eg.
like i said, the values in that column are of this shape:
01,02,M,045,1948,2,21 and the column name (field) is say column1 i will write:
select replace('column1',',','') it only returns column1 not 0102M0451948221.
please help me on this. 
phokojoe
|
|
|
|
|
phokojoe wrote: select replace('column1',',','')
is not correct bcause you have write columnename inside single qutes
Try again code down below
<br />
Select replace(columname,',','') from tablename
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
yah yah yah, without you i could have not survived this stress.
i have done it! it works.
thanks once more.
phokojoe
|
|
|
|
|
You are welcome.
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
Hi friends, I would like to query with a string variable:
For example if using
select * from Members
I get
ID(bigint) Name(varchar(max)) Owner(varchar(max))
1 N1 a000001
2 N2 a000002
3 N3 a000003
4 N4 a000003,a000004
5 N5 a000001,a000002
Then if using
select * from Members where Owner like '%a000001%'
I would get
ID(bigint) Name(varchar(max)) Owner(varchar(max))
1 N1 a000001
5 N5 a000001,a000002
Then I use a string variable test to query as follows:
System.String test="a000001";
select * from Members where Owner like '%+test+%'
I get nothing.
So could you please help me that:
how can I use the test to query with the same result as
select * from Members where Owner like '%a000001%'
when test="a000001"?
Thanks for any feedback.
|
|
|
|
|
chenayang wrote: select * from Members where Owner like '%+test+%'
This query looks for 'test' not 'a000001'. Since 'test' is a variable, you should append it.
System.String test="a000001";
string query = string.Concat("select * from Members where Owner like '%", test, "%'");Note, this method is open to SQL injection. Use parameterized queries always.
|
|
|
|
|
Thanks a lot, N a v a n e e t h
I solve the problem as follows:
System.String test="a000001";
string query = String.Concat("select * from Members where Owner like '%", test, "%'");
|
|
|
|
|
I'm running the following query and it is giving me a major problem.
SELECT Listings.ListingID, Listings.Title, Listings.HasPhoto, Listings.IsTaken, Listings.IsPickedUp, Listings.TakenDate, Listings.PickedUp, Listings.TimesViewed, Categories.CategoryName, Members.dbo.Members.LoginID, Listings.Submitted, Listings.Approved, GetTakenMemberName.LoginID AS TakenMemberName
FROM Listings INNER JOIN
Categories ON Listings.CategoryID = Categories.CategoryID INNER JOIN
Members.dbo.Members ON Listings.MemberID = Members.dbo.Members.MemberID INNER JOIN
Members.dbo.Members AS GetTakenMemberName ON Listings.TakenBy = GetTakenMemberName.MemberID
WHERE (Listings.MemberID = @MemberID)
If I remove
INNER JOIN Members.dbo.Members AS GetTakenMemberName ON Listings.TakenBy = GetTakenMemberName.MemberID
which is the last join and
GetTakenMemberName.LoginID AS TakenMemberName
from the SELECT portion, the query runs without a problem and gives a result. With those statements in however the result is an empty table. Currently there is only one record in the Listings and Members table, so as far as I'm concerned TakenMemberName should be returning as null or empty.
I even tried using an all 0 guid in the TakenBy field so at least the query would have something to look for during the SELECT. However, that didn't work either.
[Modified]
On further investigation I have found that if I put an actual member in the TakenBy field that the query executes with out problem, but if the query doesn't match a member then the entire select fails. How can I prevent that from happening?
modified on Wednesday, August 27, 2008 10:08 PM
|
|
|
|
|
Sounds like you need an LEFT OUTER JOIN instead.
|
|
|
|
|
Thank you very much. Changed the join and it worked perfectly.
I have so much to learn about SQL it unbelievable to me somedays. Thanks again for the help. Been stuck on this for about 4 hours.
|
|
|
|
|
I created a Stored Procedure to create random strings of the length and complexity desired. It is based on the widely published spt_values query for near random numbers.
It is functional, but I'm not entirely happy with the script. There has to be more elegant way to handle different complexity settings, other than the IF...ELSE clauses I used.
CODE:
<br />
-- Random String Generator<br />
-- By: Jesse Wimberley<br />
-- 27 August 2008<br />
-- Description: Creates Random strings of <br />
-- variable length and complexity.<br />
CREATE PROCEDURE [dbo].[up_RanGen]<br />
@Return varchar(200) = '' OUTPUT,<br />
-- @Len for the length of the output<br />
@Len int = 8,<br />
--Complexity set with @Complex<br />
-- 1 = Uppercase Letters<br />
-- 2 = Uppercase and Lowercase Letters<br />
-- 3 = Uppercase, Lowercase and Numbers<br />
-- 4 = Complex Password<br />
@Complex int = 4<br />
AS<br />
BEGIN<br />
SET NOCOUNT ON;<br />
declare @Random varchar(200)<br />
-- @a thru @f set the ranges of acceptable values for each character<br />
declare @a int, @b int, @c int, @d int, @e int, @f int<br />
If @Complex = 1<br />
BEGIN<br />
set @a = 65<br />
set @b = 80<br />
set @c = 81<br />
set @d = 82<br />
set @e = 83<br />
set @f = 90<br />
END<br />
ELSE<br />
If @Complex = 2<br />
BEGIN<br />
set @a = 65<br />
set @b = 90<br />
set @c = 97<br />
set @d = 100<br />
set @e = 101<br />
set @f = 122<br />
END<br />
ELSE<br />
If @Complex = 3<br />
BEGIN<br />
set @a = 65<br />
set @b = 90<br />
set @c = 97<br />
set @d = 122<br />
set @e = 48<br />
set @f = 57<br />
END<br />
ELSE<br />
BEGIN<br />
set @a = 48<br />
set @b = 60<br />
set @c = 61<br />
set @d = 75<br />
set @e = 76<br />
set @f = 122<br />
END<br />
set @Random=''<br />
select @Random=@Random+char(n) from<br />
(<br />
select top (@Len) number as n from master.dbo.spt_values <br />
where (type='p' and number between (@a) and (@b)) or (type='p' and number between (@c) and (@d)) or (type='p' and number between (@e) and (@f))<br />
order by newid()<br />
) as t<br />
set @Return = @Random<br />
END<br />
The four complexity settings are based on ranges of values returned as their ascii equivalents. Upper/Lower/Numbers has three ranges of numbers. For simplicity, I stretched all four settings across three ranges, even when only one or two ranges were needed.
|
|
|
|
|
Hi,
how can i get info on the 2nd max(id) or 3rd min(money) record on a table.
I only know how to get min(X) or max(X) record on a table
Thanks 
nelsonpaixao@yahoo.com.br
|
|
|
|
|
Select Max(id)
From <code>table</code>
where id != (Select Max(id) From <code>table</code>)
The above will give you the second maximum id.
|
|
|
|
|
Another way:
<br />
select top 1 ColumName from TableName<br />
where ColumName < (select max(ColumName ) from TableName)<br />
order by TableName desc
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
