Copy items from one SQL Database to another...

...such as stored procedures, tables etc.

Initially this started as a case of "doh, I should just be using one database here, not two", and I was simply wanting to copy database tables. In VS2K5 I tried as there is a right click menu option of copy when a table is selected but this doesn't work for me with any database object of any kind.

So fast forward to the present...I now am attempting to deploy an app to a hosting service, 1&1.com. I am allowed only one database in my current package, which should be fine for now. So I had to combine the ASPNETDB along with 2 other databases. It took a bit of time, but I got everything done, I thought, and posted to the servers. While debugging I get an error saying a stored procedure can not be found. And it indeed is not.

This really confuses me as I made the changes in VS2k5, shut down and restarted to make sure I didn't miss anything, then used SQL Server Man. Studio to make a .bak file to upload to my hosting service. It never occurred to me to verify the changes I made in VS2k5 were actually on the database when viewed there. Well, they aren't, and I have no idea why. That would be issue #1 I suppose.

So after giving the background info here, what I am looking for help with is how to get the changes I am making in VS2k5 to also be present when viewed from SQL Server Man. Studio as the only means of posting a db to my hosting provider is by using a .bak file.

Also, why is it a project template I download has a .mdf file I am not able to even see in SQL Server Man. Studio? I guess if I had this answer the issue would be resolved.

TIA

Regards,

Joe

When you say you want to copy tables, do you mean the data in the tables or just the structure of the tables? If it is just the structure you could script out the tables and stored procedures and execute them against your other database.

|||

OK...I'll start looking for pointers on what exactly you mean. I honestly have no idea as I am not an SQL guy, and am new to ASP.NET/VS/VB etc.

If you can point me in the direction of what you mean that would be great.

I appreciate the response.

Regards,

Joe

|||

I think I was too long winded and did not communicate my question well in an earlier post.

I want to transfer database tables and stored procedures from datbase1 and database2 to database3, and end up with just database3. (I don't care one way or the other about the data in the tables) I am finding posts regarding how to do this, but most expect the database to be viewable in SQL Server management studio, and I can not see all the databases there. In one case I have a website completed from a MSDN template, and in another case the database was created as a part of a web app I created from scratch. Both were created using VS2K5 pro.

The odd bit is that I created all the databases from within VS2K5 while working on web apps. So I am very confused as to why some databases show up, some don't, and some work done doesn't show up in the database that is showing up in server management studio. (to clarify, all the databases are visible from VS2K5, but only one is visible in the server management studio)

Any help would be great, thanks

TIA

Regards,

Joe

|||Right click on the database and select Tasks > Generate Scripts. Follow the steps and select stored procedures and tables to be scripted. It will generate a sql script that you can run on another database that will create those tables and stored procedures.|||

OK. I did see that option and was hoping to get a response confirming that.

My big problem though, is that I can't even see one of the databases in the management studio. I am not sure what I'm missing, but I have tried to make it visible but have failed. I do not understand this because I created all the databases from within VS2K5, so why I can see some but not others.

Any pointers in this regard would be much appreciated.

Regards,

Joe

|||

Hi Joe,

Are you sure that the database has been attached to the SQL Server instance? Because some databases are attached dynamically at runtime.

You can try to attach the database in SQL Server management studio.

|||

I am starting to feel very ignorant, lol. Not that it's a terrible thing, we all have to learn right...

I think in order to grasp what is going on here I need to understand what's happening when I add the database in VS2K5. If in VS2K5 I "add an existing item" (the database that SQL Server management studio sees) then why am I getting two instances of the database? I don't understand why I end up with two copies. I am not asking for another db to be created as far as I know.

I appreciate the responses posted so far, they are helpful, but I don't understand why I need to take either action suggested. I am certainly not unwilling to do either. But if I do I am still going to have no idea why I have tow copies of the db, and unless something changes will the same result not continue? When I make changes in either software (VS2K5/SQL Server management studio) will I not be changing just one db?

Still very uncertain here...

Any comments are appreciated.

Regards,

Joe

|||

OK...

I may have discovered the issue.

I went poking around again paying veryclose attention to every step as I am adding a database to my site. As I stepped through I get to the point where I test the connection and it fails, saying it's in use by another process, almost without thinking I begin to do what I have done numerous times in the past. I was going to stop the instance of SQL server. And it was then I realized..why? As I considered this I realized that did not make sense. So i looked at the dialog for add database and realized the dropdown had SQl Database selected, not SQL server. I feel relatively confident this is the cause of my troubles.

For anyone who is having siilar issues, I'll post as soon as i determine whether this was the problem.

Regards,

Joe

|||

OK.

That was it. I now have a connection only, not a new copy. I hope this helps anyone else as new as me...

Regards,

Joe

PS: do not know how to mark a question as answered...

sqlsql

Converting varchar to Money

I have a a Case statement which sometimes fails when converting a Varchar
column which contains numeric values to Money. I can understand why it fail
s
when "1.05E+07" is passed in, but other values appear to fail also. I have
not located the other offending values yet (500,000 rows to sift through)
but converting them to Float first avoids the errors. Any ideas?
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
"Server: Msg 235, Level 16, State 1, Line 1
Cannot convert a char value to money. The char value has incorrect syntax."
However the following code, which converts to Float, and then to Money, does
not fail.
WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
CONVERT(FLOAT,c1_PriorSalePrice) )(a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
the contents can be converted to any numeric type. See
http://www.aspfaq.com/2390 for the long-winded version of this.
(b) if using convert(money, convert(float())) works, then what is wrong with
using that?
(c) STOP STORING NUMERIC VALUES AS STRINGS!
"Snake" <Snake@.discussions.microsoft.com> wrote in message
news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>I have a a Case statement which sometimes fails when converting a Varchar
> column which contains numeric values to Money. I can understand why it
> fails
> when "1.05E+07" is passed in, but other values appear to fail also. I
> have
> not located the other offending values yet (500,000 rows to sift through)
> but converting them to Float first avoids the errors. Any ideas?
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money, c1_PriorSalePrice)
> "Server: Msg 235, Level 16, State 1, Line 1
> Cannot convert a char value to money. The char value has incorrect
> syntax."
> However the following code, which converts to Float, and then to Money,
> does
> not fail.
> WHEN ISNUMERIC(c1_SalePrice)= 1 THEN convert(money,
> CONVERT(FLOAT,c1_PriorSalePrice) )
>
>|||Brother AAron,
The data in question is being provided in bulk by an outside vendor, so I
have no choice in how the data is provided or in what format. I have never
seen this situation before and it may have implications for other procedures
.
I will go read the link you provided.
Thanks
Michael
"Aaron Bertrand [SQL Server MVP]" wrote:

> (a) Don't rely on isnumeric(). Just because isnumeric() = 1 does not mean
> the contents can be converted to any numeric type. See
> http://www.aspfaq.com/2390 for the long-winded version of this.
> (b) if using convert(money, convert(float())) works, then what is wrong wi
th
> using that?
> (c) STOP STORING NUMERIC VALUES AS STRINGS!
>
>
> "Snake" <Snake@.discussions.microsoft.com> wrote in message
> news:47497632-9B73-416F-9BE4-F729B3CEF653@.microsoft.com...
>
>

Converting text to bit issues

I need to convert a text column to a bit in a select query.
Backing up, in case there's a better way to do this:
I have a varchar column that contains one of six letters. There are two
that interest me (Q and A). I want my vb.net application to update changes
back to this column.
For my application, Q=0 and A=1
The name of the column is 'Status'
I tried:
Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
.. didn't work. Any help?
UsarianTry,
Convert(bit, (Convert(int, (CASE when (Status = 'Q') THEN 0 ELSE 1 end))))
also:
convert(bit, ascii(upper(colA)) - ascii('Q'))
AMB
"Usarian Skiff" wrote:

> I need to convert a text column to a bit in a select query.
> Backing up, in case there's a better way to do this:
> I have a varchar column that contains one of six letters. There are two
> that interest me (Q and A). I want my vb.net application to update change
s
> back to this column.
> For my application, Q=0 and A=1
> The name of the column is 'Status'
> I tried:
> Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
> ... didn't work. Any help?
> Usarian
>
>|||Usarian Skiff wrote:
> I need to convert a text column to a bit in a select query.
> Backing up, in case there's a better way to do this:
> I have a varchar column that contains one of six letters. There are two
> that interest me (Q and A). I want my vb.net application to update change
s
> back to this column.
> For my application, Q=0 and A=1
> The name of the column is 'Status'
> I tried:
> Convert(bit, (Convert(int, (CASE (Status = 'Q') THEN 0 ELSE 1))))
> .. didn't work. Any help?
> Usarian
>
cast((case when status = 'q' then 0 else 1 end) as bit)
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Stinkin Yeah!
Thanks!
Usarian Skiff
"Alejandro Mesa" <AlejandroMesa@.discussions.microsoft.com> wrote in message
news:87BB3703-E7B8-4946-B0BF-681FBF548A61@.microsoft.com...
> Try,
> Convert(bit, (Convert(int, (CASE when (Status = 'Q') THEN 0 ELSE 1 end))))
> also:
> convert(bit, ascii(upper(colA)) - ascii('Q'))
>
> AMB
>
> "Usarian Skiff" wrote:
>
changes

Converting tables to Upper case

Hello, we've an Oracle transition in the pipeline and want to convert
all our database objects to upper case. Any one got a script or
technique (other than manual) to do it?

