Hi,
I'm creating a software, and I have a dilemn... To check it I use MySQL, but I want my software to use a lot of kind of différent DB (oracle, DB2, informix, etc...)
But I have to know :
Does MSSQL support something like that (and if not how to do it) :
SELECT fields FROM Table LIMIT 0,500
????
thank you very much for a quick answer, because All development depend of the answer !!!
thank you,
bye.
What is the SQL Server equivalent of the LIMIT keyword available in MySQL?
SELECT * FROM MyTable WHERE ID = @ID LIMIT 1
this SQL does not work? I get an error saying that there is an error at keyword LIMIT. When I remove LIMIT 1, it works...
is there anything like this in SQL Server?
Do MS SQL Express support Asian character set?
If I want to input Traditional Chinese character, what collation I should use?
Is there any limitation on the no of connect to MS SQL Express?
I have no problem getting OLE DB Command transformations to support single returns by a procedure.
For example, exec name_of_procedure ?,?,? OUTPUT
However, I have a stored procedure which accepts 1 input and returns 5 outputs. This procedure works fine at the command line but when I try to incorporate it into a OLE DB Command I don't get the multiple values returned. There's no problem at all configuring the transform as it recognizes all input and output parameters. For some reason I just don't get values returned.
thanks
John
There is at present (as we accidently found) limitation of MSSQL to return per row maximally 8060 bytes. Message like this comes: "Cannot create a row of size 8279 which is greater than the allowable maximum of 8060".
My questions are :
- Is there any way how to pass it? If I split into more tables (as I have it now) and ask for result where these tables are connected over any ID the result is the same. If I use stored procedures it seems to be ok. Any other idea?
- Will be this ok in SQL server 2005?
Hi everyone,
I am looking for a function in MSSQL that performs similar to the LIMIT function in MySQL. Here is the LIMIT function's definition:
The LIMIT clause can be used to constrain the number of rows returned by the SELECT statement. LIMIT takes one or two numeric arguments, which must be integer constants.
With two arguments, the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return. The offset of the initial row is 0 (not 1):
mysql> SELECT * FROM table LIMIT 5,10; # Retrieve rows 6-15
I am aware of TOP and SET ROWCOUNT functions in MSSQL but neither provide the Offset functionality.
Any help would be appreciated,
Piet
Reader Community
I've just started hosting my newly created Microsoft Visual Web Developer 2005 Express Edition web site. Unfortunately the Login group membership functions will not function correctly. Having contacted the web service hosting provider, They replied: "We do not support SQL express2005. The only way to use the extra functions of ASP.NET2 such as group membership is if it is using an SQL 2000 database to connect to. "
Is it possible to design web sites with Microsoft Visual Web Developer 2005 Express Edition that store membership details on an SQL 2000 database?
I've just paid £88 approx. $140 for a years subscription, have I chosen the wrong web service hosting provider?
Should I have designed the web site with a better web site design software tool that also makes designing membership login functionality easy, just as Microsoft Visual Web developer 2005 express edition?
Look forward to all comments?
Regards
Philip
How many result-rows does mssql return should be used asynchronous method to use mssql cursor, can get the best performance in any time in any result offset?
i want to make the cursor fast in any time whatever how many results returned
Hi,
i was planning to create a database migration tool ..
its a certain database of a DMS (document management system) to
another DMS (two different DMS)... from DMS using msde 2000 server .. and tranfer to a DMS using a postgre sql or mssql .. depends ..
they have different table structures and names . . :D
i was thing of what language shall i use.. or what language is the best to work on this kind of project :)
hoping for your kind help guys. thanks :)
br
Frozenice
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies View RelatedHi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Thanks,
Sankar R
Hello:
I am currently work on mssql 6.5. On my workstation, I have mssql 6.5
cient software.
However, I would like to install mssql 7.0 server on my nt workstation
and work with it to become familiar with 7.0. Can I install mssql 7.0
server on my nt workstation? Can mssql 6.5 client coexist with mssql 7.0
on the same machine if they are in different directories?
Thanks.
David Spaisman
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
Thanks,
Ben"
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.
DB is developed on local computer with MSSQL 2005 Express. My host is on MSSQL 2005 workgroup. Are they compatible, because I am getting errors? Is my approach wrong?
I have tried several approaches.
A) I created a backup of database on my local, then placed a copy on the server. Then I tried to restore through Server Management Studio. I get this error.
TITLE: Microsoft SQL Server Management Studio
------------------------------
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
ADDITIONAL INFORMATION:
The backed-up database has on-disk structure version 611. The server supports version 539 and cannot restore or upgrade this database.
RESTORE FILELIST is terminating abnormally. (Microsoft SQL Server, Error: 3169)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=08.00.2039&EvtSrc=MSSQLServer&EvtID=3169&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
B: I also have tried copying the database. I put it in the same path as the other databases that can be read with server management studio on the server. Then, tried to get to it through server managements studio and it did not appear. So I tried to attach it. Then I received this error:
TITLE: Microsoft SQL Server Management Studio
------------------------------
Attach database failed for Server 'MROACH1'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.2047.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Attach+database+Server&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
Could not find row in sysindexes for database ID 10, object ID 1, index ID 1. Run DBCC CHECKTABLE on sysindexes.
Could not open new database 'LodgingDB'. CREATE DATABASE is aborted. (Microsoft SQL Server, Error: 602)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=602&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
C: I have also tried opening the Database, and back up file through Server Management Studio. without success.
D: I also tried Windows and Software update at microsoft update, but no updates were recommended for Version on Server.
I'm surprised this is so hard. My original data base was created in same family of software. 2005 MS SQL Express. I could use some direct help from someone experienced with this. Am I doing it wrong or are the DB versions incompatible.
Mark Roach
How to convert a database in MSSQL 2000 to MSSQL 2005 database.Is there any tool or documentation available for this?
View 3 Replies View RelatedI have my MSSQL hosted, any interface i can use so i can't connect to the data source?
for Mysql, i know i can use phpmyadmin, or mysql interface.
i have no idea for MSSQL, any advices?
i know there is one, which is Toad, but it has a lots of limitations, any good idea?
thanks.
Hi,
till now i have used mysql with php.now i want to use mssql with php.my php and apache versions are
php 5.2.3
apache 2.2.4
from where i can get mssql and what i need to run my php scripts with mssql database.iam newbie in mssql.any of your help will be surely appreciated
thanks
mrjameer
Hi!
What is the best way to migrate MSSQL 2005 Express -> MSSQL 2005 Srv?
Hi there guys!
I was wondering if anyone know the number of rows you can import from an external source into MS SQL Server 2000 Developer Edition?
I have tried importing a whole table from another database with over a million records. But failed after 1.7 million row.
Any ideas?
Thank you!
When creating a database, SQL Server 6.5 seems to have a 2 gig limit. What I mean is that if the device chosen is over 2 gigs SQL Server displays the size of the device as anegative number. This prevents me from being able to expand the database when I need to. Can anyone tell me why this is so, and if there`s anyway around it??
View 1 Replies View Relatedwe all know
mysql: select * from table limit ?1, ?2
equals
sqlserver: SELECT TOP ?2 *
FROM table WHERE (IDENTITYCOL NOT IN
(SELECT TOP ?1 IDENTITYCOL
FROM table order by IDENTITYCOL))
order by IDENTITYCOL
but the below SQL in mysql,how to convert?I enmesh...........
select pageid,pagename,pageaddr,pageauditflag,pageartaudi tflag,startplaytime
from pageinfo where entryid= ?1 and startplaytime= ?2
limit ?3, ?4
thanks!
hi,I have a question.Maybe You know the equivalent to command LIMIT from MySQLI couldn`t find something like this in MS SQLPSI try to display 10 records begining form e.g. 4 sort by idsomething like: "SELECT * FROM table WHERE name=... LIMIT 4, 10 ORDER BY id"in MySQLthanx,Urban
View 2 Replies View RelatedHi There
I recently read up and found an msdn forum post that said the 4gb limit only applied to data files not log files.
However recently i had an error on a sql express database saying that the log file was full, the database log file was set to auto grow and there was plenty of space left.
So i am guessing the 4gb limit applies to any database file mdf or ldf, is this correct ?
Thanx
MS states that CE does not work with ASP.Net applications 'by default'. What is needed to change this default?
Bascially, my desktop app. is configured from a browser app. (ASP.Net 2.0) and I want to use CE as the datastore for the application.
Thanks.
My documentation tells me that SQL Server Express only supports 1 gigabyte of RAM.
My computer can take 2 gigs, but I presently only have half a gig.
If I install 2 gigs on my machine, will that cause SQL Server Express to malfunction?
Or does it just mean that the extra gig will not be utilized?
Any advice would be much appreciated.
Dear friends
I am a new developer. I need some SQL Language examples to write some quaries.
Ex.
how you collect last record that relevent for the last record updated for a relevent employee in a record table which includes RecordNo and EmployeeNo, salses
Thanks
Any help is greatly apprciated
Amila
I have the fields in my table:
ID, Title, Description, Price, ImageData, Active
I only want it to be possible to have two records at any time with Active=Yes. Active is a Bit, Yes/No, field.
Any help is appreciated.
Chuck
hi,i have a union statement that works like a charm:SELECT
[Name],
[EventID] AS [ItemID],
[TourID],
[Date],
NULL AS [StartDate],
[Date] AS [SortDate],
[Type] FROM [Events]
WHERE
[TourID] IS NULL
AND
AccessLevel <= @AuthenticationLevel
UNION
SELECT
[Name],
[TourID] AS [ItemID],
[TourID],
NULL AS [Date],
[StartDate],
[StartDate] AS [SortDate],
'2' AS [Type]
FROM
[Tours]
WHERE
AccessLevel <= @AuthenticationLevel
ORDER BY
[SortDate] this statement selects all the records i want from both tables, and orders the entire new table by sort date.what i want to do is to add a limitation on the number of records. i have tried using both LIMIT and TOP commands, but i can't get it working. both of the below statements give me a syntax error saying "Incorrect syntax near the keyword 'ORDER'"first try:SELECT * FROM (<same_statement_as_above>) ORDER BY [SortDate] LIMIT 2second try:SELECT TOP 2 * FROM (<same_statement_as_above>) ORDER BY [SortDate]what am i doing wrong, and how do i achieve what i want?i am working with mssql server 2005 (express locally, and standard on my hosting service). thanks for your help!
Hi,
I have an application with a DAL that has an interface with SQL Server.
The application has 400 users that open the web forms.
My question is:
Is there a limit of the parallel connections that can be opened? Or the IIS is managing all the access to the DB? Should I worry about the performance Or it's normal behaviour for ASP.NET applications?
Thanks
hello everybody
I created user "MyUser " with rights
1. public for database "Orders"
2. select, update,delete, insert to table Orders.dbo.PersonalInfo
I have table Orders.dbo.PersonalInfoChangeLog
( it keeps information on any update on Orders.dbo.PersonalInfo including
HOST_NAME())
So I don't want anybody to see even structure of this table(Orders.dbo.PersonalInfo ).
if person loged as "Myuser" he can use
1. sp_help PersonalInfoChangeLog
2.Enterpise Manager to see properties of the table
How can limit rights to see structure without generating Application role ?
Thanks
SQL takes all CPU resource on some of the intensive
queries. Is any way to make sure there is something left for other tasks
to be processed? Let's say limit SQL to use no more than 80% of CPU.
Thanks