Copy DB to a different Machine

Hi,

I have developed an application using SQl Server 2000.

I want to transfer the database to my new server. I want all the tables and datas to be transferred.

I am new to this application. So i am not able to get mysel started. Can someone help me in this problem.

My database is large. It has stored procedures, database diagrams.

Any help in this regard would be highly appreciated.

Thanks,
-sriramFin MS Sql Server on your Programs menu. Choose 'Import/Export' data > next. The following Dialogue is the From dialogue. Choose the Databse you want to copy and press next. In the 'To' dialogue, enter the IP/URL to the remote SQL Server. In the Dropdown list, choose '<New>' and name your Database - press OK. I nteh next dialogue, choose 'Copy objects and data between SQL Server databases'. Click Next 3 times and then End. Now your Database and data will be copied to your new Server.|||I prefer doing a full backup and then restoring the backup on the new machine. I find it quicker that way. This way your DB Diagrams will survive too.|||i agree with the full backup technique.

the only time i "object copy" between db's is to keep my local DB in sync with a shared Development DB and 2 other developers.|||Hi,

Thanks for replying.

How can i do a full backup. I am not able to backup and transfer to the new machine.

So can you help me??
thanks,
-sriram|||if you go to C:\Program Files\Microsoft SQL Server\MSSQL\Data
find your db files ...( 2- the db file and the log file)

copy them over to the new machine in the same data folder...and "attach" the db...

** remember to "Stop" the sql server before you do this...

hth|||I'm working on a project with 3 other people whom are using MSDE for the DB. Is there a function similar to 'attach db' that can be used with MSDE. Our computers are not networked, so we will only be able to pass the database file around.

Thanks in advance!

Copy data from SQL Server to SQL Mobile

Hello

I'm developing an application that need to create everyday information to be stored in a SQL Mobile Database.

The SQL Mobile database will have:

Last schema from tables (in SQL Server database);

Last information in the tables;

I don't want to copy all the tables from SQL Server database than this mecanism should give me the way to choose the tables.

I have been reading about the SqlCeReplication but I don't undestand what is InternetUrl property. The SQL Mobile database is created in the PC and after is copy to the the Mobile Device.
How can I avoid the InternetURL property?

tkx in advance
Paulo Aboim Pinto
Odivelas - Portugal

Olá Paulo,

The Merge Replication process is based on an agent running on a DLL on IIS, so that's why you need the InternetURL property.

You may also consider the tools from www.primeworks-mobile.com which avoid IIS and may be used over TCP/IP or ActiveSync (RAPI). BTW, these tools are developed very close from where you live...

If you prefer to follow this in Portuguese, try the microsoft.public.pt.pocketpc newsgroup

Alberto Silva
http://msmvps.com/AlbertoSilva

Copy a table minimizing log space

