|
I get several EDI Files from vendors that have small square at the en of the file on the last line, if I don’t delete them before running my stored procedure I get unexpected end of file error. Anyone know how to make the procedure ignore this?
Brian
|
|
|
|
|
Hi Brian
Most text editors show a small square to indicate that there is a non-printable control character.
If you can identify what the control-character is (I normally use a Unix hex dump) then you can use
REPLACE(MyColumn, CHAR(n), '') to get rid of it (where "n" is the character number). The most likely control-characters are CHAR(0) [ASCII NUL] and CHAR(26) [ASCII EOF].
Hope that helps.
|
|
|
|
|
Thanks for the help but Im not sure how I use the replace statement in this content this is the code that fails due to the control character. Can I put the replace in with the bulk insert and how would I do this?
Thanks
declare @File varchar(1000)
declare @cmd varchar(2000)
declare @dealermark money
create table ##Import (prodnum varchar(20), lblabr varchar(4), lbl varchar(20), vendorname varchar(20), catnum varchar(20),config varchar(2),artist varchar(50),title varchar(50),mcat1 int,mcat2 int,mcat3 int,vidrate varchar(5),sellprice money,listprice money,UPC varchar(20),oldupc varchar(12),oldprod varchar(20),rlsdate datetime,disdate datetime, lastret datetime,specord varchar(1), modflag varchar(1), bioinfo varchar(1000))
select @File = @pFileName
select @cmd = 'bulk insert'
select @cmd = @cmd + ' ##Import'
select @cmd = @cmd + ' from'
select @cmd = @cmd + ' ''' + replace(@File,'"','') + ''''
select @cmd = @cmd + ' with (MAXERRORS = 1000, FIELDTERMINATOR=''","'''
select @cmd = @cmd + ',ROWTERMINATOR = ''' + char(10) + ''')'
truncate table ##Import
-- import the data
exec (@cmd)
|
|
|
|
|
Do you have an error message? I use the same sort of code in my application and it doesn't complain about CHAR(0) characters.
Otherwise, is it possible for you to clean the file using C# before loading it in?
|
|
|
|
|
In Access these four items would be sorted like this:
EIGHTY
'EIN EL-JARBA
EL-AJJUL, TELL
ELAM, ELAMITES
But in SQL Server they get sorted like this:
'EIN EL_JARBA
EIGHTY
ELAM, ELAMITES
EL-AJJUL, TELL
Is there a way to make SQL server sort like the Access database.
|
|
|
|
|
I kept waiting, hoping someone would reply, cause I don't know nothin about SQL Server; but since no one is stepping up to the plate...
What colation order are you using in Access? Look under Tools -> Options -> General -> "New database sort order".
My guess is that you have it set to "General", which I think uses the Western European Code Page. Try the following link:
http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnarsqlsg/html/msdn_interntl.asp[^]
which discusses code pages etc. You're probably going to have to reset your code page within SQL Server to get it to work like Access; probably to Western European.
|
|
|
|
|
Hi All,
Few days back I posted a problem related to Pivot Table, and with help of people here I solved it. Thanks a lot. Unfortunately the script had some lack of generalization. Lest see the code first, -
<br />
CREATE PROCEDURE dbo.spDayBranchSummary<br />
<br />
@RepDate DATETIME<br />
<br />
AS<br />
<br />
SELECT <br />
-- 1.A & 1.B Branch Code and Name<br />
BCode, BName, <br />
<br />
-- 2.A Total Quantity of HH sold on that day<br />
((SELECT SUM(SaleLine.Qty)<br />
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON <br />
Sale.VCode = Vendor.VCode AND<br />
Vendor.BCode = Branch.BCode)<br />
ON SaleLine.SaleCode = Sale.SaleCode AND<br />
Sale.SaleDate = @RepDate AND SaleLine.PCode = 'HH'<br />
)) AS HH,<br />
<br />
-- 2.B Total Quantity of HT sold on that day<br />
((SELECT SUM(SaleLine.Qty)<br />
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON <br />
Sale.VCode = Vendor.VCode AND<br />
Vendor.BCode = Branch.BCode)<br />
ON SaleLine.SaleCode = Sale.SaleCode AND<br />
Sale.SaleDate = @RepDate AND SaleLine.PCode = 'HT'<br />
)) AS HT,<br />
<br />
-- 3 Sales on that day<br />
dbo.NZ((SELECT SUM(SaleLine.Qty * SaleLine.Price)<br />
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON <br />
Sale.VCode = Vendor.VCode AND<br />
Vendor.BCode = Branch.BCode)<br />
ON SaleLine.SaleCode = Sale.SaleCode AND<br />
Sale.SaleDate = @RepDate), 0) AS [Net Value],<br />
<br />
-- 4.A Total Payment on that day<br />
dbo.NZ((SELECT SUM(VPH.CashAmount)<br />
FROM VendorPaymentHistory VPH INNER JOIN <br />
Vendor <br />
ON VPH.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode AND <br />
VPH.PaymentDate = @RepDate), 0) AS Cash,<br />
<br />
-- 4.B Total Coupons returned on that day<br />
dbo.NZ((SELECT SUM(VCH.Quantity*VCH.Price)<br />
FROM VendorCouponHistory VCH INNER JOIN<br />
Vendor<br />
ON VCH.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode AND <br />
VCH.[Date] = @RepDate), 0) AS Coupon, <br />
<br />
-- 5 Total of 4.A and 4.B<br />
-- (Cash + Coupon) AS [Total Value]<br />
<br />
-- 6 Day's Balance<br />
-- ([Net Value] - Cash - Coupon) AS [Day Balance]<br />
<br />
-- 7 Previous Outstanding<br />
-- Previous Outstanding = [Previous Sale] - [Previous Cash] <br />
-- - [Previous Coupon] + [Total Opening Balance]<br />
-- 7.A Previous Sale<br />
dbo.NZ((SELECT SUM(SaleLine.Qty * SaleLine.Price)<br />
FROM SaleLine INNER JOIN <br />
(Sale INNER JOIN <br />
Vendor ON Sale.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode)<br />
ON SaleLine.SaleCode = Sale.SaleCode AND<br />
Sale.SaleDate < @RepDate), 0) AS [Previous Sale],<br />
<br />
-- 7.B Previous Cash<br />
dbo.NZ((SELECT SUM(VPH.CashAmount)<br />
FROM VendorPaymentHistory VPH INNER JOIN <br />
Vendor <br />
ON VPH.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode AND <br />
VPH.PaymentDate < @RepDate), 0) AS [Previous Cash],<br />
<br />
-- 7.C Previous Coupon<br />
dbo.NZ((SELECT SUM(VCH.Quantity*VCH.Price)<br />
FROM VendorCouponHistory VCH INNER JOIN<br />
Vendor<br />
ON VCH.VCode = Vendor.VCode AND <br />
Vendor.BCode = Branch.BCode AND <br />
VCH.[Date] < @RepDate), 0) AS [Previous Coupon],<br />
<br />
-- 7.D Opening Balance<br />
dbo.NZ((SELECT SUM(Vendor.OpeningBalance)<br />
FROM Vendor <br />
WHERE Vendor.BCode = Branch.BCode), 0) AS [Opening Balance]<br />
FROM Branch<br />
ORDER BY BCode<br />
GO<br />
Now in this stored procedure 2.A and 2.B are rows in another table and taken as columns here. In future suppose rows in that table increase I will have to add lines here for new rows. Can this be automated? So that there is no need to add new lines for new rows?
Thanks in advanced
Kumar
|
|
|
|
|
Phew - that's one long procedure. If I were you, I would introduce a table that contained the codes that you were interested in, and then in your select statement show the code and the total. The link is a simple inner join, so
((SELECT SUM(SaleLine.Qty)
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON
Sale.VCode = Vendor.VCode AND
Vendor.BCode = Branch.BCode)
ON SaleLine.SaleCode = Sale.SaleCode AND
Sale.SaleDate = @RepDate AND SaleLine.PCode = 'HT'
)) AS HT, becomes
LookupTable.Code, ((SELECT SUM(SaleLine.Qty)
FROM SaleLine INNER JOIN (Sale INNER JOIN Vendor ON
Sale.VCode = Vendor.VCode AND
Vendor.BCode = Branch.BCode)
ON SaleLine.SaleCode = Sale.SaleCode AND
Sale.SaleDate = @RepDate AND SaleLine.PCode = LookupTable.Code
)) AS SumOfValues
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
Hi all,
I have questions on SQL Express 2005 performance. Will it be faster if it is installed on Windows Server compared to on Windows XP? (I guess whatever the answer to this will apply to SQL 2005 (non-Express) as well).
The other question (since I am not IT guy): what exactly are different in Server hardware compared to Client hardware? (Usually they are much more expensive). Will the performance of Windows Server (and all its programs) suffer if it is installed on a client-spec hardware?
Thanks for any input.
|
|
|
|
|
I think the bigger issue with performance is hardware. If you have a single CPU system it is not going to be very good either way. If you have a multiple cpu system that is better. sql express 2005 is limited to the amount of ram and cpus it can use. The regular version is not. It is also limited to the size of the database. So if you want you database to be fast, have multiple CPU's lots of Ram and fast hard drives. It also can help to have your database and log files on different volumns (multiple hard drives).
Here's a link on sql express 2005 limitations
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1082707,00.html[^]
Hope that helps.
Ben
|
|
|
|
|
Are you saying that the performance of SQL Express on Windows XP will be the same with performance of full SQL Server on Windows Server 2003 if both of these are using the same machine (1 CPU, 1GB RAM) serving lets say, 5 clients? (I know that full version is the logical choice for serving large number of clients).
Thanks.
|
|
|
|
|
Yes, according to their own details, it is the same thing just a smaller limited version. If you only have 5 clients and their databases will be under the limit then it sure is a lot cheaper.
Actually if you decided to install the Sql express locally on the PC that the application was running on sql express would be faster then the full version since it wouldn't have to go across the network.
Ben
|
|
|
|
|
Does anybody know if 1.1 supports the Failover Partner connection string clause? I'm just starting to investigate possibilities for mirroring a database accessed by a ASP.Net 1.1 project and can't find documentation stating that 1.1 supports it. I assume 2.0 does, but I'm not sure when the functionality was implemented, since it's new to SQL Server 2005.
|
|
|
|
|
hello all,
I have 3 tabels empployees(empid int,empname nvarchar 50),departement(depid int,depname nvarchar 50)and a table called intermediate(empid int,depid int) that join the other two tables I want to know the employees that doesn't belong to any departement.
I'v try the following:
select empname from emps join intermediate on emps.empid!=intermediate.empid
but it doesn't work as expected.
Dad
|
|
|
|
|
hadad wrote: select empname from emps join intermediate on emps.empid!=intermediate.empid
select empname from emps left join intermediate on emps.empid = intermediate.empid Look for any nulls in the Intermediate.EmpID column.
CleaKO
"I think you'll be okay here, they have a thin candy shell. 'Surprised you didn't know that.'" - Tommy (Tommy Boy)
"Fill it up again! Fill it up again! Once it hits your lips, it's so good!" - Frank the Tank (Old School)
|
|
|
|
|
select * from employees where empid not in (select empid from intermediate)
|
|
|
|
|
Hello friends,
Can anybody tell me about Hash table?
advantages of this table??
Thanks,
Ankur Bakliwal
|
|
|
|
|
Google broken where you live ?
Hash tables have nothing to do with SQL. They are a container which allows you to store values against an index. Google will tell you more.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Google hashtable and collections. There are some good articles on the collections classes on the msdn website. A hashtable is a type of collection used for storing objects in memory and has no relation to sql or Ado.net classes.
_____________________________________________________________________
Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.
The Enlightenment Project (paraphrased comment)
Visit Me at GISDevCafe
|
|
|
|
|
Hi all,
I have an asp.net webform which is exporting a crystal report in to Excel format. But what I need is, I would like to format the excel cells so that it should adjust the cell size or wrap the cell according to the data given in it by using VB.Net. If anybody know it pls. advice me or give me some code snippet if possible. Thank you.
Regards,
S/W Engineer
Akebono Soft Technologies
aleem_abdul@akebonosoft.com.
|
|
|
|
|
hi i have two tables like want to display based on the date:
eg:
table1:
Id cardDate Intime1 Outtime1
1 12/22/2006 09:00 20:00
2 12/22/2007 09:00 20:00
3 12/22/2006 09:00 13:00
and table 2:
Id leaveDate Intime2 Outtime2
3 12/22/2006 13:00 20:00
4 12/22/2006 09:00 20:00
5 11/11/2006 09:00 20:00
If i select the date 12/22/2006" then it want to display like:
Id Carddate InTime1 Outtime1 Leavedate Intime2 Outtime2
1 12/22/2006 09:00 20:00 null null null
3 12/22/2006 09:00 13:00 12/22/2006 13:00 20:00
4 12/22/2006 09:00 20:00 null null null
I am week in sqlquery,plz hlp me...,
Magi
|
|
|
|
|
Hi
Before filtering based on Date, you need to convert to a specific format.
For eg., Convert(nvarchar(12),@DateParameter,101) where 101 displays date in mm/dd/yy.
So if you want to display based on date.
Select * from Table1 where convert(nvarchar(12),DateCol,101) = Convert(nvarchar(12),@DateParameter,101)
Where @DateParameter is the parameter to be passed from front-end.
Hope this is clear.
Harini
|
|
|
|
|
Hi
i have following tables
Table:Messages, Fields: msg_id,message
Table:Msg_groups, Fields:msg_id,Group
now i want to select msg_id and group, a msg_id can have more than one group it should display like this:
msg_id....group
123.......22,23,24
222.......22
122.......23,24
Any help will be appreciated.
Shahzad Aslam
-- modified at 5:37 Tuesday 27th March, 2007
|
|
|
|
|
the below query select groups for each msg_id
SELECT Messages.msg_id, Msg_groups.Group
FROM ( Messages INNER JOIN Msg_groups
ON Messages.msg_id=Msg_groups.msg_id )
enjoy it 
|
|
|
|
|
can write a function which accepts "msgid" and fatches all "group" returns concatenated group.
using
select msgid, fugroup(msgid) ......
will give output as required.
Regards
KP
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
