|
Well, for a start this is a totally different question to your original one. My reply would work for what you asked in the first place, but this is a different problem.
I can only assume your join for asset_streets.THORFARE_REF_KEY = thoroughfares_fme1.primaryindex returns multiple rows, which kind of goes against the name of primaryindex.
Without reviewing your data (and no, I am not interested in seeing it) I can only suggest you look at introducing additional outer joins.
hope this helps
Bob
Ashfield Consultants Ltd
|
|
|
|
|
yes there are mulltiple thorfare entries for the asset
so it produces my record set as
e.g.
1 abc thorfare1
1 abc thorfare2
where as I just want it as
1 abc thorfare1,thorfare2
regards
Vijay
|
|
|
|
|
Try creating a function to string all the thorfare entries together using the same technique as I showed in my original reply, then your select bceomes something like
select asset_name, dbo.fn_thorfar(assetid), ....
If you are not sure about functions try BOL, there are some good examples.
Bob
Ashfield Consultants Ltd
|
|
|
|
|
I'm not familar with SQL, I get around with the basics which usually works fine for me.
However, I've come up with a situation where the code will no not run from a SQLDatasource object in VS.
I have, however gotten it to run properly from SSMSE so I want to write it as a stored procedure. But I really have no idea how to even begin. I'm sure easy to do, I just have never done it before.
Here is the SQL that I got to run properly.
SELECT Members.dbo.Members.LoginID, dbo.Categories.CategoryName, dbo.Listings.ListingID, dbo.Listings.Title, dbo.Listings.HasPhoto,
dbo.Listings.IsTaken, dbo.Listings.IsPickedUp, dbo.Listings.TakenDate, GetTakenUsername.LoginID AS TakenByUsername, dbo.Listings.PickedUp,
dbo.Listings.TimesViewed
FROM dbo.Listings INNER JOIN
dbo.Categories ON dbo.Listings.CategoryID = dbo.Categories.CategoryID INNER JOIN
Members.dbo.Members ON dbo.Listings.MemberID = Members.dbo.Members.MemberID INNER JOIN
Members.dbo.Members AS GetTakenUsername ON dbo.Listings.TakenBy = GetTakenUsername.MemberID
WHERE (dbo.Listings.MemberID = @MemberID)
Any help with this would be greatly appreciated.
modified on Tuesday, August 26, 2008 8:19 PM
|
|
|
|
|
you are right, it is very simple. Read BOL.
CREATE PROC MyProc<br />
--Declare <br />
@MemberID INT<br />
<br />
As<br />
<br />
SELECT Members.dbo.Members.LoginID, dbo.Categories.CategoryName, dbo.Listings.ListingID, dbo.Listings.Title, dbo.Listings.HasPhoto,<br />
dbo.Listings.IsTaken, dbo.Listings.IsPickedUp, dbo.Listings.TakenDate, GetTakenUsername.LoginID AS TakenByUsername, dbo.Listings.PickedUp,<br />
dbo.Listings.TimesViewed<br />
FROM dbo.Listings <br />
INNER JOIN dbo.Categories ON dbo.Listings.CategoryID = dbo.Categories.CategoryID <br />
INNER JOIN Members.dbo.Members ON dbo.Listings.MemberID = Members.dbo.Members.MemberID <br />
INNER JOIN Members.dbo.Members AS GetTakenUsername ON dbo.Listings.TakenBy = GetTakenUsername.MemberID<br />
WHERE (dbo.Listings.MemberID = @MemberID)
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
The template provided in SSMSE was so very NOT helpful. All that extra stuff they put into the template just made it confusing.
Thanks in the future at least I will understand what I'm looking at LOL.
|
|
|
|
|
It can be usefull looking at someone elses procs (minus the BOL overkill), some of the MS sample databases are a good start (have not looked at one in years).
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
i tried to install SQL Server 2005 in my laptop with Vista home version, i also download SP2 package to update it. but still have problem,can anyone tell me if I need to install other package? thanks
|
|
|
|
|
What is the actual problem?
|
|
|
|
|
after I update sqlserver2005SP2, it said cannnot coonect to server...and later I cannot find sql serevr management studio icon and others ( previsouly I could see that when I finished installation)
|
|
|
|
|
|
i guess the problem is with IIS, i tried to enable that in control panel, but when I start to install, still one warning telling me IIS feature not found.
should i download a IIS 7 package and install it? if yes, where I can find that to download? thanks
|
|
|
|
|
This is just a warning. If you don't have IIS, you won't be able to use SQL Server's native XML Web Services. This doesn't affect Management Studio, which can be used without IIS.
|
|
|
|
|
Hi, i understand the concept behind the physical crystal report, i struggle with the vb code behind that.
For example...I have a table with customer details...i want to have a textbox on my aspx page where you can input a certain surname or a range...like between A and D...to display on the report
I struggle with the code...can you please help me!!! I have looked at quite a few tutorials but every tutorial does something else....please help!
If you can maybe give me a step by step explanaition on what everything must happen in the code....or give me a sample application that i can study?
|
|
|
|
|
Hi,
not sure if I understood your question correctly, but if you want to filter records by a user given value then:
- add a parameter field into the report (using field explorer)
- add a criteria to the report based on parameter field by using Record Selection Formula Editor
|
|
|
|
|
I had install ms sql server 2008 over a m/c which has a network connection too. but I'm unable to access this from remotly. in 2005 we just provide the remote connections allow (in connection property). But how to allow remote access in sql server 2008 ?
please help on this.
Thanks
rajram
raj
|
|
|
|
|
Using Sql Server Configuration Manager, check that you have appropriate protocols enabeld and correctly configured.
Mika
|
|
|
|
|
Hello All;
I have a SQL db that collects environmental data. As it gets larger the performance of the server suffers. So I think that I would like to copy the "working" db to a new db then purge the working db. The be able to run querrys that span the databases.
I don't know just enough about SQL server to be dangerous so I would like to do this using VB.net
As an example.
If we backed up the db monthly and renamed them. then would it later be possible to run querry's that spanned the db's.
1. Is this a doable concept?
2. Would I?
"Drop" the working db
Copy all the data from the working db to a new db
Purge the tables in the working db
Just looking for some conceptual help here to make a plan to do this. Any help or guidence you could give be would be greatly appreciated.
tia
Rafone
Statistics are like bikini's...
What they reveal is astonishing ...
But what they hide is vital ...
modified on Tuesday, August 26, 2008 10:35 AM
|
|
|
|
|
If you are using SQL Server 2005 (or 2008) have a read about partitioned tables, this may be the answer to your performance problems.
Whatever you decide, DON'T try and do it in VB.net, you could well end up losing all your data!
Bob
Ashfield Consultants Ltd
|
|
|
|
|
You could use Replication to copy your working DB to another server and use that for all querying. SQL Server offers good replication capabilities for offline processing.
|
|
|
|
|
Helo,
Can anybody help me!
I have 3 tables
first 1 have a header table with one row
the second table are all the data records
and the third is a trailer table!
So now i want to create 1 file
with all the fields of the header table separated through ;
then new line, the data table with the fields separated through ;
and finally the trailer table with fields separated through ;
i think i can do this with Bcp utility or something like this,
but i don't know how!
Please help!
Thank you
|
|
|
|
|
You can create a SQL statement giving you correct result set and then save the result to a file.
Use UNION and concatenate all fields from a table to a single field using desired separator
Mika
|
|
|
|
|
Ok, thank you but i want to put this in a stored Procedure...
So i can call it from out of a program!
A.
|
|
|
|
|
Well, you have several options
You can use BCP (with a stored proc or a select) and a format file to create an output file, but this relies on ALL rows having the same number of fields
You can use the idea of concatonating the fields together with your delimiter and use this query in your bcp with effectively a single field per rocord
You can just read the data into your program and write the file yourself.
Try Goggling for format files in bcp
There are probably other ways too, but I can't be bothered to do your job any more...
Bob
Ashfield Consultants Ltd
|
|
|
|
|
It doesn't matter. You can return the result set from procedure almost like you can return it from SELECT statement so wrapping the statement into procedure is ok.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