Yes, I know this is a silly situation.
I have an application that uploads several tables to a remote SQL Server
that is part of a web site. The records are uploaded into a dedicated
upload table, then the table they belong in is truncated and the records
copied using
INSERT INTO ... (SELECT ...)
My problem is that the web hosting company is very stingy with log space
and this approach will place the entire table into the log during the
copy. This is failing for the larger tables.
Is there are way for a remote app to copy data from one table to
another, minimizing the use of log space. I have no access to the file
system, so bulk copy is not an option.
Thanks,
++PLSHi
How big are your tables?
What is RECOVERY mode of the database?
Can you divide you inserting into a small transaction ?
INSERT INTO Table SELECT * FROM OtherTable WHERE --put here some logic
OR
SET ROWCOUNT 1000
INSERT INTO Table SELECT * FROM OtherTable
SET ROWCOUNT 0
"pls" <elth-rs1p@.spamex.com> wrote in message
news:uqJF8i8JGHA.2304@.TK2MSFTNGP15.phx.gbl...
> Yes, I know this is a silly situation.
> I have an application that uploads several tables to a remote SQL Server
> that is part of a web site. The records are uploaded into a dedicated
> upload table, then the table they belong in is truncated and the records
> copied using
> INSERT INTO ... (SELECT ...)
> My problem is that the web hosting company is very stingy with log space
> and this approach will place the entire table into the log during the
> copy. This is failing for the larger tables.
> Is there are way for a remote app to copy data from one table to another,
> minimizing the use of log space. I have no access to the file system, so
> bulk copy is not an option.
> Thanks,
> ++PLS|||RECOVERY mode is simple.
I can't think of any logic that could be in a WHERE clause to divide the
table anything like evenly. I like your second approach (although I
think you meant ...SELECT TOP 1000). But then how do you get to the
second 1000?
++PLS
Uri Dimant wrote:
> Hi
> How big are your tables?
> What is RECOVERY mode of the database?
> Can you divide you inserting into a small transaction ?
> INSERT INTO Table SELECT * FROM OtherTable WHERE --put here some logic
> OR
> SET ROWCOUNT 1000
> INSERT INTO Table SELECT * FROM OtherTable
> SET ROWCOUNT 0
> "pls" <elth-rs1p@.spamex.com> wrote in message
> news:uqJF8i8JGHA.2304@.TK2MSFTNGP15.phx.gbl...
>
>
>|||pls
> table anything like evenly. I like your second approach (although I think
> you meant ...SELECT TOP 1000). But then how do you get to the second 1000?
No, it is exactly SET ROWCOUNT (See in the BOL)
CREATE TABLE #Test
(
col INT NOT NULL PRIMARY KEY
)
--Insert 10 rows
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
INSERT INTO #Test VALUES (3)
INSERT INTO #Test VALUES (4)
INSERT INTO #Test VALUES (5)
INSERT INTO #Test VALUES (6)
INSERT INTO #Test VALUES (7)
INSERT INTO #Test VALUES (8)
INSERT INTO #Test VALUES (9)
INSERT INTO #Test VALUES (10)
SELECT * FROM #Test
--Deletion here
SET ROWCOUNT 5 --only 5 rows at time
DELETE FROM #Test
SET ROWCOUNT 0
--(5 row(s) affected)
SELECT * FROM #Test --The first five rows were deleted
--Deletion here
SET ROWCOUNT 5 --only 5 rows at time
DELETE FROM #Test
SET ROWCOUNT 0
--(5 row(s) affected)
SELECT * FROM #Test --The table is empty now
"pls" <elth-rs1p@.spamex.com> wrote in message
news:eTetGT9JGHA.2336@.TK2MSFTNGP12.phx.gbl...
> RECOVERY mode is simple.
> I can't think of any logic that could be in a WHERE clause to divide the
> table anything like evenly. I like your second approach (although I think
> you meant ...SELECT TOP 1000). But then how do you get to the second 1000?
> ++PLS
> Uri Dimant wrote:

Coping Stored Procedures

I have and ASP.NET and VB.NET application that needs to copy selected stored procedures from one SQL Server to another SQL Server. The scenerio is that the user will select a source server from a drop down list then the source database from another dropdown listbox which then populates a listbox with the stored procedures in the database. They can select multiple stored procedures from the listbox.

Once selected, they select a target SQL Server from a dropdownlist and then a target database from another dropwdown list. I have a command button that they need to do the actual move but I am not sure how to get the selected source stored procedures copied to the target database. Any ideas would be greatly appreciated.Do you want copy store procedures at run time?
you may try sp_helptext 'sp-Name' . it ruturns the source code.
Then connect to second server and run the code generated from sp_helptext.|||Loads of ways DTS, sp_helptext to name but two methods.|||How can I use DTS to do this?|||Remember stored procs are nothing more than text in a database table. Literally.

cookieParameter and SqlDataSource

Hi..

I-m using cookies in my application web. Now I want show a gridview using the cookies as parameters for a sqldatasource.

Ex: myCoookie("name",txt_name.Text)

How I should be to do for the sqldatasource in the option DefineParameters with the wizard?

Hi dcampo,

Are you trying to define a CookieParameter as a filter to display data?

If so, you can add a CookieParameter directly in SelectParameters of a SqlDataSource.

Here is an example:

<asp:CookieParameter CookieName="name" Name="Name" Type="String" />

HTH. If this does not answer your question, please feel free to mark the post as Not Answered and we will look into it again. Thanks!

Converting T-SQL *= OUTER JOINS to ANSI-92 syntax

Hello all. I have an application that has worked smoothly using the following query syntax:
FROM tbl_participation, tbl_adult, tbl_month, tbl_school_year
WHERE tbl_participation.adult_ID =* tbl_adult.ID

AND tbl_participation.month_ID =* tbl_month.ID
AND tbl_participation.year_id =* tbl_school_year.ID

