Tuesday, 15 April 2008
|
| format files for use with bulk insert Ted 09:41:05 |
| | I used bcp to produce the apended format file.
How can it be modified to recognize the quotes that surround the text
fields and not insert the quotes along with the text? Invariably, the
first four columns have text surrounded by quotes and are terminated by
tabs. If the first column has "abc", only abc ought to be inserted
into that field in the table.
Thanks
Ted
==================format file========================
<?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="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="6"
COLLATION="Latin1_General_CI_AI" />
<FIELD ID="2" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="7"
COLLATION="Latin1_General_CI_AI" />
<FIELD ID="3" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="48"
COLLATION="Latin1_General_CI_AI" />
<FIELD ID="4" xsi:type="NativeFixed" LENGTH="4" />
<FIELD ID="5" xsi:type="CharPrefix" PREFIX_LENGTH="2" MAX_LENGTH="8"
COLLATION="Latin1_General_CI_AI" />
<FIELD ID="6" xsi:type="NativePrefix" PREFIX_LENGTH="1" />
</RECORD>
- <ROW>
<COLUMN SOURCE="1" NAME="f_supplier_code" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="2" NAME="f_product_code" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="3" NAME="f_product_name" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="4" NAME="f_asset_classes_id" xsi:type="SQLINT" />
<COLUMN SOURCE="5" NAME="f_size" xsi:type="SQLVARYCHAR" />
<COLUMN SOURCE="6" NAME="f_dist_unit" xsi:type="SQLFLT8" />
</ROW>
</BCPFORMAT>
|
| Options |  7 answers | Add comment |
Tuesday, 26 February 2008
|
| Divide by Zero Joriveek 21:20:40 |
| | When I specify a formula between Computed Column Specification, I have two
zero values, getting Divide by Zero error, any idea how can I avoid this? I
still want SQL Server to display Zero if it is 0/0, is this possible in SQL
Server database?
Thanks
J.
|
| Options | 20 answers | Add comment |
Sunday, 25 November 2007
|
| Syntax Question Guest 16:51:45 |
| | SQL Server 2000
When joining tables, some use:
AliasTableName.ColumnName = AliasTableName.Column
while others use
LEFT/RIGHT etc JOIN .......
Is one better than the other in cases where the join is a simple JOIN
of equality ?
Curious ...
Thanks,
Craig
|
| Options | 3 answer | Add comment |
Tuesday, 28 August 2007
|
| Re: Re: Re: connectionopen (SeCDoClientHandshake().]SSL Security Pigeon 09:51:10 |
| | "Erland Sommarskog" wrote: pigeon (DoNotEmail@dbForumz.com) writes: Ok.. it looks like we can only initiate a secure transaction serverside.. when we try to initiate a secure connection clientside (of course serverside encrypttion has to be off).. then we get the SSL I have never worked with encryption, so I don’t know. I did actually play with it recently for a test script, and I think I arrived at a similar conclusion. But I did not even have a certificate. (What I wanted to test was whether a module that I have handles the Encyption option correctly, but I had to leave that out.) I’ve relayed your posts to our internal MVP forum, to see if anywhere there knows about this.
Great thanks!
I figured it out.. and I think it is just a bug
the solution to my problem was to
create a key in the registry named "certificate" and put my
certificate’s thumbprint in the key.
This is somewhat normal..
but the other wierd thing is...
In order to have encrypted or unencrypted traffic, I have to tell SQL
to ’force encryption’ via creating a certificate named "encrypt" and
putting this value to 1
w2k
ms sql 2000 sp4
--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/General-Discussions-connectionopen-SeCDoClientHandshake-SSL-Security-error-ftopict235551.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=819787
|
| Options | 2 answer | Add comment |
Wednesday, 6 June 2007
|
| Free Online SQL Formatter Billy bb 14:05:34 |
| | We have just release a free Online SQL Formatter
which can beautify different sql dialects for you on the fly with many
format options.
and translate it to other languages such as C#, Java,Delphi
Try it here:
http://www.wangz.net/gsqlparser/sqlpp/sqlformat.htm
*** Sent via Developersdex http://www.developersdex.com ***
|
| Options | 3 answer | Add comment |
Tuesday, 5 June 2007
|
| Login failed for user 'null' Dobrzak 19:23:03 |
| | Hello,
I have problem with linked servers (MS SQL Server 2k)- when I try to execute
query(update, insert..) with linked servers, I have message Login failed for
user 'null'. This is very strange problem, some computers in AD (2k3 std)
don't have this error, when the other have this problem for few hours and
some have this error all the time. Network Protocols is TCP/IP.
Any idea ?
|
| Options | 4 answer | Add comment |
Tuesday, 22 May 2007
|
| SQL to Oracle update trigger fails due to distributed transaction error 7391 Guest 19:50:31 |
| | Hi,
I am having a hard time creating a Trigger to update an Oracle
database. I am using a SQL Server 2005 Express database on a Win XP Pro
SP2 desktop, linked to an Oracle 10g database on a remote Windows 2003
server. Both machines are on the same domain and very close physically
(<1ms ping).
I have set up the Oracle linked server in SQLEXPRESS, added the
login/pw information, and I can execute select and update queries
successfully using both four-part naming and OPENQUERY.
Here is the actual trigger that I created:
USE [AdventureWorks]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TRIGGER [MyTrigger]
ON [Person].[Contact]
AFTER UPDATE
AS
BEGIN DISTRIBUTED TRANSACTION
SELECT * from oradb..schema.table
WHERE username = 'user'
COMMIT TRAN
However, when I update a row in AdventureWorks.Person.Contact, there is
a lag of about 2 seconds, and then I receive an error 7391 with the
following message: "The operation could not be performed because OLE DB
provider 'MSDAORA' for linked server 'oradb' was unable to begin a
distributed transaction."
Now, when I remove the "BEGIN DISTRIBUTED TRANSACTION" and "COMMIT
TRAN" from the trigger, I can update the row without any delay or error
message.
(Don't pay attention to the fact that the triggered action is a SELECT
statement. It also fails with an UPDATE statement, whether or not I use
"BEGIN DISTRIBUTED TRANSACTION." I thought using the SELECT statement
illustrates the problem more clearly.)
If I replace the triggered code with an update to a SQL Server database
on that same server (even using "BEGIN DISTRIBUTED TRANSACTION"), then
it works correctly. This leads me to believe that MS DTC is configured
properly on both machines. There is no firewall between the two
machines, and I can Telnet from the desktop to the database server on
port 135.
I have gone through many MSKB articles (280106, 839279, 329332, 259959,
193893, "Troubleshooting Oracle Publishers" from BOL), and archived
newsgroup posts but have been unable to find any resolution for this
problem. I would appreciate any assistance you may provide.
Best regards,
George
|
| Options | 1 answer | Add comment |
Wednesday, 21 March 2007
|
unable to connect to ,ms sql server from visual c++  athi 16:14:24 |
| | hi
I am trying to connect from my small c++ program to my ms sql server, but Not able to. I tried going to the configuration manager and enabled the namedpipes, but still not able to.
I am using the default instance in the server.
someone please enlighten me.
thnks
athi |
| Options | Add comment |
Monday, 20 November 2006
|
| float numbers Guest 20:04:44 |
| | Hi!
How do I do to make t-sql not rounding the result that i returned?
For example:
0.9616458*60 = 57,698748 (in any calculator)
while following:
--------------------------------
declare @a float
declare @b int
set @a=0.9616458
set @b=60
print @a*@b
---------------------------------
will show :57.6987
How do I do to make MSSQL to show me the value whothout rounding it?
Thanks!
|
| Options | 25 answers | Add comment |
Saturday, 7 October 2006
|
| SQL Server 2005 Express Birdman 01:55:31 |
| | I previously had the beta installed on my PC. I unistalled it to install
SQL Server 2005 Express Advanced, and it is telling me I still have the beta
version installed. It is not listed in add/remove programs. I am lost.
Please help.
|
| Options | 5 answers | Add comment |
Saturday, 2 September 2006
|
| PDF Bookmarks Alon 16:59:56 |
| | When I export a report to PDF format, the bookmarks pane is closed, and
needs to be pressed in order for the bookmarks to open.
Is it possible that Upon opening the PDF, the bookmark pane will be
visible ?
|
| Options | 4 answer | Add comment |
Tuesday, 29 August 2006
|
| Urgent deliverable Kalyan 13:40:20 |
| | Hi
I have a query as follows
select * from
( select CompanyID , TickerSymbol , CompanyName,
dbo.FormatNumber(LatestClosingPrice,2) as 'CurrentPrice' from
backscreeningdata3
where dailydate= '12/31/04' and (LatestClosingPrice>100) ) as m0,
( select CompanyID , TickerSymbol , CompanyName,
dbo.FormatNumber(LatestClosingPrice,2) as 'CurrentPrice' from
backscreeningdata3 where dailydate= '12/31/04' and
(LatestClosingPrice>150) ) as m1
where 1=1 and m0.CompanyId=m1.CompanyId
Order By m0.Tickersymbol asc
And the resultset is follows
ALXALEXANDER'S INC 215 215
BRK.ABERKSHIRE HATHAWAY -CL A 87900 87900
CMECHICAGO MERC EXCH HLDGS 228.7 228.7
FFHFAIRFAX FINANCIAL HLDG 168.5 168.5
GOOGGOOGLE INC 192.79 192.79
GREYGREY GLOBAL GROUP INC 1099.99 1099.99
MKLMARKEL CORP 364 364
NVRNVR INC 769.4 769.4
NWLIANATIONAL WESTERN LIFE 166.5 166.5
SEBSEABOARD CORP 998 998
STUSTUDENT LOAN CORP 184 184
WPOWASHINGTON POST -CL B 983.02 983.02
WSCWESCO FINANCIAL CORP 393 393
YALLEGHANY CORP 285.25 285.25
ie i get the common stocks that exits in both the criteria
but my requirement is i want all the stocks that pass each criteria
|
| Options | 10 answers | Add comment |
Wednesday, 23 August 2006
|
| trigger help Matt 14:51:45 |
| |
Greetings,
I am having some trouble with a trigger. This is my first attempt at
creating a trigger so any help would be great. The function of the
trigger is, on the insert of a row to check the value inserted into to
column VEH_REPAIR_CODE. If that value is not 19, send an email to the
value inserted in column VEH_CREW_CK_LANID. The email would include
the values inserted from columns VEH_CREW_CK_LANID and
VEHICLE_CHECK_ID ( the primary key of the table so the value is
inserted from the system). If have the following syntax but it does
not seem to work.
Thank you for any help you can provide.
CREATE TRIGGER car_check_repair_insert ON dbo. VEHICLE_CHECK FOR
INSERT AS
BEGIN
DECLARE @CREW_1 VARCHAR(20)
DECLARE @UNIT_1 VARCHAR(20)
DECLARE @VEH_REPAIR_CODE INT
DECLARE @MCC_ID VARCHAR(10)
BEGIN
SELECT @VEH_REPAIR_CODE = INSERTED.VEH_REPAIR_CODE, @CREW_1 =
INSERTED.VEH_CREW_CK_LANID, @UNIT_1 = INSERTED.VEHICLE_NUM,@MCC_ID=
VEHICLE_CHECK_ID
From INSERTED
IF @VEH_REPAIR_CODE <> 19
BEGIN
--Send the email here
DECLARE @MAIL_TO VARCHAR(50)
DECLARE @AUNIT VARCHAR(150)
DECLARE @MCID VARCHAR(20)
SELECT @MAIL_TO = @CREW_1 + '@exrch.site.com',@AUNIT ='unit
number ' + @UNIT_1 + ' Reference number ' + @MCID '
exec master.dbo.xp_sendmail @recipients = @MAIL_TO,
@message = @AUNIT,
@subject = 'car check'
END
END
END
|
| Options | 9 answers | Add comment |
Friday, 18 August 2006
|
| Time Function? Dishan Fernando 14:48:14 |
| | Hi
How can I convert integer to time .
Ex:
number = 91
Result = 1h 31m
Something like that. I want to do it in DATETIME Function!
Thanks
Dishan
|
| Options | 8 answers | Add comment |
Monday, 14 August 2006
|
| transaction logs full Guest 15:31:18 |
| | I am running a website with a SQL Server database attached.
My transaction logs are full and my hosting co. won't allocate more
disk space for me.
I need to delete my database transaction logs and asume I will need to
run an SQL script to do this.
Problem: I do not have MS Enterprise Manager of any database utility
on my website apart from MS Access. Where can I download a free SQL
tool that I can use to delete the transaction logs from my database.
Any help appreciated.
Thanks
Francois Terblanche
verismall.com
|
| Options | Add comment |
|
| MS SQLServer or Pervasive SQL 2000i Guest 11:41:34 |
| | Hi All,
Thanks in advance for the help.
We are considering a new Project. This project is going to be totally
web based.
Currently my company is using Pervasive SQL 2000i for there ERP needs.
Will the existing Pervasive SQL 2000i itself serve the purpose OR
should I consider moving to MS SQL Server.
Do assist me in terms of development also i.e. Is it easier to code
with Pervasive SQL 2000i or with MS SQL Server.
Regards
Samson
|
| Options | Add comment |
|
| Recommendation os SQL Server Monitoring Tool DataPro 05:49:10 |
| | Our shop is expanding use of SQL Server, both 2000 and 2005. We have
Litespeed on some boxes to handle the backup/recovery jobs. Can I ask
what are considered the best tools for monitoring SQL Server, in terms
of things like performance monitoring, tuning and auditing if it is
possible to get all of this functionality in one?
What do you use and like?
Thanks in advance.
Gerry
|
| Options | 1 answer | Add comment |
|
| Selecting on multiple tables Henrik Goldman 00:54:02 |
| | Hi there,
I'm new to sql and thus I'm having problems with a specific query which I
hope you guys can help me with.
Basicly I have a few tables which I'm trying to do a query on:
Table groups contains information about specific groups e.g. "Windows" or
"Unix".
Table users contains information about specific users e.g. "a", "b" or "c".
Table users_groups contain information about group relationship (a user can
be in multiple groups) e.g. (a, Windows), (b, Unix), (a, Unix).
In this case user c is ungrouped.
Now I'd like to find the users which does belong to group Windows and those
who do not:
select distinct username from users_groups where groupname = "Windows" order
by username asc;
This works pretty well for finding users in the specific group. In this case
the result is a.
However I'd like to get the opposite result (b and c) but I'm stuck.
The problem is that I'd like a list of all users excluding those which are
in "Windows"
Here is a partial query:
select distinct users.username from users left join users_groups on
users.username = users_groups.username where users_groups.username is null
order by users.username asc;
This only gives me those users who are not grouped at all. This mean that
user b is not in those results.
Please advise.
Thanks in advance.
-- Henrik
|
| Options | 2 answer | Add comment |
Sunday, 13 August 2006
|
| Hey I've got a homework problem I'm working on and am stumped. Nep Tune 20:59:02 |
| | It goes like the following:
Use AdventureWorks database and HumanResources.Department table.
Create a stored procedure called spDepartmentAddUpdate. This procedure
accepts two parameters: Name, and GroupName. The data types are
VarChar(50), and VarChar(50) respectively. Define logic in this
procedure to check for an existing Department record with the same Name.
If the department record exists, update the GroupName and ModifiedDate.
Otherwise, insert a new department record.
A.Execute your stored procedure to show that the insert logic works.
B.Execute your stored procedure to show that the update logic works.
Any hints from the wizards out there would be greatly appreciated!
*** Sent via Developersdex http://www.developersdex.com ***
|
| Options | 6 answers | Add comment |
|
| SQL Server 2000 / [USA edition] = UK ??? Davidc@Estockware.Com 18:34:27 |
| | Hey guys,
this may be a stupid question but i have to ask it nonetheless because
i'm just not sure. Ok here goes, if I purchase for example SQL Server
2000 Ent 1 Proc [USA edition] , will I have any problem with installing
it if in UK, i plan to move there soon, the only issue i can think of
is support, evertyhing else should be the same, right? Any help would
be appreciated, Thanks in advance
|
| Options | 2 answer | Add comment |
|
| SQL Server Management Studio can't 'see' the 2005 engine?? But can see 2000 ?!? Jack 13:25:29 |
| | Hello,
I had SQL2000 server running fine on the windows 2003 box. I then installed
SQL 2005 Express. They both worked together fine. I then uninstalled SQL
2005 Express and installed SQL 2005 Server. But when I open SQL Server
Management Studio, I can only connect to the SQL 2000 engine. In the Object
explorer, it says v8.0.2039 (which I think is SQL 2000 Server, because I can
see the existing SQL 2000 databases).
How can I get SQL Server Management Studio to 'see' the SQL 2005 database
engine so I can create tables? I *think* its running because there is the
'sqlservr.exe' process running, and during installation there is no issues.
When I open SQL Server Management Studio, I choose 'Database Engine', then
my local Servername with Windows Authentication. How do I connect to the SQL
2005 instance?
Thanks,
Jack.
|
| Options | 5 answers | Add comment |
|
| dependent assembly microsoft.vc80.mfcloc could not be found B Tarrance 01:21:29 |
| | Hi. Looking for any help I can find!
I have a brand new virtual server running Windows Server 2003,
Enterprise Edition with Plesk Control Panel 7.5 . I just installed the
first program on the server of SQL Server 2005 Enterprise Edition. In
the system event viewer log I have the following errors:
___________________________________________________________________________
Event Type:Error
Event Source:SideBySide
Event Category:None
Event ID:32
Date:8/12/2006
Time:12:24:35 PM
User:N/A
Computer:SDSERVER
Description:
Dependent Assembly Microsoft.VC80.MFCLOC could not be found and Last
Error was The referenced assembly is not installed on your system.
___________________________________________________________________________
_Event Type:Error
Event Source:SideBySide
Event Category:None
Event ID:59
Date:8/12/2006
Time:12:24:35 PM
User:N/A
Computer:SDSERVER
Description:
Resolve Partial Assembly failed for Microsoft.VC80.MFCLOC. Reference
error message: The referenced assembly is not installed on your system.
___________________________________________________________________________
Event Type:Error
Event Source:SideBySide
Event Category:None
Event ID:59
Date:8/12/2006
Time:12:24:35 PM
User:N/A
Computer:SDSERVER
Description:
Generate Activation Context failed for
C:\WINDOWS\WinSxS\x86_Microsoft.VC80.MFC_1fc8b3b9a1e18e3b_8.0.50727.42_x-ww_DEC6DDD2\MFC80U.DLL.
Reference error message: The referenced assembly is not installed on
your system.
___________________________________________________________________________
Can anyone please provide any guidance of any kind what so ever
regarding this??? (Can you tell I'm desperate?? 
I have google'd this and have found similar errors, but nothing that I
could take from. I really appreciate any help.
TIA
|
| Options | 1 answer | Add comment |
Saturday, 12 August 2006
|
| Weird DTS Problem Kr 20:43:19 |
| | have a dts package that is run by a scheduled job which was running
successfully for several months and suddenly stopped working.
The dts package does the following: Truncates the table, does a data
pump from a specific excel file to a table on sql server, deletes the
excel file. The job does the following:
Step 1: Look to see if a file exists and if so, run the dts package. On
success of this step the job goes to step 2.
Step2: Looks to see if any files still exist after the first step
Step3: Failure notification. To be triggered on failure of step 1 or
2.
The owner of the job is the Account that is used to run the
SQLServiceAgent and in the SysAdmin server role.
The owner of the job is also in the SysAdmin server role.
Both the accounts are Windows Accounts.
The job runs at schedule time, finds the file, and runs the dts
package. The first part of the dts package runs successfully - The
table is cleared. However it is not doing the data pump part. There
are no failure notifications. The job then goes to step two and sends
a message that the file still exists. The dts task is set to output an
error file which is not being updated, which tells that the task is not
executed. If the dts package is run manually, then the package runs
successfully.
Any help would be appreciated.
Thanks
KR
|
| Options | 1 answer | Add comment |
|
| SQL 2005-Config Manager contains no data Jack 16:15:28 |
| | Hi,
I can't seem to connect to SQL 2005. When I open Configuration Manager,
these is no information in any of the items in the tree.
I checked that WMI was configured correctly at:
http://msdn2.microsoft.com/en-us/library/ms188690.aspx
I can't see any instances at all here.
However, when I use Management Studio I CAN connect to an instance of SAL
2000 and see databases I have previously defined.
I'm running WIN 2003 with SQL 2005 standard and login as Administrator. SQL
books on line don't seem to have anything to say about this.
How can I get SQL Server Management Studio to 'see' the SQL 2005 database
engine so I can create tables? I *think* its running because there is the
'sqlservr.exe' process running, and during installation there is no issues.
I had SQL2000 server running fine on the windows 2003 box. I then installed
SQL 2005 Express. They both worked together fine. I then uninstalled SQL
2005 Express and installed SQL 2005 Server. But when I open SQL Server
Management Studio, I can only connect to the SQL 2000 engine. In the Object
explorer, it says v8.0.2039 (which I think is SQL 2000 Server, because I can
see the existing SQL 2000 databases).
Any help is greatly appreciated...
Jack.
|
| Options | 1 answer | Add comment |
|
| SQL Server 200 Developer vs Enterprise versions Jim Devenish 13:00:59 |
| | I am having some problems in the process of moving an Access back-end
to an SQL Server back-end. Everything works but there are severe
problems of response times when the number of users builds up.
At present we are using the Developer version before committing to the
Enterprise version. BOL (quoted below) seems to suggest that, apart
from licensing, they are the same.
Can anyone please confirm that this is so. If not, what is the
difference?
I may explain in another topic the problems I am facing but I first
wish to be clear on this particular question.
---------------------------------------------------------------------------------------------------------------
Quote from BOL
MicrosoftВ® SQL ServerВ™ 2000 is available in these editions:
SQL Server 2000 Enterprise Edition
Used as a production database server. Supports all features available
in SQL Server 2000, and scales to the performance levels required to
support the largest Web sites and enterprise online transaction
processing (OLTP) and data warehousing systems.
SQL Server 2000 Developer Edition
Used by programmers developing applications that use SQL Server 2000 as
their data store. Although the Developer Edition supports all the
features of the Enterprise Edition that allow developers to write and
test applications that can use the features, the Developer Edition is
licensed for use only as a development and test system, not a
production server.
|
| Options | 5 answers | Add comment |
|
