To make a long story short, I'm trying to correlate the most inner query (two levels deep) with the most outer one, and it just doesn't work.
Is it because the correlation must be back-to-back, without any levels that stand before the two?
If you have no idea what I'm talking about, below is the query that does work, but doesn't produce the right results ("QUOTES" gives the same number for all records):
---------------
select printersGlobal.company, (select count(*) from (select subBidding.bid_company from bidding as subBidding where subBidding.bid_company = 'Printing Company 1' group by subBidding.bid_company, subBidding.project) group by subBidding.bid_company) as QUOTES,
count(date_picked) as wins
from printers as printersGlobal
left outer join bidding as biddingGlobal on printersGlobal.company = biddingGlobal.bid_company
group by printersGlobal.company
order by printersGlobal.company asc
----------------------
When I replace 'Printing Company 1' with printersGlobal.company (to correlate and produce dynamic and correct results), I get errors.
I would appreciate if anybody can point me in the right direction.
We developed an application . The application is an ADP with MSSQL. What i need to do if i want to deploy to users ? Because all the forms - record source belong to my server.
Hi, what is the best way to go to migrate from access to MSSQL on a remote (hosted) server? do I have to create manually de tables and then move the data? or is there a way to create the creation scripts from the ACCESS database?
We use a program that creates a access database for each of our payperiods. Its a pain to get a full history of a person from each one. I want to setup a way I can combine all of the access files into one table in mssql. I have a DTS I created into a .bas file. My plan is to make the vb.net code get a directory of the files, then run a function (the .bas file) on each directory appending the access table to the mssql table. The struckture of the access files are that each file is in its own directory, like below:
The table is called "Transactions" and they are the same in each mdb file in terms of fields. Every two weeks there is a new directory that is added to the company directory. So I can either dump all and append all, or only append new data and only use the dump/append when there is a problem.
Is it possible to install MsSQL / Access on Linux? The reason I am asking is I have a linux virtual dedicated server, and develop both php/mysql and asp/mssql/access database, is there another program or something I can run on my server to allow development of programs needs mssql/access?
Thanks in advance for replies, Jeff
EDIT: I am new to Linux, however have found this: http://www.knoda.org/ Is this what I might need?
I run the following statement from an update query in access but I can't find the way to run this same query in MSSQL. Please give me some ideas how to modify and run this in MSSQL.
Thank you
"UPDATE DISTINCTROW ZipToTerr, leadsUS SET leadsUS.Terr = [ZipToTerr]![TerrNum] WHERE ((([ZipToTerr].[BU]='W') AND (([ZipToTerr].[ZipFrom])<=[zip]) And (([ZipToTerr].[ZipTo])>=[zip])) And (([leadsUS].[terr]) = 1 ));"
Hi im new to SSIS even i did some basice things in SSIS. Now i run in to the problem, I hawe a access file with arount one milion records and i won to transfare this records in Ms SqlServer . But befora i transfare that i nead to check if that record exsist by ID if exsist i must do update else i must do insert. Can some one Help me how can i do it.. THX Sorry for my bad englisht
I am working on a couple fo the ASP.NET walkthoughs and I would like to practice with a Database that I created in Access. I realize that I could use the Access DB, but I would like to learn to work with a more industrial strength DB. Is there a method to export to MSSQL server 2000 from Access ?
Hi, I need to access the mssql database which is inbuilt in Mcafee. through jdbc i'm trying to conenct mssql but its getting the tables from the default database which in Mcafee not from the mssql.
if any one have any idea please let me know to solve this.
Oke here is the problem. I hope sombody can help me with it.
After a lot of discussion, I finaly got the people that control the MS SQL server with my appertment as far to allow me to upgrade my access database to the MS SQL server. After this I've created an access project. Devellopping in access I concluded I made a mistake in the rowsource property of one of the tablefields. The people that control the MS SQL server don'n allow me to make changes to the database structure from within access. So I've to write a SQL statement to change this.
I am converting an old MS Access database to MSSQL. While I do get some data into MSSQL, some weird things are happening that are beyond my capabilities.
The database I am trying to convert exists of several tables. The first few are converted perfectly, but one of the last is giving difficulties. What I do is: SET @SQLQuery = 'INSERT INTO TableName ( ' + '[field1], ' + '[field2], ' + '[field3], ' + '[field4], ' + '[Date], ' + '[field6]) ' + 'SELECT ' + '[field1], ' + '[field2], ' + '[field3], ' + '[field4], ' + '[Date], ' + '[field6]) ' + ' FROM '+ RTRIM(@LinkedServerName) +'...TableName'
EXEC (@SQLQuery)
I do the same for each table, all with their own TableNames and field names. 80% of the tables have a date field, which is of type DBTYPE_DBTIMESTAMP in Access and datetime in MSSQL.
As mentioned: the first tables are converted perfectly, however one fails with error message: Msg 8114, Level 16, State 8, Server XXXXXX, Line 1 Error converting data type DBTYPE_DBTIMESTAMP to datetime.
If I leave the date field out of the query for that table, it all works (obviously, with an empty date column). The weird thing is that most of the other tables have a date column as well, they are the same data types as in this column and the conversion query is very similar as well.
I have Googled on the full error message and on DBTYPE_DBTIMESTAMP alone, but all results I get seem different to what I have. I have tried using convert to put the date in the correct format (format in the date tables is DD-MM-YYYY), but this doesn't help. Would have been funny if it had helped, as all other tables use the same date format, and according to the specs, DBTYPE_DBTIMESTAMP should be automatically converted to datetime anyway (which in all other tables works)...
Basically, what I am saying is: I have 8 extremely similar tables, which I all try to convert using the exact same method, 7 tables succeed and one fails...
I have few SQL user who has permissions on different databases. When they are accessing MSSQL server from Enterprise Manager they can see all Databases but can not access them and they can change file size allocated to them. I want to restrict this and they can only see database belongs to respective user and restrict user to change allocated size. Please help me with this as I am not able to find the solution.
I have developed a web application using ASP 3.0 with a Access DB 2002 backend. I have decided to recently to upgrade the database to SQL 2005 but I am having issues that I cannot work out because I am new to MsSql Language. It appears that the FORMAT function in Access does not work in MsSql ( does not recognise the function ) along with some of the PIVOT and TRANSFORM calls (coming up with "Syntax error near "..."") . Please see the below code. The areas that are showing up as errors when the application is run are highlighted in red.
If IncludeFieldNames Then 'string concatenation issues aren't a problem for a small string of field names For Each Field In RS.Fields If FieldNames = "" Then FieldNames = Field.Name Else FieldNames = FieldNames & "," & Field.Name End If
Next FieldNames = FieldNames & vbCRLF Call objCSVFile.WriteText(FieldNames, 1) End If
Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, "")) Call objCSVFile.SaveToFile(CSVFilePath, 2) Set objCSVFile = Nothing End Sub %>
<% function evaluate(pFormula, pUserID, pMonth, pYear) if isnull(pFormula) then evaluate = 0 exit function end if x = 0 key = "" zero = "0" nine = "9" eFormula = "" lf = len(pFormula) for i = 1 to lf c = mid(pFormula,i,1) if x = 1 and c >= zero and c <= nine then key = key & c if x = 1 and (c < zero or c > nine or i = lf) then set rsd = cn.execute("select sum(total) from Data where UserID = " & pUserID & " and month(weekEnding) = " & pMonth & " and year(weekEnding) = " & pYear & " and GroupTaskID=" & key) eFormula = eFormula & rsd.fields(0) rsd.close key = "" if c < zero or c > nine then x = 0 end if if c = "v" then key = "" x = 1 end if if x = 0 then eFormula = eFormula & c next on error resume next evaluate = round(eval(eFormula),0) 'if err then evaluate = err.description & ": " & eformula if err then evaluate = 0 end function %>
<% period = split(request.form("period"),",") if ubound(period) = 1 then mmmm = period(0)+0 yyyy = period(1)+0 else mmmm = 0 yyyy = 0 end if %>
</div> <p align="center" class="MainBodyText"> <img src="../../images/si.gif" align="center"><br><b>Month to Date Dashboard</b></p> <p align="center" class="MainBodyText"> To use the Print Page function please select the period required then select view report. You can then select Print Page which will print out the rendered page.</p> <form name="f1" method="POST" action="dialmtd.asp"> <div id="printReady"> <table align="center" border="0" width="663" style="border-collapse: collapse" bordercolor="#111111" cellpadding="2"> <tr>
<td align="left" width="127"><font face="Arial" size="2"> <select name="benchmarkgroup" style="color: #000000; border: 0px solid #000000; background-color: #FFE737"> <option value="0" <%if request.form("benchmarkgroup") = "" then response.write "selected"%>>Select Report <% set rs = cn.execute("select TaskGroupID,TaskGroup from BenchmarkGroups where Active = 1 order by TaskGroupID") do until rs.eof if request.form("taskgroupid")+0 = rs.fields(0) then selected = "selected" else selected = "" %> <option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%> <% rs.movenext loop rs.close %> </select> </td>
<td align="left" width="143"><font face="Arial" size="2"> <select name="period"> <option value="" <%if request.form("period") = "" then response.write "selected"%>>Select Period <% set rs = cn.execute("select year(WeekEnding), month(WeekEnding), format(Weekending,""mmmm yyyy"") from data group by Year(WeekEnding), Month(WeekEnding), format(WeekEnding,""mmmm yyyy"") order by 1,2") do until rs.eof x = rs.fields(1) & "," & rs.fields(0) if request.form("period") = x then selected = "selected" else selected = "" %> <option value=<%=x & " " & selected%>><%=rs.fields(2)%> <% rs.movenext loop rs.close %> </select> </td>
<td align="left" width="117"><font face="Arial" size="2"> <select name="stateid"> <option value="0" <%if request.form("stateid") = "" then response.write "selected"%>>All States <% set rs = cn.execute("select stateid,state from tblState where benchmarkactive order by state") do until rs.eof if request.form("stateid")+0 = rs.fields(0) then selected = "selected" else selected = "" %> <option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%> <% rs.movenext loop rs.close %> </select> </td>
<td align="left" width="127"><font face="Arial" size="2"> <select name="countryid"> <option value="0" <%if request.form("countryid") = "" then response.write "selected"%>>All Countries <% set rs = cn.execute("select countryid,country from tblCountry where benchmarkactive order by country") do until rs.eof if request.form("countryid")+0 = rs.fields(0) then selected = "selected" else selected = "" %> <option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%> <% rs.movenext loop rs.close %> </select> </td> <td align="left" width="129"><input type="image" src="../../Images/view-report.gif" onclick="f1.submit()" name="f1" alt="Click Here to Generate Report"></td> <td width="127"><input type="image" src="../../Images/print-report.gif" rname="printMe" onClick="printSpecial()" alt="Click Here to Print the Page"></td>
</tr> </table>
<% if mmmm > 0 then %> <table border="0" width="450" align="left" style="margin-left:10px; border-collapse: collapse" bordercolor="#111111" cellpadding="2" cellspacing="4"> <thead> <tr> <% sql = " where 1 = 1 AND BenchmarkGroup =" & request("benchmarkgroup") if request.form("stateid")+0 then sql = sql & " and stateid = " & request.form("stateid") if request.form("countryid")+0 then sql = sql & " and countryid = " & request.form("countryid") set rs = cn.Execute("TRANSFORM First(UserID) SELECT description, minimum, mintype, formula FROM BenchmarksActiveUsers" & sql & " GROUP BY displayOrder, description, minimum, mintype, formula ORDER BY displayOrder PIVOT Name") lastfield = rs.fields.count-1 s = 4 %> <td align="left" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Benchmark</b></font> </td> <td align="center" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Company Average</b></font> </td> <% for i = s to lastfield %>
<% next %> </tr> </thead> <tbody> <br> <% dim v() do until rs.eof min = int(rs.fields("minimum")) mintype = rs.fields("mintype") sumv = 0 avg = 0 n = 0 for j = s to lastfield redim preserve v(j) v(j) = evaluate(rs.fields("formula"),rs.fields(j),mmmm,yyyy) sumv = sumv + v(j) n = n + 1 next if n > 0 then avg = round(sumv / n) %> <tr><td></td><td align="center" valign="bottom"><font face="Arial" size="2">Target: <%=min%> <%=mintype%></font></td><td></td></tr> <tr> <td bgcolor="#FFFFFF"> <p align="right"><font face="Arial" size="2"><%=rs.fields("description")%></font></td>
I dont know how to arrange situation when application enduser needs to access data in two databases of mssql server concurently in those circumstances that access rights to the data should be restricted by password protected role (whose password is not known to the end user).
Detailed description of problem:
So far there was an application, that manipulated its data, saved in mssql server's database. End user authenticates to application by his (mssql server's) login name and password. The application authenticates the user by connecting to the database with the given name/password credentials, and then the application sets application role with hardcoded name/password. Thus application role sets the access rights for consequent end user's requests, delivered via application to the database server.
The goal is that end user cannot manipulate application database data when connects to the database by other means (e. g. via SQL server Manager), because he does not know the application role's password.
Now suppose that there are two applications (A1, A2), both using the same model for access restrictions. Each of them has its own database (A1DB, A2DB) and its own application role (A1R residing in A1DB, A2R residing in A2DB). End user (login) X can manipulate A1DB data when connects via A1, and A2DB data when connects via A2, and NO data when connects by other means.
Finally suppose that some subset of A2 data (let's say one table) is useful to see also via A1 application. There is no problem to add to A1DB view, that shows data from A2DB table together with A1DB tables. But when the user is connected via A1, he cannot see the data, because query on A1 view fails (user has not access rights on A2 data).
The access rights for A1 enduser cannot be set by no means i know because:
1) I cannot set the rights via public (guest) access because in that case they will be accessible to any users connected by any third party products, which is supposed to be security hole.
2) I cannot set the rights via dbuser or dbrole privileges, because they will not work when connected via A1 application (setting the app role suppresses the db privileges)
3) I cannot set the rights via application role because two application roles cannot be set concurrently.
4) I cannot abandon using application roles mechanism and use database roles mechanism, because db roles cannot be protected by independent password (not known to the enduser).
Please can anybody review my problem and either find the mistake in my approach, or propose other solution? So far I suppose the problem is my ignorance, because I am not great mssql expert.
I have developed a web application using ASP 3.0 with a Access DB 2002 backend. I have decided to recently to upgrade the database to SQL 2005 but I am having issues that I cannot work out because I am new to MsSql Language. It appears that the FORMAT function in Access does not work in MsSql ( does not recognise the function ) along with some of the PIVOT and TRANSFORM calls (coming up with "Syntax error near "..."") . Please see the below code. The areas that are showing up as errors when the application is run are highlighted in red.
If IncludeFieldNames Then 'string concatenation issues aren't a problem for a small string of field names For Each Field In RS.Fields If FieldNames = "" Then FieldNames = Field.Name Else FieldNames = FieldNames & "," & Field.Name End If
Next FieldNames = FieldNames & vbCRLF Call objCSVFile.WriteText(FieldNames, 1) End If
Call objCSVFile.WriteText(FieldNames & RS.GetString(adClipString, , ",", vbCRLF, "")) Call objCSVFile.SaveToFile(CSVFilePath, 2) Set objCSVFile = Nothing End Sub %> <% function evaluate(pFormula, pUserID, pMonth, pYear) if isnull(pFormula) then evaluate = 0 exit function end if x = 0 key = "" zero = "0" nine = "9" eFormula = "" lf = len(pFormula) for i = 1 to lf c = mid(pFormula,i,1) if x = 1 and c >= zero and c <= nine then key = key & c if x = 1 and (c < zero or c > nine or i = lf) then set rsd = cn.execute("select sum(total) from Data where UserID = " & pUserID & " and month(weekEnding) = " & pMonth & " and year(weekEnding) = " & pYear & " and GroupTaskID=" & key) eFormula = eFormula & rsd.fields(0) rsd.close key = "" if c < zero or c > nine then x = 0 end if if c = "v" then key = "" x = 1 end if if x = 0 then eFormula = eFormula & c next on error resume next evaluate = round(eval(eFormula),0) 'if err then evaluate = err.description & ": " & eformula if err then evaluate = 0 end function %> <% period = split(request.form("period"),",") if ubound(period) = 1 then mmmm = period(0)+0 yyyy = period(1)+0 else mmmm = 0 yyyy = 0 end if %>
</div> <p align="center" class="MainBodyText"> <img src="../../images/si.gif" align="center"><br><b>Month to Date Dashboard</b></p> <p align="center" class="MainBodyText"> To use the Print Page function please select the period required then select view report. You can then select Print Page which will print out the rendered page.</p> <form name="f1" method="POST" action="dialmtd.asp"> <div id="printReady"> <table align="center" border="0" width="663" style="border-collapse: collapse" bordercolor="#111111" cellpadding="2"> <tr>
<td align="left" width="127"><font face="Arial" size="2"> <select name="benchmarkgroup" style="color: #000000; border: 0px solid #000000; background-color: #FFE737"> <option value="0" <%if request.form("benchmarkgroup") = "" then response.write "selected"%>>Select Report <% set rs = cn.execute("select TaskGroupID,TaskGroup from BenchmarkGroups where Active = 1 order by TaskGroupID") do until rs.eof if request.form("taskgroupid")+0 = rs.fields(0) then selected = "selected" else selected = "" %> <option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%> <% rs.movenext loop rs.close %> </select> </td>
<td align="left" width="143"><font face="Arial" size="2"> <select name="period"> <option value="" <%if request.form("period") = "" then response.write "selected"%>>Select Period <% set rs = cn.execute("select year(WeekEnding), month(WeekEnding), format(Weekending,""mmmm yyyy"") from data group by Year(WeekEnding), Month(WeekEnding), format(WeekEnding,""mmmm yyyy"") order by 1,2") do until rs.eof x = rs.fields(1) & "," & rs.fields(0) if request.form("period") = x then selected = "selected" else selected = "" %> <option value=<%=x & " " & selected%>><%=rs.fields(2)%> <% rs.movenext loop rs.close %> </select> </td>
<td align="left" width="117"><font face="Arial" size="2"> <select name="stateid"> <option value="0" <%if request.form("stateid") = "" then response.write "selected"%>>All States <% set rs = cn.execute("select stateid,state from tblState where benchmarkactive order by state") do until rs.eof if request.form("stateid")+0 = rs.fields(0) then selected = "selected" else selected = "" %> <option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%> <% rs.movenext loop rs.close %> </select> </td> <td align="left" width="127"><font face="Arial" size="2"> <select name="countryid"> <option value="0" <%if request.form("countryid") = "" then response.write "selected"%>>All Countries <% set rs = cn.execute("select countryid,country from tblCountry where benchmarkactive order by country") do until rs.eof if request.form("countryid")+0 = rs.fields(0) then selected = "selected" else selected = "" %> <option value=<%=rs.fields(0) & " " & selected%>><%=rs.fields(1)%> <% rs.movenext loop rs.close %> </select> </td> <td align="left" width="129"><input type="image" src="../../Images/view-report.gif" onclick="f1.submit()" name="f1" alt="Click Here to Generate Report"></td> <td width="127"><input type="image" src="../../Images/print-report.gif" rname="printMe" onClick="printSpecial()" alt="Click Here to Print the Page"></td>
</tr> </table> <% if mmmm > 0 then %> <table border="0" width="450" align="left" style="margin-left:10px; border-collapse: collapse" bordercolor="#111111" cellpadding="2" cellspacing="4"> <thead> <tr> <% sql = " where 1 = 1 AND BenchmarkGroup =" & request("benchmarkgroup") if request.form("stateid")+0 then sql = sql & " and stateid = " & request.form("stateid") if request.form("countryid")+0 then sql = sql & " and countryid = " & request.form("countryid") set rs = cn.Execute("TRANSFORM First(UserID) SELECT description, minimum, mintype, formula FROM BenchmarksActiveUsers" & sql & " GROUP BY displayOrder, description, minimum, mintype, formula ORDER BY displayOrder PIVOT Name") lastfield = rs.fields.count-1 s = 4 %> <td align="left" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Benchmark</b></font> </td> <td align="center" bgcolor="#C0C0C0"><font face="Arial" color="#000000" size="2"><b>Company Average</b></font> </td> <% for i = s to lastfield %> <% next %> </tr> </thead> <tbody> <br> <% dim v() do until rs.eof min = int(rs.fields("minimum")) mintype = rs.fields("mintype") sumv = 0 avg = 0 n = 0 for j = s to lastfield redim preserve v(j) v(j) = evaluate(rs.fields("formula"),rs.fields(j),mmmm,yyyy) sumv = sumv + v(j) n = n + 1 next if n > 0 then avg = round(sumv / n) %> <tr><td></td><td align="center" valign="bottom"><font face="Arial" size="2">Target: <%=min%> <%=mintype%></font></td><td></td></tr> <tr> <td bgcolor="#FFFFFF"> <p align="right"><font face="Arial" size="2"><%=rs.fields("description")%></font></td>
I'm about to embark on writing some code in perl or VBscript that automatically synchronises a constantly updated Access database with an MSSQL database.
I know MSSQL has an import tool built into Enterprise manager but I'm wondering if theres a stored procedure that does this?
The way I'm thinking of doing it is to read the all the access tables into separate hash arrays and then INSERTing them into the MSSQL database after checking for any duplicates. This all sounds a bit time consuming (there are a large number of tables) and processor intensive.
If anyones done anything like this before, I'd love to hear their views......!
I have an application that uses Acces as a backend and VBA as front end. Application is secured and is supplied on a CD with setup.exe.
Can I use VB 2005 and MS SQL to achieve the same?
Would I be able to package my application with all the neccessery files (assuming that client does not have any e.g. SQL server) so that multiple front ends can access data from common source?
Would I be able to secure such an application using only VS 2005?
What would I need to quickly learn how to achieve the above ( any books you can suggest maybe)?
I have a database that is in mssql and I'm using an odbc link to an access database where I want to add records to the mssql table. When I open the linked table in access it does not allow me to add a record. I have created a user account in mssql that has ownership to the database and I use this user in setting up the odbc link.
I'am currently testing a MSSQL 2005 mirroring implementation with automatically failover.
1 principal server ====> Server A 1 mirror server ====> Server B 1 witness server ====> Server C
When server A fails Server B becomes Principal. ( which is the general idea ) This is working perfectly.
The problem is when i try to connect to the mirror Server B ( actually the new principal )
I use the .NET connection string with the failover parameter ( even without the parameter ) the connection fails. ( the general error : unable to connect, or connection property has not been initialized )
Data Source=ServerA; Failover Partner=ServerB;Initial Catalog=DBMTest;uid=dbuser;pwd=dbpassword;
This connection property works fine when server A (original principal) is online.
When i run the SQL profiler with the mirror db as profile, i dont see the traffic.
So you think the mirror server has a network problem or was not connfigured for remote access. i thought that also, but when i create a 'stand alone - non mirrored' database the same application with the same connetion string ( well.....different db) works fine.
The problem must be in the whole mirroring implementation....... ( my idea )
Does anyone had the same problem, or better, doed anyone know how to solve it?
Hello, I'm trying to access the MSSQl 'remotey'. I open telnet and go to 127.0.0.1 1433 but can't. It seems I have done everything I can to fix it, but nothing solves the problem.
My firewall is disabled.
I have TCP/IP enabled in "SQL Server Configuration Manager" for both Client Protocol and protocols for MSSSQLSERVER (Shared Memory and NAmed Pipes are also both enabled). VIA is disabled.
In SQL Services, SQL Server Integration Services, Full Text Searc, SQL Server, SQL Server Analysis Services, and Server Browser are running. SQL Server Agent is disabled.
In Server Surface Area configuration, under Database Engine is running (automatic) and "Remote Connections" is enabled to TCP/IP and named pipes. Analysis Service has Local and Remote connections checked and is running (automatic). SQL Server agent is stopped.
Integration Services is running (Automatic) SQL Server Browser is running. (Automatic)
When I look in the error log, it shows...
Date 1/4/2007 3:21:45 AM Log SQL Server (Current - 1/4/2007 3:21:00 AM)
Source Server
Message Server is listening on [ 127.0.0.1 <ipv4> 1434].
Date 1/4/2007 3:21:45 AM Log SQL Server (Current - 1/4/2007 3:21:00 AM)
Source Server
Message Server is listening on [ 'any' <ipv4> 1433].
I have tried to telnet to port 1434 and 1433 but nothing happens. Does anyone know what else I need to be able to access that port??
Writing to tables created by regular users on MSSQL2005
I have users creating tables through an application, I gave them ddl_admin, datareader, datawriter. They can create tables but cannot insert/update data (to their own tables), I cannot insert data either using Access or any other application to those tables created by them (under dbo schema) Is there something I am missing with permissions? Thank you very much
Table #1 SELECT EID, DATEPART(wk,[Date]) AS Week, MAX([Date]) AS WeekEnding FROM Employee_Hours WHERE ([Date] Between '1/1/04' AND '12/31/04') GROUP BY EID, DATEPART(wk,[Date]) HAVING MAX([Date]) = '1/24/04' --Pay period
Table #2 SELECT EID, DisplayName FROM Employee WHERE (Active=1)
I tried subqueries and temp tables but can't get the results I need. The first query will return the employees who entered their time for the given pay period.
I have the following sql statement: INSERT INTO prg_lvl VALUES ( 'Data Recovery', 'Data Recovery', 7, 'ENG', 0, 16, 'Menu=w_data_recovery_stk;', ( select max(menu_id) + 1 from prg_lvl ), 'K'); that works for Sybase and I wanna make it works for SQL Server.
The problem is the use ot the subquery (select max(menu_id) + 1 from prg_lvl).
Could I use subqueries in SQL Server in general or is there is any other method to overcome this problem
I am very new to SQL Server, working with it in class. I am trying to do procedures involving subqueries using the Northwind database, and am having a rough time.
For instance, I have the following code, which lists all orders for the month of November, 1996:
SELECT Customers.CustomerID, Customers.CompanyName, CAST(Orders.OrderDate AS CHAR (11) )AS OrderDate FROM Customers, Orders WHERE Customers.CustomerID = Orders.CustomerID AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ;
I have been asked to do the same thing, but using a subquery. I came up with the following:
SELECT CustomerID, CompanyName FROM Customers a WHERE EXISTS (SELECT OrderDate FROM Orders b WHERE a.CustomerID = b.CustomerID AND OrderDate BETWEEN '11/1/1996' AND '11/30/1996' ) ;
However, it does not list the Order Date, as I want it to, and the information is not correct (it is missing a few orders from November '97 that should show up. Can someone tell me what I am doing incorrectly?
Please note that I do not need anyone to give me the code, but simply give me an idea of what I am doing incorrectly, or let me know how it can be done, so that I may do it. =-)
Hi all,I've seen mention that you can use nested subqueries down to as manylevels as you like but whenever I run the following:select * from table1where tab1ID in(select tab1ID from table2 where tab2ID in(select tab2ID from Table3 where Tab3ID=N))I get the error "incorrect syntax near the keyword 'where'"Can anyone confirm that sub-subqueries are possible? If so is thesyntax I'm using correct?I'm running SQL 2000 through a Delphi 5 app.RegardsJon
Guys,Got this query...---------------------------SELECT TOP 5 Tbl_admin_hotels.HotelName,(SELECT COUNT(Tbl_marketing_history.HotelID)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS CountTotal,(SELECT MAX(DateSent)FROM Tbl_marketing_historyWHERE Tbl_marketing_history.HotelID = Tbl_admin_hotels.HotelID)AS LastSentFROM Tbl_admin_hotelsWHERE NOT Tbl_admin_hotels.HotelID = 99ORDER BY CountTotal DESC---------------------------Within the table Tbl_marketing_history there is also a 'Subject' column thatI require, the row to grab this Subject column from should relate to the rowI'm selecting in the SubQuery to grab 'DateSent'.I've tried and tried to grab multiple columns but failed miserably? Cananyone help pleaseeee!Cheers, Ash
In this stored procedure, I want to grab all the information for Sales Representatives that made less then 6 visits. I have done that in the subquery. But I also want to be able to display their visits by Dates where any number of visits was made. Meaning, i want to be able to show Everyday where a visit was made, but i only want the records that are less then 6 visits. So I want nulls to show up for the dates where no visits were made. So I can track the people that didnt make visits, on days other made visits (which means they should have visits too)
So basically my report looks like this: 09-10-2007 09-11-2007 09-14-2007 Tom Smith 1 3 4 Janice Cooper 3 5 5 Troy Slater 5 4 2 Kelly Short 4 4 4
When i want it to look like this:
09-10-2007 09-11-2007 09-12-2007 09-13-2007 09-14-2007 Tom Smith 1 3 null null 4 Janice Cooper 3 5 null null 5 Troy Slater 5 4 null null 2 Kelly Short 4 4 null null 4
Its an exception report, so i want to show what was not done , on days that are supposed to have things done, i cant show this, if dates are not displayed before there were no visits less then 6. I want to display all relevant dates, where any number of visits was mad. But only show the visits that were less then 6.
Here's my stored procedure that already has the less then 6 visits showing. But doesnt always show the dates. What should i do?
Hey everyone, I'm running into an issue where I have a select statement that retrieves all employee records from a employee table. In this statement I'm displaying first and last name, titlte and the employees manager id(which is the employees id). What I want is a query that instead of displaying the manager's id, display the manager's name. The query that I'm using below doesn't work. I was hoping that someone could help me out: SELECT LastName, FirstName, Title, Extension, (SELECT FirstName, LastName FROM Employees WHERE (ReportsTo = EmployeeID)) AS Reports FROM Employees
Here's the data that I'm using: LastName, First Name Title Extension Reprots To
Davolio Nancy Sales Representative 5467 13
Fuller Andrew Vice President, Sales 3457 0
Leverling Janet Sales Representative 3355 12
When I run this query I get a error saying "Only one expression can be specified in the select list when the subquery is not introduced with Exists"
So I already no this can't be done... but I need a suitable alternative (if one exists) so I don't have to competely re-write this already too huge query. Anyways, in my select I have something like this: sum( case when code in (1,2,3,4) then 0 else 1 end ) as total which has now increase from four static values to a more dynamic format, that being a table with 47 values which may shrink or grow over time. Attempting the following fails: sum( case when code in (select code_id from ExcludedCodes) then 0 else 1 end ) as total because apparently you can't use selects or aggregates within an aggregate. So any ideas on how I can get this working... is there no Array or List type variable I could just substitute in? I've already tried using a Table Variable, but that failed as well. Please keep in mind, this is one line of a very large select containing many other fields and aggregates (on a fair amount of joins) which is used in at least four differerent reporting queries. If there is no quick and easy replacement trick I can do just let me know so I can start re-writing all of them (which is seriously going to make my head hurt).
Hi, I have searched through this forum and haven't found the answer to a problem I am having so I figured I will post it.
My SQL statement:
Select * FROM tblData WHERE LastName= (SELECT tblData.LastName FROM tblData GROUP BY tblData.LastName HAVING (Count(tblData.LastName)>1))"
What I am trying to do: I am trying to make an SQL filter that I can apply to my form in order to only show records with duplicate last names. The sub query returns names that are already in the table. I then compare what is found to be duplicate with the original table in order to just show only the duplicate records. Everything works fine as long as there is only one name thats duplicated.
When there are multiple duplicate names then I run into an error.
When the statement is put into a string and executed in VBA I get this error:
"Run-time error '3021': No current record."
When the statement is put into a query and run against the DB I get this error:
"At most one record can be returned by this subquery"
So yeah, any help would be greatly appreciated. Am I going about this all wrong or am I just forgetting something? Thanks for any help.
I would like to know which of the following query is the fastest:
select * from customers C where exists (select 0 from ORDERS O where C.Name like 'A%' and O.Charged = 1)
select * from customers C where exists (select 0 from ORDERS O where O.Charged = 1) and C.Name like 'A%'
I don't want to use a JOIN clause. I just want to know if there is a difference of efficiency when the condition on C.Name is inside or outside the sub query.