AND tbl_adult.ID = 8
AND tbl_school_year.ID = 5
It works just fine, as I want the results to include a table with one column containing the month name, the table headed by the adult's name/school year. It needs to still return a table even if there have yet been no records in tbl_participation for that adult/month/year.
However, the following is my best shot at making the query ANSI-92 compliant, as when I implement SQL Server 2005 I don't want to have to go back and change compatibility modes:
FROM ((tbl_adult

LEFT OUTER JOIN tbl_participation ON tbl_participation.adult_ID = tbl_adult.ID)

RIGHT OUTER JOIN tbl_month ON tbl_participation.month_ID = tbl_month.ID)

RIGHT OUTER JOIN tbl_school_year ON tbl_participation.year_id = tbl_school_year.ID

WHERE tbl_adult.ID = 8
AND tbl_school_year.ID = 5
This query works fine if there is any data in tbl_participation for adult 8 and school year 5. But if nothing has yet been entered, it returns nothing. I need it to work like the older T-SQL iteration, and still return a list of the 12 months and the adult's name even if no participation data has been entered.
Thanks - this one has got me pulling my hair out.

With the JOIN clause, you should logically think of the ON clause as being evaluated first followed by WHERE clause, GROUP BY and HAVING clause. Note that with inner joins the optimizer can evaluate predicates in the WHERE and ON clause together. But when you outer joins the WHERE clause is always evaluated after the ON clause. So this will essentially prevent non-matching rows from being produced based on your example. Additionally, it also depends on whether you filter the rows before the outer join. So in your example, you need to move the tbl_adult.ID check to FROM clause using a derived table like:

FROM (( (select * from tbl_adult where ID = 8) as a
LEFT OUTER JOIN tbl_participation as p ON p.adult_ID = a.ID)
RIGHT OUTER JOIN tbl_month as m ON p.month_ID = m.ID)
RIGHT OUTER JOIN tbl_school_year as y ON p.year_id = y.ID
WHERE y.ID = 5

Converting to SQL Server 2000 from Access

We have an application in Access 2003. The user interface is OK (Forms
created with Access) but we're lacking a "real" database i.e SQL Server 2000
.
Any hints on how to move the database without having to rewrite the user
interface? We could rewrite the Forms in C# but time is not on our side!
--
FolkeThere are no such things as "forms" in SQL Server; this is all handled by
external applications.
http://www.aspfaq.com/
(Reverse address to reply.)
"folke" <folke@.discussions.microsoft.com> wrote in message
news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> We have an application in Access 2003. The user interface is OK (Forms
> created with Access) but we're lacking a "real" database i.e SQL Server
2000.
> Any hints on how to move the database without having to rewrite the user
> interface? We could rewrite the Forms in C# but time is not on our side!
> --
> Folke|||That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>|||You can export the tables to SQL Server, using DTS. You'll have to apply
integrity constraints on your own. Then, alter the Access database to use
linked tables. Ideally, you should make Access use stored procs, instead of
going against the SQL Server tables directly.
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"folke" <folke@.discussions.microsoft.com> wrote in message
news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>|||Thanks!
That was what I'm looking for. Step 1 will be to export the tables to SQL
Server and let Access use linked tables!
"Tom Moreau" wrote:

> You can export the tables to SQL Server, using DTS. You'll have to apply
> integrity constraints on your own. Then, alter the Access database to use
> linked tables. Ideally, you should make Access use stored procs, instead
of
> going against the SQL Server tables directly.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
> That's why I'm asking! Can you move the tables to SQL Server and use them
> from Access via some external link? We don't want to rewrite the applicati
on
> just move the tables.
> "Aaron [SQL Server MVP]" wrote:
>
>

Converting to SQL Server 2000 from Access

We have an application in Access 2003. The user interface is OK (Forms
created with Access) but we're lacking a "real" database i.e SQL Server 2000.
Any hints on how to move the database without having to rewrite the user
interface? We could rewrite the Forms in C# but time is not on our side!
Folke
There are no such things as "forms" in SQL Server; this is all handled by
external applications.
http://www.aspfaq.com/
(Reverse address to reply.)
"folke" <folke@.discussions.microsoft.com> wrote in message
news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> We have an application in Access 2003. The user interface is OK (Forms
> created with Access) but we're lacking a "real" database i.e SQL Server
2000.
> Any hints on how to move the database without having to rewrite the user
> interface? We could rewrite the Forms in C# but time is not on our side!
> --
> Folke
|||That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>
|||You can export the tables to SQL Server, using DTS. You'll have to apply
integrity constraints on your own. Then, alter the Access database to use
linked tables. Ideally, you should make Access use stored procs, instead of
going against the SQL Server tables directly.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"folke" <folke@.discussions.microsoft.com> wrote in message
news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:

> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> 2000.
>
>
|||Thanks!
That was what I'm looking for. Step 1 will be to export the tables to SQL
Server and let Access use linked tables!
"Tom Moreau" wrote:

> You can export the tables to SQL Server, using DTS. You'll have to apply
> integrity constraints on your own. Then, alter the Access database to use
> linked tables. Ideally, you should make Access use stored procs, instead of
> going against the SQL Server tables directly.
> --
> Tom
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
> That's why I'm asking! Can you move the tables to SQL Server and use them
> from Access via some external link? We don't want to rewrite the application
> just move the tables.
> "Aaron [SQL Server MVP]" wrote:
>
>

Converting to SQL Server 2000 from Access

We have an application in Access 2003. The user interface is OK (Forms
created with Access) but we're lacking a "real" database i.e SQL Server 2000.
Any hints on how to move the database without having to rewrite the user
interface? We could rewrite the Forms in C# but time is not on our side!
--
FolkeThere are no such things as "forms" in SQL Server; this is all handled by
external applications.
--
http://www.aspfaq.com/
(Reverse address to reply.)
"folke" <folke@.discussions.microsoft.com> wrote in message
news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> We have an application in Access 2003. The user interface is OK (Forms
> created with Access) but we're lacking a "real" database i.e SQL Server
2000.
> Any hints on how to move the database without having to rewrite the user
> interface? We could rewrite the Forms in C# but time is not on our side!
> --
> Folke|||That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:
> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> > We have an application in Access 2003. The user interface is OK (Forms
> > created with Access) but we're lacking a "real" database i.e SQL Server
> 2000.
> > Any hints on how to move the database without having to rewrite the user
> > interface? We could rewrite the Forms in C# but time is not on our side!
> > --
> > Folke
>
>|||You can export the tables to SQL Server, using DTS. You'll have to apply
integrity constraints on your own. Then, alter the Access database to use
linked tables. Ideally, you should make Access use stored procs, instead of
going against the SQL Server tables directly.
--
Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
"folke" <folke@.discussions.microsoft.com> wrote in message
news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
That's why I'm asking! Can you move the tables to SQL Server and use them
from Access via some external link? We don't want to rewrite the application
just move the tables.
"Aaron [SQL Server MVP]" wrote:
> There are no such things as "forms" in SQL Server; this is all handled by
> external applications.
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> > We have an application in Access 2003. The user interface is OK (Forms
> > created with Access) but we're lacking a "real" database i.e SQL Server
> 2000.
> > Any hints on how to move the database without having to rewrite the user
> > interface? We could rewrite the Forms in C# but time is not on our side!
> > --
> > Folke
>
>|||Thanks!
That was what I'm looking for. Step 1 will be to export the tables to SQL
Server and let Access use linked tables!
"Tom Moreau" wrote:
> You can export the tables to SQL Server, using DTS. You'll have to apply
> integrity constraints on your own. Then, alter the Access database to use
> linked tables. Ideally, you should make Access use stored procs, instead of
> going against the SQL Server tables directly.
> --
> Tom
> ---
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
>
> "folke" <folke@.discussions.microsoft.com> wrote in message
> news:E84376CB-49B9-45AD-9FFF-965D0653B046@.microsoft.com...
> That's why I'm asking! Can you move the tables to SQL Server and use them
> from Access via some external link? We don't want to rewrite the application
> just move the tables.
> "Aaron [SQL Server MVP]" wrote:
> > There are no such things as "forms" in SQL Server; this is all handled by
> > external applications.
> >
> > --
> > http://www.aspfaq.com/
> > (Reverse address to reply.)
> >
> >
> >
> >
> > "folke" <folke@.discussions.microsoft.com> wrote in message
> > news:1E83D3DE-9E75-456A-8D4A-A13003FC480B@.microsoft.com...
> > > We have an application in Access 2003. The user interface is OK (Forms
> > > created with Access) but we're lacking a "real" database i.e SQL Server
> > 2000.
> > > Any hints on how to move the database without having to rewrite the user
> > > interface? We could rewrite the Forms in C# but time is not on our side!
> > > --
> > > Folke
> >
> >
> >
>

Converting text data to ntext data

Hi all,

