Problem with SSIS and auto-increment of an ID

Hello,
I have a little problem with SQL Server 2005. I have chosen the option "auto-increment" for a column which includes the primary key. Moreover, I turned the option "Ignore double values" on. I get the datasets from a SSIS-Import-project. Unfortunately, the ID is getting incremented even if there are double values.
For example:
The first entries in the table have the IDs: 1, 2, 3, 4, 5
Then I start the SSIS-project which tries to write the first 5 entries again in the database. Of course, the entries do not appear again in the db but a new entry gets the ID 11 instead of 6. Is there a setting, that the ID won't get incremented if there double values?
Thanks
M-l-GI have not used the "auto-increment" function but the only time I had to increment a value was when the field was a key in my db. I set the key as an Identity key and so the db takes care of the incrementing. Is this something you are able to do? Just a thought!sql

Problem with SQL Server 2000 DTS Package:Column Delimiter not found

I am trying to import data from a text file to SQL database through a DTS package. i am always getting "Column Delmimeter not found" error at record number 8000. i copied the 8000th record into the beginning of the file to test if there is any problem with data but still i got the error at 8000 th record. i know it was a problem before and was fixed with sp1. i installed SP4 and still gettting the same error.

any help on this is appreciated.

Thanks
Venki

Found the actual problem. The error record number that DTS shows is not accurate. Acutal error was at 8790 th record but the DTS shows that the error is at 8000. The data has Quotes in it which was causing the problem.

Thanks
Venki|||

Hi

I'm having the exact same problem, with sp4 installed, and i can't find a solution

did u find a way to solve that problem?

Problem with SQL Server 2000 DTS Package:Column Delimiter not found

I am trying to import data from a text file to SQL database through a DTS package. i am always getting "Column Delmimeter not found" error at record number 8000. i copied the 8000th record into the beginning of the file to test if there is any problem with data but still i got the error at 8000 th record. i know it was a problem before and was fixed with sp1. i installed SP4 and still gettting the same error.

any help on this is appreciated.

Thanks
Venki

Found the actual problem. The error record number that DTS shows is not accurate. Acutal error was at 8790 th record but the DTS shows that the error is at 8000. The data has Quotes in it which was causing the problem.

Thanks
Venki|||

Hi

I'm having the exact same problem, with sp4 installed, and i can't find a solution

did u find a way to solve that problem?

Problem with sql

I've got 2 table :department and employee. There's a column called deptNo reference employee to department as a foreign key.

1) When I want to delete the employee table, it gives me this error:
Server: Msg 3726, Level 16, State 1, Line 1
Could not drop object 'Employee' because it is referenced by a FOREIGN KEY constraint.

2) If I enter the statement below:
CREATE TABLE Employee(EmployeeNo int PRIMARY KEY, EmployeeName varchar(10)UNIQUE, DeptNo int NOT NULL, JobTitle char(10),FOREIGN KEY(DeptNo)REFERENCES Department(DeptNo)ON UPDATE CASCADE ON DELETE CASCADE)
it gives me an error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'ON'.It looks like you had the wrong direction for your constraint, which is what you might be trying to correct. To eliminate the error you can delete the foreign key constraint (since you are recreating the table and constraint). For the 2nd part, try the following (I modified your version slightly) :

CREATE TABLE Employee(EmployeeNo int PRIMARY KEY, EmployeeName varchar(10)UNIQUE,
JobTitle char(10),deptno int NOT NULL FOREIGN KEY REFERENCES Department(DeptNo)ON UPDATE CASCADE ON DELETE CASCADE)|||What version of SQL Server are you running?

ON DELETE CASCADE is only supported in MS SQL Server 2000

Problem with SP3

Hi,
Couple of users got this error after installing (MSDE) SP3.
Does anybody know what this means and what to do?
Invalid column name 'published_in_tran_pub'.
(Source: xxxxxxxxxxxxx (Data source); Error number: 207)
-----The
process could not deliver the snapshot to the Subscriber.
(Source: Merge Replication Provider (Agent); Error number: -2147201001)
-----
In use: Merge replication and static filtered publications.
-Katja
This column is related to a new merge agent parameter added with sp3. Can
you check that sp3 has been fully (successfully) installed on distributor,
publisher and subscriber.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)

