Basic Syntax Explanation
Jul 23, 2005In T sql for sql server, what is the technical difference between the
comparisons "is" and "="
for example:
set @test = null
print @test is null -> true
print @test = null -> false
ADVERTISEMENT
Just An Explanation Of What This Means Please
Jan 22, 2008Hi,
I have a project that has been given to me and need help please. The complete class is as follows
Public Class CarAccessData
Public Function Getcarinfo() As List(Of CarInfo)
Dim AllCarInfo As New List(Of CarInfo)
Dim SQL As String SQL = "SELECT [SKU], [CarMake], [CarModel], [Carprice]"
Dim MyConnection As SqlConnection MyConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("AntConnectionString1").ConnectionString)
Dim aCmd As SqlCommand aCmd = MyConnection.CreateCommand
aCmd.CommandText = SQL aCmd.CommandType = CommandType.Text
Dim aDataReader As SqlDataReader
Try
MyConnection.Open()
aDataReader = aCmd.ExecuteReader
While aDataReader.Read()
AllCarInfo.Add(New CarInfo(aDataReader)) End While
Catch ex As Exception Throw ex Finally
aDataReader.Close()
MyConnection.Close() End Try
Return AllCarInfo End Function
End Class
The bit I dont quite understand is the following snippet from above
Dim aCmd As SqlCommand aCmd = MyConnection.CreateCommand
aCmd.CommandText = SQL aCmd.CommandType = CommandType.Text
Dim aDataReader As SqlDataReader
Try
MyConnection.Open()
aDataReader = aCmd.ExecuteReader
While aDataReader.Read()
AllCarInfo.Add(New CarInfo(aDataReader)) End While
Catch ex As Exception Throw ex Finally
Can anyone explain this in real ABC style step by step please, just so I can start to understand this, ( I am quite new to this)
many thanks,
Anteater
Explanation Of Columns_updated()
Jun 1, 2004the statement
if ((columns_updated() & 2 + 4 + 8)) > 0) is supposed to tell me if the 2nd, 3rd or 5th columns were updated. My question is, what desginates the column 2,3,5, when 2,4,8 are in the statement
Need Some Explanation On Transactions
Apr 28, 2005I am having some problems with transactions, although it seems that the problem exists within the vb code making the db call it may be that sql is the source of the problem.
1.
There is a number of stored procs that contain transactions most of which are inserts followed by a select statement to retrieve the most recently added ID. So to start with is a select the best way or is the @@Identity. I have read the @@Identity is global, so for a external server that is running a number of databases I stayed away from it. Did I take the right actions or is there a better way?
2.
Is having sql transactions within a call from vb.net ok? I know that the sqlClient class doesn't support nested transactions, but does that include transactions within sql?
3.
if a stored proc is called, from vb contained in a sqlClient transaction, are ALL the calls from within the sproc able to be rolled back?
If there is a reliable way to obtain the Identity without containing it in a transaction, and having the internal sql transactions in the problem, then I am home free, so I am hoping this is the case.
Thanks for any help
Simple Explanation Please
Jul 23, 2005PREDICATESUsed as a clause.A. What does PREDICATES mean?B. What does it mean when used in a where clause?I checked BOL (Glossary) but get no explanation there.ThanksJay
View 5 Replies View RelatedSQL Queries - Explanation Of Od
Oct 31, 2007I am wondering what are od1 and od2 used for in SQL Queries? Are they used for joining... can anyone explain their significance in the queries below... (especially the commands in red)
USE Northwind
SELECT OrderID, CustomerID
FROM Orders o
WHERE 20 < (SELECT Quantity
FROM [Order Details] od
WHERE o.OrderID = od.OrderID AND od.ProductID = 23)USE Northwind
SELECT DISTINCT ProductName, Quantity
FROM [Order Details] od1 JOIN Products p
ON od1.ProductID = p.ProductID
WHERE Quantity = (SELECT MAX(Quantity)
FROM [Order Details] od2
WHERE od1.ProductID = od2.ProductID)
Query To Find Nth Value Explanation
Jun 20, 2007can anyone explain how this works:
USE NORTHWIND
GO
SELECT freight
FROM orders E1
WHERE (N =
(SELECT COUNT(DISTINCT (E2.freight))
FROM orders E2
WHERE E2.freight >= E1.freight))
replace N by a number. To find that Nth value from the table.
Ashley Rhodes
Very Chalanging Question - Explanation
May 31, 2006Ok, here is a asample table representing the problem more clearlyA | B | C | D-----------------a1 b1 c1 d1a1 b2 c2 d2a3 b3 c1 d3a4 b4 c4 d3a5 b5 c5 d5a6 b6 c6 d3Tha duplications are:row 1+2 in param Arow 1+3 in param Crow 3+4+6 in param Donly row 5 is unique in all parameters.conclusion: row 1+2+3+4+6 are the same usergoal: to find all duplicated rows & to delete them all accept oneinstance to leave.Note:Finding that row 1similar to 2 in A & deleting it will loose databecause we won't know that row 1 is ALSO similar to 3 on C & later onfinding that 3 is similar to 4 & 6 on D & so onThe simple time consuming (about 2 weaks) query to acomplish the taskis:SELECT count(*),A.B,C,DFROM tblGROUP BY A,B,C,DHAVING count(*)>1I THANK YOU ALL
View 3 Replies View RelatedExplanation Of SQL Server Database Files
Feb 22, 2000When a database is created in SQL Server, 4 files are made:
(example)
DB_Data.DAT
DB_data.mdf
DB_Log.DAT
DB_log.ldf
What do each of these files contain? I can figure out that the main database is the DB_Data.DAT, but why is the transaction log a .DAT and why is there four files instead of two? etceterea.
Llyal
Instr (vbscript) Function Explanation ???
Jan 15, 2001I went to Microsoft to find some info about the function Instr. I need to
perform a search with a string similar to their example I found below. Can
anyone explain to me Microsoft's example?? I am little confused by the
parameters used and the explanation it gives back to me??
Dim SearchString, SearchChar, MyPos
SearchString ="XXpXXpXXPXXP" ' String to search in.
SearchChar = "P" ' Search for "P".
MyPos = Instr(4, SearchString, SearchChar, 1) ' A textual comparison
starting at position 4. Returns 6.
MyPos = Instr(1, SearchString, SearchChar, 0) ' A binary comparison
starting at position 1. Returns 9.
MyPos = Instr(SearchString, SearchChar) ' Comparison is binary by default
(last argument is omitted). Returns 9.
MyPos = Instr(1, SearchString, "W") ' A binary comparison starting at
position 1. Returns 0 ("W" is not found).
My problem is this:
I need to scan within SearchString for blanks/spaces characters. When I
find one, then place the values to the left and right of it in seperate
columns. For example, I would need to scan 'John Smith A' and then place
'John' in FirstName column, 'Smith' in LastName column, and 'A' in MidName
column.
I think this is how my code would read, but I am confused on how to place
the results into my table to the correct columns?
my search string would be SearchString = 'John Smith A'
my SearchChar would be SearchChar = ' ' (note I am searching for a
space/blank character)
So would then my code be like:
Dim SearchString, SearchChar, MyPos
SearchString = 'John Smith A'
SearchChar = ' '
MyPos = Instr(1, SearchString, SearchChar, 0)
How do I get whatever is returned from the Instr function to a column in a
table??
Any help would be great.
Rey
Need An Explanation Of Msdtssrvr.ini.xml And Named Instances
Apr 30, 2008We've read about a single installation of SSIS applies to all SQL instances on a server. We've also read how by default SQL Server stores SSIS packages in the MSDB database on the default instance and if you want to have packages stored on a named instance you must modify msdtssrvr.ini.xml. Here are our questions.
1 - If msdtssrvr is modified to reference a named instance, such as <ServerName>SQLTESTINST1<ServerName>, when I connect to Integration Services through Management Studio do I still specify the default instance name in the login window? That's the only way we have been able to connect and despite specifying the default instance name, the packages displayed are those of the named instance.
2 - If I am correct that you must always specify the default instance name when connecting to Integration Services, then in order to eliminate confusion regarding what SQL instance's SSIS packages I am viewing under the folder structure I can create SQL Server folders by modifying msdtssrvr.ini.xml. For example:
<?xml version="1.0" encoding="utf-8"?>
<DtsServiceConfiguration xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<StopExecutingPackagesOnShutdown>true</StopExecutingPackagesOnShutdown>
<TopLevelFolders>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB</Name>
<ServerName>.</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST1</Name>
<ServerName>SQLTESTINST1</ServerName>
</Folder>
<Folder xsi:type="SqlServerFolder">
<Name>MSDB-INST2</Name>
<ServerName>SQLTESTINST2</ServerName>
</Folder>
<Folder xsi:type="FileSystemFolder">
<Name>File System</Name>
<StorePath>..Packages</StorePath>
</Folder>
</TopLevelFolders>
</DtsServiceConfiguration>
I then restart the SSIS service and can now see the following folder structure.
Running Packages
Stored Packages
+ File System
+ MSDB
+ MSDB-INST1
+ MSDB-INST2
If I expand MSDB-INST1 I will only see packages saved on the instance named INST1. The same is true for INST2. If I expand MSDB I will only see packages saved on the default instance.
Does this sound like I am doing things correctly?
Thanks, Dave
Explanation Wanted About The Service Principal Name ( SPN )
Jun 26, 2007Hello,
In the ErrorLog of my Sql Server , i found this line :
2007-06-26 05:35:18.37 Serveur The SQL Network Interface library could not register the Service Principal Name (SPN) for the SQL Server service. Error: 0x54b, state: 3. Failure to register an SPN may cause integrated authentication to fall back to NTLM instead of Kerberos. This is an informational message. Further action is only required if Kerberos authentication is required by authentication policies.
Operating System XP Home SP2
SQL Server 2005 Express Edition with Advanced SP1 )
( idem for another workstation with XP Pro SP2 and same version of SQL Server 2005 Express
My problem is :
i want use the windows authentification but my computers are on Worhkgroup linked by a router ( no window server )
i have read that's possible to connect from a remote computer to a computer having a SQL Server 2005 Express through SPN
How can i do it ? ( activating NTLM ? but how ?)
I'm writing a C# program which must be executing on several computers with a SQL Server 2005 Express installed on a particular computer. These computers will belong to a domain of Windows Server 2003.
As i can't connect to this "normal" network, i am trying to simulate this network at home because i want to test this program and especially the possible locks problems.
I don't know whether i'm querying with the "correct" forum.
Sorry for my poor english.
I shall appreciate any help about this problem
Have a nice day
Simple Explanation Of Mining Algorithms
Jun 9, 2007Hello Everyone,
I am reading DataMining Tutorial and right now I am at the Mining Algorithms section. I cannot understand any of the algorithms. For example take the following text... what a bunch of mouthful bla bla bla it is ....
"The Microsoft Decision Trees algorithm supports both classification and regression and it works well for predictive modeling. Using the algorithm, you can predict both discrete and continuous attributes.
In building a model, the algorithm examines how each input attribute in the dataset affects the result of the predicted attribute, and then it uses the input attributes with the strongest relationship to create a series of splits, called nodes. As new nodes are added to the model, a tree structure begins to form. The top node of the tree describes the breakdown of the predicted attribute over the overall population. Each additional node is created based on the distribution of states of the predicted attribute as compared to the input attributes. If an input attribute is seen to cause the predicted attribute to favor one state over another, a new node is added to the model. The model continues to grow until none of the remaining attributes create a split that provides an improved prediction over the existing node. The model seeks to find a combination of attributes and their states that creates a disproportionate distribution of states in the predicted attribute, therefore allowing you to predict the outcome of the predicted attribute"
In the above text what is meant by discrete and continious attributes? what is regression? what is predicted attributes? what are input attributes? what is distribution of states?
Is there a source which explains these algorighms in a easier way ....
Explanation Of Security Groups For 2005
Sep 21, 2005I've been reconfiguring my Windows service accounts for the SQL Server service and the SQL Agent service to comply with the security best practices for SQL Server 2005. Specifically, I created two new network accounts. One account runs the SQL Server service, the other runs the SQL Agent service.
View 1 Replies View RelatedLearning SQL: Rather Difficult Query Needed: And If You Know This With Explanation What You're Doing If Possible :)
Jan 7, 2008If you know the answer please explain what you're doing if possible, that'll help me :)I have the following tables:CREATE TABLE [dbo].[tblUserData]( [UserCode] [int] IDENTITY(1,1) NOT NULL, [UserName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [DisplayName] [nvarchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,) ON [PRIMARY]CREATE TABLE [dbo].[tblFriends]( [UserCodeOwner] [int] NOT NULL, [UserCodeFriend] [int] NOT NULL, [createdate] [datetime] NOT NULL CONSTRAINT [DF_tblFriends_createdate] DEFAULT (getdate())) ON [PRIMARY]in tblFriends relations are stored twice, so for a relation between user 5 and 6, there will be 2 rows: 5-6 and 6-5Now, I want to get the columns (UsercodeOwner,UsercodeFriend,createdate,username,displayname) for relations that were created in tblFriends in the last 10 days for the FRIENDS of a person with usercode 5.Example:tblUserdata5 peter Petertje6 john Johnny11 simon SimonSays15 monique MontjetblFriends5 6 'createdate 30 days ago'5 11 'createdate 5 days ago'6 5 'createdate 30 days ago'6 11 'createdate 3 days ago'6 15 'createdate 7 days ago'11 5 'createdate 5 days ago'11 6 'createdate 3 days ago'15 6 'createdate 7 days ago'The resultset for a query on usercode 5 would now be (usercode1, username1, displayname1,usercode2, username2, displayname2,createdate):6 john Johnny 11 simon SimonSays 'createdate 3 days ago'6 john Johnny 15 monique Montje 'createdate 7 days ago'As you can see each relation is only returned twice even though there are always two entriesWhat would be the SQL statement, if possible without temp table..Thanks!
View 21 Replies View RelatedFunny SQL Server Error Messages - Anyone Has An Explanation?
Apr 3, 2006I am getting a new server online at a customer, and our system shows very funny erorrs under full load. No explanations could be found. Anyone has some?
Here we go:
Server failed to resume the transaction, desc: 360000054a. The transaction active in this session has been committed or aborted by another session.,
at System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
and
New request is not allowed to start because it should come with valid transaction descriptor.
System.Data.SqlClient.SqlException
I am a little lost on those. Simple fact is that I do not find any description of those. Anyone an idea what causes these?
Checksum_agg And Row Size Error. Require Explanation.
Jul 23, 2005Hi,I can see that by using the object ID rather that the object name, thefollowing SQL query works. Has anybody got any idea what is causing theerror?-- Works OKselect o.id,checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by o.id-- Error-- Server: Msg 1540, Level 16, State 1, Line 1-- Cannot sort a row of size 8096, which is greater than the-- allowable maximum of 8094.select object_name(o.id),checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by object_name(o.id)-- Error-- Server: Msg 1540, Level 16, State 1, Line 1-- Cannot sort a row of size 8096, which is greater than the-- allowable maximum of 8094.select o.name,checksum_agg(binary_checksum(m.text))from sysobjects o,syscomments mwhere o.id = m.idand o.xtype in ('FN','IF','P','TF','TR','V')group by o.name-- Workaroundselect getdate(),object_name(x.id),check_sumfrom (select m.id,checksum_agg(binary_checksum(m.text)) as check_sumfrom syscomments minner joinsysobjects oon m.id = o.idwhere o.xtype in ('FN','IF','P','TF','TR','V')group by m.id) as xRegardsLiam
View 1 Replies View RelatedLinear Regression With Nested Explanation Variable
Jan 22, 2007We are trying to create a model of linear regression with nested table. We used the create mining model sintax as follow :
create mining model rate_plan3002_nested2
( CUST_cycle LONG KEY,
VOICE_CHARGES double CONTINUOUS predict,
DUR_PARTNER_GRP_1 double regressor CONTINUOUS ,
nested_taarif_time_3002 table
( CUST_cycle long CONTINUOUS,
TARIFF_TIME text key,
TARIFF_VOICE_DUR_ALL double regressor CONTINUOUS
)
) using microsoft_linear_regression
INSERT INTO MINING STRUCTURE [rate_plan3002_nested2_Structure]
(CUST_cycle ,
VOICE_CHARGES ,
DUR_PARTNER_GRP_1 ,
[nested_taarif_time_3002](SKIP,TARIFF_TIME ,TARIFF_VOICE_DUR_ALL)
)
SHAPE {
OPENQUERY([Cell],
'SELECT CUST_cycle ,
VOICE_CHARGES ,
DUR_PARTNER_GRP_1
FROM dbo.panel_anality_3002
order by CUST_cycle ')}
APPEND
({OPENQUERY([Cell],
'select CUST_cycle,
TARIFF_TIME,
CYCLE_DATE
from dbo.nested_taarif_time_3002
order by CUST_cycle,TARIFF_TIME')
}
relate CUST_cycle to CUST_cycle
) as nested_taarif_time_3002
The results we got are a model with intercept only. if we don't use the nested variable (the red line) we get a rigth model . (we had more variable ....)
Is there a way to do this regression correctly?
Thanks,
Dror
ReportBuilder / Report Model Explanation Needed
May 1, 2008(I wish I was able to attach pictures....anyhow I'll try my best without them)
The customer's goal is to via ReportBuilder to select fields from multiple
many-to-many relationships. They are able to see the fields that exists
within the various many-to-many relationships but IS ONLY able to select
fields from ONE many-to-many relationship. Example using AdventureWorks:
DataSourceView (relationships automatically generated by wizard)
Contact ---< VendorContact >--- Vendor
Contact ---< StoreContact >--- Store
Contact ---< ContactCreditCard >--- CreditCard
So the goal in the ReportBuilder is to be able select fields from
Contact/Vendor/Store/ and CreditCard.
PROBLEM is that the ReportBuilder ONLY allows user to select fields from
Contact/Vendor OR Contact/Store OR Contact/CreditCard (exclusively)
The only workaround I've found is to reverse the relationships in the
DataSourceView to the following
Contact >--- VendorContact ---< Vendor
Contact >--- StoreContact ---< Store
Contact >--- ContactCreditCard ---< CreditCard
This works however we need an explanation as to why it only works this way?
Thanks in advance
Simple Explanation To Concatenating Multiple Items Into One Column
Jan 17, 2008Hi, I am a extreme beginer to sql server and i am i'm having big trouble trying to display my sql query properly. Bascially i want to put the results of a one to many query into one row per record. I have read articles and forums discussing 'concatenating the values' or creating a function??? but i dont follow what they mean and i am completely lost. Can anyone provide a really simple explanation on what i need to do to resolve my duplicate row issue? i urgently need to find a solution to this.
Regards
Can Anyone Give Me A Layman's Explanation Of The Difference Between CURRENT_TIMESTAMP And GETDATE() (if There Is A Difference)?
Oct 24, 2007Question is in the subject.
Thanks in advance
-Jamie
Incorrect Syntax Near The Keyword CONVERT When The Syntax Is Correct - Why?
May 20, 2008Why does the following call to a stored procedure get me this error:
Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'CONVERT'.
Code Snippet
EXECUTE OpenInvoiceItemSP_RAM CONVERT(DATETIME,'01-01-2008'), CONVERT(DATETIME,'04/30/2008') , 1,'81350'
The stored procedure accepts two datetime parameters, followed by an INT and a varchar(10) in that order.
I can't find anything wrong in the syntax for CONVERT or any nearby items.
Help me please. Thank you.
Incorrect Syntax When There Appears To Be No Syntax Errors.
Dec 14, 2003I keep receiving the following error whenever I try and call this function to update my database.
The code was working before, all I added was an extra field to update.
Exception Details: System.Data.SqlClient.SqlException: Incorrect syntax near the keyword 'WHERE'
Public Sub MasterList_Update(sender As Object, e As DataListCommandEventArgs)
Dim strProjectName, txtProjectDescription, intProjectID, strProjectState as String
Dim intEstDuration, dtmCreationDate, strCreatedBy, strProjectLead, dtmEstCompletionDate as String
strProjectName = CType(e.Item.FindControl("txtProjectName"), TextBox).Text
txtProjectDescription = CType(e.Item.FindControl("txtProjDesc"), TextBox).Text
strProjectState = CType(e.Item.FindControl("txtStatus"), TextBox).Text
intEstDuration = CType(e.Item.FindControl("txtDuration"), TextBox).Text
dtmCreationDate = CType(e.Item.FindControl("txtCreation"),TextBox).Text
strCreatedBy = CType(e.Item.FindControl("txtCreatedBy"),TextBox).Text
strProjectLead = CType(e.Item.FindControl("txtLead"),TextBox).Text
dtmEstCompletionDate = CType(e.Item.FindControl("txtComDate"),TextBox).Text
intProjectID = CType(e.Item.FindControl("lblProjectID"), Label).Text
Dim strSQL As String
strSQL = "Update tblProject " _
& "Set strProjectName = @strProjectName, " _
& "txtProjectDescription = @txtProjectDescription, " _
& "strProjectState = @strProjectState, " _
& "intEstDuration = @intEstDuration, " _
& "dtmCreationDate = @dtmCreationDate, " _
& "strCreatedBy = @strCreatedBy, " _
& "strProjectLead = @strProjectLead, " _
& "dtmEstCompletionDate = @dtmEstCompletionDate, " _
& "WHERE intProjectID = @intProjectID"
Dim myConnection As New SqlConnection(System.Configuration.ConfigurationSettings.AppSettings("connectionstring"))
Dim cmdSQL As New SqlCommand(strSQL, myConnection)
cmdSQL.Parameters.Add(new SqlParameter("@strProjectName", SqlDbType.NVarChar, 40))
cmdSQL.Parameters("@strProjectName").Value = strProjectName
cmdSQL.Parameters.Add(new SqlParameter("@txtProjectDescription", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@txtProjectDescription").Value = txtProjectDescription
cmdSQL.Parameters.Add(new SqlParameter("@strProjectState", SqlDbType.NVarChar, 30))
cmdSQL.Parameters("@strProjectState").Value = strProjectState
cmdSQL.Parameters.Add(new SqlParameter("@intEstDuration", SqlDbType.NVarChar, 60))
cmdSQL.Parameters("@intEstDuration").Value = intEstDuration
cmdSQL.Parameters.Add(new SqlParameter("@dtmCreationDate", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@dtmCreationDate").Value = dtmCreationDate
cmdSQL.Parameters.Add(new SqlParameter("@strCreatedBy", SqlDbType.NVarChar, 10))
cmdSQL.Parameters("@strCreatedBy").Value = strCreatedBy
cmdSQL.Parameters.Add(new SqlParameter("@strProjectLead", SqlDbType.NVarChar, 15))
cmdSQL.Parameters("@strProjectLead").Value = strProjectLead
cmdSQL.Parameters.Add(new SqlParameter("@dtmEstCompletionDate", SqlDbType.NVarChar, 24))
cmdSQL.Parameters("@dtmEstCompletionDate").Value = dtmEstCompletionDate
cmdSQL.Parameters.Add(new SqlParameter("@intProjectID", SqlDbType.NChar, 5))
cmdSQL.Parameters("@intProjectID").Value = intProjectID
myConnection.Open()
cmdSQL.ExecuteNonQuery
myConnection.Close()
MasterList.EditItemIndex = -1
BindMasterList()
End Sub
Thankyou in advance.
Which Is Faster? Conditional Within JOIN Syntax Or WHERE Syntax?
Mar 31, 2008Forgive the noob question, but i'm still learning SQL everyday and was wondering which of the following is faster? I'm just gonna post parts of the SELECT statement that i've made changes to:
INNER JOIN Facilities f ON e.Facility = f.FacilityID AND f.Name = @FacilityName
OR
WHERE f.Name = @FacilityName
My question is whether or not the query runs faster if i put the condition within the JOIN line as opposed to putting in the WHERE line? Both ways seems to return the same results but the time difference between methods is staggering? Putting the condition within the JOIN line makes the query run about 3 times faster?
Again, forgive my lack of understanding, but could someone agree or disagree and give me the cliff-notes version of why or why not?
Thanks!
Converting Rrom Access Syntax To Sql Syntax
Sep 23, 2007
Ok I am tying to convert access syntax to Sql syntax to put it in a stored procedure or view..
Here is the part that I need to convert:
SELECT [2007_hours].proj_name, [2007_hours].task_name, [2007_hours].Employee,
IIf(Mid([task_name],1,3)='PTO','PTO_Holiday',
IIf(Mid([task_name],1,7)='Holiday','PTO_Holiday',
IIf(Mid([proj_name],1,9) In ('9900-2831','9900-2788'),'II Internal',
IIf(Mid([proj_name],1,9)='9900-2787','Sales',
IIf(Mid([proj_name],1,9)='9910-2799','Sales',
IIf(Mid([proj_name],1,9)='9920-2791','Sales',
)
)
)
)
) AS timeType, Sum([2007_hours].Hours) AS SumOfHours
from................
how can you convert it to sql syntax
I need to have a nested If statment which I can't do in sql (in sql I have to have select and from Together for example ( I can't do this in sql):
select ID, FName, LName
if(SUBSTRING(FirstName, 1, 4)= 'Mike')
Begin
Replace(FirstNam,'Mike','MikeTest')
if(SUBSTRING(LastName, 1, 4)= 'Kong')
Begin
Replace(LastNam,'Kong,'KongTest')
if(SUBSTRING(Address, 1, 4)= '1245')
Begin
.........
End
End
end
Case Statement might be the solution but i could not do it.
Your input will be appreciated
Thank you
Urgent !!!!! Nee Explanation On Primary Keys And FK Keys
Jul 15, 2002Can somebody explain to me how to best do inserts where you have primary keys and foreign keys.l'm battling.
Is there an article on primary keys/Pk ?
A Very Basic Q
Mar 18, 2005This is probably a very silly question.I started learning ASP.net by following ASP.NET Unleashed. I am stuck where he wants me to open a connection to SQL Server database. I have just downloaded
MSDE. But I dont know where to type this code and how to run it..so as to connect to the database.
<%@ Import Namespace="System.Data.SqlClient" %>
<Script Runat="Server">
Sub Page_Load
Dim conPubs As SqlConnection
conPubs = New SqlConnection( "server=localhost;uid=webuser;pwd=secret;database=pubs" )
conPubs.Open()
End Sub
</Script>
Connection Opened!
Now do i have to change the uid to SA ? (i had to assign one when i downloaded and installed MSDE?
Thanks for the help.
Basic DTS...
Nov 16, 2005Hi all,
am not very experienced in using DTS and really need your help. I have a dts package that i have scheduled to run every day. Here's what i want the package to do:
1. Check whether a value for a certain column in a certain row of a table in my database is 0 or 1. If it is 1, then
2. Run the dts task (which i have created and is working)
In other words, when the package is started, i want to execute a stored procedure or sql task or whatever, and if that returns 1 then i want to continue, if it returns 0 i want to finish the package without running the dts task. I'm sure there's a simple way to do this, but i could use your help...!
Thanks,
Elisabet
Need Basic.
Jul 11, 2007Hi All,
Can this be done and if so can you give a bullet list of the steps need to accomplish this.
I need to load a bunch of files into a stagging table. Need to loop through the files and load them.
Thanks,
Michael
BASIC Q
Mar 12, 2008
Hi,
what this statement do?
does it add all the values or combine all the values.
REPLACE combine WITH lc_tran + lc_exp + lc_war + ll_boc
Regards
kk
Incorrect Syntax Near The Keyword 'from'. Line 1: Incorrect Syntax Near ')'.
May 27, 2008This is the error it gives me for my code and then it calls out line 102. Line 102 is my buildDD(sql, ddlPernames) When I comment out this line the error goes away, but what I don't get is this is the same way I build all of my dropdown boxes and they all work but this one. Could it not like something in my sql select statement. thanksPrivate Sub DDLUIC_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles DDLUIC.SelectedIndexChanged
Dim taskforceID As Byte = ddlTaskForce.SelectedValueDim uic As String = DDLUIC.SelectedValue
sql = "select sidstrNAME_IND from CMS.dbo.tblSIDPERS where sidstrSSN_SM in (Select Case u.strSSN from tblAssignedPersonnel as u " _
& "where u.bitPresent = 1 and u.intUICID in (select intUICID from tblUIC where intTaskForceID = " & taskforceID & " and strUIC = '" & uic & "'))"ddlPerNames.Items.Add(New ListItem("", "0"))
buildDD(sql, ddlPerNames)
End Sub
Basic Problem
Feb 19, 2007I downloaded SQLExpress and Visual Studio Express to my home computer.
I built a simple database, adding data through theSQLexpress admin tool.
I built a web page using MS Studio. I connected to the database and used the webpage for a few days. Then I restarted the computer. Now the web page won't open, and MS Studio won't open the MDF file in the App_Data folder.
I can still see and work in the database through SQL server Express.
The web page and the MSStudio attempt to connect to the mdf file both fail with this message:
Cannot open user default database. Login failed.Login failed for user 'KAAAK/Administrator'.
So it seems to be trying to connect as the Windows user.
When I try to modify the connection to connect through a user/password I created in SQL manager, I get a message that the user is not a trusted SQL user.
from web.config:
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|info.mdf;Integrated Security=True;User Instance=True;User ID=Admin;Password=12345" providerName="System.Data.SqlClient"/>
</connectionStrings>
That was changed from the original string created automatically by MS Studio
<connectionStrings>
<add name="stocksConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|stocks.mdf;Integrated Security=True;User Instance=True;" providerName="System.Data.SqlClient"/>
</connectionStrings>
I am sure this is some simple problem, but why would the system refuse to access an mdf file it had already been accessing.?
Thanks, Michael
Basic SQL Connection
Mar 5, 2007 Hi all, having trouble with my first sql communication. I've got hosted service with an SQL database i've populated with a row.
When it gets to the third line the page crashes with an error.
SqlConnection connection = new SqlConnection("Server=mydbserver.com;Database=db198704784;");// +"Integrated Security=True"); SqlCommand cmd = new SqlCommand("SELECT UserName FROM Users",connection); SqlDataReader reader = cmd.ExecuteReader();
is there somewhere i need to put in my username or password? or is this code just wrong
Many thanks burnside.
-- Edited by longhorn2005