|
Does this work?
Set ROWCOUNT @aNumber
Farhan Noor Qureshi
|
|
|
|
|
Hi Farhan,
Thanks for your support.
I am using SQL SERVER EVERYWHERE 2005, which is a latest mobile and desktop database solution from Microsoft released very recently in this year. This is actually SQL Server Mobile upgraded version. And this SQL Server Everywhere doesnot support TOP and ROWCOUNT keyword. So, I thought there might be some tehcnique to perform this similar task.
Thanks and regards.
|
|
|
|
|
As with all computer technology there are usually many ways to perform one task.
At our shop we're new at the whole SQL database process. On our old apps with our mainframe we have test databases, we make changes and then copy the framework of the test database into production.
With SQL we have two servers. A development server and a production server. What is the best way to move a database from our development server with select tables and select stored procedures.
Does the backup / restore process lend itself to this kind of move or should we be researching into DTS?
I'm looking for suggestions from those of you who have worked with SQL databases for years and can guide newbies to the best solution you've found.
Any suggestions are welcome 
Lost in the vast sea of .NET
|
|
|
|
|
KreativeKai wrote: What is the best way to move a database from our development server with select tables and select stored procedures.
One way would be to have sql scripts that create new tables / stored procedures and update altered tables / stored procedures. Run these scripts on the production server after you've tested on the development server.
I am sure there are other ways, but this is the first to come to my mind
That's no moon, it's a space station. - Obi-wan Kenobi
|
|
|
|
|
Using the enterprise manager you could choose the copy database item from the context menu. This could be used to copy the database and create a script to be run for later updates.
only two letters away from being an asset
|
|
|
|
|
Cool!! 
That seems way to simple, but I tested it and it works great!!
Are there any drawbacks? Is it accurate creating the scripts or are there disadvantages to this method??
Thanks for your idea!!
Lost in the vast sea of .NET
|
|
|
|
|
KreativeKai wrote: Is it accurate creating the scripts or are there disadvantages to this method??
Use scripts unless you want to update any changes by hand.
That's no moon, it's a space station. - Obi-wan Kenobi
|
|
|
|
|
I would rather use scripts than doing the work by hand. I just don't always trust Microsoft, so it always helps to ask if there are any drawbacks to using the scripts that you copy. Maybe I should be more trusting, but with Microsoft sometimes it is hard. 
Lost in the vast sea of .NET
|
|
|
|
|
KreativeKai wrote: Maybe I should be more trusting, but with Microsoft sometimes it is hard.
I know that feeling.
KreativeKai wrote: I would rather use scripts than doing the work by hand.
I think we all would rather do the scripts When Enterprise Manager or SQL Server Management Studio Express generates the scripts, you can place comments into the script and this can serve as an audit point in the life cycle of the project. Always make a backup of the live data before running the scripts. I haven't had any serious trouble with running scripts whenever something is added/changed.
Paul
That's no moon, it's a space station. - Obi-wan Kenobi
|
|
|
|
|
Thanks for your help!! 
Lost in the vast sea of .NET
|
|
|
|
|
As long as you are just refreshing data from dev to prod this will work. As Paul says though if you change the schema you will either need to rerun the copy or modify the generated scripts.
only two letters away from being an asset
|
|
|
|
|
Thanks for your help!!
Lost in the vast sea of .NET
|
|
|
|
|
Hi all,
Currently I am working on importing data from SQL 2000 database to SQL
2005
database. The original SQL 2000 database contains Japanese as well as
English text. For importing data from SQL 2000 to SQL 2005 I have followed
following steps.
1. I have created SQL script for generating table structure in SQL 2005.
Which I executed and it created table structure in SQL 2005.
2. I have created SQL script for generating stored procedures in SQL 2005.
This was also successful.
3. Same way, I have created user defined functions in SQL 2005.
4. But the problem comes while importing data to SQL 2005.
For this I have done following things.
- Exported the data from SQL 2000 database to an excel sheet.and
- Import to SQL 2005 database which is causing problems. 
There is no problem with importing data which is in English to SQL 2005.
But as we have to import Japanese text alongwith English, it is causing
problems.
* * * Error is * * *
"The character-string data type of the Unicode
form and the character-string data types of the form other than Unicode
cannot be converted by the row "Description".
So, I guess the problem is related to the importing of combination of
UNICODE and Non-Unicode data to SQL 2005.
So, please let me know if we can use some other alternatives for importing
of Japanese text to SQL 2005.
Any suggestion/solution from you on this issue is greatly appreciated.
Thanks in advance.
Thanks & regards,
Vaibhav
|
|
|
|
|
Hmmm..I have no idea if this is at all relevant, but it almost sounds as if you are using a form to do the import? Shouldn't you just be doing something akin to BCP or bulk copy or directly in code without involving a UI part at all?
|
|
|
|
|
Hi John,
Thanks for your prompt reply. As per your suggestion I executed bcp command in the command prompt.
URL referred :
http://www.databasejournal.com/features/mssql/article.php/3391761
First I used following command to export data from source table in SQL 2000 database table to a .bcp file
[DatabaseName].dbo.[tablename] out c:\file1.bcp -n -S[servername] -U[username] -P[password]
Next I used following command to import data to the target table on SQL 2005
database
[DatabaseName].dbo.[tablename] out c:\file1.bcp -n -S[servername] -U[username] -P[password]
But it looks to me as if I need to write as many statements to export/import data for as many tables.
Anyway, thanks again for your valuable suggestions as it gave me right direction.
Thanks & regards,
Vaibhav
|
|
|
|
|
how to use the @@identity function in stored procedure pls
|
|
|
|
|
yazan_zahi wrote: how to use the @@identity function in stored procedure pls
Same way as you'd use it anywhere else.
You might, however, find that SCOPE_IDENTITY() is a better choice if you have a lot of other database interaction going on at the same time.
|
|
|
|
|
Hi,
what do you want to exactly with this, or better how do you want to use it?
I think for storing it into another variable you can something like:
<br />
SELECT @@identity into @myVariable<br />
or using it within a query
<br />
SELECT * FROM my_table where my_PK = @@identity<br />
Hope this helps,
regards
Sebastian
|
|
|
|
|
when i submit my settings from my application it gives an error.
GORSHPPNWS0100K:Unable to process a customer number.
-2147217887 [Microsoft][ODBC driver for Oracle][Oracle]ORA-01460: unimplemented or unreasonable conversion requested
Here "GORSHPPNWS0100K" is a production server through which application interact.
Pls suggest the cause of the problem.
Thanks
|
|
|
|
|
What were you trying to do at this point in time (relevant code snippets would be good also)
|
|
|
|
|
hi
actually there is an application which save user preferences to database.
since number of user is using the same application with their ligin id but only one user is facing the mentioned problem.
Preferences are like changing name,email,address etc.
|
|
|
|
|
I meant lower level than that. What were you doing (in code)?
|
|
|
|
|
Frndz,
How do I get time part from DateTime value in SQL 2005?
Regards,
Vipul Mehta
Regards,
Vipul Mehta
Sr. Software Engineer
NIIT Technologies Ltd
|
|
|
|
|
Here are a few ways:
select right(convert(varchar,DateTimeField,0),7) as JustTime from MyTestTable
or
select convert(varchar,DateTimeField,14) as JustTime from MyTestTable
or
select cast(DATEPART(hh, DateTimeField) as varchar) + ':'
+ cast(DATEPART(mi, DateTimeField) as varchar) as JustTime from MyTestTable
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
AS IS – Scenario
================
BIZTALK Rule Engine recognizes USERS only from an Active Directory. These users connect to the BizTalk SQL Server 2005 database through Windows Authentication.
Our windows application accesses the BIZTALK rule Engine using the user context of the logged-in user. In short, the Authentication for the application happens when a user logs in to the windows OS. To connect to the BIZTALK database a connection string is used, among its parameters is the INTEGRATED SECURITY=TRUE value, which simple means it uses Windows Authentication.
Also a LOGIN for the windows user needs to be created on the SQL SERVER 2005 Instance, Which then is associated with a User of the BIZTALK database.
PROBLEM
=======
The problem we face right now begins with the increase in the number of users accessing the application. For every user added to the Active directory, a corresponding login has to be added in the SQL Server 2005 lists of LOGINS. This has created a problem in terms of administration.
I’ve tried using a TRUSTED CONNECTION by creating and using SQL AUTHENTICATED users in the connection string, but this approach has failed.
I’ve tried group the users in the active directory and using this group as login in the SQL SERVER 2005 database, but that has failed too.
TO BE Scenario
==============
The Ideal solution for this problem would be to create a Trusted Sub-System, Which would mean creating only one Login at the SQL Server end and using it to connect it to the BIZTALK database. And all the users in the Active Directory would use the user context of this new Login to get connected to the BIZTALK database.
I haven’t been able to find a solution for this problem. Would really appreciate you help.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
