copy error

Can someone point me in the right direction to figure out
what's wrong here?
I'm trying to copy a very simple DB between two servers
using the SQL 2000 Enterprise Manager DB Copy Wizard. All
steps looks good up to detaching the source DB, but then
the actual copy of the share database mdf file to the
destination server fails. The Error Code in the Task
Detail box is 'failed'. The Error Code in the Step Detail
box is '-2147467259'.
The Package Log Error Description is:
Step Error Source: Microsoft Data Transformation Services
(DTS) Package
Step Error Description:Unspecified error
Step Error code: 80004005
Step Error Help File:sqldts80.hlp
Step Error Help Context ID:1100The reason and solution to your exact error/problem are given at:
http://support.microsoft.com/default.aspx?scid=kb;en-us;274463&Product=sql2k
"Tech - SBT" <bglynn@.sbtontheweb.com> wrote in message
news:1MCjb.147527$Of2.4215035@.twister.tampabay.rr.com...
> Can someone point me in the right direction to figure out
> what's wrong here?
> I'm trying to copy a very simple DB between two servers
> using the SQL 2000 Enterprise Manager DB Copy Wizard. All
> steps looks good up to detaching the source DB, but then
> the actual copy of the share database mdf file to the
> destination server fails. The Error Code in the Task
> Detail box is 'failed'. The Error Code in the Step Detail
> box is '-2147467259'.
> The Package Log Error Description is:
> Step Error Source: Microsoft Data Transformation Services
> (DTS) Package
> Step Error Description:Unspecified error
> Step Error code: 80004005
> Step Error Help File:sqldts80.hlp
> Step Error Help Context ID:1100
>|||I read that and don't see the exact problem. MORE INFO does not give me any
details nor an exact error.
"I_AM_DON_AND_YOU?" <user@.domain.com> wrote in message
news:%23eK7ukClDHA.2060@.tk2msftngp13.phx.gbl...
> The reason and solution to your exact error/problem are given at:
>
http://support.microsoft.com/default.aspx?scid=kb;en-us;274463&Product=sql2k
>
> "Tech - SBT" <bglynn@.sbtontheweb.com> wrote in message
> news:1MCjb.147527$Of2.4215035@.twister.tampabay.rr.com...
> > Can someone point me in the right direction to figure out
> > what's wrong here?
> >
> > I'm trying to copy a very simple DB between two servers
> > using the SQL 2000 Enterprise Manager DB Copy Wizard. All
> > steps looks good up to detaching the source DB, but then
> > the actual copy of the share database mdf file to the
> > destination server fails. The Error Code in the Task
> > Detail box is 'failed'. The Error Code in the Step Detail
> > box is '-2147467259'.
> >
> > The Package Log Error Description is:
> >
> > Step Error Source: Microsoft Data Transformation Services
> > (DTS) Package
> > Step Error Description:Unspecified error
> >
> > Step Error code: 80004005
> > Step Error Help File:sqldts80.hlp
> > Step Error Help Context ID:1100
> >
> >
>
>

Copy db with dtswizard doesnt export triggers and stored procs

I am using dtswizard to export my db but it doesn't export triggers and all stored procedures. Am I doing anything wrong or is there a better way to do an exact copy of my DB? I am not able to use backup/restore because my db is on a server to which I don't have admin-right, and I have to pay my hoster to create a backup and email me.

TIA

Thomas

The SQL Server team have created a tool to move your database to a hosting server, try the link below for details. Post again if you still need help.

http://weblogs.asp.net/scottgu/archive/2007/01/11/tip-trick-how-to-upload-a-sql-file-to-a-hoster-and-execute-it-to-deploy-a-sql-database.aspx

|||

If you know IP or dns name of SQL server on your Host and you have user name and password to connect to your database I would recommend to script database to Query window next switch connection for this window to you host SQL server and just run it.

I did it for my web host SQL server and it worked perfectly.

If you can connect to your web SQL server from management studio it will pay in the future because you can modify you database using Microsoft tools which is probably better than any asp.net page.

Thanks

|||Actually I am trying to export the db (incl. triggers and stored procs) FROM my webhoster. I don't have permission to script the db :-(|||

thomasabcd:

Actually I am trying to export the db (incl. triggers and stored procs) FROM my webhoster. I don't have permission to script the db :-(

If you can see the stored procs and triggers just right click copy all and paste in your Management Studio. Hope this helps.

|||