Problem with sp_repldropcolumn propagation

Hi,
Im tyring to modify a column datatype in a replicated table using
sp_repladdcolumn and sp_repldropcolumn. First I create a new column
that is propagated to the subscriber, but when I drop a column with
sp_repldropcolumn the change is not propagated to the subscriber.
Does anyone has an idea of why this is happening?
Thanks,
Diego
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/Replication-...ict237980.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=827285
Some columns cannot be dropped, i.e. columns which are part of PKs. Is this
the case? Can you drop it through EM? Right click on your publication,
select Properties, click on the Filter Columns tab and try to drop it there.
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
"Diego" <UseLinkToEmail@.dbForumz.com> wrote in message
news:4_827285_2d4aa9fc6f9f611260ea17bddcfcd7da@.dbf orumz.com...
> Hi,
> I'm tyring to modify a column datatype in a replicated table using
> sp_repladdcolumn and sp_repldropcolumn. First I create a new column
> that is propagated to the subscriber, but when I drop a column with
> sp_repldropcolumn the change is not propagated to the subscriber.
> Does anyone has an idea of why this is happening?
> Thanks,
> Diego
> --
> 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/Replication-...ict237980.html
> Visit Topic URL to contact author (reg. req'd). Report abuse:
http://www.dbforumz.com/eform.php?p=827285
|||"" wrote:
> Some columns cannot be dropped, i.e. columns which are part of
> PKs. Is this
> the case? Can you drop it through EM? Right click on your
> publication,
> select Properties, click on the Filter Columns tab and try to
> drop it there.
> --
> 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
> "Diego" <UseLinkToEmail@.dbForumz.com> wrote in message
> news:4_827285_2d4aa9fc6f9f611260ea17bddcfcd7da@.dbf orumz.com...
> using
> column
> column with
> subscriber.
> standards
> http://www.dbforumz.com/Replication-...ict237980.html
> abuse:
> http://www.dbforumz.com/eform.php?p=827285
Hillary,
The column is not a PK. Ive also could drop the column with
sp_repldropcolumn in the distributor, the problem is that the change
is not propagated to the subscription.
This is not the case with the sp_repladdcolumn that is added in both
places. For example to replace the field data type (thats what im
trying to do) where you run a sp_repldropcolumn before a
sp_repladdcolumn after the second command is run ive get an error in
the distribution agent that says it cannot add a column with the same
name.
Thank you very much,
Diego

Problem with size of text of title of column.

Hello :

In a title of a column of a table, I put a too long text, with sorting, but when I spread (display) the report has a problem on text of the title of the column there.

The probleme settles (arises) when I activate the sorting to post (show) the image of sorting.

How to fit the size of the cell to the text?

Thenk's.

.......|||Please click the Report Abuse link only if you have abuse to report. It is not going to attract attention to your post.

Problem with single quotes

Hi,
I've a column (type: varchar) in a table. I need to add single quotes in the
value while inserting the rows. But I get an error. Please see below:
create table #t1 (address varchar(20))
go
insert into #t1 values ('St John's street')
Error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 's'.
Server: Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark before the character string ')
Since the database is on hosting providers server, I cannot change the
settings of the server. Please help me by providing any suitable solution.
Also, note that I've an ASP script which insert rows into the table.
Thanks in advance.
-VenkatHi Venkat,
To solve the problem you need to generate insert statement like following in
your ASP script:
insert into #t1 values ('St John''s street')
i.e. replace single quote ( ' ) by two single quotes ( '' )
Krish
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq#aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
the
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||Hi
A single quote within a string can be escapped with a second quote.
insert into #t1 (address) values ('St John''s street')
John
"G.V.Reddy" wrote:

> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in t
he
> value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>
>|||Thank you very much Krish and John.
Since the address is entered by the visitors on the web site, do we need to
check each and every value entered/inserted into the varchar field for the
single quotes? In case the answer is Yes, I think we can do it by writing a
function which replaces a single quote with adding another quote. Is there
any other simple method to integrate this functionality (escaping with
another single quote) into the ASP code?
Thanks in advance.
-Venkat
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I've a column (type: varchar) in a table. I need to add single quotes in
> the value while inserting the rows. But I get an error. Please see below:
> create table #t1 (address varchar(20))
> go
> insert into #t1 values ('St John's street')
> Error:
> Server: Msg 170, Level 15, State 1, Line 1
> Line 1: Incorrect syntax near 's'.
> Server: Msg 105, Level 15, State 1, Line 1
> Unclosed quotation mark before the character string ')
> Since the database is on hosting providers server, I cannot change the
> settings of the server. Please help me by providing any suitable solution.
> Also, note that I've an ASP script which insert rows into the table.
> Thanks in advance.
> -Venkat
>|||G.V.Reddy wrote:
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we
> need to check each and every value entered/inserted into the varchar
> field for the single quotes? In case the answer is Yes, I think we
> can do it by writing a function which replaces a single quote with
> adding another quote. Is there any other simple method to integrate
> this functionality (escaping with another single quote) into the ASP
> code?
> Thanks in advance.
> -Venkat
>
Look into the use of "parameters". I don't know the exact details for *asp*,
but they are something like:
* provide placeholders in you sql string :
insert into #t1 values (?)
* create a parameter, fill it with the "plain" value ("St John's street"), n
o need
to escape quotes, then add that parameter to the command object
* execute the query
Hans Kesting|||Hi
For ASP/ADO check out the SQL Server samples
http://msdn.microsoft.com/library/d...
5ym.asp
John|||replace(strParam, "'","''")|||Yes, a function to "double up" the single quote items will help. It would be
even better to use parameterized command objects instead of concatenated SQL
strings for communicating with the database (I am guessing you are using ADO
in your ASP application). Building concatenated SQL strings leaves you
application open to SQL Injection attacks, which is a severe security issue.
For information on ADO Command objects and parameters, see:
http://msdn.microsoft.com/library/d...rsreference.asp
For information about SQL injection attacks, see:
http://search.microsoft.com/search/.../>
0&s=1&swc=0
http://www.google.com/search?hl=en&q=sql+injection
"G.V.Reddy" <vreddyg@.go.com> wrote in message
news:OxZF4q$aFHA.3132@.TK2MSFTNGP09.phx.gbl...
> Thank you very much Krish and John.
> Since the address is entered by the visitors on the web site, do we need
to
> check each and every value entered/inserted into the varchar field for the
> single quotes? In case the answer is Yes, I think we can do it by writing
a
> function which replaces a single quote with adding another quote. Is there
> any other simple method to integrate this functionality (escaping with
> another single quote) into the ASP code?
> Thanks in advance.
> -Venkat
>
> "G.V.Reddy" <vreddyg@.go.com> wrote in message
> news:uS9GYq%23aFHA.2668@.TK2MSFTNGP12.phx.gbl...
below:
solution.
>
>sql

Problem with Searching unicode strings

Hi,
I have problem with Contains command . I've made index on FName column of my
database which is nvarchar(100).
When I call contains command to search for english strings , it works very
well .But when I search for unicode strings ( I use farsi strings and insert
them in unicode ), sometimes it can not find them , in more than 70 percent
unicode searches , it works correctly , but sometimes it can't find very
simple strings, for example : it can't find " ^??? " strings . I use
these commands to find :
Select * from MyDB where Contains(FName, N' ^??? ' )
or
Select * from MyDB where Contains(FName, ' ^??? ' )
but no one can find the string.
please tell me what is wrong in command or what can I do to solve this
problem.
In addition , I want to send result of Select @.@.version command for you .
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Thanks in Advance
Hamid.
what collation are you using? This works for me.
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
"Hamid" <hamid@.omid.ca> wrote in message
news:OcaRgT9IFHA.904@.tk2msftngp13.phx.gbl...
> Hi,
> I have problem with Contains command . I've made index on FName column of
my
> database which is nvarchar(100).
> When I call contains command to search for english strings , it works very
> well .But when I search for unicode strings ( I use farsi strings and
insert
> them in unicode ), sometimes it can not find them , in more than 70
percent
> unicode searches , it works correctly , but sometimes it can't find very
> simple strings, for example : it can't find " ^??? " strings . I use
> these commands to find :
> Select * from MyDB where Contains(FName, N' ^??? ' )
> or
> Select * from MyDB where Contains(FName, ' ^??? ' )
> but no one can find the string.
> please tell me what is wrong in command or what can I do to solve this
> problem.
> In addition , I want to send result of Select @.@.version command for you
..
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
> ----
> Thanks in Advance
> Hamid.
>
>
|||Hi,
These documents could not help me.
I've used SQL_Latin1_General_CP1_CI_AS collation in database. As I told, all
English strings will find by use of contains where-clause , My problem is
with Unicode stringe , which it can not find them . It find about 70 percent
of strings and it ignores some strings in search.
Please tell me how can I solve this problem.
Any Comments or suggestions appreciated.
Hamid.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:u2ZyToEJFHA.2356@.TK2MSFTNGP12.phx.gbl...[vbcol=seagreen]
> what collation are you using? This works for me.
> --
> 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
> "Hamid" <hamid@.omid.ca> wrote in message
> news:OcaRgT9IFHA.904@.tk2msftngp13.phx.gbl...
of[vbcol=seagreen]
> my
very[vbcol=seagreen]
> insert
> percent
you
> .
> Windows
> ----
>

Problem with Search Unicode Strings by use of <Contains>

Hi,
I have problem with Contains command . I've made index on FName column of my
database which is nvarchar(100).
When I call contains command to search for english strings , it works very
well .But when I search for unicode strings ( I use farsi strings and insert
them in unicode ), sometimes it can not find them , in more than 70 percent
unicode searches , it works correctly , but sometimes it can't find very
simple strings, for example : it can't find " ^??? " strings . I use
these commands to find :
Select * from MyDB where Contains(FName, N' ^??? ' )
or
Select * from MyDB where Contains(FName, ' ^??? ' )
but no one can find the string.
please tell me what is wrong in command or what can I do to solve this
problem.
In addition , I want to send result of Select @.@.version command for you .
Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on Windows
NT 5.0 (Build 2195: Service Pack 4)
Also the Collation of Database and FName-field is :
SQL_Latin1_General_CP1_CI_AS
Thanks in Advance
Hamid.
Hamid,
First of all, thank you for providing the @.@.version (SQL Server 2000 SP3 on
Windows 2000 Server SP4) information along with the language (Farsi -
Iranian / Persian) and an example of your CONTAINS queries as this is most
helpful in helping you solve this problem!
Unfortunately, Farsi is not one of the subset of SQL Server 2000 supported
languages that Full Text Search supports, see SQL Server 2000 Books Online
title "Column-Level Linguistic Analysis", Note that it states that you
should "Use neutral when a column contains data in multiple languages or in
an unsupported language". Most likely, you have the FName column's "Language
for Word Breaker" set to US English. Could you confirm this with your reply
via sp_help_fulltext_columns ?
Assuming that you have your FT-enable column (FName) "Language for Word
Breaker" set to US English, you should drop the FT Catalog and re-create it
with the "Language for Word Breaker" set to Neutral and then run a Full
Population and re-test your CONTAINS query.
Additionally, you might want to checkout the new "Microsoft Arabic
Word-Breaker (Arabic Search Engine) - Beta" at
http://www.microsoft.com/middleeast/...v/beta/search/ and download the
Installation Guide and the Microsoft Arabic Word-Breaker. Now, I am not a
linguist, and I do not know how close or far apart Farsi or Persian is from
Arabic, but I'd suggest that you test it and let this newsgroup know if you
find it effective in resolving your FTS issues with Farsi strings.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Hamid" <hamid@.omid.ca> wrote in message
news:e5Z4#hsJFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have problem with Contains command . I've made index on FName column of
my
> database which is nvarchar(100).
> When I call contains command to search for english strings , it works very
> well .But when I search for unicode strings ( I use farsi strings and
insert
> them in unicode ), sometimes it can not find them , in more than 70
percent
> unicode searches , it works correctly , but sometimes it can't find very
> simple strings, for example : it can't find " ^??? " strings . I use
> these commands to find :
> Select * from MyDB where Contains(FName, N' ^??? ' )
> or
> Select * from MyDB where Contains(FName, ' ^??? ' )
> but no one can find the string.
> please tell me what is wrong in command or what can I do to solve this
> problem.
> In addition , I want to send result of Select @.@.version command for you
..
> Microsoft SQL Server 2000 - 8.00.760 (Intel X86) Dec 17 2002 14:22:05
> Copyright (c) 1988-2003 Microsoft Corporation Enterprise Edition on
Windows
> NT 5.0 (Build 2195: Service Pack 4)
> ----
> Also the Collation of Database and FName-field is :
> SQL_Latin1_General_CP1_CI_AS
> Thanks in Advance
> Hamid.
>
>

Problem With Result Set in SQL Task

Hello,

I have a SQL Task configured to return a single row, and a single column value. The SQL Statement looks like this;

SELECT MAX(InvoiceDate) AS InvoiceDate
FROM dbo.DailySettlementData

The statement parses without a problem. In the 'Result Set' of the SQL Task I have the following;

Result Name; InvoiceDate, Variable Name; LatestTableDate

'LatestTableDate' is of type 'DateTime' (and I'm wondering now if this needs to be of type 'Object')

I need this MAX(InvoiceDate) value in a later step that checks this value against another date type variable.

I'm not getting the result I expect. Do I have the variable for the result set in the SQL Task set up correctly?

Thank you for your help!

cdun2

I assume that the variable name is "User::LatestTableDate", right?

Otherwise that looks fine. It doesn't need to be an object datatype. You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.|||

Phil Brammer wrote:

You can try a result name of "0" instead of "InvoiceDate" and see if that returns anything different.

One of my variables needed to have 'EvaluateAsExpression' set to True. Now it works fine.

A couple of questions related to working with SSIS in Visual Studio;

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Thank you again for your help.

cdun2

|||

cdun2 wrote:

While I'm on the subject of Visual Studio, I've noticed that all of my packages related to a specific project will open when I try to debug just one package. Why does that happen? The packages are independent of each other.

Close them before saving the project and closing it. It will open whatever you left open the previous time.|||

cdun2 wrote:

I'm looking for some way to 'watch' my SSIS variables in the 'watch' window. I thought this would be available from the Debug or Windows menu in Visual Studio 2005, but I don't see it.

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=250573&SiteID=1

problem with reportviewer columns

How do I resize the columns in reportviewer?

At the moment my report wich has 7 columns displays each column as about half a cm width.

have tried the various properties with out success

I use firefox as my browser, once I installed the extension IETab and told firefox to always use the IETab when

opening my site then the report displayed properly. Apparently MS are aware of this problem with firefox and

have a fix in the next asp.net sp.

problem with query where column name is the same as a keyword

hi

I am having trouble with the following query within my store procedure.
as you can see, i am making an union of 2 separate queries.

in the 2nd part of the union, i encounter a column in the database where the column name is the same as the keyword "desc"

is there a way which i can get around this, or is there any other way that i can sepecify the column? (excluding the possibility of using *)

CREATE PROCEDURE topcat.getTransHistory
(
@.contact_id numeric(9)
)
AS
BEGIN
DECLARE @.phone_no varchar(255)

set @.phone_no = (select top 1 phone_num from topcat.class_contact where _id = @.contact_id)

select cast(trans_new.trans_date as varchar(50)) date,
'' code,
cast(payment.date_paid as varchar(50)) datepaid,
'' "desc",
case payment.payment_type
when 'cheque' then trans_new.item_total
else ''
end pledged,
'' mail,
case payment.payment_type
when 'cheque' then ''
else trans_new.item_total
end received,
'' receipt
from topcat.class_transaction trans_new left outer join topcat.class_payment payment on trans_new._id = payment.transaction_id
where trans_new.contact_id = @.contact_id
union
select cast(trans_old.date as varchar(50)) "date",
trans_old.code,
cast(trans_old.datepaid as varchar(50)) "datepaid",
trans_old.desc,
cast(trans_old.pledged as varchar(128)),
trans_old.mail,
cast(trans_old.received as varchar(128)),
trans_old.receipt
from topcat.MMTRANS$ trans_old
where phone = @.phone_no

END
GO

Cheers
James :)wrap your column name that is a keyword in square brackets eg [desc]|||thank you
thank you
thank you

you are a life saver, i was goin to change the column name of the table if i couldn't find an alternative way of doing it.

:)|||no worries,... try to avoid keywords in the future would be my advise though... ;)|||i was on a contract once and there converted database had tables and columns all over the place named with keywords.

