Conversion problems between mssql and access

Hello

When I trying to insert data with datatype datetime or smalldatetime from SQL Server into a table in a linked access database I get this error :

Server: Msg 257, Level 16, State 3, Line 1
Implicit conversion from data type smalldatetime to float is not allowed. Use the CONVERT function to run this query.

I dont understand why it try to insert it as a float?!Because from a SQL Server point of view DateTime values ARE float values.

So you cannot do an implict cast but you have to do explictly by using the T-SQL statement "CONVERT"

Look in Books On Line for better help on conversions.|||Hello

I have tried CONVERT. But I dont know to which datatype I should convert my source value.
I have converted to varchar and nvarchar but I still got the same error.|||can you post your code, and specify from what kind of data type you what convert to?|||INSERT INTO LINKEDACCESS...ContactTarget
Select ChangedBy, ChangedDate, PersonIdNo,IdNo,TargetCode,TargetCodeproductCode,T argetCodePotentialCode
from tblContactTarget
WHERE Id NOT IN(select Id from LINKEDACCESS...ContactTarget CT
WHERE CT.Target = tblContactTarget.TargetCode)

ChangedDate is of datatype Datetime in SQL and Date/Time in Access.
This INSERT will trigger the error I wrote about.|||Try to convert it to a varchar. use the CONVERT function so that you can even specify the dateformat you need to have

Conversion from MS Access SQL Code...nested if statements

INSERT INTO EligSummary ( PlanVariation)
SELECT DISTINCT

IIf(Left([BPI],1)=2 Or Left([BPI],1)=3,

'1. MED EE',

'2. MED DEP' AS PlanVariation)

The above is the code i would use in access to Assign either the value

'MED EE' or 'MED DEP' to the PlanVariation field.

I am new to SQL Server - how would I accomplish this in SQL Server 2000?

If I use SQL

INSERT INTO EligSummary (PlanVariation)
SELECT Planvariation =

CASE left([BPI],1)
WHEN 1 THEN 'EE NON MED'
WHEN 2 THEN 'MED DEP'

..it requires me to GROUP by on BPI which would cause it to enter 15 different rows for each BPI code as opposed to 2 for EE NON MED and MED DEP......

I would appreciate any help you could give me with this!

CASE has a second syntax, which will give you what you want