Isn't there a way to copy the entire db with tables, rows, stored procedures and triggers? It seems cumbersome to e.g. go to every table, open triggers, script each trigger instead of just doing a bulk copy of the entire DB.

tia

|||

If you have access to your MDF and LDF you can use the code in thread below just put the files in the data sub folder in Microsoft SQL Server folder in programs and modify the code below for your database and box. Hope this helps.

http://forums.asp.net/thread/977493.aspx

|||

OR you could open up your query analyser ,set the output to text and then run this:

declare @.objNamesysnamedeclare objCurcursor for select [name]fromsysobjectswhere xtypein ('P','TR')OPEN objCurwhile 1=1begin-- Fetch datafetch objCurinto @.objNameif@.@.fetch_status <> 0break;execsp_helptext @.objNameENDCLOSE objCurDEALLOCATE objCur

The output then contains all the stored procs xtype = P and triggers xtype='TR'.Each objects script will be separated by the text, but I'm sure you can deal with that.

|||

Sounds like you have very limited permission on the hosted database. The best bet is to do just copy and paste all the stored proc and views.

If the data is important, i would pay your hoster for a backup or mdf of your database.

Most hosters nowaday offers free database backup.

Coplicated view - picking from a=b or from c&d =e

I realized there was something wrong with my view when an entire line
was missing. I believe it is caused by an inner join I did on one
column to create another column which may not actually have a value.
let me show you.
Table A ViewA ViewB
type1 type1
type2 type2
type3 type3
answer answer
Pretend that any / all items in Table A can be null. What I want the
view to do is either 1) have both viewA and ViewB answer show up even
if its null or 2) try to get viewB answer but if not, then go to ViewA
answer, then null.
Yes, that is a VIEW doing this...or so I hope.
Any ideas?
John
RocketMan,
Try:
select
coalesce(c.c1, b.c1, a.c1)
from
tableA as a
full outer join
viewA as b
a.c1 = b.c1
full outer join
viewB as c
on a.c1 = c.c1
GO
AMB
"RocketMan" wrote:

> I realized there was something wrong with my view when an entire line
> was missing. I believe it is caused by an inner join I did on one
> column to create another column which may not actually have a value.
> let me show you.
> Table A ViewA ViewB
> type1 type1
> type2 type2
> type3 type3
> answer answer
> Pretend that any / all items in Table A can be null. What I want the
> view to do is either 1) have both viewA and ViewB answer show up even
> if its null or 2) try to get viewB answer but if not, then go to ViewA
> answer, then null.
> Yes, that is a VIEW doing this...or so I hope.
> Any ideas?
> John
>
|||Better try this one:
SELECT
coalesce(c.c1, b.c1, a.c1) AS whatever
from
tableA as a
full outer JOIN
(
viewA as b
full outer join
viewB as c
ON b.c1 = c.c1
)
ON a.c1 = COALESCE(c.c1, b.c1)
GO
AMB
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> RocketMan,
> Try:
> select
> coalesce(c.c1, b.c1, a.c1)
> from
> tableA as a
> full outer join
> viewA as b
> a.c1 = b.c1
> full outer join
> viewB as c
> on a.c1 = c.c1
> GO
> AMB
> "RocketMan" wrote:
|||In a way, I answered my own question with a question...
When is a null not a null? When its a ''
Adding this to the views ( of other tables) made the join work and I
was able to get the line to appear.
THANKS for the help. I KNOW someone sometime will be searching for
this kind of help

Coplicated view - picking from a=b or from c&d =e