i will never forget they had a column called select in a table called order.
nightmare to say the least.

Problem with query

Hi All!
i want to update a column based on another column and using following
quey but its giving me error any help
update hospital1 set hospital1.father_name = temp.father_name
join temp on temp.id = temp.id
and hospital1.father_name <> 'Not Available'
thanx!
Farid
*** Sent via Developersdex http://www.examnotes.net ***update h
set h.father_name = t.father_name
from hospital1 h
join temp t on t.id = h.id
and h.father_name <> 'Not Available'
I assumed you can join temp and hospital1 on id
http://sqlservercode.blogspot.com/|||Ghulam Farid wrote:
> Hi All!
> i want to update a column based on another column and using following
> quey but its giving me error any help
> update hospital1 set hospital1.father_name = temp.father_name
> join temp on temp.id = temp.id
> and hospital1.father_name <> 'Not Available'
> thanx!
> Farid
>
> *** Sent via Developersdex http://www.examnotes.net ***
I'm guessing you want something like this:
UPDATE hospital1
SET father_name =
(SELECT father_name
FROM temp
WHERE id = hospital1.id
AND father_name <> 'Not Available') ;
WHERE ... ?
Note that this statement assumes id is unique in Temp for each hospital
row to be updated. If that assumption is incorrect then please explain
how you want to handle the duplicates (i.e. more than one father_name).
David Portas
SQL Server MVP
--|||Your proprietary answer makes no sense in terms of the SQL language
model. A FROM clause is always suppose effectively materialize a
working table that disappears at the end of the statement. Likewise,
an alias is supposed to act as it materializes a new working table with
the data from the original table expression in it. To be consistent,
this syntax says that you have done nothing to the base table.
Sybase and some other vendors had the same syntax but with different
semantics. Worst of both worlds!
And on top of that, it is unpredictable. This is a simple example from
Adam Machanic
CREATE TABLE Foo
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Foo VALUES ('A', 0);
INSERT INTO Foo VALUES ('B', 0);
INSERT INTO Foo VALUES ('C', 0);
CREATE TABLE Bar
(col_a CHAR(1) NOT NULL,
col_b INTEGER NOT NULL);
INSERT INTO Bar VALUES ('A', 1);
INSERT INTO Bar VALUES ('A', 2);
INSERT INTO Bar VALUES ('B', 1);
INSERT INTO Bar VALUES ('C', 1);
You run this proprietary UPDATE with a FROM clause:
UPDATE Foo
SET Foo.col_b = Bar.col_b
FROM Foo INNER JOIN Bar
ON Foo.col_a = Bar.col_a;
The result of the update cannot be determined. The value of the column
will depend upon either order of insertion, (if there are no clustered
indexes present), or on order of clustering (but only if the cluster
isn't fragmented).

Problem with parenthesis in the default value of a column

I am having a problem with the format of default values in SQL Server 2005.
I
am using a legacy application that is not expecting the double parenthesis
around the default value that SQL Server adds. For instance, if
I set 0 (zero) as the default value for a column of type int as follows
ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
Class)
then SQL Server will set the default value as ((0)). The problem is that the
legacy application is validating the default values and expecting just 0. I
own the database but have no control over the application. Therefore, I
cannot change the application to remove the parenthesis after reading the
value.
Is there any way to force SQL Server to store the default value without the
parenthesis or to return it without the parenthesis (note that the
application is reading the database tables directly).
Regards,
ArturHi
I don't think you can change the way it is stored but you could use the
REPLACE function to strip out the braces when you return it.
John
"artur" wrote:

> I am having a problem with the format of default values in SQL Server 2005
. I
> am using a legacy application that is not expecting the double parenthesis
> around the default value that SQL Server adds. For instance, if
> I set 0 (zero) as the default value for a column of type int as follows
> ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
> Class)
> then SQL Server will set the default value as ((0)). The problem is that t
he
> legacy application is validating the default values and expecting just 0.
I
> own the database but have no control over the application. Therefore, I
> cannot change the application to remove the parenthesis after reading the
> value.
> Is there any way to force SQL Server to store the default value without th
e
> parenthesis or to return it without the parenthesis (note that the
> application is reading the database tables directly).
> Regards,
> Artur

Problem with parenthesis in the default value of a column

I am having a problem with the format of default values in SQL Server 2005. I
am using a legacy application that is not expecting the double parenthesis
around the default value that SQL Server adds. For instance, if
I set 0 (zero) as the default value for a column of type int as follows
ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
Class)
then SQL Server will set the default value as ((0)). The problem is that the
legacy application is validating the default values and expecting just 0. I
own the database but have no control over the application. Therefore, I
cannot change the application to remove the parenthesis after reading the
value.
Is there any way to force SQL Server to store the default value without the
parenthesis or to return it without the parenthesis (note that the
application is reading the database tables directly).
Regards,
ArturHi
I don't think you can change the way it is stored but you could use the
REPLACE function to strip out the braces when you return it.
John
"artur" wrote:
> I am having a problem with the format of default values in SQL Server 2005. I
> am using a legacy application that is not expecting the double parenthesis
> around the default value that SQL Server adds. For instance, if
> I set 0 (zero) as the default value for a column of type int as follows
> ALTER TABLE Entity ADD CONSTRAINT [DF_Entity_Class] DEFAULT 0 FOR
> Class)
> then SQL Server will set the default value as ((0)). The problem is that the
> legacy application is validating the default values and expecting just 0. I
> own the database but have no control over the application. Therefore, I
> cannot change the application to remove the parenthesis after reading the
> value.
> Is there any way to force SQL Server to store the default value without the
> parenthesis or to return it without the parenthesis (note that the
> application is reading the database tables directly).
> Regards,
> Artur