My organization have a web-based application and needs it to support multilingual so we will be adapting our app to use unicode. However, one of our problems is to convert existing data from text to ntext. I couldn't find anything that document this. What is the best way to do that? I would like to be able to migrate the data from an existing text column to another ntext column in the table.

I brief you about my system, I used List manager system to store the messages and distribute to all members. Right now,by design the Lyris system keep the message in the text field which mean it 's not support multilanguage directly because of unicode field. We needs to create new Db which has the data structure as same as Lyris but just one difference is keep the message in unicode format (ntext) which we need the sql script to automatically update the new record get from Lyris to new DB.

If I can't do that, what are the options? If it's possible, I would like to be able to do this in sql script.

Thanks a mil, in advance.

Eddie

Moving to engine folder.|||

You should be able to accomplish this by using a command similar to the following:

ALTER TABLE table_name ALTER COLUMN column_name ntext

go

After that you should run DBCC CLEANTABLE command to reclaim space occupied by old column. Also you space requirement for the altered column will double (just plan for that).

Alternatively, you can bulk copy your data out, modify the table definition, truncate the table and bulk copy data back in.

HTH,

Boris.

Converting string to unicode string in T-SQL

Hi,
We have stored proc name proc_test(str nvarchar(30)). So far this proc
has been invoked from a .NET application assuming that only English
character strings will be passed to it. The calls are like
proc_test('XYZ')
We now have a requirement for passing Chinese strings as well. Rather
than changing the calls throughout the application, we would like to
handle it in the stored procedure so that it treats the string as a
unicode string. Can we apply some function to the parameter to convert
it to unicode so that we don't have to call with an N prefixed to the
string?
Thanks,
YashHi
> unicode string. Can we apply some function to the parameter to convert
> it to unicode so that we don't have to call with an N prefixed to the
> string?
Do you mean to get an INTEGER of the string , then you have UNICODE function
, see in the BOL.
What's wrong with calling with an N prefixed to the string?
<yashgt@.gmail.com> wrote in message
news:1174896401.921135.170240@.b75g2000hsg.googlegroups.com...
> Hi,
> We have stored proc name proc_test(str nvarchar(30)). So far this proc
> has been invoked from a .NET application assuming that only English
> character strings will be passed to it. The calls are like
> proc_test('XYZ')
> We now have a requirement for passing Chinese strings as well. Rather
> than changing the calls throughout the application, we would like to
> handle it in the stored procedure so that it treats the string as a
> unicode string. Can we apply some function to the parameter to convert
> it to unicode so that we don't have to call with an N prefixed to the
> string?
> Thanks,
> Yash
>|||On Mar 26, 11:06 am, yas...@.gmail.com wrote:
> [...]
> We now have a requirement for passing Chinese strings as well. [...]
> Can we apply some function to the parameter to convert it to unicode
> so that we don't have to call with an N prefixed to the string?
No, you can't. If you don't prefix it with N, then the chinese
characters are lost in the implicit conversion to varchar, so you
cannot get them back (unless the varchar has a DBCS collation, which
would be if you have the default server collation on a Chinese_*
collation, but I don't think that would be a good idea).
Razvan|||<yashgt@.gmail.com> wrote in message
news:1174896401.921135.170240@.b75g2000hsg.googlegroups.com...
> Hi,
> We have stored proc name proc_test(str nvarchar(30)). So far this proc
> has been invoked from a .NET application assuming that only English
> character strings will be passed to it. The calls are like
> proc_test('XYZ')
You should go back and change your code to properly parameterize your
queries instead of concatenating the parameter values into strings,
VB5-style. Then you wouldn't have to worry about the N prefix. Or SQL
Injection.|||Are you building query strings in your .Net code by concatenating character
values? If so - why? Use parameters, or better yet use stored procedures.
ML
--
http://milambda.blogspot.com/

Converting string to datetime

Hi,

I'm in a bit of a tricky situation. I'm upgrading and existing application (VB.NET 05 and sql server 2000). One of the tables has field having datatype varchar(20) but actually storing dates. From different parts of the application the datetime values are saved basically in three formats.

1. 2004/11/26 00:00:00

2. Nov 25 2004 12:00AM
3. 24/11/2004

The problem is I need to run a datediff to calculate a date difference. I can't get my head around to convert all the three types to one data type using a sql.

Really appritiate if some one can help me out on this.

Regards,

Vije

If your data is exactly the three formats you provided, then this approach should work for you:

Code Snippet

DECLARE @.MyTable table
( RowID int IDENTITY,
MyDate nvarchar(30),
MyNewDate datetime
)

INSERT INTO @.MyTable ( MyDate ) VALUES ( '2004/11/26 00:00:00' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( 'Nov 25 2004 12:00AM' )
INSERT INTO @.MyTable ( MyDate ) VALUES ( '24/11/2004' )

UPDATE @.MyTable
SET MyNewDate = CASE isdate( MyDate )
WHEN 1 THEN cast( MyDate AS datetime )
ELSE convert( datetime, MyDate, 103 )
END
SELECT *
FROM @.MyTable

RowID MyDate MyNewDate
-- - -
1 2004/11/26 00:00:00 2004-11-26 00:00:00.000
2 Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
3 24/11/2004 2004-11-24 00:00:00.000

|||

Vije:

You might be able to get by with something like this:

Code Snippet

select theDt,
case when charindex(':', theDt) <> 0
and isDate(theDt) = 1
then convert(datetime, theDt)
when charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
then parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
end as convertedDT
from ( select '2004/11/26 00:00:00' as theDT union all
select 'Nov 25 2004 12:00AM' union all
select '24/11/2004' union all
select 'invalid'
) a
/*
where isDate(theDt) = 1
or ( charindex(':', theDt) = 0
and isDate(theDt) = 0
and isDate
( parsename(replace(theDt, '/', '.'), 1)
+ parsename(replace(theDt, '/', '.'), 2)
+ parsename(replace(theDt, '/', '.'), 3)
) = 1
)
*/

/*
theDt convertedDT
-
2004/11/26 00:00:00 2004-11-26 00:00:00.000
Nov 25 2004 12:00AM 2004-11-25 00:00:00.000
24/11/2004 2004-11-24 00:00:00.000
invalid NULL
*/

If you don't want the invalid data to appear just uncomment the WHERE clause (that is shown in red).

As an aside, you ought to consider converting this column to a DATETIME data type. These kinds of problems will only grow until you resolve the real problem here -- which is a design problem.

|||

In one single update statement you can't do it..

But the following batch may help you.

Code Snippet

Create Table #datedata (

[Dates] Varchar(20)

);

Insert Into #datedata Values('2004/11/26 00:00:00');

Insert Into #datedata Values('Nov 25 2004 12:00AM');

Insert Into #datedata Values('24/11/2004');

Set DateFormat DMY

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) , 120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Set DateFormat YMD

