A very beginer question: error in creating table

I am new in T-SQL. I have just create a empty database and would like to create some table in it. So I type the following in SQL Query Analyzer:

CREATE TABLE mydatabasename.mytablename (

some code here
)
But when execute, it give me an error from the first line "Specified owner name 'mydatabasename' either does not exist or you do not have permission to use it."

when I remove mydatabasename it inserts the table in to the master database, not in my database.

How can I insert the table into my database? Should I use SQL Query Analyzer or use others program? I 've tried to use the command "create table" in enterprise manager but it immediately give another error, some what like "could not use empty column name ... "
Seem to be too simple question, but I am really appreciate your helpWhen you use the SQL Query Analyzer do you choose from the dropdown your newly created Database?|||CREATE TABLE mydatabasename.mytablename

should be

CREATE TABLE mydatabasename.dbo.mytablename

A fully qualified name is
servername.databasename.owner.object

You can't skip one like you're doing. You can omit them from left to right, but can't skip a part.|||Thanks all. I will try both, select the newly database and use databasename.dbo.tablename.

Infact, I use to think about using "databasename.dbo.tablename" but then think it 's ridiculous for a try. What a mistake. It's my because my poor English make me sometime not understand the full syntax.

Thank you.

a time out has occurred while waiting for buffer latch type 4

We are getting this error on SQL Server 2000 ( SP3)
" a time out has occurred while waiting for buffer latch type 4 "
As per Microsoft this may happen when memory is >3GB and awe enabled ,and
this is fixed in SP2
But in my case
SQL Server 2000 -Standard Edition (SP3)
AWE disabled
OS Memory -- 2GB
Service Pack -- SP3
Any one experienced this and solved ? Any help is appreciated .
Thanks
BinuLook at this article.
There are couple of reasons why it can be:
http://support.microsoft.com/default.aspx?scid=kb;en-
us;310834&Product=sql
Regards,
Sergey.
>--Original Message--
>We are getting this error on SQL Server 2000 ( SP3)
>" a time out has occurred while waiting for buffer
latch type 4 "
>As per Microsoft this may happen when memory is >3GB and
awe enabled ,and
>this is fixed in SP2
>But in my case
>SQL Server 2000 -Standard Edition (SP3)
>AWE disabled
>OS Memory -- 2GB
>Service Pack -- SP3
>Any one experienced this and solved ? Any help is
appreciated .
>Thanks
>Binu
>
>.
>

a small SQL help regrading a criteria

i want the ordline.qty * product.prodprice AS ordercost

where ordercost = > 150
but it does not work. Every time i type: where ordercost => 150, it would give me an error.
this is my full query:

select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno where (ordertbl.orddate = datevalue('01/23/2007')) and (making ordercost = > 150) ..............

Thanks for help in advancethis has to be either microsoft access or sql server, it doesn't look very much like ANSI SQL

in any case...

one solution is to wrap the query in another SELECT

select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) as d where orddate = datevalue('01/23/2007') and ordercost = > 150|||sorry
im not sure where to ask
im using this in sql server and access|||doesn't the star calls out all the rows of the tables?
i'll try|||doesn't the star calls out all the rows of the tables?
i'll tryno, the star "calls out" all the columns

in this case it's all the columns of the derived table, show here in blue --

select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) as d where orddate = datevalue('01/23/2007') and ordercost = > 150|||hey it works!
why does the * makes it work?|||nm
you have explained.

select * from ( select customer.custno, customer.custfirstname + " " + customer.custlastname as custfullname, ordertbl.ordno, ordertbl.orddate, employee.empno, employee.empfirstname + " " + employee.emplastname as empfullname, product.prodno, product.prodname, ordline.qty * product.prodprice AS ordercost from (((ordertbl inner join customer on ordertbl.custno = customer.custno) inner join employee on employee.empno = ordertbl.empno) inner join ordline on ordline.ordno=ordertbl.ordno) inner join product on product.prodno=ordline.prodno ) where orddate = datevalue('01/23/2007') and ordercost = > 150

u had "as d" as typos =p
__________________|||it works because of the derived table, which uses column names as defined in its SELECT