problem with output parameter stored procedure

My stored procedure below compiled - not sure if it is even correct though.
I have to get the sum of a totalpaid column from one table and get the sum o
f
a totalpaid column from a second table. I need to return the difference of
these sums.
---
CREATE PROCEDURE [stp_SumDiffTotalPaid]
@.SumDiff decimal output
AS
declare @.a decimal, @.b decimal
Select @.a = sum(pd_totl_amt) from tblncanschd
Select @.b = sum(totalpaid) from tblncalnonschedemipaid
Set @.SumDiff = @.a - @.b
Return
GO
----
-
Here is how I call my sp from query analyzer:
declare @.a decimal
stp_sumdiffTotalpaid, @.sumdiff = @.a output
This is not working. Any suggestions appreciated how to get this to work or
if there is a simpler way to do this.
Thanks,
RichI figured it out
Declare @.a
Excec stp_sumdiffTotalpaid @.a output
or
Excec stp_sumdiffTotalpaid @.sumdiff = @.a output
print @.a
I was missing Execute
I guess I don't need the comma after the sp either.
"Rich" wrote:

> My stored procedure below compiled - not sure if it is even correct though
.
> I have to get the sum of a totalpaid column from one table and get the sum
of
> a totalpaid column from a second table. I need to return the difference o
f
> these sums.
> ---
> CREATE PROCEDURE [stp_SumDiffTotalPaid]
> @.SumDiff decimal output
> AS
> declare @.a decimal, @.b decimal
> Select @.a = sum(pd_totl_amt) from tblncanschd
> Select @.b = sum(totalpaid) from tblncalnonschedemipaid
> Set @.SumDiff = @.a - @.b
> Return
> GO
> ----
--
> Here is how I call my sp from query analyzer:
> declare @.a decimal
> stp_sumdiffTotalpaid, @.sumdiff = @.a output
> This is not working. Any suggestions appreciated how to get this to work
or
> if there is a simpler way to do this.
> Thanks,
> Rich