How to develop database locally and post to web host?

Hongju - can you be a little more specific on how to back up and restore the
databases. I have a very similar problem. I created a project that has a
database in APP_DATA. I'm hosting on GoDaddy and I need to take the APP_DAT
A
database (.mdf) and move it to one of their MySQL databases. I have no clue
on how to do that. Any help would be greatly appreciated.
Thanks
"hongju" wrote:
> You can backup local database and restore on the host database.
> If you developed database file using Visual Studio, you can attach databas
e
> on the host.
> And can modifiy connection string.
> "Noozer"?? ??? ??:
>Moving it to a MySQL database might take a little more work. Use the
"Generate scripts..." function in Management Studio and run those script
in the mysql database.
You will most likely need to modify them some to get them to work.
It's probably a good idea not to generate scripts for everything at
once, but to start with the tables first, then the views, then the SP's
etc etc...
Michelle wrote:
> Hongju - can you be a little more specific on how to back up and restore t
he
> databases. I have a very similar problem. I created a project that has a
> database in APP_DATA. I'm hosting on GoDaddy and I need to take the APP_D
ATA
> database (.mdf) and move it to one of their MySQL databases. I have no cl
ue
> on how to do that. Any help would be greatly appreciated.
> Thanks
> "hongju" wrote:
>

How to determine if SQl Server is installed

What would the best way be to programmatically determine if SQL Server is
installed?
Looking in a specific folder on the C: drive would not be useful, since it
might be installed in a non-standard location. And scanning all available
hard disks is not very desirable.
The best solution would be if there's a registry key that gives me the
location.
Thanks,
PeterWhat type of application do you plan to use to do this? VB? Etc. Your best
bet would be to look for the SQL Service.
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:CC3DE372-56C7-45B8-932B-C6485C54F759@.microsoft.com...
> What would the best way be to programmatically determine if SQL Server is
> installed?
> Looking in a specific folder on the C: drive would not be useful, since it
> might be installed in a non-standard location. And scanning all available
> hard disks is not very desirable.
> The best solution would be if there's a registry key that gives me the
> location.
> Thanks,
> Peter
>|||> The best solution would be if there's a registry key that gives me the
> location.
What location are you referring to?
The reg key for a default instance is:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\MS
SQLServer
And for named instances:
HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Mi
crosoft SQL Server
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"jpstewart" <jpstewart@.discussions.microsoft.com> wrote in message
news:CC3DE372-56C7-45B8-932B-C6485C54F759@.microsoft.com...
> What would the best way be to programmatically determine if SQL Server is
> installed?
> Looking in a specific folder on the C: drive would not be useful, since it
> might be installed in a non-standard location. And scanning all available
> hard disks is not very desirable.
> The best solution would be if there's a registry key that gives me the
> location.
> Thanks,
> Peter
>

How to determine if data imported into database table

We have a SQL 2000 database that we import a csv file into. The csv file
contains specific metrics from our accounting system from the the prior day.
The data gets dumped out to the csv file sometime at night, and a DTS packag
e
imports it into SQL at 7:30 am.
I need to verify that the data populated into SQL every morning. I've been
manually querying the table, E1_Data, on one of three date columns, ENDED,
with the following script,
SELECT ENDED
FROM E12SQL.E1_DATA
WHERE (MONTH(ENDED) = '08') AND (DAY(ENDED) = '29') AND (YEAR(ENDED) =
'2005')
If I get data back, I know yesterdays csv file imported into SQL.
Surely there is a better way to do this.
AlwaysLearningYou could create a stored proc and add it as a second step in your job to ru
n
the DTS package.
create proc as
declare @.myDate varchar(20)
set @.myDate = convert(varchar,dateadd(day,-1,getDate()),1) --yesterday's
date
if not exists(select Ended from E1_DATA where Ended > @.myDate) begin
raiserror('No imported records',16,1)
return 1
end
return 0
"AlwaysLearning" wrote:

> We have a SQL 2000 database that we import a csv file into. The csv file
> contains specific metrics from our accounting system from the the prior da
y.
> The data gets dumped out to the csv file sometime at night, and a DTS pack
age
> imports it into SQL at 7:30 am.
> I need to verify that the data populated into SQL every morning. I've bee
n
> manually querying the table, E1_Data, on one of three date columns, ENDED,
> with the following script,
> SELECT ENDED
> FROM E12SQL.E1_DATA
> WHERE (MONTH(ENDED) = '08') AND (DAY(ENDED) = '29') AND (YEAR(ENDED) =
> '2005')
> If I get data back, I know yesterdays csv file imported into SQL.
> Surely there is a better way to do this.
>
> --
> AlwaysLearning|||The "yesterday's date" was all supposed to be a comment, it didnt' wrap well
.
Also, you may need to have the where clause be >= instead of >.
"Kathi Kellenberger" wrote:
> You could create a stored proc and add it as a second step in your job to
run
> the DTS package.
> create proc as
> declare @.myDate varchar(20)
> set @.myDate = convert(varchar,dateadd(day,-1,getDate()),1) --yesterday
's
> date
> if not exists(select Ended from E1_DATA where Ended > @.myDate) begin
> raiserror('No imported records',16,1)
> return 1
> end
> return 0
>
>
> "AlwaysLearning" wrote:
>|||So how do you want to verify the that the data was inserted. And why don't
you trust that the DTS package worked? Have there been cases where it
didn't but didn't raise any errors?
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> wrote in message
news:D3420366-7668-49EB-B245-6A668FC6B552@.microsoft.com...
> We have a SQL 2000 database that we import a csv file into. The csv file
> contains specific metrics from our accounting system from the the prior
> day.
> The data gets dumped out to the csv file sometime at night, and a DTS
> package
> imports it into SQL at 7:30 am.
> I need to verify that the data populated into SQL every morning. I've
> been
> manually querying the table, E1_Data, on one of three date columns, ENDED,
> with the following script,
> SELECT ENDED
> FROM E12SQL.E1_DATA
> WHERE (MONTH(ENDED) = '08') AND (DAY(ENDED) = '29') AND (YEAR(ENDED) =
> '2005')
> If I get data back, I know yesterdays csv file imported into SQL.
> Surely there is a better way to do this.
>
> --
> AlwaysLearning|||Louis, you ask, "...why don't you trust that the DTS package worked?"
Actually, the DTS package works just fine, it is that sometimes the
accounting system doesn't export its data to the csv file which results in
DTS pulling in the same data twice.
Regarding your first question, "...how do you want to verify the that the
data was inserted". If the data imported successfully, then the table,
E1_Data, will contain the priors day date in the ENDED column. It seems
logical to me that if I count the number of records in the table that have
yesterdays date and store that value somewhere, Excel or another SQL table,
then I can verify the data came over.
AlwaysLearning
"Louis Davidson" wrote:

> So how do you want to verify the that the data was inserted. And why don'
t
> you trust that the DTS package worked? Have there been cases where it
> didn't but didn't raise any errors?
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often convincing.
"
> (Oscar Wilde)
> "AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> wrote in messa
ge
> news:D3420366-7668-49EB-B245-6A668FC6B552@.microsoft.com...
>
>|||That's . I am guessing that the other suggestion of having a step that
checks the table and raises an error if no data is in the table would
probably be a good idea.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"AlwaysLearning" <AlwaysLearning@.discussions.microsoft.com> wrote in message
news:88D357A1-27B8-44C2-AE6B-E7CEF4767DA3@.microsoft.com...
> Louis, you ask, "...why don't you trust that the DTS package worked?"
> Actually, the DTS package works just fine, it is that sometimes the
> accounting system doesn't export its data to the csv file which results in
> DTS pulling in the same data twice.
> Regarding your first question, "...how do you want to verify the that the
> data was inserted". If the data imported successfully, then the table,
> E1_Data, will contain the priors day date in the ENDED column. It seems
> logical to me that if I count the number of records in the table that have
> yesterdays date and store that value somewhere, Excel or another SQL
> table,
> then I can verify the data came over.
> --
> AlwaysLearning
>
> "Louis Davidson" wrote:
>

how to deploy a script

Hello,