INSERT INTO EligSummary ( PlanVariation)
SELECT DISTINCT
CASE WHEN (left([BPI], 1) = '2' OR left([BPI], 1) = '3' THEN '1. MED EE'
ELSE '2. MED DEP' END AS PlanVariation

|||

Thanks! That did the trick..i appreciate the help.

Conversion from float to varchar

--SCRIPT :
CREATE TABLE [t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [t1] VALUES(1.0 , null )
INSERT INTO [t1] VALUES(3.1099999999999999 , null )
INSERT INTO [t1] VALUES(2.1000000000000001 , null )
What is required that copying data from column [id] to column [charid] with
all trailing decimal values.
-KhurramCREATE TABLE [#t1] (
[id] [float] NULL ,
[charid] [varchar] (10)
)
GO
INSERT INTO [#t1] VALUES(1.0 , null )
INSERT INTO [#t1] VALUES(3.1099999999999999 , null )
INSERT INTO [#t1] VALUES(2.1000000000000001 , null )
UPDATE #t1
SET charid =FLOOR(id)
Select * from #t1
HTH, Jens Suessmeyer.|||Look up the STR function in Books Online.
http://msdn.microsoft.com/library/d.../>
us_412q.asp
Plus some reading on data modeling might prove to be of great help.
MLsqlsql

conversion from CHAR to DATETIME error

on a column DateNew = DateTime

i am trying :
INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')

and i get an error :
conversion of char data type to datetime data type resulted in an out of range datetime value

I had never this error before , do you know why ?
i must enter a yyyy/mm/dd format because this database will be used for Fr and Us langages

thank you for helpingIm not getting any error with ur insert statement.I think u are passing date value as a variable which is in char.

try trim that variable on both side using ltrim,rtrim before inserting.|||lookup SET DATEFORMAT in Books online. Maybe that would help.|||You can try this -
insert <tablename>
select convert(datetime,'2003/01/31 10:04:14')

Well, for more info check this out...
http://groups.google.co.in/group/microsoft.public.sqlserver.programming/browse_frm/thread/ada70fc46e7005ac/419fae5346ac4bc0?lnk=st&q=dateformat()+in+ms+sql+server+200&rnum=3&hl=en#419fae5346ac4bc0|||try this instead --

INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')|||INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003/01/31 10:04:14')

insert <tablename>
select convert(datetime,'2003/01/31 10:04:14')

or this one

try this instead --

INSERT INTO [dbo].[Users] (DateNew) VALUES ('2003-01-31 10:04:14')
__________________

These statements all are working fine in my machine also,so no problem in statements...I think mailler made a point ..plz check that.
Joydeep|||i got it with
INSERT INTO [dbo].[Users] (DateNew) VALUES (convert(datetime,'2003/01/31 10:04:14',111))

thank you|||i got it with convert(datetime,'2003/01/31 10:04:14',111)

thank you|||I know that I'm being pendantic here, but I'd invest a bit of time now into making your application much more portable/flexible/etc. The ISO 8601 (http://www.iso.org/iso/en/prods-services/popstds/datesandtime.html) format for date/time information is CCYY-MM-DD HH:MM:SS.TTT and that format is used by virtually the entire computing universe. It has been adopted by W3C (http://www.w3.org/TR/NOTE-datetime) which means that almost anywhere you find time on the Internet, you'll find it in this format.

You'll almost certainly save yourself lots of time and energy if you switch to using this format now, instead of having to switch to it later!

As a side note, if you expect your application to grow to the point where you may need to support more than one server, I'd suggest you spend the time to convert your application to use UCT (aka GMT) now too... This is easy to do up front, and almost impossible to do "after the fact" due to many very difficult problems caused by different locales.

-PatP|||...due to many very difficult problems caused by different locales...
PatP
You mean time zones, right?|||You mean time zones, right?You can think of the problem that way, but it is really more complex than just time zone... A locale rolls the problem up into a nice tidy (but not simple bundle). Time zones reflect a difference between local time and UCT, essentially a time offset. The problem comes from Daylight Savings Time, where different locales observe different shift dates, not all of which adjust by the same amount (some only move 30 minutes).

Unravelling the mess is easy if done while recording an event because it is easy for a computer to find UCT from its local time if necessary. Once the time is stored, there may not be any way to recover true UCT again. This gets really hard to explain, but there have been a couple of good whitepapers done on the problem.

-PatP|||Thanks a lot I shall do it at once but HOW do you convert a normal date into ISO 8601 ?

what is the SQL command for it ?|||for the moment I store all my dates time inthe format
yyyy/MM/dd hh:mm:ss

2000/12/31 18:50:06
I have just to replace / by - ?

thanks a lot|||for the moment I store all my dates time inthe format
yyyy/MM/dd hh:mm:ss

2000/12/31 18:50:06
I have just to replace / by - ?

thanks a lotYes! Exactly.

This is a relatively small change "up front", but it makes your date/time format match the format used by nearly everything else. That makes your code much easier to port to other programming languages, databases, etc at a later time. It is a small investment up front, that can pay off hugely in the future.

As a side note, SQL Server stores the data internally in a completely different form... Once you get the data into a column or variable, the work has been done. The only place you need to change anything is in the actual conversion from a character representation to a DATETIME.

-PatP|||Pat I did it , I have inserted 100 rows in my SQL database
in the good format but the database seems change it for the french format
31/12/2005 18:20:45

Once you get the data into a column or variable
I store the date in a datetime format column ?!

thanks a lot

and for searching any row in my database where a date
>
<
=
<>
=<
<=
to another date but on the date not on the datetime (yyyy-MM-dd) ?|||What is actually happening is that the database stores the value internally as a bunch of bits... They don't look like anything to the average human eye, and are logically close to a pair of integer counters. When your application retrieves the DATETIME value, the client component of the software converts those bits to a human readable form based on the locale that I mentioned in an earlier post, and the rules for that conversion happen to make the converted text appear "French" on your machine.

There are a number of ways to search for dates within a range (such as entered at any time on a given date). I prefer to do this by finding the minimum value (the very start of the day, at midnight) and the maximum value (or just past the maximum value if that is easier), then finding values between the minimum and maximum that I've selected. So for instance to find values that happened on Saint Valentine's Day 2006, I'd use:SELECT *
FROM myTable
WHERE '2006-02-14' <= myDate -- Note "equals"
AND myDate < '2006-02-15' -- Note no "equals"Using this logic is a bit strange at first, but it allows the database to use indicies to find dates of interested quickly and easily. That makes it possible to pick the rows for one day out of ten years worth of data in seconds instead of hours!

-PatP|||you helped me a lot Pat !!! in a few answers more than a few weeks looking everywhere, thanks a TON

a last question !!

I am using now

WHERE CONVERT(CHAR(10), myDate, 120) = CONVERT(CHAR(10), myDateValue, 120)

it is very easy with server side language to generate it, but for the database on millions of rows (the application will be very big) is it faster or slower than your method ?

WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'

because with your method I must add a day to the normal date and it is more complicated for server side programming

thanks again for helping|||I am using now

WHERE CONVERT(CHAR(10), myDate, 120) = CONVERT(CHAR(10), myDateValue, 120)

it is very easy with server side language to generate it, but for the database on millions of rows (the application will be very big) is it faster or slower than your method ?slower, much slower

first of all, you don't have to convert a datetime value such as '2006-02-15' to datetime, as you do on the right side of that condition, because the database will treat it that way (as a datetime value) by default

however, if you convert your table column to a string, as you do on the left side of that condition, then the database cannot use the index, if any, on that column, and will do a table scan

in other words, performing a function on a column means that the condition is not sargable (http://netknowledgenow.com:81/CS/blogs/onmaterialize/archive/2006/01/11/65.aspx) (this link is not working today but it was fine yesterday, it's a really good explanation -- you can also do a quick search to find other articles which also explain that word)|||then i must absolutly keep this only way ? :

SELECT FROM myTable
WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'

but in my database datetimes are stored in that was yyyy/mm/dd hh:mm:ss
and for the moment i couldnt get any row comparing yyyy/mm/dd hh:mm:ss to yyyy/mm/dd

of course the column is a datetime datatype|||then i must absolutly keep this only way ? :

SELECT FROM myTable
WHERE '2006-02-14' <= myDate AND myDate < '2006-02-15'that is the only way to achieve good performance (except you need to change the first operand from <= to >=)

but in my database datetimes are stored in that was yyyy/mm/dd hh:mm:ssno, actually, they are not stored that way -- datetimes are stored as two integers|||and is it better to use a datetime columns or a smalldatetime
all my dates are starting after 2000 ?

thank you|||that depends on whether you need precision in the time|||smalldatetime : Date and time data from January 1, 1900, through June 6, 2079,
with an accuracy of one minute

datetime :Date and time data from January 1, 1753, through December 31, 9999,
with an accuracy of 3.33 milliseconds

if i dont need (who needs ?) a precision of 1 minutes is it better for performances on millions of rows to use smalldatetime ?|||now I get all int that way and it seems to work :

-----------

>= 2006-02-10

SELECT FROM Users
WHERE (DateColumn > '2006-02-11')

-----------

< 2006-02-10

SELECT FROM Users
WHERE (DateColumn < '2006-02-10')
-----------
<= 2006-02-10

SELECT FROM Users
WHERE (DateColumn < '2006-02-11')
-----------

= 2006-02-10
SELECT FROM Users
WHERE
(DateColumn >= '2006-02-10')
AND
(DateColumn < '2006-02-11')

-----------

<> 2006-02-10

SELECT FROM Users
WHERE
(DateColumn > '2006-02-11')
OR
(DateColumn <= '2006-02-10')
-----------

>= 2006-02-15

SELECT FROM Users
WHERE (DateColumn < '2006-02-15')|||if i dont need (who needs ?) a precision of 1 minutes is it better for performances on millions of rows to use smalldatetime ?Yes, a SMALLDATETIME will perform better than a DATETIME for many reasons. Maybe looking at things from the machine's perspective will help (and maybe that will just confuse issues even more):DECLARE @.d DATETIME, @.s SMALLDATETIME

SELECT @.d = GetUTCDate()
SELECT @.s = @.d

SELECT @.d, Convert(VARBINARY(20), @.d)
SELECT @.s, Convert(VARBINARY(20), @.s)

SELECT @.d = DateAdd(minute, 1, @.d)
SELECT @.s = @.d

SELECT @.d, Convert(VARBINARY(20), @.d)
SELECT @.s, Convert(VARBINARY(20), @.s)-PatP|||thanks again a lot Pat that was really usefull, a deep help

thank you to everybody|||2006-02-16 03:10:53.967 | 0x0000976A00346E9E

2006-02-16 03:11:00 | 0x976A00BF

2006-02-16 03:11:53.967 | 0x0000976A0034B4EE

2006-02-16 03:11:53.967 | 0x0000976A0034B4EE

2006-02-16 03:12:00 | 0x976A00C0

here is the result of your Query
not easy to read and understand|||hard to understand?

these numbers -- 0x0000976A00346E9E, 0x976A00BF -- show you exactly how datetime values are stored internally in sql server

:)|||not easy to read and understandThe results show a couple of the issues that I was trying to explain, in a concrete form (so we don't have to talk abstractly, but can deal with real values. Please bear with me, this explanation is long, but I think it will help.

The first two results show the difference between a DATETIME (as displayed in character form) and how that DATETIME value converts to both raw binary (on the same line), and how it converts to a SMALLDATETIME (which appears one line down), and also to the SMALLDATETIME expressed as raw binary. All of the values are different, but they represent the same moment in time in different ways!

A DATETIME is accurate to +/- 3 milliseconds (I know the docs say 3.33, but that is a case where the doc writers took some liberty with what is actually stored... There's no such thing as a third of a bit). It is actually stored as a bunch of bits that mean very little to the untrained eye, except for one minor thing I'll get to later.

A SMALLDATETIME is accurate to +/- 1 minute. When you assign a DATETIME to a SMALLDATETIME, rounding takes place to the nearest whole minute. The same time is represented in a slightly less acurate form. The binary value is also quite a bit smaller, and radically different.

As an interesting side note (of little practical value), note that the value 976A appears in both of the binary strings (although in different places). This is not an accident. It has to do with how the date values are actually stored.

Another more interesting note is that the binary values of a DATETIME and the corresponding SMALLDATETIME are not directly comparable. If you take the time to understand the details, you can work around this, but it is of very little use except as an academic exercise.

Things become a bit more interesting when we add a minute to the original DATETIME value. The character form makes it easy to see this addition, and it makes perfect sense.

When we convert the changed DATETIME value to a SMALLDATETIME, the same rounding takes place, and the binary values are still quite different from each other.

The interesting part comes when you compare the binary values of the DATETIME before and after adding a minute, and the binary values of the SMALLDATETIME before and after adding a minute. The important part to notice is that the binary values of the later values have larger binary values too! There is a direct, one to one relationship between the time and the corresponding binary value.

This is why Rudy pointed out earlier that computing the minimum and maximum values of interest was much more efficient than converting the date values to character form and comparing them... You can convert a DATETIME into many different character forms, most of which are not usable for range computations like this, so in order to search for a character form SQL Server has to query every possible row. SQL Server understands dates as either DATETIME or SMALLDATETIME values, and knows how to search an index for values in a specific range. This means that it can "ride the index" to only the rows of interest in your range, and it knows exactly when it has reached the end of that range. For large sets of data, this is MANY times more efficient!

-PatP|||wow ! perfect !
I never like to apply something without understanding it
now I get a better idea of the way SQL is working with dates

and I think that generally i need only smalll
datetime datatype, i had never used it before

thanks a lot once more !|||One more thing to throw out, just so you don't get surprised... The SMALLDATETIME datatype allows entry of temporal (time based) values from 1900-01-01 through 2079-06-06 23:39. This is fine for many purposes (I won't be alive to deal with any problems it might cause when it runs out, and I sincerely doubt that SQL Server will still be in use (at least in its present form) 70 years from now! However, many contracts (like Japanese mortgages) already extend well past that limit, so I usually use DATETIME even though a SMALLDATETIME would do.

I'm a lazy bum... If I can code/create something once then safely forget about it, I'll almost always do that instead of something a bit simpler that will work for a while, but might be a problem for me (or my successor) in the future. I won't do a lot of work to avoid a potential problem, but I'll do easy things to avoid getting a call at 03:00 wondering why a job failed and how soon can I get it fixed!

-PatP|||Pat your code works fine for me in SQL 2005, but a customer with SQL 2000 get errors again everywhere on dates, i have started a new thread here >> http://dbforums.com/showthread.php?t=1212861

i am really lost with dates .. and i dont know what to do

thanks again

Conversion failed when converting the varchar value

I keep receiving this error:

Conversion failed when converting the varchar value 'INSERT INTO temp_tableZ (Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, time_member_key, Revenue, Fiscal_Year ) select Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, ' to data type int

When running the code below. I believe the problem has something to do with the @.time_member_key in the dynamically created SELECT statement. However, I don't understand the problem or how to fix it.

Can anyone provide some advice?

thank you,

Erik

droptable temp_tableZ
go

createtable temp_tableZ(
Customer_Category_Key INTNULL,
Item_Category_Key INTNULL,
Sales_Rep_Key INTNULL,
Revenue moneyNULL,
time_member_key INTNULL,
Fiscal_Year INTNULL)
go

dropprocedure temp_testA
go

createprocedure temp_testA as
BEGIN
declare @.time_member_key asint
declare @.calendar_date_dt asdatetime
declare @.FY0YTD asMONEY
declare @.sql_string asnvarchar(1024)
set @.calendar_date_dt ='10/1/2005'
while @.calendar_date_dt <'9/30/2006'
BEGIN

select @.time_member_key = time_member_key
from dim_time
where @.calendar_date_dt = dim_time.calendar_date_dt

SET @.sql_string =
'INSERT INTO temp_tableZ'+' ('+
'Customer_Category_Key, '+ 'Item_Category_Key, '+ 'Sales_Rep_Key, '+ 'time_member_key, '+
'Revenue, '+ 'Fiscal_Year '+ ') '+

'select Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, '+ @.time_member_key +', sum(Revenue), 2006'+char(13)+
'from Fact_Sales t1, dim_time t2 '+char(13)+
'where t1.time_member_key = t2.time_member_key'+char(13)+
'and t1.time_member_key < '+ @.time_member_key +char(13)+
'and t2.Fiscal_Year = 2006'+char(13)+
'group by Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, '+ @.time_member_key +char(13)+
'go '

EXECsp_executesql @.sql_string

set @.calendar_date_dt = @.calendar_date_dt + 1

END

END

GO

execute temp_testA

You need to convert @.time_member_key to an NVARCHAR(x), see below.

Read this document on 'Data Type Precedence' to find out why:

http://msdn2.microsoft.com/en-us/library/ms190309.aspx

Chris

SET @.sql_string =

'INSERT INTO temp_tableZ' +' (' +

'Customer_Category_Key, ' + 'Item_Category_Key, ' + 'Sales_Rep_Key, ' + 'time_member_key, ' +

'Revenue, ' + 'Fiscal_Year ' + ') ' +

'select Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, ' + CAST(@.time_member_key AS NVARCHAR(20)) + ', sum(Revenue), 2006' + char(13) +

'from Fact_Sales t1, dim_time t2 ' + char(13) +

'where t1.time_member_key = t2.time_member_key' + char(13) +

'and t1.time_member_key < ' + CAST(@.time_member_key AS NVARCHAR(20)) + char(13) +

'and t2.Fiscal_Year = 2006' + char(13) +

'group by Customer_Category_Key, Item_Category_Key, Sales_Rep_Key, ' + CAST(@.time_member_key AS NVARCHAR(20)) + char(13) +

'go '

Conversion failed when converting from a character string to uniqueidentifier.

Hi, i have a problem, i keep getting this Error.

I want to insert an uniqueidentifier using a textbox, i use the following code to insert.

SqlDataSource1.InsertParameters[

"RWID"] =newParameter("RWID",TypeCode.String, RWID);

SqlDataSource1.Insert();

The databasetype is an uniqueidentifier of that column.

Anyone who can help me with this problem?

Hi friend,

Have you tried TypeCode.Object

|||

I tried using TypeCode.Object, then I get another error:

Implicit conversion from data type sql_variant to uniqueidentifier is not allowed. Use the CONVERT function to run this query.

|||

Hi friend,

I tried a sample to reproduce the error. But it its working fine for me. I created a table named t1 with one column c1 of datatype unique identifier.

SQL datasource code

<asp:SqlDataSourceID="SqlDataSource1"runat="server"ConnectionString="<%$ ConnectionStrings:iGoldWebConnectionString %>"

SelectCommand="SELECT * FROM [T1]"InsertCommand="insert into t1 values(@.g)" ></asp:SqlDataSource>Data Insert Code

SqlDataSource1.InsertParameters["g"] =newParameter("g",TypeCode.String,Guid.NewGuid().ToString());

SqlDataSource1.Insert();

Its working fine for me.

I hope the problem is with the guid which you get from textbox . Have you checked you receive only valid GUID.

Controlling Updated fields on trigger

In myTable i've got a trigger for INSERT, UPDATE, DELETE
I Update myField1 of myTable in the trigger but not in the query that starts
the trigger.
Then i check if UPDATE(myField1). This is true or false?
CREATE TRIGGER tr_MyTable ON dbo.MyTable
FOR INSERT, UPDATE, DELETE
AS
...
UPDATE myTable SET myField1 = 'XYZ' WHERE ...
...
if update(myField1 ) /* IS TRUE OR FALSE ' */
begin
..
endFalse.
The UPDATE( ) clause comes from the state of the data that fired the
trigger, not from any manipulations inside the trigger.
Also, FWIW, if your update sets a column to the same value it had before the
update, that will also be considered an updated column. (Since I did not
find the clause useful, I stopped using it. If it is smarter in 2000,
someone should know.)
RLF
"checcouno" <checcouno@.discussions.microsoft.com> wrote in message
news:9B1547D0-05DA-438B-947D-8DFDA29FE77A@.microsoft.com...
> In myTable i've got a trigger for INSERT, UPDATE, DELETE
> I Update myField1 of myTable in the trigger but not in the query that
> starts
> the trigger.
> Then i check if UPDATE(myField1). This is true or false?
> CREATE TRIGGER tr_MyTable ON dbo.MyTable
> FOR INSERT, UPDATE, DELETE
> AS
> ...
> UPDATE myTable SET myField1 = 'XYZ' WHERE ...
> ...
> if update(myField1 ) /* IS TRUE OR FALSE ' */
> begin
> ...
> end
>|||Russell Fields (RussellFields@.NoMailPlease.Com) writes:
> Also, FWIW, if your update sets a column to the same value it had before
> the update, that will also be considered an updated column. (Since I
> did not find the clause useful, I stopped using it. If it is smarter in
> 2000, someone should know.)
I have not tried it, but there is no reason to expect it to be smart.
If you update 1000 rows, and one of them changes value, what should IF
UDPATE return?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

control transaction in sybase database using sql-server 2000

Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction

Create procedure myCurrentDataBaseProcedure
as
begin

begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end

The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.

But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions

Pl.Helpcan anyone help me regarding this topic|||what was the error?
go thru books on line (BOL) about
sp_addlinkedserver and sp_addlinkedsrvlogin.

Originally posted by RajiniKasturi
Can anyone help me with this scenario!!!!
I have a sybase database and a sqlserver 2000 database.
I want to insert data into sybase database table thru sql-server 2000 using distributed queries
When i execute the following the transaction

Create procedure myCurrentDataBaseProcedure
as
begin

begin tran
insert into mytable values(1)
if @.@.error <>0
begin
rollback transaction
return
end
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
if @.@.error <>0
begin
rollback transaction
return
end
commit transaction
end

The procedure is created in sql server database
trying to execute this procedure..shows error
The first part of the procedure is executed.

But the error is here
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)
The data is succesfully inserted in the local database
I am unable to insert data into the remote database
Can anyone suggest me wht shd i do in this scenario
Are there any drivers to be loaded to commit this transactions

Pl.Help|||Thanks for the reply

See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.

But if i have all the sql statements as a single stored procedure
i am unable to control the transaction

That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback

when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)

the error says remote server not found.

but if i execute the single statement it is working perfectly

Wht could be wrong in the transaction.|||I guess it should be BEGIN DISTRIBUTED TRANSACTION
instead of begin tran.
let me check it out.

Originally posted by RajiniKasturi
Thanks for the reply

See, actually I dont have any problem connecting to the remote nor executing the individual statement which i mentioned in the code.

But if i have all the sql statements as a single stored procedure
i am unable to control the transaction

That is I shd first insert some values into the local server database.table then the same data shd get inserted in the remote server. If both are correct then the transaction shd be committed else both shd rollback

when i execute the second statement
insert into sybasedatabaseserver.databasename.dbo.tablename values(1)

the error says remote server not found.

but if i execute the single statement it is working perfectly

Wht could be wrong in the transaction.|||Thanks again for the prompt reply

It is not working with that option too
Is there is anyway I can achieve this??
Can u pl.help me

Continue on INSERT error.

Hi!

Imagine this SQL statement:

Code Snippet

INSERT INTO B SELECT * FROM A

If one of the insert fails ... don't continue, the statement fail. For example if any field in A violate a constraint in B, the statement fails.

I want that the statement continue if errors occurs, if i lost a number of rows don't matter ... but if i can save or log this row will be great too !!

Is posible? Any way to do it?

Regards.

Make two statements, by adding a WHERE clause, you can verify the CONSTRAINT and add rows ONLY if the CONSTRAINT passes. Then in the second statement, in the WHERE clause, get the rows that do not pass.

FOR illustration:

Code Snippet

SET NOCOUNT ON

DECLARE @.MyTable table
( RowID int IDENTITY,
Name varchar(20) PRIMARY KEY
)

INSERT INTO @.MyTable VALUES ( 'Bill' )

DECLARE @.MyOtherTable table
( RowID int IDENTITY,
Name varchar(20)
)

DECLARE @.Failures table
( RowID int,
Name varchar(20)
)

INSERT INTO @.MyOtherTable VALUES ( 'Bill' )
INSERT INTO @.MyOtherTable VALUES ( 'Mary' )
INSERT INTO @.MyOtherTable VALUES ( 'Omar' )

-- First, isolate the CONSTRAINT Failures
INSERT INTO @.Failures
SELECT
t.RowID,
t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name = t.Name

-- Insert the rows that pass the CONSTRAINT test
INSERT INTO @.MyTable ( Name )
SELECT t.Name
FROM @.MyOtherTable t
JOIN @.MyTable m
ON m.Name <> t.Name

SELECT *
FROM @.MyTable

RowID Name
-- --
1 Bill
2 Mary
3 Omar

SELECT *
FROM @.Failures

RowID Name
-- --
1 Bill

|||

Thanks for your reply.

I will write my question in another way. What I want is if I can change the SQL/Server constraint behaviour when a error is thrown. I know that I can do the insert with a "WHERE" clause. But it some cases is useful to perform your own behaviour when the table has a lot of fields and a lot of rows and you are using a INSERT ... SELECT ... clause. There is some utility (NOTIFICATION, TRIGGERS) that help to do this in a speedy way?

Regards.

|||

A CONSTRAINT failure occurs BEFORE the data is inserted into the table -so a AFTER INSERT TRIGGER would not work.

You could create a BEFORE INSERT TRIGGER, but then you would STILL have to use the two step process I demonstrated in my earlier post. And there may be increased locking and blocking behavior as a result of using a TRIGGER.

Bottom line is that the CONSTRAINT prevents the data from getting into the table. Without the data getting to the table, there is little to offer in the form of Notifications, etc., and you are also, pardon the ironic pun, constrained in the ability to use a TRIGGER.

|||

OK! Thanks.

Regards.

Continue INSERT after key violation?

Hi there.
I have a simple table with a UNIQUE constraint on one field. I wish to
populate this table using a stored procedure that returns the equivalent
results set like so:
INSERT INTO MyTable EXECUTE p_GetMyResultsSet
My stored proc returns unique, distinct results each time it's called
(unique within each call!). The problem I'm having though is that, if the
stored proc returns any record that already exists in the table, the whole
statement quits with a 'constraint violation'.
I would like the statement to continue inserting the results - only leaving
out records which fail the criteria - is this possible?
e.g: 1. p_GetMyResultsSet returns one row with a key value of '1' - record
is inserted into the table OK.
2. p_GetMyResultsSet returns 4 rows with key values of 1, 2, 3, 4.
What happens now: none of the records are inserted because '1' already exist
s.
What I'd like to hapen: records 2, 3, 4 to be inserted.Can't you change the proc so that it only returns the rows that don't
exist? For example:
SELECT x, ...
FROM foo
WHERE NOT EXISTS
(SELECT *
FROM MyTable
WHERE x = foo.x) ;
If not, you could insert the results of the proc to a temp table and
then to MyTable using the same WHERE NOT EXISTS logic.
David Portas
SQL Server MVP
--|||Yes - I think I'll have to use the temp table approach. I'm restricted from
using the 'not exists' because the stored proc needs to be available for use
by other apps and processes- the "MyTable" population is just one process o
f
many using the proc to generate data...
Thanks for the help!
"David Portas" wrote:

> Can't you change the proc so that it only returns the rows that don't
> exist? For example:
> SELECT x, ...
> FROM foo
> WHERE NOT EXISTS
> (SELECT *
> FROM MyTable
> WHERE x = foo.x) ;
> If not, you could insert the results of the proc to a temp table and
> then to MyTable using the same WHERE NOT EXISTS logic.
> --
> David Portas
> SQL Server MVP
> --
>

continually increasing number of open connections

Hi,
I have an application that uses batch updates to insert data into the
database.
The sequence is basically:
Statement.prepareCall()
loopForever
{
GetDataFromSomewhere
CallableStatement.clearBatch()
CallableStatement.addBatch()
CallableStatement.addBatch()
...
CallableStatement.addBatch()
CallableStatement.executeBatch()
}
The application uses the same Statement object which is never closed.
However, the number of connections is constantly growing (netstat & lsof
shows 100,000 connections to the database after several hours and after that
the machine crashed...).
It appears that the driver creates a connection for every statement in the
batch (or at least for each batch). Should it work in this way? is there a
way to use the same connection for the entire batch?
Also, is there way to prevent this connections leak? I'd like to use the
same Statement object to eliminate the need for prepareCall() on each batch
in order to get improved performance.
Any help will be appreciated.
Thanks,
Noam
Noam,
In "selectMode=direct" the MS driver (and all other DataDirect based
drivers) create a "cloned" connection (i.e. a new physical connection)
for every new statement. That may be the source of your problem if you
are creating Statement objects inside a loop.
If you are absolutely sure you are not doing that and that there are no
other places in your code where this might happen, then I can only
recommend you try another (non-DataDirect) driver and see if the same
happens.
Alin.
|||I've tried using selectMode=cursor but it does not help. This is a single
thread that access the database so there are no other places that create new
statenet. Also, this started to happen when I decied to imrpove the code and
use batch mode.
In the mean time, I'm creating a new statement inside the loop (and closing
it at the end if the look) and it works OK.
Thanks,
Noam
|||"Noam Ambar" <NoamAmbar@.discussions.microsoft.com> schrieb im Newsbeitrag
news:37218689-6264-4B3C-B8D9-5EDC05385136@.microsoft.com...
> I've tried using selectMode=cursor but it does not help. This is a
single
> thread that access the database so there are no other places that create
new
> statenet. Also, this started to happen when I decied to imrpove the code
and
> use batch mode.
> In the mean time, I'm creating a new statement inside the loop (and
closing
> it at the end if the look) and it works OK.
Do you ever commit in between? Or is this autocommitted?
robert
|||It is true that the Microsoft SQL Server JDBC driver creates "cloned
connections" and that earlier versions of the DataDirect SQL Server JDBC
driver did as well. The current 3.4 DataDirect SQL Server JDBC driver does
not clone connections.
Sue Purkis
DataDirect Technologies
our current 3.4 SQL Server JDBC driver does not clone connections anymore
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1109516553.438410.284260@.o13g2000cwo.googlegr oups.com...
> Noam,
> In "selectMode=direct" the MS driver (and all other DataDirect based
> drivers) create a "cloned" connection (i.e. a new physical connection)
> for every new statement. That may be the source of your problem if you
> are creating Statement objects inside a loop.
> If you are absolutely sure you are not doing that and that there are no
> other places in your code where this might happen, then I can only
> recommend you try another (non-DataDirect) driver and see if the same
> happens.
> Alin.
>
|||Sue Purkis wrote:
> It is true that the Microsoft SQL Server JDBC driver creates "cloned
> connections" and that earlier versions of the DataDirect SQL Server
JDBC
> driver did as well. The current 3.4 DataDirect SQL Server JDBC
driver does
> not clone connections.
Sue,
Thanks for the update; I didn't know about that. So does this mean that
"selectMethod=direct" now supports transactions (autoCommit == false)
too?
Alin,
The jTDS Project.
|||Alin,
Yes, it is true that selectMethod=direct now supports transactions with
the DataDirect 3.4 SQL Server driver.
Sue
DataDirect Technologies
"Alin Sinpalean" <alin@.earthling.net> wrote in message
news:1109930907.961426.268700@.g14g2000cwa.googlegr oups.com...
> Sue Purkis wrote:
> JDBC
> driver does
> Sue,
> Thanks for the update; I didn't know about that. So does this mean that
> "selectMethod=direct" now supports transactions (autoCommit == false)
> too?
> Alin,
> The jTDS Project.
>

CONTAINS

Hi,
I have a table like this:
CREATE TABLE T1(
C1 int identity(1,1) PRIMARY KEY,
C2 NVARCHAR(50))
INSERT T1(C2) VALUES('test')
INSERT T1(C2) VALUES('Xtest')
INSERT T1(C2) VALUES('testX')
Assuming that C2 is enabled for FTS, this query does not return "Xtest":
SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
It returns only "test" and "testX". How can I do that?
Thanks in advance,
Leila
"Leila" <Leilas@.hotpop.com> wrote in message
news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have a table like this:
> CREATE TABLE T1(
> C1 int identity(1,1) PRIMARY KEY,
> C2 NVARCHAR(50))
> INSERT T1(C2) VALUES('test')
> INSERT T1(C2) VALUES('Xtest')
> INSERT T1(C2) VALUES('testX')
> Assuming that C2 is enabled for FTS, this query does not return "Xtest":
> SELECT * FROM T1 WHERE CONTAINS (*,'"*test*"')
> It returns only "test" and "testX". How can I do that?
You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
searches, and so the leading * is ignored. Another option would be to fall
back to LIKE when a leading * is used.
Dan
|||I think you mean prefix (comes before) which SQL FTS does not support. SQL
FTS does support suffix (comes at the end) type searches when you use the
wildcard operator in the Contains predicate.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:e1DACM3iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:O7ujWH2iFHA.3300@.TK2MSFTNGP10.phx.gbl...
> You can't, unless you use "Xtest" or "Xtest*". FTS doesn't handle suffix
> searches, and so the leading * is ignored. Another option would be to fall
> back to LIKE when a leading * is used.
> Dan
>
|||"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
>I think you mean prefix (comes before) which SQL FTS does not support. SQL
> FTS does support suffix (comes at the end) type searches when you use the
> wildcard operator in the Contains predicate.
Possibly, I can't remember which way around they go. If it refers to the
string part, it's suffix that isn't supported as the wildcard will be the
prefix. Thanks for pointing out my error though
Dan
|||Thank you all :-)
Then LIKE will be the only poosible way?
"Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...[vbcol=seagreen]
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:%23RPqSH4iFHA.3064@.TK2MSFTNGP15.phx.gbl...
SQL[vbcol=seagreen]
the
> Possibly, I can't remember which way around they go. If it refers to the
> string part, it's suffix that isn't supported as the wildcard will be the
> prefix. Thanks for pointing out my error though
> Dan
>
|||Unfortunately so.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Leila" <Leilas@.hotpop.com> wrote in message
news:ehN0lR%23iFHA.576@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> Thank you all :-)
> Then LIKE will be the only poosible way?
>
> "Daniel Crichton" <msnews@.worldofspack.co.uk> wrote in message
> news:ek0j$34iFHA.3216@.TK2MSFTNGP10.phx.gbl...
> SQL
> the
the
>

constraint/trigger in sql server in Asp.net

In my Project

i want to check the date at the time of insert in A-Table

that it should be Greater than (>) Date Defined in B-Table

Note:-B-table have only one record

so plz tell me how can i do using Sql-Server Backend only

Hopefully you are using a stored proc wherein you could:

CREATE PRO...@.param1 ..@.param2datetimeDeclare @.datefromBdatetimeSELECT @.datefromB = datecolumnFROM tableBIF @.param2 > @.datefromBBEGIN--Do the insert hereENDELSEBEGIN

-- dp whatever needs to be done return 0END

|||But where i defined that stored procedure in my code|||Looks like you need DB 101. Check out documentation for "stored procedures".

CONSTANTS in bcp .fmt files

Hi,
How can we insert CONSTANTS in table columns using bcp's .fmt files? In case SQLLoader, we can specify CONSTANTs in .ctl files. How can it be done in case of bcp utility?
Thanks in advance
VijayYou can't! BCP does not support this.|||Originally posted by Paul Young
You can't! BCP does not support this.

Any workaround used normally?

Thanks|||Either add the data to your file before BCPing or run an update script to add the data after BCPing the file.|||Originally posted by vijay_kumar74
Any workaround used normally?

Thanks

What do you mean by Constant?

UPDATE myTable SET col1 = 'Something'

???????????????|||Yes, I think I'll go by updating table columns with the "CONSTANT" (e.g. $PROGRAM_NAME) values after running bcp.

Thanks for the help!|||You can check %ERRORLEVEL% to see if the BCP went okay and then follow up with an OSQL command to update the table. Not eloquent but functional.

Consolidation - Changing replicated data in a central subscribing site

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

Hi,

Do you use transactional replication and merge replication? For transactional replication, you can use @.ins_cmd parameter in sp_addarticle to create your custom insert stored procs. For details, you can refer to the following documents: http://msdn2.microsoft.com/en-us/library/ms152489.aspx. For readonly transactional replication, the replication is one way and the changes won't be relayed back to the publisher.

Peng

|||

Hi,

Thank you for your response. We're not actually using any kind of replication yet, merely planning to do so. But it seems from what you're saying that "readonly transactional replication" fits the bill nicely.

Cheers,

Dag

Consolidation - Changing replicated data in a central subscribing site

Hi all,

I am new to replication and have a few questions.

1) Are there any "hooks" available to insert processing when a subscriber is about to copy data from a replicating site?

