What's The Equivalen Function Of StrConv ([FirstName] , 3 ) In ACCESS In Sql Server 2000?
Jul 20, 2005
HI,
i got a problem while using StrConv function in sql server.
My requirement is:-
suppose in a name field i have "jhon smith" Now i want to run a sql
which will give me the result like "Jhon Smith"--which means upper
case first.
Now i am running a query like below which is giving me "Jhon smith"
but i want "Jhon Smith".
SELECT [CustomerID] ,upper(left(firstname,1)) + lower(right(firstname,
len(firstname)-1)) AS [FirstName_] from my_table;
But the above query is not giving me the right result. Please
suggest...
Thanks
Hoque
View 2 Replies
ADVERTISEMENT
Jan 2, 2001
In VBA (Access 97) there is a function strconv() when used with the option "3" it converts things into proper case.
eg. 181 ISLINGTON RD. becomes 181 Islington Rd.
Is their a function or even a stored procedure that I can call to do this type of conversion?
View 1 Replies
View Related
Jul 20, 2005
Hi All,I am facing a problem with a sql what i used in MS Access but its notreturning the same result in MS Sql Server 2000. Here i am giving thesql:SELECT TOP 3 format( MY_DATE, "dddd mm, yyyy" ) FROM MY_TAB WHEREMY_ID=1The above sql in ACCESS return me the date in below format in onecolumn:Friday 09, 2003But in Sql server 2000 i am not getting the same format eventhough iam using convert function, date part function etc.Please if you find the solution would be helpful for me..ThanksHoque
View 3 Replies
View Related
Jul 23, 2005
Hi,Simple question: A customer has an application using Access 2000frontend and SQL Server 2000 backend. Data connection is over ODBC.There are almost 250 concurrent users and is growing. Have theysqueezed everything out of Access? Should the move to a VB.Net frontendtaken place ages ago?CheersMike
View 4 Replies
View Related
May 27, 2008
Parameter
Access 2000/XP
SQL Server 7.0
SQL Server 2000
MSDE 2000
Number of instances per server
n/a
n/a
16
16
Number of databases per instance / server
n/a
32,767
32,767
32,767
Number of objects per database
32,768
2,147,483,647
2,147,483,647
2,147,483,647
Number of users per database
n/a
16,379
16,379
16,379
Number of roles per database
n/a
16,367
16,367
16,367
Overall size of database (excluding logs)
2 GB
1,048,516 TB
1,048,516 TB
2 GB
Number of columns per table
255
1024
1024
1024
Number of rows per table
limited by storage
limited by storage
limited by storage
limited by storage
Number of bytes per row
(Excluding TEXT/MEMO/IMAGE/OLE)
2 KB
8 KB
8 KB
8 KB
Number of columns per query
255
4,096
4,096
4,096
Number of tables per query
32
256
256
256
Size of procedure / query
64 KB
250 MB
250 MB
250 MB
Number of input params per procedure / query
199
1,024
2,100
2,100
Size of SQL statement / batch
64 KB
64 KB
64 KB
64 KB
Depth of subquery nesting
50
32
32
32
Number of indexes per table
32
250 (1 clustered)
250 (1 clustered)
250 (1 clustered)
Number of columns per index
10
16
16
16
Number of characters per object name
64
128
128
128
Number of concurrent user connections
255
32,767
32,767
5
View 1 Replies
View Related
Jul 23, 2005
I've created a small company database where the tables reside in a SQLServer database. I'm using Access 2000 forms for a front end.I've got a System DSN set-up to SQL Server and am using links withinAccess 2000 to get to the SQL Server tables.My forms worked fine until I made a few minor changes to the databaseschema on SQL Server (e.g. added a foreign key, or added a column).After that, all the links break - I click on a table link and get anerror msg like "invalid object name."Deleting the links after a schema change and re-adding the links seemedto fix the problem. The forms I'd already created seemed to work fineafter re-creating the links.But then I got more advanced with my forms. I have it set up so thatfor certain entry fields, the combobox gets populated with values froma table (the description appears in the drop-down and the correspondingprimary key value gets populated in the table). I created a number offorms using this technique, entered data, and everything worked fine.Made a small schema change and it broke everything -- not the actualtable links, but the functionality for the drop-downs. My values nolonger appeared, and this was true for forms that accessed tables whoseschemas did not change.This is driving me nuts. Is there any way to keep my forms frombreaking each time I make a small schema change?Thanks.- Dana
View 5 Replies
View Related
Oct 5, 2005
What is the counterpart of this function when using SQL Server 2000? Im
getting this error "System.Data.SqlClient.SqlException: 'Val' is not a
recognized function name"
What do I have to change in my queryString?
SELECT ASP_MainForm.UserID, Val([FormNo]) AS Expr1,
ASP_MainForm.DateCreated, ASP_MainForm.DateNeeded,
ASP_MainForm.FormStatus, ASP_MainForm.Print, ASP_MainForm.PRNo,
ASP_MainForm.ForUse, [FirstName]+' '+[LastName] AS CompName FROM
ASP_MainForm INNER JOIN CEN_USERS ON ASP_MainForm.UserID =
CEN_USERS.UserBadgeNo WHERE
(((ASP_MainForm.FormStatus)<>'Approved') AND
((ASP_MainForm.ForUse)<>'Test')) ORDER BY Expr1 DESC
Pls help thanks
View 2 Replies
View Related
Oct 18, 2004
Hi,
I worked on a project in ASP.NET using SQL server 2000 as the back end. Its a conversion application that I rewrote in ASP.NET using C#. I need to import the old data in Access db into SQL server 2000 and I have very little knowledge about doing it. The data in not a direct one -one transformation. There are considerable changes to the Database design and data types. Any help and suggestions wud be really helpful. Also, any article links wud be great.
Thanks
View 1 Replies
View Related
Mar 13, 2001
We have a SQL Server 7.0 system in NT 4.0 environment. We upgraded our users to Access 2000 and started to work with this. Now we installed a new
server which is Windows 2000 based and the domain is different from
the SQL servers domain. We then installed Access 2000 on Windows 2000
to use with terminal server. But I noticed that there was a problem
with the program. I then looked at the program which was written on Access 2000 and saw that the tables and views can't be seen. The program runs but
I can't see the views and tables. Another thing is access disconects from SQL Server when I want to see the tables. So what can be the problem.
In one part there is an Access 2000 on Windows 2000 server. On the other part SQL Server 7.0 on Windows NT 4.0. And Access can't see the tables in SQL server.
View 1 Replies
View Related
Oct 16, 2006
I am translating some of my Access queries to SQL views. In one of those, I had a very convenient function called "IIF" (e.g. IIf(IsNull([Remark]),"NULL","NOT NULL").
How is this function called in the MS SQL Server 2000? Apparently I cannot use either "IIF" nor "CASE" in the query/view.....
View 10 Replies
View Related
Jul 20, 2005
We are experiencing a problem with Sql Server 2000 linking to anAccess 97 file. We have two machines that link to this .mdb file, andwe recently upgraded one to newer hardware, SP3a, MDAC 2.8, etc. Thelink on this upgraded machine no longer works, giving this message:Server: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: Cannot open a database created witha previous version of your application.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'IDBInitialize::Initialize returned 0x80004005: ].The link on the older machine still works. We decided to tryconverting a copy of the file to Access 2000 to see if the newerpatches/drivers/whatever no longer supported 97. We set up a link onboth machines to this file, and they both work. However, on theupgraded machine, the following error is receievedServer: Msg 7399, Level 16, State 1, Line 1OLE DB provider 'Microsoft.Jet.OLEDB.4.0' reported an error.[OLE/DB provider returned message: System resource exceeded.]OLE DB error trace [OLE/DB Provider 'Microsoft.Jet.OLEDB.4.0'ICommandText::Execute returned 0x80004005: ].when making 1-3 connections to the the linked server, while the oldermachine supports at least 7 simultaneous queries connecting to thelinked server and still hasn't produced that error.Does anyone have any idea if there is a known issue with linking toAccess 97/2000 files under MDAC 2.8, Jet 4.0, etc? Any light anyonecan shine on this subject would be greatly appreciated.
View 1 Replies
View Related
Dec 7, 2007
Hello, I need some help being pointed in the right direction. I have a field in my Customer table called Name that is "LastName,FirstName". I need to be able to return a result set with "FirstName,LastName". Any ideas?
View 14 Replies
View Related
Aug 25, 2015
How to retrieve data as a two names from one columns.
For example: My column has naming like this Shannon.Cooley, I want to display like this:
FirstName LastName FullName
Shannon Cooley
Shannon Cooley
How to write the query to get the above table?
View 7 Replies
View Related
Mar 15, 2004
I am converting a MS Access 2000 project to use SQL Server. This project has uncovered a number of problems, my latest seems to be very odd.
When I query using ADO against the SQL Server database, the results don't seemed to be returned immediately. It's like the access methods being used are waiting too long to write/read the data.
This could be a simple ADO configuration error, but I cannot find any settings that would make this behave so strange.
I'm using OLE/DB drivers with trusted security and attaching using client-side recordsets (I tried server-side as well, same results) to get data for the Access form. In many instances, the data from the form is not yet retrieved when I check the results using the debugger. If I execute the same section of code just seconds later, it works without fail.
I ran the SQL Server Profiler just for grins and found that records were not getting written to the database as I would have expected. Apparently ADO had generated a transaction and rolled it back. (why I don't know) I have looked in Microsoft's support database and on MSDN to no avail. Rather then rewriting my T-SQL as stored procedures for these functions, I'm hoping for some insights from anyone else who has been down this nasty road.
Here is the exact scenario:
Problem #1:
A form that accepts data and has an update button does not update the recordset being used to populate the form. I even coded a rst.Update statement into the code. The record within the recordset isn't updated until the DoCmd.GotoRecord , , acNewRec call has been made.
I suppose I could code the insert statement myself, but shouldn't Access be updating the database for me. (it does have r/w access)
Problem #2:
A subform that performs a query and allows records to be added to a table does not correctly detect data returned from the query. A check is made in the code for a field (which has a value of 0) but the code detects a null value. If I step through this code in the debugger, enough time passes that the value is present and I never see the problem. If I set a breakpoint at the error, the null condition is indicated, even though the debugger shows a value of 0 for the field.
SQL Server version is 2000, with SP3a applied.
I updated the MDAC to 2.8, JET to 8.0 and still the same problem. I cannot find anything in my ADO or Access books for a setting that would cause this behavior. My connection is using the following info:
Set Conn = New ADODB.Connection
Conn.CursorLocation = adUseClient
Set rst = New ADODB.Recordset
Connection string: Provider=SQLOLEDB;Data Source=myserver;Initial Catalog=mydb;Trusted_Connection=Yes;Integrated Security=SSPI;
Recordset Cursor type: adOpenDynamic
Recordset LockType: adLockOptimistic
I open the recordset using:
rst.Open mytable, Conn, adOpenDynamic, adLockOptimistic, adCmdTableDirect
Problem # 1
After manipulating the data in the form, I press a "save" button on the form. This button calls a number of subroutines, eventually executing the code below:
rst.Update
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
Problem # 2
I set the recordsource using: (in the form_open)
Me.RecordSource = "Select * from mytable where myfield = 'data'"
In the form_unload, the problems occur. It tests for a value stored in the form from the query. This value is null at the time of the code execution, it has a value when inspected within the debugger however.
Maybe I'm missing something here, this project was originally coded using DAO. My conversion to ADO is 95% complete. (all of these two components are converted to ADO) This problem is causing me to miss out on sleep and look like a rookie.
Has anyone seen or heard of something such as this? Is there any hope for this snarled mess of 25K lines of code?
Thanks!
View 3 Replies
View Related
Jul 20, 2005
Hi,I have developed an application using VB 6 (SP 5), MS Access 2000 andCrystal Reports 9. I migrated from from access to SQl Server 2000.This wasn't a problem as i could import all the tables to SQL Server.Also, i could make VB6 talk to SQl Server.The problem arsies when i run my application. The sql syntax foraccess seems to be different than that for SQL Server. A simpleexample being: In access boolean datatype is true/false ,whereas inSQL Server the boolean equivalent is bit (numerical 1 or 0). Thesekind of issues are causing problems and most queries don't run.Would i need to go and change all the queries in accordance with SQlServer syntax ,which would be very time consuming or is there anyfunction which will convert the access datatype into its equivalentSQl Server datatype??Any input/thoughts/suggestions would be appreciated.ThanksJatin
View 1 Replies
View Related
Mar 16, 2007
Could anyone shed some light on the syntax of accessing system function on a linked server?I'm trying to get the recovery models of databases on a linked. However using databasepropertyex locally generates wrong results.e.g. select databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases I tried select [server/databasename].databasepropertyex(name, 'recovery') RecoveryModel from [server/databasename].master.dbo.SysDatabases which does not work. Thanks.
View 1 Replies
View Related
Mar 7, 2001
I am used to working in Access and just recently became somewhate proficient using custom functions in modules.
I am trying to figure out what the equivalent of functions is in SQL Server. I mean, does a Stored Procedure in SQL Server replace a module in Access? Can you declare different functions in SQL Server like you can in Access?
Thanks for your help.
Mike
View 1 Replies
View Related
Apr 26, 2006
This is my problem: I do not know how to get the servername from a C# user defined function . Is this possible?
I am writing a User Defined Function (UDF). Inside of this user defined function I need the name of the databaseserver that it is running on. Does anyone have an idea how I might do this? Is there an enviromental variable that I could access within the C# code I use to write the UDF?
I could always use a parameter to pass in the name of the server, but I would like to have as few parameters as possible.
Thanks in advance,
Sean
View 4 Replies
View Related
Sep 6, 2006
I'm sure you all have seen situations where a field has a combined namein the format of "Lastname, Firstname Middlename" and I've seenexamples of parsing that type of field. But what happens when the datawithin this type of field is inconsistent? Here are some examplesApple, John A.Berry John B.CherryJohn CDonald John DHow does one parse the data when the data isn't consistent?
View 2 Replies
View Related
Apr 11, 2007
I'm having trouble figuring out how to use a UDT class. I'm simply trying to test out the class in a query window in the management studio. Here's the simple snippet:
declare @test TestObject;
set @test.FirstName = 'bob'
select @test.FirstName
Obviously my object has a string property called FirstName. When I execute this simple query I get the following error:
Mutator 'FirstName' on '@test' cannot be called on a null value.
Any ideas?
View 1 Replies
View Related
Aug 1, 2005
I have this function in access I need to be able to use in ms sql. Having problems trying to get it to work. The function gets rid of the leading zeros if the field being past dosn't have any non number characters.For example:TrimZero("000000001023") > "1023"TrimZero("E1025") > "E1025"TrimZero("000000021021") > "21021"TrimZero("R5545") > "R5545"Here is the function that works in access:Public Function TrimZero(strField As Variant) As String Dim strReturn As String If IsNull(strField) = True Then strReturn = "" Else strReturn = strField Do While Left(strReturn, 1) = "0" strReturn = Mid(strReturn, 2) Loop End If TrimZero = strReturnEnd Function
View 3 Replies
View Related
Aug 5, 2005
My Table Structure is
Mem IDNameBoss ID
=======================
1Mohan0
2Raju1
3Prem1
4Priya2
5Sara3
6Ray4
7Chan0
I need to procedure to retrive all the down Members by giving a member number.
I have created a recresive function to achive this but at one stage it's giving error(above 32 level it's throwing an error). So i want a procedure which populates the temp. table with the required Information.
View 2 Replies
View Related
Sep 20, 2006
If I define a table-valued function in a SQL Server 2005 database, can I link to it from Access 2003 using ODBC?
I've defined the function successfully, and I can link from Access to tables in the database (so my ODBC link is basically functioning), but I can't see the table-valued function in the Linked Table Manager in Access.
I can define a pass-through query to grab the table, but with a pass-through query I have to provide the ODBC password every time.
What am I missing?
Suggestions?
View 1 Replies
View Related
Sep 21, 2004
I am facing a problem with Convert function in SQL server 2000 with sp3 installed , Japanese version
In sql server 2000 (sp1 installed) Japanese version, the convert(datetime,,111) gives me date in the format yyyy-mm-dd hh:min:sec
But in sql server 2000 (sp3 instaled) Japanese version, the convert(datetime, ,111) gives me date in the format yyyy-mm-dd.
I need the hh:min:sec to do an exact date comparison.
The problem is solved if I use Convert(datetime,,120).
Any pointers on the root cause of why the problem occurs with convert() function once sp3 is installed?
View 1 Replies
View Related
Feb 19, 2008
Is it possible to define your own function? If so could you give me an example.
Keep in mind that I said in SQL Server 2000. I want no CLR SQL Server 2005 solutions.
Cheers,
David
View 1 Replies
View Related
May 4, 2007
Hi everyone,
Well, it's a bit difficult to explain this issue. I have written an e-commerce asp.net web site the web site will be hosted on a regular web host that i bought from a hosting company. and we have 2 servers in our company which one of them has internal ip address and the other one has fixed ip address. I have to install my sql 2000 db on the first server(the one with internal ip), I wanted to know that how can I link the the data base to the website via the second server(the one with fixed ip address). I was thinking about using link servers in sql server....
Could any one told me that if it is a good idea or not????
View 1 Replies
View Related
Jun 10, 2002
Hello Team,
Could you tell whate are best way or infor regarding accessing SQL Server 2000 and DB2-AS/400 database?.
Thanks,
Ravi
View 3 Replies
View Related
Jul 13, 2001
Hello folks,
To begin with, I aplogize for not posting a pure SQL Server question in this forum. I thought some of you might be able to help me out.
I have an Access application (both front and back ends built in Microsoft Access). I don't have the source code which has access forms and the VB code. All I have is an .mde (Access executable) and .mdb (Access database) that carries the actual data. All the forms in .mde are linked to data in this .mdb
We are planning to convert the whole thing into ASP & SQL Server, but before it gets ready, we want to change the back end to SQL Server leaving the front-end App in Access. My plan is to make all the tables in my MDB to linked tables that point to SQL Server. But since my .mde already has linked tables pointing to the access tables in MDB, it is not allowing me to have a linked table pointing to another linked table.
Can anyone let me know if there is any workaround to achieve this? Your help will be very much appreciated.
Thanks,
RV
View 2 Replies
View Related
Aug 30, 2001
I am linking to an SQL table through an access database.
I need to add records to the SQL table through the Acess .mdb but
the linked table will not let me.
How can I fix this?
Any help is appreciated.
Kelley
View 1 Replies
View Related
Apr 2, 2006
hi,
somebody asked me this question but I haven't succeeded to give a good answer...
The question was...
Access 2000 is a DBMS and Sql server is an RDBMS,so what makes sql server a RDBMS in context of Codd's rule?What are the properties that Access doesn't have to be a RDBMS?
May be this is not so important issue,if anybody wants to answer plz help,gurus are requested not to waste their valuable time....If any of the gurus come I would be delighted....:D
View 3 Replies
View Related
Jun 12, 2007
Hi,
I am part of windows domain administrator group and I want to access SQL Server 2000 database running on Windows 2000 server, how can I do that ?. I need to reverse a transaction in SQL Server DB.
Thank you
View 2 Replies
View Related
Feb 6, 2008
I am trying to convert some MS Access queries to SQL Server 2000 and normally i can get past any problems but so far i haven't found a solution to this one:
Code Snippet
SELECT FIRST([Column1]), [Column2], FIRST([Column3]), FIRST[Column4]
FROM QryIMadeAlready
GROUP BY [Column2]
the results are not number values so i can not use max/min as far as i know. an ex: of the results would be.:
Code Snippet
First Qry Results (QryIMadeAlready)
C1Value1 ** C2Value1 ** C3Value1 ** C4Value1
C1Value1 ** C2Value2 ** C3Value2 ** C4Value2
C1Value1 ** C2Value2 ** C3Value3 ** C4Value3
C1Value1 ** C2Value3 ** C3Value4 ** C4Value4
The Select First Qry Results need to be:
C1Value1 ** C2Value1 ** C3Value1 ** C4Value1
C1Value1 ** C2Value2 ** C3Value2 ** C4Value2
C1Value1 ** C2Value3 ** C3Value4 ** C4Value4
How do i get this to work in SQL Server 2000? t-sql?
WoFe
View 4 Replies
View Related
Feb 8, 2007
Hello,
In Microsoft Access, there is a feature to create relationships between tables. When creating these relations, you have the option to:
1. Establish the relation type. For example, 1 to many (primary key to foreign key)
2. Enforce referential integrity
3. Allow cascading updates
4. Allow cascading deletions
5. View these relations in a nice diagram
Does SQL Server 2000 contain this functionality? If so, where is this found in the Enterprise Manager? If not, what is the alternative to this very useful feature found in Access?
Thanks You For Any Help!
View 3 Replies
View Related
