I guess that I had "Unit" (instead of "UnitForConc"), when I executed the sql code last time!!!???
How can I delete the old, wrong CTE that is already in the ChemDatabase of my SSMSE?
In my SQL Server Management Studio Express (SSMSE)=>Object Explorer=>Databases, the "NORTHWND" database was screwed up and is deleted completely. But under the same Object Explorer =>Databases, there is a database "C:SSMSE-BookChapter12WINCH12NORTHWND.MDF". I need to use this "NORTHWND" database in the SqlConnection. How can I specify the "Initial Catalog" (or "database") in the New SqlConnection code statement (in the ADO.NET 2.0-VB 2005 Express programming)? Should it be like the following:
Dim sqlConnection As SqlConnection = New SqlConnction("Data Source=.SQLEXPRESS; Initial Catalog=C:SSMSE-BookChapter12WINCH12NORTHWND; Integrated Security=SSPI;") ? Or what is the right code statement for connecting to it?
I have just found out i/we need to upgrade to the full version of this management tool to make use of SSRS for a new CRM we are putting in place, The CRM company tells me we can just install the tool from our SQL 2005 disk.
Im wondering what the best way to go about this is? i would guess we just un-install the express version install the full version of the management tool and reconnect to the database. unless im missing anything?
I'm using CTEs to try and get the totals of two different criteria queries that I want to group by Month depending on the associated Inquiry Date. Here is what I have right now;
Code: ;With CTE(total, InitDate) as ( SELECT count(Inquiry.ID), Inquiry.Date from Inquiry Inner Join Inquirer on Inquirer.ID = Inquiry.InquirerID_fk Left Join Transfer on Transfer.TransferInquiryID_fk = Inquiry.ID WHERE (Inquiry.Date >= '3/1/2012' AND Inquiry.Date <= '9/26/2012' AND Inquiry.Date IS NOT NULL) AND (Inquirer.Program = 'Res. Referral Coord.')AND TransferInquiryID_fk IS NULL Group By Inquiry.Date
[code]...
I get 170 for InitCount, but for TransCount I only get 19, not 26. I assume it is with my left outer join statement grouping but I am not sure how I would change this to get the proper counts. All I want to do is group the values together depending on the month they were done in.
I have uploaded a table into sql management studio express. However, when I right click on the table and try and open it, I get an error message saying;
We are developing the web application using ASP.NET 2.0 using C# with Backend of SQL Server 2005.
Now we have one requirement with my client, Already our application live in ASP using MS Access Database. In Access Database our client already wrote the query, those query call the another query from same database. So I want to over take this functionality in SQL Server 2005.
In ASP Application, We call this query €œ081Stats€? from Record set.
This €˜081Stats€™ query call below sub query itself
Master Query: 081Stats
SELECT DISTINCTROW [08Applicants].school, [08Applicants].Applicants, [08Interviewed].Interviewed, [Interviewed]/[Applicants] AS [interviewed%], [08Accepted].Accepted, [Accepted]/[Applicants] AS [Accepted%], [08Dinged].Dinged, [Dinged]/[Applicants] AS [Dinged%], [08Waitlisted].Waitlisted, [Applicants]-[Accepted]-[Dinged] AS Alive, [08Matriculating].Matriculating, [Matriculating]/[Accepted] AS [Yield%] FROM ((((08Applicants LEFT JOIN 08Interviewed ON [08Applicants].school = [08Interviewed].school) LEFT JOIN 08Accepted ON [08Applicants].school = [08Accepted].school) LEFT JOIN 08Dinged ON [08Applicants].school = [08Dinged].school) LEFT JOIN 08Waitlisted ON [08Applicants].school = [08Waitlisted].school) LEFT JOIN 08Matriculating ON [08Applicants].school = [08Matriculating].school;
Sub Query 1: 08Accepted
SELECT statusTbl.school, Count(1) AS Accepted FROM statusTbl WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
Sub Query 2: 08Applicants
SELECT statusTbl.school, Count(1) AS Accepted FROM statusTbl WHERE (((statusTbl.decision)=1) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
Sub Query 3: 08Dinged
SELECT statusTbl.school, Count(1) AS Dinged FROM statusTbl WHERE (((statusTbl.decision)=0) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
Sub Query 4: 08Interviewed
SELECT statusTbl.school, Count(1) AS Interviewed FROM statusTbl WHERE (((statusTbl.interview)=True) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
Sub Query 5: 08Matriculating
SELECT statusTbl.school, Count(1) AS Matriculating FROM statusTbl WHERE (((statusTbl.userdec)=True) AND ((statusTbl.yearapp)="2008")) GROUP BY statusTbl.school ORDER BY Count(1) DESC;
So now I got the solution from SQL Server 2005. I.e. Common Table Expressions, So I got the syntax and other functionality, Now my doubts is how do implement the CTE in SQL Server 2005, where can I store the CTE in SQL Server 2005.
How can I call that CTE from ASP.NET 2.0 using C#?
CTE is replacing the Stored Procedure and Views, because it is memory based object in SQL Server 2005.
How can I implement the CTE, where can I write the CTE and where can I store the CTE.
And how can I call the CTE from ASP.NET 2.0 using C#.
Hi all, In the Object Explorer of my SQL Server 2005 Management Studio Express, I do not have €˜Northwind€™ Database installed yet. I executed the following source code (that was copied from a book) in my VB 2005 Express: /////////////////////----Form9.vb----////////////////////////// Imports System.Data.SqlClient Imports System.Data Public Class Form9
Dim cnn1 As New SqlConnection
Private Sub Form5_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Compute top-level project folder and use it as a prefix for 'the primary data file Dim int1 As Integer = InStr(My.Application.Info.DirectoryPath, "bin") Dim strPath As String = Microsoft.VisualBasic.Left(My.Application.Info.DirectoryPath, int1 - 1) Dim pdbfph As String = strPath & "northwnd.mdf" Dim cst As String = "Data Source=.sqlexpress;" & _ "Integrated Security=SSPI;" & _ "AttachDBFileName=" & pdbfph cnn1.ConnectionString = cst
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Create a command to create a table Dim cmd1 As New SqlCommand cmd1.CommandText = "CREATE TABLE FromExcel (" & _ "FirstName nvarchar(15), " & _ "LastName nvarchar(20), " & _ "PersonID int Not Null)" cmd1.Connection = cnn1
'Invoke the command Try cnn1.Open() cmd1.ExecuteNonQuery() MessageBox.Show("Command succeeded.", "Outcome", _ MessageBoxButtons.OK, MessageBoxIcon.Information) Catch ex As Exception MessageBox.Show(ex.Message) Finally cnn1.Close() End Try
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
'Create a command to drop a table Dim cmd1 As New SqlCommand cmd1.CommandText = "DROP TABLE FromExcel" cmd1.Connection = cnn1
'Invoke the command Try cnn1.Open() cmd1.ExecuteNonQuery() MessageBox.Show("Command succeeded.", "Outcome", _ MessageBoxButtons.OK, MessageBoxIcon.Information) Catch ex As Exception MessageBox.Show(ex.Message) Finally cnn1.Close() End Try
End Sub
Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button3.Click
'Declare FromExcel Data Table and RowForExcel DataRow Dim FromExcel As New DataTable Dim RowForExcel As DataRow
'Create TextFieldParser for CSV file from spreadsheet Dim crd1 As Microsoft.VisualBasic.FileIO.TextFieldParser Dim strPath As String = _ Microsoft.VisualBasic.Left( _ My.Application.Info.DirectoryPath, _ InStr(My.Application.Info.DirectoryPath, "bin") - 1) crd1 = My.Computer.FileSystem.OpenTextFieldParser _ (My.Computer.FileSystem.CombinePath(strPath, "Book1.csv")) crd1.TextFieldType = Microsoft.VisualBasic.FileIO.FieldType.Delimited crd1.Delimiters = New String() {","}
'Loop through rows of CSV file and populate 'RowForExcel DataRow for adding to FromExcel 'Rows collection Dim currentRow As String() Do Until crd1.EndOfData Try currentRow = crd1.ReadFields() Dim currentField As String Dim int1 As Integer = 1 RowForExcel = FromExcel.NewRow For Each currentField In currentRow Select Case int1 Case 1 RowForExcel("FirstName") = currentField Case 2 RowForExcel("LastName") = currentField Case 3 RowForExcel("PersonID") = CInt(currentField) End Select int1 += 1 Next int1 = 1 FromExcel.Rows.Add(RowForExcel) RowForExcel = FromExcel.NewRow Catch ex As Microsoft.VisualBasic.FileIO.MalformedLineException MsgBox("Line " & ex.Message & _ "is not valid and will be skipped.") End Try Loop Try cnn1.Open() Using sqc1 As SqlBulkCopy = New SqlBulkCopy(cnn1) sqc1.DestinationTableName = "dbo.FromExcel" sqc1.WriteToServer(FromExcel) End Using Catch ex As Exception MessageBox.Show(ex.Message) Finally cnn1.Close() End Try
'Read the FromExcel table and display results in 'a message box Dim strQuery As String = "SELECT * " & _ "FROM dbo.FromExcel " Dim str1 As String = ""
Dim cmd1 As New SqlCommand(strQuery, cnn1) cnn1.Open() Dim rdr1 As SqlDataReader rdr1 = cmd1.ExecuteReader() Try While rdr1.Read() str1 += rdr1.GetString(0) & ", " & _ rdr1.GetString(1) & ", " & _ rdr1.GetSqlInt32(2).ToString & ControlChars.CrLf End While Finally rdr1.Close() cnn1.Close() End Try MessageBox.Show(str1, "FromExcel")
End Sub
End Class /////////////////////////////////////////////////////////////////////// This is Part 1 (The length of input exceeds 50000 characters). Part 2 will be posted in this site shortly.
What I am trying to do is count persons in buckets "non-recidivists" and "recidevists" based on how many bkg_nbr they have per STATE_NBR. If they have more than 1 bkg_nbr per STATE_NBR then put them in the "recdivists" bucket. If they only have a 1 to 1 then put them in the "non-recidivists" bucket.
I have a multi-tenant database where each row and each table has a 'TenantId' column. I have created a view which has joins on a CTE. The issue I'm having is that entity framework will do a SELECT * FROM MyView WHERE TenantId = 50 to limit the result set to the correct tenant. However it does not limit the CTE to the same TenantId so that result set is massive and makes my view extremely slow. In the included example you can see with the commented line what I need to filter on in the CTE but I am not sure how to get the sql plan executor to understand this or weather it's even possible.I have included a simplified view definition to demonstrate the issue...
ALTER VIEW MyView AS WITH ContactCTE AS( SELECT Col1, Col2, TenantId
I have a database in my "App_Data" folder of my visual studio project. I can view it fine in Visual Studio's built-in tools for managing a database attached to a solution. However i recently started playing around with the SQL Server Management Studio Express program. When i attach my database to Management Studio, and try to run my program it crashes. I think it might be a permissions error?!? When i detatch it and reattach it in visual studio it runs fine again. Any suggestions? ThanksJason
I have recently installed the SQL Server Management Studio Express but I do not find Management Tools in order to create scheduled backups and shrinking of the databases. I was under the impression that this should be included in the Management Studio. I use the SQL 2005 Express for smaller customers who run the SQL on a desktop unit. I need a way to backup the data to a server machine for backup purposes. I have uninstalled and reinstalled to no avail.
I have the following code and 3 table columns on my table. All I want to do is when the description inputed into textbox1 is equal to the description on the table delete the row. I cant figuere out for the life of me what is wrong with my statement can anyone please point out the obvious for this young. .net noob. It looks like it should work. Thanks in advance.
Protected Sub DEL_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles DEL.Click Dim myConnection As SqlConnectionDim myCommand As SqlCommand 'Dim ra As IntegermyConnection = New SqlConnection("server=****.cis.temple.edu;Database=****;User id=****;Password=*****") myConnection.Open()myCommand = New SqlCommand("Delete From Product Where 'Description' = '" & TextBox1.Text.Trim & "', )", myConnection) myCommand.ExecuteNonQuery() myConnection.Close()
When I downloaded/started using Visual Web Developer I was under the impression that I needed to install SQL Server Management Studio Express in order to create/manage databases, and to provide the engine to access the data. Since then I have found tutorials and have successfully created/used databases solely from within Visual Web Developer. I'm assuming that Visual Web Developer includes a database engine, much like the webserver that is included. (This is an awesome thing). When I tried to upload my web application with database to my production server, the database would not work, it started working after I installed SQL Server Management Studio Express on the server. Is it my understanding that you need SQL Server Management Studio Express if you do not have Visual Web Developer Express installed in order to provide the data access engine? Also, I am unable to "attach" my Visual Web Developer Express created database to SQL Server Management Studio Express. Are there any posts that provide more information about this topic?
The only reason I'm asking is that I have extra whitespace on the end of my text fields, and I thought ANSI_PADDING was turned on. I do not see the option in Visual Web Developer Express, but have found it in SQL Server Management Studio Express.
For the first time, I want to set up the configuration of my SQL Server Management Studio Express (SSMSE) to allow me in doing the non-User-Instance/ADO.NET 2.0 programming from my VB 2005 Express. The SSMSE and VB 2005 Express are in my Windows XP Pro PC that is part of our NT 4 LAN System in our office. I read the article "How to configure SQL Server 2005 to allow remotre connections" in http://support.microsoft.com/kb/914277/ about (i) "Enable remote connections for SQL Server 2005 Express", (ii) Enable the SQL Server Browser service", (iii) Create exception in Windows Firewall, and (iv) Create an exception for the SQL Server Browser service in Windows Firewall. I entered the SQL Server Surface Area Configuration and I could not decide what options I should take for doing the non-User-Instance/ADO.NET 2.0 programming from my VB 2005 Express. I have the following questions on the page of "Minimize SQL Server 2005 Surface Area": (1) I saw "Configure Surface Area for localhost [change computer]". I clicked on [change computer] and I saw the following: Select Computer The Surface Area Configuration of this surface area of this computer or a remote computer. Specify a computer to configure: O Local computer O Remote computer Should I choose the "Local computer" or the "Remote computer" option? (2) Below the "Configure Surface Area for localhost [change computer]", I clicked on "Surface Area Configuration for Service and Connections", Select a component and then configure its services and connections: |-| SQLEXPRESS |-| Database Engine Service I picked => Remote Connection On the right-hand side, there are: O Local connections only O Local and remorte connections O Using TCP/IP O Using named pipes only O Using both TCP/IP and named pipes Should I choose O Local and remorte connections and O Using named pipes only?
Please help and tell me what options I should choose in (1) and (2).
I am a newbie so i apologies beforehand for any mistakes i make on this forum. Anyway, i created an asp.net website using the MS Visual Web developer tool. This has a couple of SQL SERVER databases within it. I then latervinstalled the SQL SERVER management studio express in order to manage the SQL SERVER DBs that i had as part of my website but they do not appear within the management studio db list. As this is the express version, is there anyway of importing the existing SQL SERVER dbs that i have to the management studio so i can manage them from there?
As I said in the subject I've a problem trying to restore a backup of a previous db created in sql 2000 server
When I try to do it I recive the following message:
____________________________________________________________________________________ System.Data.SqlClient.SqlError: Il set di backup include il backup di un database diverso dal database 'musica2007' esistente. (Microsoft.SqlServer.Express.Smo)
------------------------------ For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&LinkId=20476
------------------------------ Program Location:
in Microsoft.SqlServer.Management.Smo.ExecutionManager.ExecuteNonQueryWithMessage(StringCollection queries, ServerMessageEventHandler dbccMessageHandler, Boolean errorsAsMessages) in Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server server, StringCollection queries) in Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv) ____________________________________________________________________________________
What should I do? What's the probem? I've already tried to look for the solution in other messages but I didn't find anything..... Thanks for help,,, by Luke
I was studying Common Table expression in Sql server 2005. I have written the code Declare @PictureArray as varchar(200) Set @PictureArray = ''; with UserProfile_CTE(UserPicture) As( select @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102 select @PictureArray ) select * from UserProfile_CTE
But I am getting the error Incorrect syntax near '=' I am getting the error in the lineselect @PictureArray = @PictureArray + '~' + PictureName from UserPicture where UserProfileID = 1102 But I don't know the reason for this, Kindly advice Regards Karan
What is the SQL Server equivalent of DB2 common table expressions? Forexample,with gry(year,count) as(select floor(sem/10),count(distinct ssn)from gradesgroup by floor(sem/10))select year,sum(count) Head_Count from grygroup by yearhaving year >= 1980;N. ShamsundarUniversity of Houston
I've seen the following error posted when people try to connect to their server via their applications but didn't see anyone having problems when trying to connect with SQL Server Management Studio Express. Am I missing something here? This is the error:<<An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)(Microsoft SQL Server, Error: 2)>>The server is not remote so I don't see why I need to enable remote connection. I am also using the default connection settings.
On my home machine without permanent network connections enabled, I cannot get the Management Studio connect to the database server. Always get this error:
Cannot connect ot MPLIAMSQLEXPRESS
Additional Information:
An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, erro: 26 - Error Locating Server/Instance Specified)(Microsoft SQL Server)
I have used the SQL Surface Area Configuration Tool to reset the defaults to allow remote connections, stopped and restarted the server, but still get the same message.
Really confused ... I created 2 tables in a VB Express program (which went well). Problem is that I can't see/find those tables with MSME. I don't really understand how to connect the MSME to where the tables are. I created the tables under My DocumentsData and they are DBapp.mdf + DBapp_log.LDF and the tables are Items and Preferences. The VB application can "see" the tables OK.
Do I need to move the tables to C:Program FilesMicrosoft SQL ServerMSQL.1MSQLDATA (like the Adventureworks installation which I haven't done) or what?
Hi, I'm completely new to SQL Server and ASP.NET, so please bear with me on my learning curve! I've installed Visual Web Developer Express and SQL Server Express (on the same PC). I then used Web Developer Express to successfully create a new database with a couple of tables in it. So far so good. I then installed SQL Server Management Studio Express on the same PC and tried to connect to my new database. The connection to the SQL Server instance appears to work OK (using Windows Authentication) but I can't see the database I previously created. All I can see are the System Databases. Does anyone know what the solution might be?
In the last one and half years, I used the Northwind Database in SQL Server Management Studio Express (SSMSE) to learn the programming of SqlConnections, Data sources, Database Exploere, ADO.NET 2.0, etc. via VB 2005 Express.
The Northwind Database in my SSMSE got lost very often, but I was not aware of it. How can I know where the Northwind Database is used or processed by my VB 2005 Express projects that were the examples of some tutorial books or my trial projects? How can I release the Northwind Database back to my SSMSE from the VB 2005 Express projects? Please help and advise.
Issue: SQL Server 2005 Management Studio displays the old filename in the tab at top if you open a .sql file and then do "file save as". I have upgraded to SP2 in hopes that this behavior would be corrected but to no avail.
Hi, I'd like to perform a number of different operations on my Common Table expression but I seem to be limited to only one operation. For example I cannot both delete duplicate rows and then perform a select statement. I can only execute one of the statements referencing the common table expression.
What is wrong with my syntax?
;With OrderedTable
AS
(
select Row_number() OVER (partition BY SSNumber order by Department_Id desc ) AS ROWID,* from Employee
)
delete from OrderedTable where RowId != 1
SELECT COUNT(*),SSNumber FROM OrderedTable group by Department_Id order by count(*) desc
I want to do conditional processing depending on values in the rows of a CTE. For example, is the following kind of thing possible with a CTE?:
WITH Orders_CTE (TerritoryId, ContactId) AS ( SELECT TerritoryId, ContactId FROM Sales.SalesOrderHeader WHERE (ContactId < 200) ) IF Orders_CTE.TerritoryId > 3 BEGIN /* Do some processing here */ END ELSE BEGIN /* Do something else here */ END
When I try this, I get a syntax error near the keyword 'IF'
Any ideas? I know this kind of thing can be done with a cursor but wanted to keep with the times and avoid using one!
I tried to use WITH to factor out the shared star join portion from a sql statement as it's usually the most expensive part.
However, examing the execution plan shows that the WITH clause is merely a syntactic suger that will internally be plugged back as derived tables where the same star join is executed repeatedly.
Is the intermediate rowset produced by a WITH caluse ever shared during the query execution?