Many thanks, Kevin.The two statements below should get you started. If you need to rename
indexes in addition to tables then you can change the u.type in the
WHERE clause of the first statement. Run these statements in every
database in which you need to do this. It will generate the code that
you need to run, so copy and paste the results in the query window and
run that.

I ran both statements and eyeballed the results and they looked ok, but
I have not actually tested this by running the results, so you should
go over it yourself as well.

HTH,
-Tom.

SELECT 'EXEC sp_rename ''[' + u.name + '].[' + o.name + ']'', ''[' +
UPPER(o.name) + ']'''
FROM sysobjects o
INNER JOIN sysusers u ON u.uid = o.uid
WHERE o.type = 'U'

SELECT 'EXEC sp_rename ''[' + u.name + '].[' + t.name + '].[' + c.name
+ ']'', ''[' + UPPER(c.name) + ']'', ''COLUMN'''
FROM sysobjects t
INNER JOIN sysusers u ON u.uid = t.uid
INNER JOIN syscolumns c ON c.id = t.id
WHERE t.type = 'U'|||Thanks very much, this works a treat and is a great idea. Apologies
for delay in replying.

kevin.

"Thomas R. Hummel" <tom_hummel@.hotmail.com> wrote in message news:<1112379305.650858.281710@.f14g2000cwb.googlegroups. com>...
> The two statements below should get you started. If you need to rename
> indexes in addition to tables then you can change the u.type in the
> WHERE clause of the first statement. Run these statements in every
> database in which you need to do this. It will generate the code that
> you need to run, so copy and paste the results in the query window and
> run that.
> I ran both statements and eyeballed the results and they looked ok, but
> I have not actually tested this by running the results, so you should
> go over it yourself as well.
> HTH,
> -Tom.
> SELECT 'EXEC sp_rename ''[' + u.name + '].[' + o.name + ']'', ''[' +
> UPPER(o.name) + ']'''
> FROM sysobjects o
> INNER JOIN sysusers u ON u.uid = o.uid
> WHERE o.type = 'U'
> SELECT 'EXEC sp_rename ''[' + u.name + '].[' + t.name + '].[' + c.name
> + ']'', ''[' + UPPER(c.name) + ']'', ''COLUMN'''
> FROM sysobjects t
> INNER JOIN sysusers u ON u.uid = t.uid
> INNER JOIN syscolumns c ON c.id = t.id
> WHERE t.type = 'U'

Converting string to datetime

Hi

What's wrong about

SELECT SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @.FY + '/04/01')
AND CONVERT(Datetime, @.FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,
[Item No_]
FROM table A

It looks like you are trying to use the aggregate function sum() and the [item no_] column is not in an aggregate function or group by clause

|||

here it is,

Code Snippet

SELECT

SUM(CASE WHEN [PO Date] BETWEEN CONVERT(Datetime, @.FY + '/04/01')

AND CONVERT(Datetime, @.FY -1 + '/03/31') THEN Quantity ELSE 0 END) AS Expr1,

[Item No_]

FROM table A

Group By

[Item No_]

|||

Still can't! It show error massage" wrong parameter".

|||

Please provide the entire procedure code and the error message in its entirity.|||

Hi

I use this query on reporting service. After perview will show "Conversion failed when converting to varchar value"/04/01" to date type int.

|||

You are attempting to concatenate the varchar value '/04/01' to the integer value @.FY.

You need to first cast the variable @.FY as a char()/varchar().

However, this could be avoided IF you used an actual datetime value for the fiscal year instead an integer value. This may be an excellent opportunity to finally explore using the 'Calendar' table you've most likely heard about before. I recommend reviewing this article to see if there is utility in the concept for you.

(From this example, I suspect that there are other places in your code where you are attempting to handle 'date' issues in 'creative' and non-functional methods.)

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

|||

Hai,

As Arnie said, you need to first convert @.FY value to varchar type.

And, also, in your query, first date in Between is > second date which always returns the Expr1 to 0.

you can try this, sample, query:

DECLARE @.FY int

DECLARE @.TableA Table([PODate] datetime, [ItemNo_] int, Quantity int)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/01',1, 200)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/03',1, 100)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/05',1, 50)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/10',2, 30)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/03/22',2, 40)

INSERT INTO @.TableA([PODate], [ItemNo_], Quantity) VALUES('2007/04/01',3, 60)

SET @.FY = 2007

SELECT

SUM(CASE WHEN [PODate] BETWEEN CONVERT(Datetime, CONVERT(VarChar(4),(@.FY-1)) + '/03/31') AND

CONVERT(Datetime, CONVERT(VarChar(4),@.FY) + '/04/01')

THEN Quantity ELSE 0

END) AS Expr1,

[ItemNo_]

FROM @.TableA

GROUP BY [ItemNo_]

Hope this will work, Please clarify If'm wrong.

Regards,

Y.Kiran Kumar.