Update

#datedata

Set

Dates = Convert(varchar,cast(Dates as datetime) ,120) -- Finaly converted as ANSI Format

Where

Isdate(Dates) = 1

Select * From #dateData

|||

Woooh .. We all 3 given unique and different solution.... . I like Arnie's solution, Kent you are really hard worker..

Converting Stored Procedures back to MS Access Queries

I know its a weird request, but we have created an application with sql server but our client wants a version which can be put onto disk.

We decided to create the stored procedures into queries, would this be the best idea and if so does anyone know if there is a freeware software that can do this or will I have to painstakingly re-create the queries?

Any advice would be greatly appreciated.

Many thanks
SmillaYou can't convert any but the simplest sprocs into Access queries. Sprocs can be mult-step, while Access queries are simple statements like SQL Views. You would have to write VB code to simulate the sprocs, and there is no utility I know of that will do this for you.

Converting MS-Access to MS-SQL Server

Hello all,
I got a application which is developed using VB6.0 as front end and
MS-Access as backend. Now i would like to change the database from MS-Access
to MS-SQLserver.
How do I change the databse from MS-Access to MS-SQLServer?
Regards
Jack
You can use the Access upsizing wizard (not recommended because it
creates a lot of junk of the server that you have to clean up). You
can also use DTS (or SSIS in SQLS 2005), but then you have to create
keys, indexes, etc. The other option you should seriously consider is
that this is a golden opportunity for rewriting the database schema to
optimize it for SQL Server. SQLS is not just a bigger Jet--it's a
completely different RDBMS. Migrating the data is the least of the
issues involved.
--Mary
On Sat, 21 Jan 2006 17:45:02 -0800, "Jack"
<Jack@.discussions.microsoft.com> wrote:

>Hello all,
>I got a application which is developed using VB6.0 as front end and
>MS-Access as backend. Now i would like to change the database from MS-Access
>to MS-SQLserver.
>How do I change the databse from MS-Access to MS-SQLServer?
>Regards
>Jack