Database

Re: T-SQL: How to force rows insertion ignoring transactions?

19-Jan-10 10:20

Yuval Naveh wrote:
I would like to have the log entry (i.e. row) to be inserted and persisted regardless of what the outer (nested) transactions are doing. The log entry should never roll back

Could someone point me to a solution using T-SQL?

You could use a raiserror[^] to write your message to the sys.messages view. You'd have to predefine the errormessages using sp_addmessage though.

Alternatively, if it's from a stored procedure then you might choose to add them to a variable, as opposed to logging them immediately. Next, RETURN that variable when exiting the sproc, and ~~write~~ insert it's contents to the ~~logfile~~ loggingtable.

If you have the option to drop the TSQL-requirement, then go for extended procedures, like xp_cmdshell (executing isql.exe to execute an SQL statement).

I hope someone else comes up with something better Smile | :)

I are Troll Suspicious | :suss:

modified on Tuesday, January 19, 2010 7:31 PM

Yuval Naveh20-Jan-10 2:58

Yuval Naveh

20-Jan-10 2:58

Hi Eddy,
Using the xp_cmdshell to come back and insert rows is a possibility - looks a bit cumbersome though.
If I can't figure it out a more elegant way this might be a (temporary) solution.
Thanks,
Yuval

"The true sign of intelligence is not knowledge but imagination." - Albert Einstein

Re: T-SQL: How to force rows insertion ignoring transactions?

debrah.h4819-Jan-10 19:00

debrah.h48

19-Jan-10 19:00

Check out quires here.
http://technet.microsoft.com/en-us/library/ms187373.aspx[^]

Re: T-SQL: How to force rows insertion ignoring transactions?

Yuval Naveh20-Jan-10 2:59

Yuval Naveh

20-Jan-10 2:59

Hi,
I could not find a specific hint that solves the problem.
Thanks
Yuval

"The true sign of intelligence is not knowledge but imagination." - Albert Einstein

Centralized Query Optimization

AmbiguousName19-Jan-10 6:34

AmbiguousName

19-Jan-10 6:34

Please guys help me...Can anyone give me some links for
1) INGRES Algorithm
2) System R Algorithm
3) SDD Algorithm
i tried to find out these, but could not get some satisfactory results.

Re: Centralized Query Optimization

Eddy Vluggen19-Jan-10 9:43

Re: Centralized Query Optimization

19-Jan-10 9:43

Muzammil Saeed wrote:
Please guys help me...Can anyone give me some links for

I posted some random* links, since you didn't mention whether you wanted explanatory material or reference-implementations.

Muzammil Saeed wrote:
1) INGRES Algorithm

There's the Ingres[^]-database.

Muzammil Saeed wrote:
2) System R Algorithm

An IBM-database[^], never used it personally.

Muzammil Saeed wrote:
3) SDD Algorithm

An example implementation[^].

You're studying databases and the way they work?

I are Troll Suspicious | :suss:

*) Not entirely random, but rather arbitrary

AmbiguousName19-Jan-10 21:14

AmbiguousName

19-Jan-10 21:14

I am student and studying Dist. databse. I want these with some example....

Re: Centralized Query Optimization

Eddy Vluggen20-Jan-10 0:15

BULK INSERT problem with SQL Server 2008 DATE format

20-Jan-10 0:15

The only examples that I could find[^] are part of a presentation. It seems that osun.org[^] has some good information too, but I didn't dig in deep enough to judge whether it's what you're looking for.

Have you considered turning your research-results into a CodeProject-article? Smile | :)

I are Troll Suspicious | :suss:

Gawiz19-Jan-10 6:16

Gawiz

19-Jan-10 6:16

SQL Server 2008 has the new 3-byte DATE format that I would like to use, being as I don't need the extra overhead of storing TIME.

I used bcp to generate the following format file from my table definition:

"<?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="CharTerm" TERMINATOR="\t" MAX_LENGTH="10" COLLATION="SQL_Latin1_General_CP1_CI_AS"/>
   <FIELD ID="2" xsi:type="CharTerm" TERMINATOR="\r\n" MAX_LENGTH="11"/>
</RECORD>
<ROW>
   <COLUMN SOURCE="1" NAME="Symbol" xsi:type="SQLVARYCHAR"/>
   <COLUMN SOURCE="2" NAME="myDate" xsi:type="SQLDATE"/>
</ROW>
</BCPFORMAT>"

xsi:type="SQLDATE" is used to define the transformation for the date in my ascii file to the DATE in the table.

A snippet from the input file is:

AAI     19940101
ACL     19981231
AKA     20091208

BULK INSERT interprets all of these dates to be 1900-01-01, which would be the default value I suppose.

But if I change my input file to:

AAI     1994-01-01
ACL     1998-12-31
AKA     2009-12-08

BULK INSERT inserts the correct date values into my table.