the expression with the column alias ordercost becomes an actual column in the derived table|||as d was not a typo|||i took the as d out and it works too
and what's as d
sorry for being a noob|||i took the as d out and it works too
and what's as d
This sets "d" as the alias name (actually: table name) for the temporary "table" inside the parentheses.

According to standard SQL, every table (or view or nested table expression) must have a name. By having the "AS d" after the definition, it's as if you created a view, viz:
CREATE VIEW d (custno, custfullname, ordno, orddate, empno,
empfullname, prodno, prodname, ordercost)
AS
SELECT customer.custno,
customer.custfirstname || ' ' || customer.custlastname,
ordertbl.ordno,
ordertbl.orddate,
employee.empno,
employee.empfirstname + " " + employee.emplastname,
product.prodno,
product.prodname,
ordline.qty * product.prodprice
FROM ordertbl inner join customer on ordertbl.custno = customer.custno
inner join employee on employee.empno = ordertbl.empno
inner join ordline on ordline.ordno=ordertbl.ordno
inner join product on product.prodno=ordline.prodno
Now this view can be interrogated:SELECT *
FROM d
WHERE orddate = datevalue('01/23/2007')
and ordercost => 150
Rudy's query is exactly this, except for the fact that no view with the name "d" is ever created, it's just temporarily available for the scope of the current query. Such a "view" is often called a "nested table expression". It's to be written asSELECT d.whatever
FROM (SELECT whatever, ... -- the NTE
) AS d
WHERE ...Note the "d.whatever", where the table name "d" is used. That's why the "AS d" is needed: it's really the name of the NTE !|||thanks peter, i was away from the computer all day, but your answer was much better than mine would've been :)

and i call it a derived table instead of nested table expression|||i call it a derived table instead of nested table expressionNTE is the DB2 terminology.
I'm probably a bit biased...

a short question ?

how can i convert the varchar value to a column of data type int?

i am trying to do this

declare @.w varchar(50)
select @.w = col1 from myTable
select colname from mtable
where mtableID in (@.w)

but the mtableID is a int

and i always got

Syntax error converting the varchar value to a column of data type int.

thanks for everyone for helpingtry this

where mtableID in CAST(@.w AS int)

not sure if you want to swap your "in" for an "=" or not...

a search module.

okay so pretty much I am using the asp.net 2.0 membership/roles.

i wanna make a search box that users can type in a name and it will search the database. most likely in the table "aspnet_Users"

so when users search i want it to be like this:

cmd = select * from [table] WHERE username = textbox1.text (ofcourse with parameters instead of concatenation).

but i want it to have a "like" in there

so if the users type in "fenix" they should get results matching CLOSE to fenix. so

masfenix, fenix, fenxi, and you know related names. that are similar.

should I use LIKE? and if so HOW do i use that?

is it just gonna be

cmd = select * from [table] where Username LIKE @.username

@.username = textbox1.text

?

thanks

Hello my friend,

The LIKE clause uses the % wild character. Here are some examples: -

-- get countries beginning with c
select * from tblcountry where countryname like 'c%'

-- get countries ending with land
select * from tblcountry where countryname like '%land'

-- get countries containing the word 'land' or 'stan'
select * from tblcountry where countryname like '%land%' or countryname like '%stan%'

Kind regards

Scotty

|||

hi thanks for the answer

i knew about that before just thoguht there would be more solutions

|||

Hi,

Maybe the following link is helpful to you.

http://forums.asp.net/thread/1677621.aspx

Thanks.

A puzzle of data type in SQL Server 2005

Recently I began to use SQL 2005. But yesterday I found a puzzle about data type.
I use this script to create a table:

CREATE TABLE [dbo].[InvoiceMaster](
.....
[InvoiceMemo] [varchar](max) NULL,
.....
)

But when I run this cmd : exec sp_columns InvoiceMaster. I find the type_name of this column changed to "text".
Can anybody tell me why?

Thanks in advance!Recently I began to use SQL 2005. But yesterday I found a puzzle about data type.
I use this script to create a table:

CREATE TABLE [dbo].[InvoiceMaster](
.....
[InvoiceMemo] [varchar](max) NULL,
.....
)
But when I run this cmd : exec sp_columns InvoiceMaster. I find the type_name of this column changed to "text".
Can anybody tell me why?

Thanks in advance!

Don't put the VARCHAR keyword in square brackets. It's killing my feng shui.

Anyhow, according to this article (http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1098157,00.html), VARCHAR(MAX) is different from TEXT in that string manipulation functions work on it whereas they don't work on TEXT. It used to be that if you wanted to do string manipulation on text that it had to be less than 8000 characters long. Now you can do it thanks to the MAX datatypes. Since they're still VARCHAR values, all the functions will work on them just fine.

Most importantly, to the marketing department, they have these new MAX datatypes that sound cool. We're lucky it's not VARCHAR(EXTREME) or something like that.

If it weren't for marketing they probably would have just extended the string ops to TEXT and done the equivalent for the binary types. In fact, this is precisely what they did, and the MAX datatypes are just aliases to BLOB and CLOB types. So the reason VARCHAR(MAX) shows up as TEXT is because it *is* TEXT.|||Most importantly, to the marketing department, they have these new MAX datatypes that sound cool. We're lucky it's not VARCHAR(EXTREME) or something like that.

