Where To Apply My SQL Knowledge...
Jul 22, 2007
Hello everyone...
i am new to Structured Query Language. I am learning it for the past 6 months form a book by myself. I don't have a teacher available to me. Now when i have finished that with all of the exercises completed. i want to make sure what i've learned is i've really learned it. the problem is that there is no such organization here that i join and have some hand on practical. So now i am asking u guys to help me. Tell me some way so that i make sure of whatever knowledge i have about SQL.
don't worry be happy
View 1 Replies
ADVERTISEMENT
Nov 27, 2007
Hi... I'm reading the MS Press 70-442 Self-Paced Training kit, and I'm having problems with this example.
I'd like help getting it to work correctly, or understand why it is isn't working the way I planned.
On page 67, the lab is about the APPLY operator (CROSS APPLY and OUTER APPLY). I first have to input a sample table-valued function into the AdventureWorks database:
Code Block
CREATE FUNCTION fnGetAvgCost(@ProdID int)
RETURNS @RetTable TABLE (AvgCost money)
AS
BEGIN
WITH Product(stdcost)
AS
(
SELECT avg(standardcost) as AvgCost
FROM Production.ProductCostHistory
WHERE ProductID = @ProdID
)
INSERT INTO @RetTable
SELECT * FROM Product
RETURN
END
and then run a sample T-SQL statement
Code Block
SELECT p.Name, p.ProductNumber,
Convert(varchar, cost.AvgCost,1) AS 'Average Cost'
FROM Production.Product p
CROSS APPLY fnGetAvgCost(p.ProductID) AS cost
WHERE cost.AvgCost IS NOT NULL
ORDER BY cost.AvgCost desc
My problem is with the WHERE clause... According to page 56, CROSS APPLY returns only rows from the outer table that produces a result set, so why do I need to explicitly filter NULL values?
When I remove the WHERE clause, the query retrieves lots of NULL AvgCost values.
Again, according to page 56, it is the OUTER APPLY that returns all rows that return a result set and will include NULL values in the columns that are returned from the table-valued function.
So, in short, I don't see the difference between CROSS APPLY and OUTER APPLY, using this example, when I remove the WHERE clause?
(Please refrain from introducing another example into this question.)
View 8 Replies
View Related
Mar 13, 2008
I am loosing my mind on this one. I select info from a database. I then take that selected info, and select info from another table based on that. Straight forward. The CompanyKey attached to my username is 7 which displays in the ReaderResults.Text. I the following line:
selectSQL2 = "SELECT * FROM Company WHERE companyKey = ('" + CompanyKey + "')"
I can change it to:
selectSQL2 = "SELECT * FROM Company_Membership WHERE companyKey = 7 "
and it works. I have no idea what is going on. Dissapearing string? Here is the code:
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'Job1 InfoDim currentUserID
currentUserID = Context.User.Identity.Name.ToString()
Label1.Text = currentUserID
Dim selectSQL1 As String
selectSQL1 = "SELECT companyKey FROM Company WHERE UserID = ('" + currentUserID + "')"Dim cmd1 As New SqlCommand(selectSQL1, con)
Dim reader As SqlDataReaderDim CompanyKey
'Job2 Info
Dim selectSQL2 As String
selectSQL2 = "SELECT * FROM Company WHERE companyKey = ('" + CompanyKey + "')"Dim cmd2 As New SqlCommand(selectSQL2, con)
'Job1 Select
Try
con.Open()
reader = cmd1.ExecuteReader()Do While reader.Read()
CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
ReaderResults.Text = CompanyKey
con.Close()
End Try
'Job2 Select
Try
con.Open()
reader = cmd2.ExecuteReader()
GridView1.DataSource = reader
GridView1.DataBind()
reader.Close()Catch err As Exception
ReaderError.Text = "Error selecting record."
ReaderError.Text &= err.Message
Finally
ReaderResults.Text = CompanyKey
con.Close()
End Try
View 7 Replies
View Related
Mar 31, 2004
Hi,
I need help to test person's skills for
knowledge of SQL stored procedures
and DTS
Could someone point on good resources.
Thank you
Alex
View 4 Replies
View Related
Jun 3, 2004
Hello, everyone:
Are ther any basic information about patch on SQL Server? How to develop patch, and run it? Thanks a lot
ZYT
View 7 Replies
View Related
Aug 29, 2006
Dear Friends, please tell me how can i gain domain knowledge in erp related modules?
View 3 Replies
View Related
Jul 23, 2005
I 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 Related
Jan 12, 2004
Yo!
My company asked me to do a tech interview on a potential contractor for a three month assignment writing SQL Server stored procedures.
I have some things I can ask him to test his qualifications, such as cross-tab query design, UDF usage, running total queries, select first 'N' queries, Primary Keys vs Clustered Indexes, etc...
I was curious what questions other people might ask in order to assess a person's skill. Remember that this is SQL programming only, not admin stuff, and I want to ask questions with a range of difficulty from easy to head-cracker.
Any suggestions?
View 14 Replies
View Related
Sep 24, 2007
I am having a puzzling problem with a DTS package in SQL 2000 that uses a combination of "Execute Process Tasks" and "ActiveX Script Tasks." The issue occurs with one of (the second) the ActiveX Script Task. The script invokes a COM object that was written in C#. This COM object connects to the database and writes a record set and then calls a stored procedure.
When I run this step individually, everything works fine. When I run it as part of the package, the record set is being written but the stored procedure is not invoked (SQL Profile confirms this).
I haven't really begun a serious attempt to troubleshoot the problem, though I have played around with the Transactions and OLE DB properties of the DTS Package.
Any suggestions as to why the behavior might be different, or some straightforward tips on troubleshooting would be appreciated. I have access to the COM component source.
View 1 Replies
View Related
Nov 21, 2006
I dont know where to put this thread, I just wanna ask a question..actually what is happen when subscription synchronized the publications among IIS process.. (SQLMobile)
could you guys give me a diagram/schema of it?
thanks before
View 6 Replies
View Related
Jun 12, 2006
I don't have any knowledge about SQL Server at all... I wanna know:
1. What is SQL Server?
2. Have SQL Server something to do with databases and Microsoft Access to do?
3. What is the link between Visual C++ Express Edition and SQL Server Express Edition?
4. Where on the web can I learn (tutorials) how to use SQL Server Express Edition?
View 3 Replies
View Related
Feb 26, 2007
Dear Friends,
please suggest me the best book to gain sound knowledge in DBA concepts as well as
writing in sub queries, complex queries as well as inner queries.
thank you very much
Vinod
View 4 Replies
View Related
Nov 26, 2007
Hi,
I've been working as a .NET developer for many years. However, my educational background isn't in IT at all, so I occasionally find that there are big gaps in my knowledge when I'm called on to do certain things.
My current role is the first one I've had where the efficiency of my database calls has needed to be top-notch. Most of my previous posts have been in building intranet functionality for medium-sized companies so it's just not been an issue and my fairly basic level of SQL knowledge has been enough to get me by.
However, I'm increasingly finding I'm needing to look stuff up, ask for help and so forth and I'm not at all sure that what I'm putting out is up to scratch. I have three main areas of concern:
1) The developers here seem to like doing a lot of the "work" of data sorting and manipulation in SQL whereas I'd previously have just grabbed the whole table and done the manipulation in .NET. I'm finding building my complex queries is just doing my head in - I'm just not used to "thinking" in the way that SQL requires which seems very different from procedural programming.
2) A lot gets said about properly indexing tables for maximum efficiency. I know what an index is and vaguely what it does, but the means of applying them to ensure the best performance is pretty much beyond me.
3) In the same manner I'm now having to worry a lot more about performance issues in my database design. I now the five normal forms and so on, but is there anything else here I need to do to maximise performance?
What I'm asking, really, is whether anyone can recommend a good source for SQL knowledge (book, site, whatever) that specifically focuses on my first problem - it'll teach me the proper way to structure my queries and think about them properly so that they don't look so damn impenetrable. It then needs to go on and address my other issues.
Any suggestions?
View 4 Replies
View Related
Jul 20, 2005
Greetings,I have to admit that I'm still a beginner in the database field, but I'mactively studying, and this is why I will be utterly grateful for theproper, accurate, and wise guidance to the right direction or specificpaths of database studies.I simply want to make my first major database project a "personalknowledge database" or a "personal encyclopedia", so to speak. By this Imean that I want to gather diverse, multi-format bits of knowledge intoa single database divided into major categories, sub-categories, and soon. For example, I want to scan collected articles from newspapers &magazine and save them as images, type hand-written notes into thecomputer and save them as MS Word or Text files, save audio files withsearchable keywords, even save searchable MS OneNote audio & text files,all into directories & sub-directories. Then I want to be able to searchall these files for specific words or subjects; sort them according todate, subject, etc; update, add to or append, edit, or even cut & paste(within) any of those records in the database, which rely on easilycustomizable formats like text & images (in contrast with audio files orOneNote files, for example).I also want to prevent wasting time in the future, by avoidingrepetitive, same-keywords typing for every non-text new entry, like animage or audio file, by having a customizable keyword list open (byright clicking for example), where one could mark all the relevantkeywords for the picture or audio file. Also have a short descriptionfor each one of those entries when needed. And I want to be able to addslick, good-looking graphics to the interface of the database and theforms of the records.Basically, my priorities include: saving time filling the database withknowledge, quickly & accurately finding the future targets of anysearch, presenting the search results in a highly comprehensible &organized form, and a good looking interface & forms for every entry,which are pleasing to the eye and encouraging to study.I already use graphics applications for the "looks" part. So my questionis: what are the technologies or applications that I should learn byheart and use in order to design such a database in the best way? WouldMS Access alone do it all for me? Or should I learn other technologies?I'll be grateful for anyone taking the time to help. Thanks.Sincerely,Yasso"Claiming that God does not exist because there are people suffering anddying is like saying that barbers do not exist because there are peoplewith long hair! Truth is: they suffer because they did not find God ordo not go to Him, just like the others did not find a barber or do notgo to one."*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!
View 1 Replies
View Related
Jan 17, 2006
Hello,
I've been trying for a couple of days now with the following problem in SQL
Ive a table called tblRresults that has the following
Date - just a regular date
OfficeLocation - just a unique id such as a zip code
SampleType - contains things like wall, floor, beam
SampleResult - "Detected" or "NotDetected"
When populated the table can look like
12/12/05 99505 Wall Detected
12/12/05 99505 Wall NotDetected
10/04/05 99211 Beam Detected
10/04/05 99211 Beam Detected
10/04/05 99111 Floor NotDetected
10/04/05 99111 Floor NotDetected
What I want is (I guess) a crosstab query that produces the following
Date Location SampleType CountDetected CountNotDetected
12/12/05 99505 Wall 1 1
10/04/05 99211 Beam 2 0 (or null would be ok)
10/04/05 99111 Floor 0 2
Any help gratefully accepted, this has been driving me mental!
thanks
View 2 Replies
View Related
Mar 26, 2007
I have a sql server problem for which the solution is at,
http://support.microsoft.com/kb/889521
The article talks about a hot fix, but there is no link for download the hot fix.
Can anyone have a look and guide me.
Thanks
------------------------
I think, therefore I am - Rene Descartes
View 4 Replies
View Related
May 23, 2007
I am using SETROWCOUNT 10 in my stored procedure. At end of thi sp I use SETROWCOUNT 0.
Will all other sp's that are executing at the same time as the above sp, get affected by the above SETROWCOUNT statement?
View 1 Replies
View Related
Sep 7, 2007
I have a table called Courses with columns "courseCode" "courseName" and "TeacherName"Im passing in a value @prefixText ...I have the following...Select courseModuleCode from courses Where courseModuleCode like @prefixTextwhat I actually want to do is return one list, i.e. one column that will do the same for "courseName" and "TeacherName" aswell based on what the @prefix tag is...so i will end up with one column that may contains some or all of the following courseModuleCode, courseName, TeacherName. Im basically implementing a search on this table and want the users to search by any of the column nameshope this makes sense.
View 6 Replies
View Related
Jun 24, 2004
I am new to DTS.
In ExecuteSQLTask:
want the original sql statement of <<backup database databaeABC to backup_databaseABC with init>> to be
backup database ? to 'backup_' + ? with init
But it looks the parameter could not place a globalVariable to it and parse it with error.
One alternate way probably need to create a proc to pass a parm to it....
Also, how could pass a globalVariable to a FTP task?
thanks
-D
View 4 Replies
View Related
Aug 27, 2004
We have a Pub, Dist and Sub Server all running under SP2 with worm update and are looking to apply SP3a . Replication is transactional with heavy usage . What is the best sequence to apply SP3
can it be spread out in 3 weekends or has to be done all together ?
Thanks
View 5 Replies
View Related
Sep 12, 2013
I want to apply lock on a row, where by when another application is trying to access the locked row the application will automatically move to the next row instead of waiting for the locked row
View 3 Replies
View Related
Feb 26, 2004
I am trying to install sql server sp3a. I checked the version before and after the sp is applied, and it shows:
8.00.194
Now, according to the sp3a readme:
http://support.microsoft.com/default.aspx?scid=/support/servicepacks/SQL/2000/SP3ReadMe.asp
the following versions should show:
SQL Server 2000 Original Release 8.00.194 RTM
Database Components SP1 or Desktop Engine SP1 8.00.384 SP1
Database Components SP2 or Desktop Engine SP2 8.00.534 SP2
Database Components SP3 or SP3a, or Desktop Engine SP3 or SP3a 8.00.760 SP3
This would imply that after installing sp3a and restarting, the version reported should now be 8.00.760. However, it still shows 8.00.194. So it appears to have not really installed, althought the sp3a installation ran without incident. So, how can I get the sp3a to not just act like it installed, but actually get it to install? Also, I have shut down all mssql services and rebooted, applied patch, and rebooted again. Doesn't help.
Also, if it is really installing, is there another way to tell?
Anyway, I really need to be able to install sp3a to see if it fixes another problem I'm having. Unfortunetly, since I can't even tell if the sp3a is installing, I can't tell if it really doesn't help or not. Thanks.
Tony F
View 2 Replies
View Related
May 22, 2008
What is Cross Apply, when it will be used ?
View 2 Replies
View Related
Oct 14, 2007
We would like to apply a hotfix to solve the issue that brought our server down. The hotfix we are trying to apply is in Build 3175. But the latest Cumulative Update 3 Package is Build 3186. Should we apply 3175? Or we can go for the latest build 3186? Which is recommended?
View 6 Replies
View Related
Nov 20, 2007
I want to show top selling items for each store. Sample SQL Query is "SELECT TOP n ItemNumber FROM Inventory Group By Store" n - is the dynamic value. I am accepting this n parameter from the user. If the user is giving 5 for n then, I want to show top 5 selling items for each store. It will be greatfull, If anybodies giving me an idea of how to do this?.
Thanks
View 5 Replies
View Related
Feb 20, 2007
Should I apply SP2 even though I have already installed the community preview?
View 1 Replies
View Related
Apr 17, 2008
HI,
I am going to make a big web application. so im going to use sql connection with class for example i have 100 web pages and i will make a slq connection in class.vb
Class1.vb(see the below code)
Imports System.Data
Imports System.Data.SqlClient
Imports Microsoft.VisualBasicPublic Class Class1
Private conn As New SqlConnection("Data Source=DANISHSQLEXPRESS;Initial Catalog=ARBASHHR.MDF;Integrated Security=True")Private cmd As New SqlCommand()
Private da As New SqlDataAdapter(cmd)
End Class
default.aspx(here is asp.net page i want to use the sql connection from that class.)
but this below page saying that Name "cmd" is not declared and connection
Try
cmd.Connection = conn
conn.Open()Dim ds As New DataSet
cmd.CommandText = "SELECT * FROM Users where (UserName = N'" & TextBox1.Text & "')" 'And (Password = N '" & TextBox2.Text & "')"da.Fill(ds, "data")
conn.Close()
For Each dt As DataTable In ds.Tables
For Each dr As DataRow In dt.RowsTextBox3.Text = dr.Item("Password")
Next drNext dt
If TextBox2.Text = TextBox3.Text ThenResponse.Redirect("Welcome.aspx")
Else
Label3.Text = "Invalid Username/password"
End IfCatch ex As Exception
' MsgBox(ex.Message) '("Error Loading Information From Database..", MsgBoxStyle.Critical, "Error")
End Try
End Sub
please let me know where im doing mistake.....
Thank in Advance
View 2 Replies
View Related
Aug 20, 1999
Is anyone else having trouble with the Apply button under the permissions tab?
I can change permissions, click apply, the changes are shown, but when I access the database, the changes arn't made. If I close the permissions windows and re open the changes arn't made.
Will SP1 fix this?(it's not listed in the fix text file)
View 2 Replies
View Related
Feb 7, 2004
HI FRIENDS,
IS THERE ANY PERFORMANCE IMPACT WHEN I USE "CHAR" AS A DATA TYPE INSTEAD OF USING "INT" FOR RETRIEVING DATA OR FOR SOME COMPLEX QUERY.
THANKS
WITH BEST REGARDS,
DHIRAJ
View 3 Replies
View Related
Apr 28, 2008
Hi,
Why am I getting a different numbers of distinct ids in those queries?
USE AdventureWorks
go
Declare @myXml as xml
set @myXml = '
<lol>omg</lol>
<lol>rofl</lol>
';
select locations.*, T.c.value('.','nvarchar(max)') from
(
select newid() as Id
from Production.ProductModel
where ProductModelID in (7, 8)
) as locations cross apply @myXml.nodes('(/lol)') T(c);
select mytable.* , T.c.value('.','nvarchar(max)') from
(
select newid() as Id
union
select newid()
) as mytable cross apply @myXml.nodes('(/lol)') T(c);
Thanks,
Victor
View 8 Replies
View Related
Apr 29, 2008
I have a question, is there any equivalent for the CROSS APPLY operator in SQL server 2000?
I have the following code in SQL Server 2005 and it works fine, but I need an equivalent code in SQL server 2000.
SELECT *
FROM Customers Cust CROSS APPLY dbo.GetAccountAttributes(Cust.AccountNo) Att
what I need is to join a function and passing it a dynamic parameter.
I need it urgently
Thanks in advance,
Imad Elayyan
View 1 Replies
View Related
Mar 2, 2007
how can i apply unique cosntraint in an existing table's column
i want to do it like this as below
sql server enterprise manager ==> DB==>table (R.click)==>design table
View 1 Replies
View Related
Jul 23, 2005
Hi all!I want to create a constraint that uses data from other tables,specifically i want to make sure that a varchar has exactly the lengthspecified in an integer-column in a table that I pointed out with aforeign key.I would like this to be solved something like this:create table string_size_limits(row_id INTEGER PRIMARY KEYstring_size INTEGER)create table strings(string_size_limit_row_id INTEGERREFERENCES string_size_limits(row_id)string varcher(50)CONSTRAINT check_string_size CHECK ???)Is it possible to solve this problem without SP using above model?Is it possible to solve this problem with SP using above model?Must the above problem be solved using triggers?Any help appreciated
View 4 Replies
View Related
