Hi all,
We found a SQL problem when searching in a large communty (GCN). Please, we need someone how can help with this problem. It's urgent.
For details see post:
Page 1: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=693806
Page 2: http://www.asp.net/Forums/ShowPost.aspx?tabindex=1&PostID=693806&PageIndex=2
Thanks in advance, Tomas.
I know, it's a job posting site, but our company is urgently looking for an expert senior level sql server DBA who can be rated as 9.5 out of 10 in sql server dba activities who are hard to find! Top hourly pay on a long contract in East Coast! so anyone interested? please rush resume to MessageDBfan@yahoo.com
View 1 Replies View RelatedHello all,
There is a new way to search through all community content relating to SQL Server Integration Services. Head over to:
http://search.live.com/macros/jamiet/ssis
to use this new Live Search macro.
This will search through this forum, wikis, SSIS websites and blogs. For a complete list and further details head over to: http://blogs.conchango.com/jamiethomson/archive/2007/04/10/SSIS_3A00_-Live-Search-Macro-for-SQL-Server-Integration-Services.aspx
-Jamie
Has anyone any suggestions how I might find the most recent of a series of dates from different tables? I am joining tables which all have a date_altered field and I am only interested in displaying the most recent of these. Eg.
select a.x,b.y,c.z, a.date_altered, b.date_altered, c.date_altered
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
What I would like is in effect a function Highest() Eg.
select a.x,b.y,c.z, highest(a.date_altered, b.date_altered, c.date_altered) as last_alteration
from a inner join b on (a.id=b,id) inner join c on (b.id=c.id)
I am using SQL Server 7 so cannot write myself a function to achieve this - frustrating, as it is something I could have done 4 years ago when I used mostly Oracle.
Many thanks
Idea here is to output results of a stored proc to a text file..
I am trying to use this query to execute a stored proc which in turn accepts 3 parameters. Can some one help me as I am getting syntax errors when I try this way:
EXEC sp_querytofile 'MyDB', "exec sp_myproc ''11/03/04'', ''ABK'', ''TFC'", 'c:sp_myproc_out.txt'
I tried different combination of quotes but couldn't make it work! Any one to help?
--------code for sp_QueryToFile---------
Create PROCEDURE sp_QueryToFile
( @db sysname,
@query VARCHAR(1000),
@file VARCHAR(255)
)
AS
SET NOCOUNT ON
SET @Query = 'SET NOCOUNT ON ' + @Query
EXECUTE ('master..xp_cmdshell ''osql -w8000 -S sqldev14corpappsdev -r -s" ", -h-1 -o' + @file + ' -d'+ @db + ' -Q"' + @query + '" -E''')
/* Procedure Ends */
Hi, I need help with updating a record. When I run the following code underneath the update button, the record does not update and I get no error message. Anyone have any ideas?
Code Snippet
Dim ListingID As String = Request.QueryString("id").ToString
Dim Description As String = DescriptionTB.Text
Dim PlaceName As String = PlaceNameTB.Text
Dim Location As String = LocationTB.SelectedValue
Dim PropertyType As String = LocationTB.SelectedValue
Dim Price As Integer = PriceTB.Text
Dim con As New SqlConnection(ListingConnection)
Dim sql As String = "Update Listings SET Description = ?, PlaceName = ?, Location = ?, PropertyType = ?, Price = ? WHERE ListingID = ?"
Dim cmd As New SqlCommand(Sql, con)
cmd.Parameters.AddWithValue("Description", Description)
cmd.Parameters.AddWithValue("PlaceName", PlaceName)
cmd.Parameters.AddWithValue("Location", Location)
cmd.Parameters.AddWithValue("PropertyType", PropertyType)
cmd.Parameters.AddWithValue("Price", Price)
Try
con.Open()
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
I have done a couple of little projects in VB express and was pleased with the results. Now I have something bigger in mind and need to know if VB express version of SQL will support merge replication. If not will the full version support it?
Thanks
Dave
I’m working on a project that will allow a user to search through approx 100,000 records in a SQL table. Three of the columns are ‘text’ fields that hold paragraphs of text. The user interface has a ‘general search’ option so that they can enter a number of key words and the database will return a count of the records found containing the keywords.
At the moment I split the input and then build a query based on their input. For instance if they enter ‘hello world’ the input is split into two strings ‘hello’ and ‘world’. I then build the query in a loop and get a query like so:
Select Count(ID) as myCount FROM myTable WHERE (colOne like ‘%hello%’ AND colOne like ‘%world%’) OR (colTwo like ‘%hello%’ AND colTwo like ‘%world%’) OR (colThree like ‘%hello%’ AND colThree like ‘%world%’)
Unfortunately this query runs EXTREMELY slowly and just seems wrong. Is there a more efficient way I should be doing these types of searching? This method works ok on 100 records, but this is the first time I have worked on such a large database.
Is it also possible to search a text column and look for exact matches?
For instance I have 2 records with their textfield containing:
Rec 1: the news for today is blah blah. Rec 1: this is a new item
If I currently search for ‘new’ (select colID from myTable where colOne like ‘%new%’) I will get both these records, but I’d really only like to pull out the second record.
Any help would be great appreciated! :)
Hi everyone,
I'm trying to implement SQL Server database search. The details are:-1. I have table called EMPLOYEE has FNAME,LNAME etc cols.2. User might look for any employee using either FNAME or LNAME3. I have search box in asp.net where user could enter search stringThe sample data:FNAME LNAMEabc georgedef georgerkis litarose lita
The query i wrote:SELECT * FROM EMPLOYEE WHERE lname like '%' + searchArg + '%'My problem is:-1. let's say user is looking for employee "george"; In search string instead of typing actual word "george", user could type "jeorge"; because the name pronounce or sounds like similar.Same thing with user could type "leta" instead of "lita". Again these are all similar sounds.
When you look for "jeorge" in GOOGLE; it says "did you mean george"; i would like implement something like that. somewhere i saw SOUNDEX would do what i am looking for; but i no luck for me.
Is this possible anyway in T-SQL or Fulltext search.
Your help is greatly appreciated.
ThanksBob
I need to search through a database that has a large number of tables to see if each table has an index that is based on the
primary key. I need this information in order to determine whether I can setup database replication or not. I believe all tables
involved with a replication need to be indexed on the primary key. Does anyone know of a stored procedure or command file that
will provide this information for me in a timely manner? I've used the sp_pkeys stored procedure but it is very time consuming
running this against one table at a time.
I've a database with a table that has 16 columns that are searchable. There can be a numerous combination of those columns used for searching...
In this case the best solution is to create an index on each column individually or at least the most used?
I have a query below which filters detail field in the #TempLogins table. The details field is a text field which contains many types of text strings, some containing urls that have parts like "ResultID=5" which is what is contained in the ResultIDSearch and ResultSetIDSearch fields. The records with entries like "ResultID=5" are the ones I'm trying to filter for.
The problem I have is that the query takes way too long to run. The TempLogin table has around 200 K records and the TempSearch table has around 80 K records.
select * from #TempLogins a where exists
(select 1 from #TempSearch t1 where
a.detail like '%' + t1.ResultIDSearch + '%'
or
a.detail like '%' + t1.ResultSetIDSearch + '%')
Hi! Hello. I have now started to build my own community. And I have some questions on the database.For the users to login I use the login control and all the users information is stored in the ASPNETDB.MDF database.In the web.config file I have created some profiles for saving some information about the users (Name, Birth, Town) and so on.Now. All the users in this community will have their own profile page, Guestbooks ++.So I was wondering if I should create tables for all features like guestbook, profile pages or should I do this by using Profile (ASP.NET).How many users does ASPNETDB support?.
View 1 Replies View RelatedI have noticed that the area of writing stored procedures for muti-user databases is a very specialised field and requires knowledge that's much more than the locking topics covered in 'online books' . I am sure there are some standard tips and tricks that are used in mutil-user databases for writing to tables. Most books have a chapter or two on locking, but I think this topic should be dealt with separately in a dedicated book to locking with extensive examples on locking. Does anyone know of such a dedicated book out there?
View 1 Replies View RelatedI posted this question in the Community Server forums, but thought I would try here as well...
I am trying to use SQL Integration Services (SSIS) to dump out the images from Community Server and I'm getting zero length files. Here's what I'm trying....
I have a Data Flow Task with an OLEDB Data Source with this query (The TOP is for testing purposes) :
SELECT Top 10 cs_Posts.Body, cs_Posts.PostDate, cs_PostAttachments.FileName, cs_PostAttachments.[Content], cs_PostAttachments.ContentType,
cs_PostAttachments.ContentSize, cs_Post_Categories.Name
FROM cs_Posts INNER JOIN
cs_PostAttachments ON cs_Posts.PostID = cs_PostAttachments.PostID INNER JOIN
cs_Posts_InCategories ON cs_Posts.PostID = cs_Posts_InCategories.PostID INNER JOIN
cs_Post_Categories ON cs_Posts_InCategories.CategoryID = cs_Post_Categories.CategoryID
WHERE (cs_Posts.ApplicationPostType = 64)
Then I have an Export Column Transformation set to export cs_PostAttachments.[Content] with cs_PostAttachments.FileName as well the file name. Ultimately I'm going to break the filepath all down into folders by the category name, but for now I'm just trying to get the export working.
Anyway when I run it I end up with files with the right names, but they are all zero length files.
Any ideas?
Thanks,
-p
Hi,
I am trying to install Microsoft SQL Server September 2005 Community Technology Preview.
I get the generic error saying "SQL Server 2005 setup has detected incompatibe components...."
So I checked the C:Program FilesMicrosoft SQL Server90Setup BootstrapLOGSQLSetup0020_Core.log file
which says
C:SQLENT2005Serverssetup.exe Version: 2005.90.1314.0
Running: LoadResourcesAction at: 2005/9/11 13:3:29
Complete: LoadResourcesAction at: 2005/9/11 13:3:29, returned true
Running: ParseBootstrapOptionsAction at: 2005/9/11 13:3:29
Loaded DLL:C:SQLENT2005Serversxmlrw.dll Version:2.0.3604.0
Complete: ParseBootstrapOptionsAction at: 2005/9/11 13:3:30, returned true
Running: ValidateWinNTAction at: 2005/9/11 13:3:30
Complete: ValidateWinNTAction at: 2005/9/11 13:3:30, returned true
Running: ValidateMinOSAction at: 2005/9/11 13:3:30
Complete: ValidateMinOSAction at: 2005/9/11 13:3:30, returned true
Running: PerformSCCAction at: 2005/9/11 13:3:30
Complete: PerformSCCAction at: 2005/9/11 13:3:31, returned true
Running: ActivateLoggingAction at: 2005/9/11 13:3:31
Complete: ActivateLoggingAction at: 2005/9/11 13:3:31, returned true
Running: DetectPatchedBootstrapAction at: 2005/9/11 13:3:31
Complete: DetectPatchedBootstrapAction at: 2005/9/11 13:3:31, returned true
Action "LaunchPatchedBootstrapAction" will be skipped due to the following restrictions:
Condition "EventCondition: __STP_LaunchPatchedBootstrap__1952" returned false.
Running: PerformSCCAction2 at: 2005/9/11 13:3:31
Loaded DLL:C:WINNTsystem32msi.dll Version:3.1.4000.2435
Product "{53F5C3EE-05ED-4830-994B-50B2F0D50FCE}" versioned 9.00.1187.07 is not compatible with current builds of SQL Server.Expected at least version: 9.00.1314
The Product Name is "Microsoft SQL Server Setup Support Files (English)"
Loaded DLL:C:WINNTsystem32msi.dll Version:3.1.4000.2435
Error: Action "PerformSCCAction2" threw an exception during execution.
Return Code: 70032
Message displayed to user
SQL Server 2005 Setup has detected incompatible components from beta versions of Visual Studio, .NET Framework, or SQL Server 2005. Use Add or Remove Programs to remove these components, and then run SQL Server 2005 Setup again. For detailed instructions on uninstalling SQL Server 2005, see the SQL Server 2005 Readme.
I understand that I need to uninstall "Microsoft SQL Server Setup Support Files (English)"
But when I try to do that I get
"A network error occured while attempting to read from C:WinntInstallerSQLSupport.msi"
how do i fix this...
I'm just wonder if this is a bug in MS Search or am I doing something wrong.
I have a query below
declare @search_clause varchar(255)
set @Search_Clause = ' "hepatitis b" and "hepatocellular carcinoma"'
select * from results
where contains(finding,@search_clause)
I don't get the correct result at all.
If I change my search_clause to "hepatitis" and "hepatocellular carcinoma -- without the "b"
then i get the correct result.
It seems MS Search doesn't like the phrase contain one letter or some sort or is it a know bug?
Anyone know?
Thanks
I am looking into a position requiring "SQL PLUS Expert" - anbody out there heard of this - is it a querying tool a la query analyzer of PL SQL.
View 2 Replies View RelatedI am looking into a position requiring a "SQL PLUS Expert" - anybody out ther heard of this - is it a querying tool a la query analyzer or PL SQL ?
View 1 Replies View RelatedHello,
Lets just say that I have really only logged into phpmyadmin once and messed up my forum.
What I was trying to do? Install a shoutbox (chatroom type software).
When I logged into phpmyadmin I clicked the phpbb_config tab on the left hand side. Then I clicked the SQL tab and pasted the code required for the shoutbox accordingly and hit the "Go" button. But what I did next is what created problems.
I thought I may have put extra spaces or loaded the code wrong in the SQL area because the shoutbox did not work properly after everything was installed. So while in phpmyadmin and after clicking phpbb_config agian, instead of hitting SQL, I hit the "Empty" button thinking that I could just erase what I had previously inserted and then try inserting it again to make sure the code was inserted properly.
If you havn't figured it out by now, I clearly don't have a clue what I am doing, what exactly I did, or what I can do to fix it.
Click on the link to see the error..
http://www.cflzone.com/forum/index.php
Can someone that knows what they are doing please help me???
thanks,
- Reider
Hello,Consider I have a String:Dim MyString As String = "Hello"or an Integer:Dim MyInteger As Integer = 100or a class which its properties:Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100Or even a control:Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass" Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?Something like: Define something (Integer, String, Class, Control, etc) Save in SQL 2005 Database Later in code Retrive from database given its IDIs this possible?How should I do this?What type of SQL 2005 table field should be used to store the information?Thanks,Miguel
Fact is:
I have two tables Orderposreg and Temp1 (both in [My Database]), Orderposreg is from 1994, Temp1 from 1995 and i need to Update the old table with the new one.
This should my Query do: I need it to Update old primary keys, or if the row does not exist to insert the new primary key into the Orderposreg.
But if I start the Query i get this failure message:
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'dbo.Temp1' does not match with a table name or alias name used in the query.
It seems that the colums do not have the same name, but the column of both tables have exactly the same column name and data type just the table name is different.
For your information the whole Code:
Code:
declare @error varchar, @rowcount varchar
select count(*) from dbo.temp1
if (@@error <> 0)
begin
Print 'An error occurred reading the temporary import table'
goto exitpoint
end
begin transaction
update dbo.Orderposreg
set dbo.Orderposreg.Ordernr = dbo.Temp1.Ordernr,
dbo.Orderposreg.Pos = dbo.Temp1.Pos,
dbo.Orderposreg.Produktnr = dbo.Temp1.Produktnr,
dbo.Orderposreg.Artbenämn = dbo.Temp1.Artbenämn,
dbo.Orderposreg.Artikelgrupp = dbo.Temp1.Artikelgrupp,
dbo.Orderposreg.Förs_pris = dbo.Temp1.Förs_pris,
dbo.Orderposreg.Kvant = dbo.Temp1.Kvant,
dbo.Orderposreg.Total_Intäkt = dbo.Temp1.Total_Intäkt,
dbo.Orderposreg.SSort = dbo.Temp1.Sort,
dbo.Orderposreg.Datum = dbo.Temp1.Datum,
dbo.Orderposreg.Utskriven = dbo.Temp1.Utskriven,
dbo.Orderposreg.Levtid_Ö = dbo.Temp1.Levtid_Ö,
dbo.Orderposreg.Levtid_L = dbo.Temp1.Lev_kvant,
dbo.Orderposreg.Inköpskostnad = dbo.Temp1.Inköpskostnad,
dbo.Orderposreg.Vikt_tol = dbo.Temp1.Vikt_tol,
dbo.Orderposreg.AntalSt = dbo.Temp1.AntalSt,
dbo.Orderposreg.Spec_nr = dbo.Temp1.Spec_nr,
dbo.Orderposreg.Spec_utgåva = dbo.Temp1.Spec_utgåva,
dbo.Orderposreg.ID_Beräknad = dbo.Temp1.ID_Beräknad,
dbo.Orderposreg.Bredd = dbo.Temp1.Bredd,
dbo.Orderposreg.Tjocklek = dbo.Temp1.Längd,
dbo.Orderposreg.ValsnGrad_kod = dbo.Temp1.ValsnGrad_kod,
dbo.Orderposreg.Kant_Kod = dbo.Temp1.Kant_Kod,
dbo.Orderposreg.Planhets_kod = Temp1.Yt_kod,
dbo.Orderposreg.LevForm_kod = dbo.Temp1.LevForm_kod,
dbo.Orderposreg.Rakhets_kod = dbo.Temp1.Rakhets_kod,
dbo.Orderposreg.BreddTol_kod = dbo.Temp1.Breddtol_kod,
dbo.Orderposreg.TjocklTol_kod = dbo.Temp1.TjockTol_kod,
dbo.Orderposreg.LängdTol_kod = dbo.Temp1.LängdTol_kod,
dbo.Orderposreg.Stål_kod = dbo.Temp1.Stäl_kod,
dbo.Orderposreg.TotaltSek = dbo.Temp1.TotaltSek,
dbo.Orderposreg.Tullstatnr = dbo.Temp1.Tullstatnr,
dbo.Orderposreg.Fakturera = dbo.Temp1.Fakturera,
dbo.Orderposreg.Leveransstatus = dbo.Temp1.Leveransstatus,
dbo.Orderposreg.Momsbelopp = dbo.Temp1.Momsbelopp,
dbo.Orderposreg.Total_inkl_moms = dbo.Temp1.Total_inkl_moms,
dbo.Orderposreg.KloMPS = dbo.Temp1.KloMPS,
dbo.Orderposreg.ShopFloor = dbo.Temp1.ShopFloor,
dbo.Orderposreg.Status = dbo.Temp1.Status,
dbo.Orderposreg.Stålkod = dbo.Temp1.Stålkod,
dbo.Orderposreg.Kontonyckel = dbo.Temp1.Kontonyckel,
dbo.Orderposreg.PlanLevDat = dbo.Temp1.PlanLevDat,
dbo.Orderposreg.Legtillägg = dbo.Temp1.Legtillägg,
dbo.Orderposreg.StålGrp = dbo.Temp1.StålGrp,
dbo.Orderposreg.KundNr = dbo.Temp1.KundNr,
dbo.Orderposreg.FKundNr = dbo.Temp1.FKundNr,
dbo.Orderposreg.Godsmärke = dbo.Temp1.Godsmärke,
dbo.Orderposreg.LagerMtr = dbo.Temp1.LagerMtr,
dbo.Orderposreg.Sortkvant = dbo.Temp1.Sortkvant,
dbo.Orderposreg.Sortpris = dbo.Temp1.Sortpris,
dbo.Orderposreg.KundsProdNr = dbo.Temp1.KundsProdNr,
dbo.Orderposreg.Godsmärke2 = dbo.Temp1.Godsmärke2,
dbo.Orderposreg.RegDatum = dbo.Temp1.RegDatum,
dbo.Orderposreg.PlanBetDag = dbo.Temp1.PlanBetDag,
dbo.Orderposreg.Säkring = dbo.Temp1.Säkring
where dbo.Orderposreg.Ordernr_o_pos = dbo.Temp1.ordernr_o_pos
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error updating - Error number '+@error+'. Rolling back'
--this reverses your updates
rollback
--this jumps to the end
goto exitpoint
end
print @rowcount+' rows updated'
insert into dbo.Orderposreg
(Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring)
select
Ordernr, Pos, Ordernr_o_pos, Produktnr, Artbenämn, Artikelgrupp, Förs_pris, Kvant, Total_Intäkt, Sort, Datum, Utskriven, Levtid_Ö, Levtid_L, Lev_kvant, Inköpskostnad, Vikt_tol, AntalSt, Spec_nr, Spec_utgåva, ID_Beräknad, Bredd, Tjocklek, Längd, ValsnGrad_kod, Kant_kod, Planhets_kod, Yt_kod, LevForm_kod, Rakhets_kod, BreddTol_kod, TjocklTol_kod, LängdTol_kod, Stål_kod, TotaltSek, Tullstatnr, Fakturera_kvant, Leveransstatus, Fri_text, Momsbelopp, Total_inkl_moms, KloMPS, ShopFloor, Status, Stålkod_text, Kontonyckel, PlanLevDat, Legtillägg, StålGrp, KundNr, FKundNr, Godsmärke1, LagerMtr, Sortkvant, Sortpris, KundsProdNr, Godsmärke2, RegDatum, PlanBetDag, Säkring
from dbo.Temp1
where dbo.Temp1.Ordernr_o_pos not in (select Ordernr_o_pos from dbo.Orderposreg)
select @error = @@error,@rowcount=@@rowcount
if @error <> 0
begin
print ' Error inserting - Error number '+@error
print ' Rolling back updates and inserts'
--this reverses your updates and inserts
rollback
--this jumps to the end
goto exitpoint
end
print @rowcount+' rows were inserted'
--this saves your data
commit
exitpoint:
Thanks a million for somebody who helps me!!!
Hi !
I have been worked with VC++, MS SQL SERVER, Transact-SQL for
3 years. I made an axtended stored procedure (xp_test) which returns
an recordset.
From Query Analizer, I can see the recordest : exec xp_test
I want to make an User Defined Function - MyTest which return
the recordset that it is returned by xp_test after its execution.
Something like that :
CREATE function dbo.MyTest ( )
RETURNS @table ...
AS
BEGIN
exec master.. xp_test table1 output -- can I do this ?
RETURN table1
END
Table and table1 are the same design.
Thank you very much !
Hello,
I am using Enterprise Library Data Access and SQL 2005 application block and I need to know if it is possible to do the following:
Consider I have a String:
Dim MyString As String = "Hello"
or an Integer:
Dim MyInteger As Integer = 100
or a class which its properties:
Dim MyClass As New MyCustomClass
MyClass.Property1 = "Hello"
MyClass.Property2 = Unit.Pixel(100)
MyClass.Property3 = 100
Or even a control:
Dim MyLabel As Label
MyLabel.Id = "MyLabel"
MyLabel.CssClass = "MyLabelCssClass"
Is there a way to save a String, an Integer, a Boolean, a Class, a Control in an SQL database?
Something like:
Define something (Integer, String, Class, Control, etc)
Save in SQL 2005 Database
Later in code Retrive from database given its ID
Is this possible?
How should I do this?
What type of SQL 2005 table field should be used to store the information?
Thanks,
Miguel
Is there a expert here on full text indexing on 2005 sql server express????
View 8 Replies View RelatedI do a lot of hiring for my company and a lot of the people I interviewsay that they are experts at SQL queries, but when I give them somethingsimple just beyond the typical SELECT type of queries, they choke.For example I have a table that looks like this:PK_ID - primary keyPARENT_ID - a FK to another row in the same tableThis essentially is a tree structure. I will ask interviewees to writea query that will return all the rows that have 2 direct children. Noone knows how to do this.I don't get it. I have done queries which boggle the mind and they arefar more complex than this.Am I asking too much?--* Don VaillancourtDirector of Software Development**WEB IMPACT INC.*phone: 416-815-2000 ext. 245fax: 416-815-2001email: Join Bytes! <mailto:donv@webimpact.com>web: http://www.web-impact.com/ This email message is intended only for the addressee(s)and contains information that may be confidential and/orcopyright. If you are not the intended recipient pleasenotify the sender by reply email and immediately deletethis email. Use, disclosure or reproduction of this emailby anyone other than the intended recipient(s) is strictlyprohibited. No representation is made that this email orany attachments are free of viruses. Virus scanning isrecommended and is the responsibility of the recipient./
View 15 Replies View RelatedHi folks, I have a very typical database for an ASP.net application. There is a table which will contain a hierarchical data..much like files-folders structure of a file system.
But we know that the table will be a giant one in production. There will be a huge collection of data need to persist in it. we are already facing some performance problem with some queries during the QA/test machine.
Currently there is a table which is keeping all file and folder information and another table maintaing their hierarchy relation using two column namely, parentID and childID.
My first question is, would it be better to keep this hierarchy relation into the same table rather using a different one? (much like managerID and empID in AdventureWorks sample?)
My Second question, what is the best way to design this kind of structure to get the highest performance benifit?
All kind of thoughts will be appreciated much! thanks
Hi,
I have a table describing items with 3 rows:
ID : its an identity Primary key
Type : nvarchar string for itemtype
ItemData : numeric data for an item
I would like to create a query to get the results where each returned row contains
- Type
- number of items for the given type
- concatenated string of the ItemData numbers for all items for the given type
ex: Items
1,"Big",1
2,"Big",56
3,"Small",45
4,"Big",22
Expected result:
"Big",3,"1 56 22"
"Small",1,"45"
I started with this query:
SELECT Type , COUNT(Type) AS Amount FROM Items GROUP BY Type
but i cannot figure out how to do the string concatenation (its like SUM but we dont need to add the values but we need to concatenate). I was thinking some stored procedure but this query will run on Compact SQL so thats not possible according to Compact SQL specs. Can any SQL expert help, how to do this, or is it possible to do anyhow?
Thanks for the help.
First i am designing a small database fo similar types of books ( Stories table , Short stories table , ...etc ) so i the columns in each table of them is similar , so instead of using a Stored Procedure for every table to select the TOP book i used one SP with variable table name and it works well as i pass the table name from the page , here it is :
---------------------------------------------
CREATE PROCEDURE Top1
@tblname Varchar(255)
AS
EXEC ( 'SELECT TOP 1 *
FROM ' + @tblname + '
WHERE Status="OLD"
ORDER BY ID DESC')
GO
-----------------------------------------------
The Problem now that i want to make the same thing with the INSERT STATEMENT to input new books to the table by passing the table name from the page like the SELECT one , and i `ve tried this one but gives me errors :
--------------------------------------------------------------------------------------------------
CREATE PROCEDURE AddNewStory
@tblname varchar(50),
@title varchar(50),
@Content varchar(5000) ,
@Picture varchar(200) ,
@date datetime
AS
EXEC('INSERT INTO' + @tblname + '( Sttitle , StContent, StPic, StDate )
VALUES ( '+ @Title + ',' + @Content+ ',' + @Picture+ ',' + @date + ')' )
GO
----------------------------------------------------------------------------------------------------
& th error is
Server: Msg 128, Level 15, State 1, Line 2
The name 'Dickens' is not permitted in this context. Only constants, expressions, or variables allowed here. Column names are not permitted.
Stored Procedure: db1sql.dbo.AddNewStory
Return Code = 0
SO PLEASE HELPPPPPPPPPPP
Is anybody out there using SQL Expert Pro? Can you give me some feedback please?
I've been evaluating it for two weeks now and am very pleased with the results but I'm a bit hesitant to spend $5000.
Thanks!
Pete Karhatsu
Hi,
I posted last year when we were having problems with a new SQL box we had moved to here...
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=74650
...we coded round most of the issues and deadlocks and things seemed to improve for quite a while but we have recently run into performance problems again of late.
The CPUs on our SQL box often thrash away at 100% and our ColdFusion website has begun running painfully slow.
Our developers/hosts have suggested we might need to look for a 3rd party SQL guru who could look at the SQL box, do some tracing etc. and perhaps make some recomendations around optimising the DB architecture or changing the way we run certain queries. At the moment we have only gut instinct on which bits of the DB are likely to be being hit most hard.
Our website has grown from being very small to being really quite busy and it's possible we are running into shortcomings with the design of the DB that need to be tackled before we can expand further.
I'm not sure what the protocol is (I see there is a Jobs part of the site) but I wondered about the possibility of getting one of you guys in for a short while to review our server and database, for a fee of course. I'm not sure how long it would take to review that kind of data and get a feel for the usage?
We are based in the UK and whilst I guess it could be done remotely it might be easiest if the person was UK based too.
I'm as much interested in feedback about the idea (it might be not workable/a good idea for example) as I am to people offering their services.
If this post breaks any rules please let me know.
Cheers,
Tem
Hello friends,I have to design a database for banking application,but it is my first db design so i need some expert advice.
Data base has some performance constraint i.e high output ,minimum query time,need to process 2 to 5 million transaction per day.
Further specification can be revealed if appropriate help is provided.
your advice will be of great help for me.
Thx
I have two tables - T_Assets and T_Inspections.
I am trying to create a view where I see the date of the last inspection for each asset. Here is some sample SQL:
SELECT T_Assets.I_AssetID, T_Inspections.I_InspectionID, Max(T_Inspections.SDT_DateOfInspection)
FROM T_Assets
INNER JOIN T_Assets.InspectionID = T_Inspections.I_InspectionsID
GROUP BY T_Assets.I_AssetID, T_Inspections.I_InspectionID
Now, as anyone who is experienced in sql will know, this will not work. This is because the I_InspectionID is different - so the group by will not work. The results I get are basically the same as if the MAX() function was not applied.
Can anyone please tell me a way around this.
Jagdip