they wanted to call it varchar(TO_THE_MAX) but it was shot down sadly. :(

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

I am also having this same issue. I cannot seem to find any help for this out there by Google search or otherwise. I've seen some blip about installing on a domain controller. Please help.|||I discovered a workaround. I used the IP address of the DBMS instead of the local computer name. Then I used the DOMAINNAME\SP_Setup_User (or admin) account and password. This worked. Hopefully no ill effects.|||

Unfortunately, this approach didn't work for me. If any other idea, please let me know.

Thanks

|||

Erik,

I came to the conclusion to use the IP address of the SQL Server instead of the name because, you know how you want to map a drive to the same server using two different user accounts? Well, you have to use the server name for one mapping and then something else for the second mapping, like the IP address. It probably has nothing to do with this problem, but it reminded me of it. So it did work, at least on my system. Again, I do not know if there are any obscure consequences of doing this. So far all is well. Maybe there is a Microsoft person listening in or someone who could lend some thoughts to the situation for you.

Mike

|||

Hi Guys, had the same problem. Tried with IP address and yes it worked. However, when I went into Visual Studio and created a report when I went to deploy it a username/password form pops up. No matter what username/password is inserted can't get it to deploy. Any help would be greatly appreciated.

Keith

|||

Try using Fully Qualified domain Name of the SQL Server..

and It Works!..

|||

do you mean servername\username & password because it did not work. I enterede servername/username in the reporting services UI and then went into visual studio to deploy (on same machine) but when I went to deploy you would enter servername/username and it would just keep poping up and never do anything.

Any thing to look at for the account. I have tried a user account and on last atempt I used administrator, but no luck.

Thanks for any help!

|||

I have done some more work on it and in sharepoint central administration, reporting services, manage integration services, set to windows account. and it all saves with out complaing. Now when I gointo Visual studio on the same machine as reporting service and sharepoint I no longer get a log in box but I get "a connection could not be made to the report server... this operation is not supported on a report server that is configured to run in sharepoint integration...

anybody and thoughts?

Thanks

|||

did some more work/investigation and I stumbled into that if you go the sharepoint site, document library and try to add the new document type of reporting services, it does not show up in the list. So I think everything deployed okay but it appears there is no link between sharepoint and report services. Also incase this helps, sharepoint is not in the default website, but is it's own web site that instead of www we have companyweb. Other that reporting services sharpoint works fine.

Help!

|||

Keith,

Our report server web service is at http://{Report Server servername}:8080/ReportServer2K5, port 8080 being the new port of the re-enabled IIS default web site. However, the deployment properties for the VS 2005 report project actually had to be set like so: (1) TargetDataSourceFolder and TargetReportFolder = http://{SharePoint servername}:{Admin Site Port Number}/{Our Report Library Name}/, and (2) TargetServerURL = http://{SharePoint servername}:{Admin Site Port Number}/. I can deploy right from Visual Studio with this configuration. The account used for the Windows service identity is the same as that used for SharePoint administration in my case. There are a myriad of possible combinations for these accounts, though.

Mike

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

I am also having this same issue. I cannot seem to find any help for this out there by Google search or otherwise. I've seen some blip about installing on a domain controller. Please help.|||I discovered a workaround. I used the IP address of the DBMS instead of the local computer name. Then I used the DOMAINNAME\SP_Setup_User (or admin) account and password. This worked. Hopefully no ill effects.|||

Unfortunately, this approach didn't work for me. If any other idea, please let me know.

Thanks

|||

Erik,

I came to the conclusion to use the IP address of the SQL Server instead of the name because, you know how you want to map a drive to the same server using two different user accounts? Well, you have to use the server name for one mapping and then something else for the second mapping, like the IP address. It probably has nothing to do with this problem, but it reminded me of it. So it did work, at least on my system. Again, I do not know if there are any obscure consequences of doing this. So far all is well. Maybe there is a Microsoft person listening in or someone who could lend some thoughts to the situation for you.

Mike

|||

Hi Guys, had the same problem. Tried with IP address and yes it worked. However, when I went into Visual Studio and created a report when I went to deploy it a username/password form pops up. No matter what username/password is inserted can't get it to deploy. Any help would be greatly appreciated.

Keith

|||

Try using Fully Qualified domain Name of the SQL Server..

and It Works!..

|||

do you mean servername\username & password because it did not work. I enterede servername/username in the reporting services UI and then went into visual studio to deploy (on same machine) but when I went to deploy you would enter servername/username and it would just keep poping up and never do anything.

Any thing to look at for the account. I have tried a user account and on last atempt I used administrator, but no luck.

Thanks for any help!

|||

I have done some more work on it and in sharepoint central administration, reporting services, manage integration services, set to windows account. and it all saves with out complaing. Now when I gointo Visual studio on the same machine as reporting service and sharepoint I no longer get a log in box but I get "a connection could not be made to the report server... this operation is not supported on a report server that is configured to run in sharepoint integration...

anybody and thoughts?

Thanks

|||

did some more work/investigation and I stumbled into that if you go the sharepoint site, document library and try to add the new document type of reporting services, it does not show up in the list. So I think everything deployed okay but it appears there is no link between sharepoint and report services. Also incase this helps, sharepoint is not in the default website, but is it's own web site that instead of www we have companyweb. Other that reporting services sharpoint works fine.

Help!

|||

Keith,

Our report server web service is at http://{Report Server servername}:8080/ReportServer2K5, port 8080 being the new port of the re-enabled IIS default web site. However, the deployment properties for the VS 2005 report project actually had to be set like so: (1) TargetDataSourceFolder and TargetReportFolder = http://{SharePoint servername}:{Admin Site Port Number}/{Our Report Library Name}/, and (2) TargetServerURL = http://{SharePoint servername}:{Admin Site Port Number}/. I can deploy right from Visual Studio with this configuration. The account used for the Windows service identity is the same as that used for SharePoint administration in my case. There are a myriad of possible combinations for these accounts, though.

Mike

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

Hi Did anyone have a resolution to this situation. We're getting the same error message following the same sequence of events. Any assistance is appreciated.

|||

No... nothing... niet...

If you find any info, let me know please.

|||

Anybody find anything on this? I'm getting same error|||

I'm getting the same frustrating error. Any news on a solution?

Thanks!

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

Hi Did anyone have a resolution to this situation. We're getting the same error message following the same sequence of events. Any assistance is appreciated.

|||

No... nothing... niet...

If you find any info, let me know please.

|||

Anybody find anything on this? I'm getting same error|||

I'm getting the same frustrating error. Any news on a solution?

Thanks!

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

Hi Did anyone have a resolution to this situation. We're getting the same error message following the same sequence of events. Any assistance is appreciated.|||

No... nothing... niet...

If you find any info, let me know please.

|||Anybody find anything on this? I'm getting same error|||

I'm getting the same frustrating error. Any news on a solution?

Thanks!

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

Hi Did anyone have a resolution to this situation. We're getting the same error message following the same sequence of events. Any assistance is appreciated.|||

No... nothing... niet...

If you find any info, let me know please.

|||Anybody find anything on this? I'm getting same error|||

I'm getting the same frustrating error. Any news on a solution?

Thanks!

A new member could not be added to a local group because the member has the wrong account type

Hi all,

I installed SQL 2005 SP2 + ReportServices Add-in for Sharepoint (WSS 3.0). All it's OK until I try to grant database access in the Sharepoint Central Admin site.

I setup the Reporting Services Integration (Manage integration settings). I use the default SQL instance, I put the USERNAME and the PASSWORD of my ADMIN account in the ENTER CREDENTIALS windows.. When I click the "OK" button, I receive always the error ...

"A new member could not be added to a local group because the member has the wrong account type"

I tried a lot of things... without success.

Is there someone who can help me....

PS: There is no error in the LOG

Thanks

I am also having this same issue. I cannot seem to find any help for this out there by Google search or otherwise. I've seen some blip about installing on a domain controller. Please help.|||I discovered a workaround. I used the IP address of the DBMS instead of the local computer name. Then I used the DOMAINNAME\SP_Setup_User (or admin) account and password. This worked. Hopefully no ill effects.|||

Unfortunately, this approach didn't work for me. If any other idea, please let me know.

Thanks

|||

Erik,

I came to the conclusion to use the IP address of the SQL Server instead of the name because, you know how you want to map a drive to the same server using two different user accounts? Well, you have to use the server name for one mapping and then something else for the second mapping, like the IP address. It probably has nothing to do with this problem, but it reminded me of it. So it did work, at least on my system. Again, I do not know if there are any obscure consequences of doing this. So far all is well. Maybe there is a Microsoft person listening in or someone who could lend some thoughts to the situation for you.

Mike

|||

Hi Guys, had the same problem. Tried with IP address and yes it worked. However, when I went into Visual Studio and created a report when I went to deploy it a username/password form pops up. No matter what username/password is inserted can't get it to deploy. Any help would be greatly appreciated.

Keith

|||

Try using Fully Qualified domain Name of the SQL Server..

and It Works!..

|||

do you mean servername\username & password because it did not work. I enterede servername/username in the reporting services UI and then went into visual studio to deploy (on same machine) but when I went to deploy you would enter servername/username and it would just keep poping up and never do anything.

Any thing to look at for the account. I have tried a user account and on last atempt I used administrator, but no luck.

Thanks for any help!

|||

I have done some more work on it and in sharepoint central administration, reporting services, manage integration services, set to windows account. and it all saves with out complaing. Now when I gointo Visual studio on the same machine as reporting service and sharepoint I no longer get a log in box but I get "a connection could not be made to the report server... this operation is not supported on a report server that is configured to run in sharepoint integration...

anybody and thoughts?

Thanks

|||

did some more work/investigation and I stumbled into that if you go the sharepoint site, document library and try to add the new document type of reporting services, it does not show up in the list. So I think everything deployed okay but it appears there is no link between sharepoint and report services. Also incase this helps, sharepoint is not in the default website, but is it's own web site that instead of www we have companyweb. Other that reporting services sharpoint works fine.

Help!

|||

Keith,

Our report server web service is at http://{Report Server servername}:8080/ReportServer2K5, port 8080 being the new port of the re-enabled IIS default web site. However, the deployment properties for the VS 2005 report project actually had to be set like so: (1) TargetDataSourceFolder and TargetReportFolder = http://{SharePoint servername}:{Admin Site Port Number}/{Our Report Library Name}/, and (2) TargetServerURL = http://{SharePoint servername}:{Admin Site Port Number}/. I can deploy right from Visual Studio with this configuration. The account used for the Windows service identity is the same as that used for SharePoint administration in my case. There are a myriad of possible combinations for these accounts, though.

Mike

A MergeJoin using two fields doesnt work !

Hi,

I have an issue. When I am performing a Merge Join using two fields of type
DT_WSTR on both sides of the Join, the Dataviewer after the Join only shows the left side 2 key
fields having values populated but both fields from the right hand side as being NULL. The Dataviewers I put in before the join show both sides of the join fields being populated ( 2 sides x 2 fields ). If I remove one of the fields from the join it shows both fields from the left side and the right side of the join being populated . I am trying to knock out duplicate values from the insert using 'genuine' Nulls from the right hand side so I need both parts of the 'Key'. I have checked the adnvanced tab that ignores Case etc but has had no effect.

I am using the technique from here : http://www.sqlis.com/311.aspx

Is this an SSIS bug ? It is driving me up the wall.

Help would be much appreciated please.

Thanks
Jon

SORTED !!

Sorry for this wasted post but this may be usefull for someone else in the future.

I changed the package to perform a lookup instead of a merge join and that failed as well. When I looked at the DB one of the two fields I was looking up to was defined as an NCHAR(15) and so always had whitespace at the end. I changed the datatype to be an NVARCHAR(15), reloaded the contents of the field and Voila, all was well.

Thanks anyway.

Jon

A float data type is recognized as varchar

Hello,
I am on a c# project right now. If I want to insert a float data type (c#)
into the sql server, I am getting the message then, that a varchar cannot be
inserted into a float. I tried it with money, small money and decimal, too.
And the result is ever the same. (Varchar cannot be entered into a
float/decimal, money etc..)
I am using direct sql command, not a SP.
As example: (simplified, without connection opening, etc.)
In c#:
float variable = 20;
sqlcommandobject.commandtext = "insert into products (price) VALUES
('"+variable+"')"Why are you putting the value between apostrophes?

> sqlcommandobject.commandtext = "insert into products (price) VALUES
> ('"+variable+"')"
sqlcommandobject.commandtext = "insert into products (price) VALUES
(" + variable + ")"
AMB
"the friendly display name" wrote:

> Hello,
> I am on a c# project right now. If I want to insert a float data type (c#)
> into the sql server, I am getting the message then, that a varchar cannot
be
> inserted into a float. I tried it with money, small money and decimal, too
.
> And the result is ever the same. (Varchar cannot be entered into a
> float/decimal, money etc..)
> I am using direct sql command, not a SP.
> As example: (simplified, without connection opening, etc.)
> In c#:
> float variable = 20;
> sqlcommandobject.commandtext = "insert into products (price) VALUES
> ('"+variable+"')"
>
>|||Get rid of the single quotes around the value, e.g.
sqlCommandObject.CommandText =
"INSERT INTO PRODUCTS (price) VALUES (" + variable.ToString() + ")"
Better yet, use parameters in your query so that it will automatically do
the formatting for you, this is also more flexible for data types like
binary, etc. and you don't have to worry about escaping text strings::
sqlCommandObject.CommandText =
"INSERT INTO PRODUCTS (price) VALUES (@.var)";
sqlCommandObject.Parameters.Add("@.var", variable);
Mike
"the friendly display name"
<thefriendlydisplayname@.discussions.microsoft.com> wrote in message
news:26A40CD8-45CE-4149-BF00-6064A0741640@.microsoft.com...
> Hello,
> I am on a c# project right now. If I want to insert a float data type (c#)
> into the sql server, I am getting the message then, that a varchar cannot
> be
> inserted into a float. I tried it with money, small money and decimal,
> too.
> And the result is ever the same. (Varchar cannot be entered into a
> float/decimal, money etc..)
> I am using direct sql command, not a SP.
> As example: (simplified, without connection opening, etc.)
> In c#:
> float variable = 20;
> sqlcommandobject.commandtext = "insert into products (price) VALUES
> ('"+variable+"')"
>
>|||Thank you.
The parameters solved the problem.
"Mike Jansen" wrote:

> Get rid of the single quotes around the value, e.g.
> sqlCommandObject.CommandText =
> "INSERT INTO PRODUCTS (price) VALUES (" + variable.ToString() + ")"
> Better yet, use parameters in your query so that it will automatically do
> the formatting for you, this is also more flexible for data types like
> binary, etc. and you don't have to worry about escaping text strings::
> sqlCommandObject.CommandText =
> "INSERT INTO PRODUCTS (price) VALUES (@.var)";
> sqlCommandObject.Parameters.Add("@.var", variable);
> Mike