This is peculiar because if I define the table to be SMALLDATETIME instead of DATE and SQLDATETIM4 instead of SQLDATE, then either input file works OK.

It wouldn't be such a big issue for me except that I have thousands of files each with thousands of rows and converting them all before importing is going to be time consuming.

I think this is a bug in SS2008.   I tried to report it to Microsoft, but to open a support ticket costs $99 Confused | :confused:

1) Is there a free/simple way to report the bug to Microsoft
2) Is there an updated version of bcp.exe (I can't find one)
3) Is there a workaround that doesn't involve pre-formatting the input

Thanks

Steve

Re: BULK INSERT problem with SQL Server 2008 DATE format

Eddy Vluggen19-Jan-10 11:04

Opening A Database Connection [SOLVED]

19-Jan-10 11:04

Gawiz wrote:
1) Is there a free/simple way to report the bug to Microsoft

Microsoft Connect[^]?

Gawiz wrote:
3) Is there a workaround that doesn't involve pre-formatting the input

Converting the results, by importing into the SMALLDATETIME and SQLDATETIM4 types and ALTERing the columntype afterwards.

I are Troll Suspicious | :suss:

#realJSOP19-Jan-10 4:24

19-Jan-10 4:24

I've written a program using C# that is supposed to access a database. When I try to actually open the connection, I get this exception:

A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)

The database access code is contained in a separate assembly. This same assembly is used in another program (written in VB if that matters) that connects to the same database without any problems at all (on the same machine, with the same user logged in, and using the same connection string).

Why am I getting this exception in one program and not in another?

EDIT ===================

Here are the constructors for both apps:

VB: Dim myClass as new MyClass("abc", "def\geh")
C# MyClass myClass = new MyClass("abc", "def\geh");

The problem is that VB automatically escapes the backlslash, but c# requires @"def\geh"...

Stupid programmer tricks...

.45 ACP - because shooting twice is just silly
-----
"Why don't you tie a kerosene-soaked rag around your ankles so the ants won't climb up and eat your candy ass..." - Dale Earnhardt, 1997
-----
"The staggering layers of obscenity in your statement make it a work of art on so many levels." - J. Jystad, 2001

modified on Tuesday, January 19, 2010 2:32 PM

Re: Opening A Database Connection [modified]

loyal ginger19-Jan-10 4:34

loyal ginger

19-Jan-10 4:34

Maybe some strings not escaped correctly?

#realJSOP19-Jan-10 4:57

19-Jan-10 4:57

The connection string is identical (it's created in the common assembly, and that was the very first thing I checked).

EDIT ===========

Why was this 1-voted?

modified on Wednesday, January 20, 2010 5:50 AM

Eddy Vluggen19-Jan-10 5:08

19-Jan-10 5:08

A shot in the dark; perhaps a firewall that's blocking one application, and not the other?

I are Troll Suspicious | :suss:

#realJSOP19-Jan-10 5:26

19-Jan-10 5:26

I wrote both apps from scratch (neither existed before I got here), and as far as I know, there are no application-specific blocks on our internal firewall.

Luc Pattyn19-Jan-10 5:52

Luc Pattyn

19-Jan-10 5:52

app.config?
app manifest?
elevation code missing/wrong?

Smile | :)

Luc Pattyn [Forum Guidelines] [Why QA sucks] [My Articles]

I only read code that is properly formatted, adding PRE tags is the easiest way to obtain that.
[The QA section does it automatically now, I hope we soon get it on regular forums as well]

#realJSOP19-Jan-10 6:18

19-Jan-10 6:18

There is no app.config. There is no manifest (unless vb creates on automagically). There is no elevation code. Authorization is via Windows login

#realJSOP19-Jan-10 7:15

19-Jan-10 7:15

Out of curiosity, I created a manifest file, but the vb's version uses the defaults that the C# one does, so I don't think that's the issue.

#realJSOP19-Jan-10 8:37

19-Jan-10 8:37

Solved it (see original post for solution)

Re: Opening A Database Connection [modified]

Shameel19-Jan-10 8:04

Shameel

19-Jan-10 8:04

The only possibility is a bad connection string.

#realJSOP19-Jan-10 8:25

Consuming a web service using a stored procedure

19-Jan-10 8:25

That's what I thought but the connection string for both apps is the same. Solved it (see original post for solution)

modified on Tuesday, January 19, 2010 2:36 PM

Mohammad Al Hoss19-Jan-10 3:10

Mohammad Al Hoss

19-Jan-10 3:10

Hi all I am trying to consume a webservice using TSQL but I am getting a null result
The method URL is http://localhost/SSISWebservice/service1.asmx
The invoked url is http://localhost/SSISWebservice/service1.asmx/HelloWorld
I found two samples and used them on a local web service but both of them did not get result s or generated an error.
thanks

Mohammad Al Hoss

Development To Me Is A Pleasure more than a Job

Re: Consuming a web service using a stored procedure

Eddy Vluggen19-Jan-10 5:06