2) Is it possible for a subscriber to change only his local copy of the data - without replicating the changes back to the publisher?

I realise that once the data changes in one place it isn't really replicated anymore, and I realise that my limited knowledge of the subject might well mean I'm not even asking the right questions. Therefore, I shall try to describe as best I can my scenario.

I wish to use many servers for transactional input (to distribute the workload) and use replication to publish the inputted data to a subscribing central site. One of the tables I wish to replicate has an identity column as primary key, but the records should otherwise be unique - i.e. no two records should differ only in the value of the key. Another table, which should also be replicated, uses this id value as a foreign key.

I can use the identity increment and seed to guarantee no key violations will occur when copying data to the central server. However, there is another issue: Several servers can create the same record but with different id values.

I need to "merge" such records by deleting duplicate entries in the table with the identifier as primary key, and update the foreign keys correspondingly. To clarify (I hope!), here's an example of what data I might have on the central site after copying data from two input sites:

TRANSACTION table

amount = 200, metadata_id = 1001 // Replicated from server INPUT_1

amount = -117, metadata_id = 2001 // Replicated from server INPUT_2

METADATA table:

id=1001 Actitiy=Sales, Country=USA

id=2001 Activity=Sales, Country=USA

What I would like is basically for the central site to identify that metadata 2001 is really the same as metadata 1001, update the foreign key in the TRANSACTION record accordingly and not import (or delete, if this "merging" is done in a post-treatment) the duplicate metadata record.

If anyone can offer any advice on how to achieve this I would appreciate your input.

Hi,

Do you use transactional replication and merge replication? For transactional replication, you can use @.ins_cmd parameter in sp_addarticle to create your custom insert stored procs. For details, you can refer to the following documents: http://msdn2.microsoft.com/en-us/library/ms152489.aspx. For readonly transactional replication, the replication is one way and the changes won't be relayed back to the publisher.

Peng

|||

Hi,

Thank you for your response. We're not actually using any kind of replication yet, merely planning to do so. But it seems from what you're saying that "readonly transactional replication" fits the bill nicely.

Cheers,

Dag