What i'm looking for is a way to write a script which would change few data type fields in a specific table, in a specific database. Like i want to change it from char(4) to varchar(15). And then deploy this script, so that when he executes it, changes are reflected in his database. I may use query analyzer to excute i think. Plz also tell me how to write that script.(not sure if its called script).

Thank You.

Use the ALTER TABLE ALTER COLUMN command. Here is an example:

Code Snippet

ALTER TABLE MyTable ALTER COLUMN Col1 VarChar(100)

where MyTable is the table you're updating and Col1 is the column that you want to change its type.

I hope this answers your question.

Best regards,

Sami Samir

|||

if you don't need to alter your source database you can use this approach,

You can get all the table scripts from "SQL Server - Enterprise Manager" using Generate SQL Script.

After that you can find the Char(7) datatype and replace with varchar(15). (you can simply use any text editors Find & Replace)

|||

ya, i used following,

ALTER TABLE GRNs
ALTER COLUMN GRNNo VARCHAR(15) NOT NULL

i was just wondering if it can be conditional, like what's use of running it if its already varchar.

is their anything like if else?

|||

Try the following script:

Code Snippet

Declare @.Type as Int

Declare @.Length as Int

Select @.Type = System_Type_ID, @.Length = Max_Length

From sys.objects Inner Join sys.columns On sys.objects.Object_ID = sys.columns.Object_ID

Where sys.objects.Name = GRNs And sys.columns.Name = GRNNo

--Check that the column actually exists

If @.@.RowCount <> 0

Begin

--Check if it is already Varchar (ID is 167) and length is already 15

If @.Type <> 167 Or @.Length <> 15

Alter Table GRNs Alter Column GRNNo Varchar(15)

End

I hope this helps.

Best regards,

Sami Samir

how to delete a specific record inside a DB?

In one of our DB's, we've got a corrupted record. My question to this group,
is there a command to delete a specific record out of the DB? If so, what
all information do i have to provide to the command, so it specifies this
record. Any help is greatly appreciated
Ken Zimmerman
MIS Dept
American Red Cross
The DELETE statement deletes a row identified by its column values. If you
aren't sure what the key columns of the table are then before you delete
anything you'll want to be sure you've found the right row(s). Check by
using a SELECT statement to view the data you are going to delete. For
example:
SELECT *
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
Once you're happy that you've defined the correct criteria for the row(s)
you want to delete:
DELETE
FROM YourTable
WHERE col1 = 'X'
AND col2 = 'Y'
May be wise also to make sure you have a recent backup before deleting
anything.
David Portas
SQL Server MVP
|||KZimmerman wrote:
> In one of our DB's, we've got a corrupted record. My question to
> this group, is there a command to delete a specific record out of the
> DB? If so, what all information do i have to provide to the command,
> so it specifies this record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross
I assume by corrupted, you mean that there is strange data in one of the
columns and you believe this to be a "bad" data issue, not a corruption
in the database.
If so, use David's recommendation and locate the primary key for the row
or rows with the problem so you can issue a delete statement to remove
them from the database.
If this is a database corruption issue, try issuing DBC CHECKDB on the
database to check for problems.
David G.
|||If none of that works. you might back up your database and try.
Dbcc checkdb repair_allow_data_loss (read about this in books on line first)
or
export the rows out using bcp ( you'll get the rows you can see)
truncate the table and re-import them (Be careful to get all of the rows ,
you might have to do some tricks to select forward (and it dies when you get
to the bad row, then select backwards to get the rows on the other side.)
Also,
Call MS Tech support, I think they have some tools which might be useful I
think their fee is $250... Not much considering the time you might spend
messing with this ( if the data is important.)
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"KZimmerman" <KZimmerman@.discussions.microsoft.com> wrote in message
news:1087034D-3D76-4827-9166-EA3AB7D6CE91@.microsoft.com...
> In one of our DB's, we've got a corrupted record. My question to this
group,
> is there a command to delete a specific record out of the DB? If so, what
> all information do i have to provide to the command, so it specifies this
> record. Any help is greatly appreciated
> Ken Zimmerman
> MIS Dept
> American Red Cross