I realized there was something wrong with my view when an entire line
was missing. I believe it is caused by an inner join I did on one
column to create another column which may not actually have a value.
let me show you.
Table A ViewA ViewB
type1 type1
type2 type2
type3 type3
answer answer
Pretend that any / all items in Table A can be null. What I want the
view to do is either 1) have both viewA and ViewB answer show up even
if its null or 2) try to get viewB answer but if not, then go to ViewA
answer, then null.
Yes, that is a VIEW doing this...or so I hope.
Any ideas?
JohnRocketMan,
Try:
select
coalesce(c.c1, b.c1, a.c1)
from
tableA as a
full outer join
viewA as b
a.c1 = b.c1
full outer join
viewB as c
on a.c1 = c.c1
GO
AMB
"RocketMan" wrote:
> I realized there was something wrong with my view when an entire line
> was missing. I believe it is caused by an inner join I did on one
> column to create another column which may not actually have a value.
> let me show you.
> Table A ViewA ViewB
> type1 type1
> type2 type2
> type3 type3
> answer answer
> Pretend that any / all items in Table A can be null. What I want the
> view to do is either 1) have both viewA and ViewB answer show up even
> if its null or 2) try to get viewB answer but if not, then go to ViewA
> answer, then null.
> Yes, that is a VIEW doing this...or so I hope.
> Any ideas?
> John
>|||Better try this one:
SELECT
coalesce(c.c1, b.c1, a.c1) AS whatever
from
tableA as a
full outer JOIN
(
viewA as b
full outer join
viewB as c
ON b.c1 = c.c1
)
ON a.c1 = COALESCE(c.c1, b.c1)
GO
AMB
"Alejandro Mesa" wrote:
> RocketMan,
> Try:
> select
> coalesce(c.c1, b.c1, a.c1)
> from
> tableA as a
> full outer join
> viewA as b
> a.c1 = b.c1
> full outer join
> viewB as c
> on a.c1 = c.c1
> GO
> AMB
> "RocketMan" wrote:
> > I realized there was something wrong with my view when an entire line
> > was missing. I believe it is caused by an inner join I did on one
> > column to create another column which may not actually have a value.
> > let me show you.
> >
> > Table A ViewA ViewB
> > type1 type1
> > type2 type2
> > type3 type3
> > answer answer
> >
> > Pretend that any / all items in Table A can be null. What I want the
> > view to do is either 1) have both viewA and ViewB answer show up even
> > if its null or 2) try to get viewB answer but if not, then go to ViewA
> > answer, then null.
> >
> > Yes, that is a VIEW doing this...or so I hope.
> >
> > Any ideas?
> >
> > John
> >|||In a way, I answered my own question with a question...
When is a null not a null? When its a ''
Adding this to the views ( of other tables) made the join work and I
was able to get the line to appear.
THANKS for the help. I KNOW someone sometime will be searching for
this kind of help

Converting to decimal

Hi all,
OK, I'm sure very simple question, what am I doing wrong.
how do I get the percentage? of 68 / 1812
@.result keeps showing 0.00
Declare @.result varchar(20)
set @.result = cast(round(100 * (68 / 1812) , 2)as numeric(5,2))
print @.result
thanks
GVMake the inner numbers decimals as the calculations happen before the
cast:
Declare @.result varchar(20)
set @.result = cast(round(100 * (68.0 / 1812.0) , 2)as numeric(5,2))
print @.result
or
Declare @.result varchar(20)
set @.result = cast(round(100 * (cast(68 as numeric(5,2)) / cast(1812 as
numeric(6,2))) , 2)as numeric(5,2))
print @.result|||Integer division = Integer result. Try casting or representing one of values
involved in the formula to numeric.
Declare @.result varchar(20)
set @.result = cast(round(100.00 * (68.00 / 1812.00) , 2)as numeric(5,2))
print @.result
AMB
"gv" wrote:

> Hi all,
> OK, I'm sure very simple question, what am I doing wrong.
> how do I get the percentage? of 68 / 1812
> @.result keeps showing 0.00
> Declare @.result varchar(20)
> set @.result = cast(round(100 * (68 / 1812) , 2)as numeric(5,2))
> print @.result
> thanks
> GV
>
>|||thanks
gv
"gv" <viatorg@.musc.edu> wrote in message
news:%23I$MWu9LFHA.3852@.tk2msftngp13.phx.gbl...
> Hi all,
> OK, I'm sure very simple question, what am I doing wrong.
> how do I get the percentage? of 68 / 1812
> @.result keeps showing 0.00
> Declare @.result varchar(20)
> set @.result = cast(round(100 * (68 / 1812) , 2)as numeric(5,2))
> print @.result
> thanks
> GV
>

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.

Converting NULL to a Text String

I'm using UPDATE as follows"

UPDATE Dubai

SET DB = 'D'

WHERE DB = NULL

but nothing happens. What am I doing wrong?

Thanks.

Your update needs to be modified from this:

Code Snippet

UPDATE Dubai

SET DB = 'D'

WHERE DB = NULL

to this:

Code Snippet

UPDATE Dubai

SET DB = 'D'

WHERE DB IS NULL

In older version of SQL Server your syntax would have worked. Null is considered a state and not a value. Null is considered a state. To check to see if a column is in a null state the syntax is to check if the column "is null".

|||

Just in addition and for reference:

http://www.sqlservercentral.com/columnists/jtravis/understandingthedifferencebetweenisnull.asp

Jens K. Suessmeyer

http://www.sqlserver2005.de