I Miss MS Access Crosstabs In SQL-Server ;-)
Apr 25, 2005
Hi there...
im my DB there is a table. Something like
Who, Value1, Value2, ...
John, 1, 2
Jim, 2, 2
Bob, 1, 1
Jack, 0, 0
Mike, 1, 1
...
Now i want to have as a result of a query:
Valuefield, 0, 1, 2
Value1, 1, 3, 1
Value2, 1, 2, 2
as the count of rows that equals 0, 1 or 2 to each column...
I know the select-case-statement in SQL-Server, but how do i get the columnnames in the first column??? I have 28 columns like that in my table...
hope you can provice help...
kind regards,
Cappu
View 5 Replies
ADVERTISEMENT
Dec 4, 2007
Hi Everybody,
I am working on Sql Server 2000. I know that, like MS Access, creating Crosstab queries are not possible in SQL 2000. Is there any way or any query through which we acheive the same.
View 1 Replies
View Related
Apr 18, 2006
Hi everybody,
I am a bit of a SQL Server 2005 newbie. I have to run a report extracting survey responses for my group using SQL Server. My question is what is the best way to organize the data so it converts from rows into columns. I know SQL Server was not meant for creating presentation reports but I have over 800 surveys that I compile so any help would be appreciate. I have to extract data from 2 tables within a view.
For example, I would like the data show in this format:
Last Name|First Name|Question1|Question2|Question3|..etc
Doe|John|answer1|answer2|answer3|...etc.
Doe|Jill|answer1|answer2|
Currently it shows in the this format:
Doe|John|Question1|answer1
Doe|John|Question2|answer2
Doe|Jill|Question1|answer1
Doe|Jill|Question 2|answer2
Here is my query. I just need to know how to just get started and I can probably do the rest.
SELECT Distinct
dbo.uvwReporting_SurveyAnswers.DateCreated AS DateCreated
,dbo.uvwReporting_SurveyAnswers.questionid AS QuestionID
,dbo.uvwReporting_SurveyAnswers.userid AS UserID
,dbo.uvwReporting_SurveyAnswers.surveyid AS SurveyID
,dbo.uvwReporting_User.LastName1 AS LastName
,dbo.uvwReporting_User.FirstName AS FirstName
,dbo.uvwReporting_SurveyQuestions.ordernumber AS OrderNumber
,dbo.uvwReporting_SurveyQuestions.QuestionText AS QuestionText
,dbo.uvwReporting_SurveyAnswers.QuestionAnswer AS QuestionAnswer
FROM
dbo.uvwReporting_Surveys
INNER JOIN dbo.uvwReporting_SurveyQuestions
ON dbo.uvwReporting_Surveys.surveyid = dbo.uvwReporting_SurveyQuestions.surveyid
INNER JOIN dbo.uvwReporting_SurveyAnswers
ON dbo.uvwReporting_SurveyQuestions.QuestionID = dbo.uvwReporting_SurveyAnswers.QuestionID
INNER JOIN dbo.uvwReporting_User
ON dbo.uvwReporting_SurveyAnswers.userid = dbo.uvwReporting_User.userid
WHERE
dbo.uvwReporting_SurveyAnswers.surveyid = 100
Order by dbo.uvwReporting_SurveyAnswers.DateCreated
,dbo.uvwReporting_SurveyQuestions.ordernumber
Thanks
MickeyD
View 1 Replies
View Related
Nov 23, 2005
Hi Guru,My profiler trace does not display SP:CACHEMISS event, even thought Idrop store proc, clear both data cache and buffer cache but still doesnot work.Every thing works fine like: cachehit,cacheinsert,cacheremove,executecontexthit etc...Is there any special option that I need to turn it on?Please help,Thanks,Silaphet,
View 1 Replies
View Related
Jun 2, 2006
Maybe a silly thing but can you imagine that the tasks showed Heigh,Left, Top location properties as whether they were VB's control?
View 4 Replies
View Related
Aug 4, 2006
In QA from MSSQL2K5, is there a way Cntrl + W through the windows like there way in MSSQL2K5? I've tried and tried to find this type of setting and this is driving me nuts! :mad:
TIA, cfr
View 8 Replies
View Related
May 3, 2006
I have setup a transactional replication from oracle to MSSQL2005. One of the publication includes 26 articles but I found that there are only 24 articles replicated to the subscriber DB. I found that the repldata folder includes only files for 24 articles only. Besides, I double check the publication properties, totally, there 26 articles. Please advise. Thanks in advise.
View 2 Replies
View Related
Jul 1, 2004
Hi all,
Wow...since I've been gone, the search feature is apparently working again! Kewl!
However, after doing a search and not finding anything pertaining to my current issue, here I am again :D
I access a table on a linked server/database apart from my application's DB.
Every day, a process on the other server/DB drop and recreate the table....which, of course, zaps my user permissions. I then have to go into the Enterprise Mgr and re-enable my SELECT access to the table.
My question is this: Is there a way to auto-magically set my user ID on the linked server to give me access to the table without having to go in explicitly on a daily basis and re-enabling my UID Access rights.
This is the only table my user ID has been given access to on the remote server, so I am not able to simply set up my user ID on the remote system to "allow everything"
Am I missing something obvious? Do I have to create a SP on the remote server to do a daily GRANT SELECT ON Remote_Table TO Paul_Dammit after the table is rebuilt each day???
Suggestions?
It gets
View 3 Replies
View Related
May 15, 2007
I tried. I really did to adapt to using Management studio as a replacement, but I just can't make the jump. I went ahead and installed SQL 2000 QA and have gone back to using it for ad-hoc query support and other routine SQL tasks. For me the main thing is the "Notepad-esque" lightness of QA compared to the heavier SQL tools that allows me to quickly open a SQL script file and get to work on it quickly. I appreciate an integrated tool, but I would like to see future versions allow you to use it either as a standalone or within integrated platform. Am I alone in this? Anyone else feeling this pain?
View 6 Replies
View Related
Nov 16, 2006
hi
I have a float column . only in cursor I get a type missmatch.
does any one knows it ?
the error
Msg 8114, Level 16, State 5, Line 14
Error converting data type varchar to float.
the code
DECLARE @RON_FLOAT FLOAT
DECLARE RON_CURSOR CURSOR FOR
SELECT RON_FLOAT
FROM RON_TABLE1
OPEN RON_CURSOR
FETCH NEXT FROM RON_CURSOR
INTO @RON_FLOAT
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'VALUE IS ' + @RON_FLOAT
FETCH NEXT FROM RON_CURSOR
INTO @RON_FLOAT
END
CLOSE RON_CURSOR
DEALLOCATE RON_CURSOR
the code for the table
CREATE TABLE [dbo].[RON_TABLE1](
[RON_FLOAT] [float] NULL,
[RON_CHAR] [nchar](10) COLLATE Hebrew_BIN NULL
)
View 1 Replies
View Related
Jun 10, 2015
I have recently upgraded to SQL2014 on Win2012. The Access front end program works fine.
But, previously created Excel reports with built in MS Queries now fail with the above error for users with MS 2013. The queries still work for users still using MS 2007.
I also cannot create any new queries and get the same error message. If I log on as myself on the domain to another PC with 2007 installed it works fine, so I don't think it is anything to do with AD groups or permissions.
View 6 Replies
View Related
Oct 30, 2007
Hi guys,
I've been developing desktop client-server and web apps and have used Access and SQL Server Standard most of the time.
I'm looking into using SQL CE, and had a few questions that I can't seem to get a clear picture on:
- The documentation for CE says that it supports 256 simultaneous connections and offers the Isolation levels, Transactions, Locking, etc with a 4GB DB. But most people say that CE is strictly a single-user DB and should not be used as a DB Server.
Could CE be extended for use as a multi-user DB Server by creating a custom server such as a .NET Remoting Server hosted through a Windows Service (or any other custom host) on a machine whereby the CE DB would run in-process with this server on the machine which would then be accessed by multiple users from multiple machines??
Clients PCs -> Server PC hosting Remoting Service -> ADO.NET -> SQL CE
- and further more can we use Enterprise Services (Serviced Components) to connect to SQL CE and further extend this model to offer a pure high-quality DB Server?
Clients PCs -> Server PC hosting Remoting Service -> Enterprise Services -> ADO.NET -> SQL CE
Seems quite doable to me, but I may be wrong..please let me know either ways
Thanks,
CP
View 3 Replies
View Related
Nov 29, 2006
Is there a way to specify a MS Access table (or query object) in the select query of SQL Server.
Ex.:
MSAccessTable (in file.mdb)
col1
col2
a1
a2
b1
b2
SQL query in SQL Server:
SELECT col1, col2 into SqlTable from [file.mdb].MSAccessTable;
Thanks,
View 3 Replies
View Related
Apr 22, 2015
When i am trying to start our hospital software based on SQL server 2000, it shows Following Error.Search Condition is not valid, (DBNETLIB) Connection Open (connect()). SQL server does not exist or excess denied. Due to Fetch data.I run our software in Windows 8.1, while it smothly runs in previous version of Windows XP and 7.
View 2 Replies
View Related
Oct 31, 2005
Hello allWould it be possible to migrate the MS Access 2000 to MS SQL Server2000?My application is using MS Access 2000 as database and as userinterface such as forms. Now, I want to migrate the backend databasefrom MS Access 2000 to MS SQL Server 2000. However, I want to keep theMS Access 2000 interface. Would it be possible?If I migrate the MS Access to SQL Server, would the queries, back-endVBA, macro, tables and forms be affected? Do I need to change the MSAccess data type to SQL server supported data type?Which tool I can use to do the migration? Upsizing wizard or exportingthe Access database and then importing it to the SQL server?Thanks in advanceCheersBon
View 4 Replies
View Related
Dec 18, 2006
I am trying to connect through ODBC connectivity, but it will not allow me to do so. I have investigated this matter. It leads me back to the server, because as I was configuring my client side database. It kept asking for the DSN(datasource name), but I was unable to choose one because there wasn't one to choose. Which is my current dilemma, How can I do this and have it available to choose from the server to satisfy the Access database?
I went to the domain where the software resides but I don't know what steps to take? I also found an interesting piece on microsoft about Kerberos, but I can't follow along according to the instructions it has. I have Access 2003 & SQL SERVER 2005, HELP...!
Basically, this is right off the heels of the install. I setup the server without the connectivity, but it is running the current configuration.
View 7 Replies
View Related
Dec 6, 2000
Hi,
Can anyone tell me how i can access an application or open tables in MS sql server that are in MS Access. Its urgent and any help will be appriaciated.
View 2 Replies
View Related
Jun 6, 2007
Hi,
When I am trying to access SQL Server 2000 database from another machine i got this error
Server: MSg 17, Level 16, State 1 [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied
but I could access the database on same server and in that server i could access other databases in different server.
View 6 Replies
View Related
Sep 6, 2007
We are using Win2k3 R2 with SQL 2000 in a domain environment.
Is it possible to create a domain group to grant admin level and user level access to SQL2000/2005 without giving users server admin or domain admin access?
It has always been my impression that to have admin access to SQL that you had to at least had admin level access on the server.
Any clarification would be greatly appreciated.
Thanks!
View 1 Replies
View Related
Sep 29, 2005
Hi..
when I use MSSQL in local ASP files , it is fine.
But If I try to connect another ASP file that is hosted by another machine, I get the following error!
[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
in local I connect to my sql like below
Server=AAA*DDDDDDD
when I connect to mssql from another machine I try that
Server=IP/AAA*DDDDDDD
Server=IP
View 4 Replies
View Related
Dec 5, 2014
I've recently started working with a public sector organisation who have 4 clustered sql instances that has 80% of it's db mirrored.
Looking at the transaction log - it seems that a transaction log backup is a good idea as the log is 4x larger than the data file.But I'm not allowed access to the physical server to check onto which drive I can create the trn. No RDP, no vmware - let's be honest I'm not even allowed to launch cmd line Also the Server Manager informs me "We will need to carefully look at database backups if you guys want to start doing these backups on box, as that will break our off box backup routine (it will screw the transaction chain)."
I don't understand how backing up the transaction log could break the "transaction chain"?
View 9 Replies
View Related
Aug 30, 2006
I'm trying to link SQL Server 2000 sp3 to SQL 2005 64 bit. I keep getting Error 17 sql server does not exist or access denined. I tried adding remote user mapping and chaning the linked server properties to "Be made using this security context" without any success.
Any help is appreciated.
View 4 Replies
View Related
Nov 8, 2015
I am receiving the following error when starting a program called ShelbySystems that is supposed to connect to a local database. I don't think this is a security issue but I don't know much about SQL server either so...
DIAG [08001] [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied. (17)
DIAG [01000] [Microsoft][ODBC SQL Server Driver][Shared Memory]ConnectionOpen (Connect()). (2)
System Info:
Windows 10 Home - upgrade from 8 64 bit
SQL server 2012 Express
SQL Backwards compatibility 2005 64 bit
ShelbySystems software v5.4
I am including the trace log in case it is useful.
DBInstall 130c-728ENTER SQLAllocHandle
SQLSMALLINT 1 <SQL_HANDLE_ENV>
SQLHANDLE 0x00000000
SQLHANDLE * 0x02EC58F4
[code]....
View 2 Replies
View Related
Jul 26, 2007
Hi,
when I was sending 50 simultaneous connections to the web services, I got these 2 errors:
- "1[Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied. "
- "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: TCP Provider, error: 0 - A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond.)"
Everything worked fine if I ran 10 simultaneous connections. I am using Microsoft ACT to load test the web service. The database is SQL Server 2000. Any idea about why this is happening?
Appreciate your help! Thanks!
Jason Zhu
View 3 Replies
View Related
Dec 3, 2004
I am running Windows 2003 Standard edition with IIS and SQL 2000 SP3a installed on it.
The web clients use ASP pages which use DSN to connect to SQL 2000 database. It has been working fine since last 1.5 years except 2 days ago when I faced this error suddenly on server:
In accessing any ASP page, it said:
[Microsoft][ODBC_SQL_Server_Driver][TCP/IP_Sockets_SQL_Server_does_not_exist_or_access_den ied.
On SQL Server error log, this was the entry at that time which I found:
SQLServer Error: 17, SQL Server does not exist or access denied. [SQLSTATE 08001]
The server (including SQL Server and agent service) were last stopped atleast a couple of days before, so it was not the problem about startup.
To troubleshoot, I restarted SQL Server and it worked for some time.
Then again on ASP pages, I found this error:
|80004005|[Microsoft][ODBC_SQL_Server_Driver][TCP/IP_Sockets]General_network_error._Check_your_network_document ation.
I checked the error logs of SQL server and didn't find anything.
Only entries were that the SQL is starting database, ports and IPs on which it is listening etc. The usual stuff. No errors in starting.
The SQL Agent log showed following errors:
2004-11-30 04:45:33 - ! [298] SQLServer Error: 2, ConnectionOpen (Connect()). [SQLSTATE 01000]
2004-11-30 04:45:33 - ! [382] Logon to server '(local)' failed (JobManager)
2004-11-30 04:45:36 - ! [298] SQLServer Error: 17, SQL Server does not exist or access denied. [SQLSTATE
I don't understand what was the problem. It first occured when nothing on server was changed, next it occured 30-32 hours later, and still now it hasn't happened again.
Does anyone know what can be wrong?
Thanks.
View 5 Replies
View Related
Sep 22, 2015
I want to import the data from specific Access Database and Table to SQL Server, using SQL Script. I am trying to implement the solution as given in this link. URL....Here is the code that I have tried -
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ad Hoc Distributed Queries', 1;
GO
[code]...
The access database file path is - 'C:SQL ProjectTestDB1001.mdb'.The Table from which I want to import the data is - [Table1001]. but when I run this script, I get this error -9 The OLE DB provider "Microsoft.ACE.OLEDB.12.0" for linked server "MSAccessConnect" reported an error. Authentication failed.
Secondly I need to make 2 more changes to the code posted above.
1.If some access database .mdb, has got password protection, then how to include the password in the script, so that one does not have to manually feed in the password during data import work.
2. How to limit the data that is to be imported from the table [Table1001] by including a WHERE Clause, like for example - SELECT * FROM [Table1001] WHERE xdate = '2015-9-16 00:00:00.000')
View 7 Replies
View Related
Jul 26, 2007
Hello,
I just installed SQL Server Compact Edition, since I am considering using it instead of SQL Server Express for a local database in my application. The documentation mentioned that I could use SQL Server Management Studio Express to connect to the Compact Edition and create and manipulate databases.
To try to connect, I run Management Studio and bring up the "Connect to Server" dialog. Unfortunately, the pull-down list of "Server name"s does not include the SQL Server Compact Edition server. I do not know how to type in the server name manually, so I cannot connect.
To install Compact Edition, I downloaded it and ran "SQLServerCE31-EN.msi". This installed, and I assume registered, a number of DLLs in "C:Program FilesMicrosoft SQL Server Compact Editionv3.1".
Is there perhaps an additional step that I left out to complete the installation?
Might I need an upgrade to some other components? My Management Studio Version is:
- Microsoft SQL Server Management Studio Express 9.00.2047.00
I would appreciate any help you can provide.
Thank you,
WTW
View 7 Replies
View Related
Jul 6, 2007
I have deployed some reports on the reporting server, i have noticed that every one in the domain in which the reports are published can access the reports, except one user. when he tries to access the report he got the following error message or the blank report.
"An error occured during report processing"
"can no read the next data row for the data set dataset1".
Although this user has all the roles on the reporiting server kike browser,content manager etc.
i tried many things but still problem persists.
Can anyone tell me the exact reason for this ?
I shall be extremely thankfull.
View 1 Replies
View Related
Apr 4, 2007
Subject problem has me quite vexed.
I am receiving the following error when attempting to access reporting services... to sum things up real nice and tidy-
I get three login prompts - then the access denied response. It is almost as if it is unable to authenticate the user... anyway... here's the actual error response, I'd really appreciate any input/insight/resolution.
Server Error in '/Reports' Application.
Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerin' is denied.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.UnauthorizedAccessException: Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerin' is denied.
ASP.NET is not authorized to access the requested resource. Consider granting access rights to the resource to the ASP.NET request identity. ASP.NET has a base process identity (typically {MACHINE}ASPNET on IIS 5 or Network Service on IIS 6) that is used if the application is not impersonating. If the application is impersonating via <identity impersonate="true"/>, the identity will be the anonymous user (typically IUSR_MACHINENAME) or the authenticated request user.
To grant ASP.NET access to a file, right-click the file in Explorer, choose "Properties" and select the Security tab. Click "Add" to add the appropriate user or group. Highlight the ASP.NET account, and check the boxes for the desired access.
Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.
Stack Trace:
[UnauthorizedAccessException: Access to the path 'C:Program FilesMicrosoft SQL ServerMSSQL.2Reporting ServicesReportManagerin' is denied.]
System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath) +2014163
System.IO.Directory.InternalGetFileDirectoryNames(String path, String userPathOriginal, String searchPattern, Boolean includeFiles, Boolean includeDirs, SearchOption searchOption) +1817
System.IO.Directory.GetDirectories(String path, String searchPattern, SearchOption searchOption) +36
Microsoft.ReportingServices.Diagnostics.Localization.GetInstalledCultures() +112
Microsoft.ReportingServices.Diagnostics.Localization..cctor() +66
[TypeInitializationException: The type initializer for 'Microsoft.ReportingServices.Diagnostics.Localization' threw an exception.]
Microsoft.ReportingServices.Diagnostics.Localization.SetCultureFromPriorityList(String[] localeList) +0
Microsoft.ReportingServices.UI.GlobalApp.Application_BeginRequest(Object sender, EventArgs e) +157
System.Web.SyncEventExecutionStep.System.Web.HttpApplication.IExecutionStep.Execute() +92
System.Web.HttpApplication.ExecuteStep(IExecutionStep step, Boolean& completedSynchronously) +64
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
View 1 Replies
View Related
Jan 8, 2007
Hello,I have windows small business server 2003 premium installed, I can see the server service manager but can not start any service(nothing is listed).How do I set it up?
I can not find the enterprise manager to create a database.Thanks for your help
View 1 Replies
View Related
Nov 10, 2003
Hello
please assist me I will appreciate it
I have a problem in my scripting I gues,I "ve done the config and server registrations and allow a public permission ,but stil giving me trouble
well this is my coding:::::::
<
<script language="vb" runat="server">
Dim MyConnection As SqlConnection
Sub Page_Load(Sender As Object, E As EventArgs)
MyConnection = New SqlConnection("server=(localHost);Database=CampusLANDB;Trusted_Connection=yes")
If Not (IsPostBack)
Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
MyCommand = New SqlDataAdapter("Select distinct State from Authors",MyConnection)
DS=New DataSet()
MyCommand.Fill(DS,"tStudents") ' This is where the pro underlies
MySelect.DataSource=DS.Tables("Name").DefaultView
MySelect.DataBind()
End if
End Sub
sub GetName_Click(Sender As Object,E As EventArgs)
Dim SelectCmd As String = "Select From tStudents Where Name =@Name "
Dim DS As DataSet
Dim MyCommand As SqlDataAdapter
MyCommand = new sqldataAdapter(SelectCmd, MyConnection)
MyCommand.SelectCommand.Parameters.Add(New SqlParameter("@Name",SqlDbType.VarChar,2))
MyCommand.SelectCommand.Parameters("@Name").Value = MySelect.Value
DS= new DataSet()
MyCommand.Fill(DS,"tStudents")
MyDataGrid.DataSource=DS.Tables("tStudents").DefaultView
MyDataGrid.DataBind()
End sub
</script>
and I will apreciate if anyone vcan help me urgently
PPM
View 3 Replies
View Related
Feb 18, 1999
I have tried several times to install SQL server onto an NT4.0 server which is more than capable of having more than one application to cope with, however, when going through the install procedure the programm stops with the following message
DR.Watson
Access Violation
Setup.exe
In address :
Any hints as to where the problems lies or point me inthe right direction would be appreciated.
View 1 Replies
View Related
Jul 16, 2015
We are upgrading from SQL 2008R2 to SQL2014 but we have discovered that a couple of our applications are not supported on 2014. We'd like to keep one 2008R2 server and one 2014 server until we have time to upgrade the applications and move everything to the new server. The problem is we have custom code in some of the 2014 databases that access tables in the 2008 databases.
I know we can easily do cross server joins by using a linked server, but it would be a huge undertaking to find all that code and add a linked server name in front of every table, stored procedure, etc. So my question is, is there any way to move a database to a different server and still be able to access it without having to qualify the object names with a linked server? Is there some kind of server/database synonym that can be setup that would be recognized by all databases?
View 8 Replies
View Related
