I am trying to automate a scheduled restore of databases from several SQL
Servers to my local development SQL Server (all SQL 2000 STD & Dev.). I can
successfully create the RESTORE scripts and run them directly in QA. However,
I want the scripts stored in a table on the local SQL Server and then run
them using:

USE SQLMaintenance
GO
DECLARE @Script VARCHAR(750)
SELECT @Script = restorescript FROM dbo.DatabaseRestoreScripts WHERE
restorecompleted = 0 AND databasename = 'somedatabasename'
EXEC @Script


QA returns:

"Could not execute procedure on remote server 'RESTORE DATABASE
somedatabasename FROM DISK =
'\\SQLSERVER\d$\MSSQL\BACKUP\somedatabasename_db_2 00806142105' because SQL
Server is not configured for remote access."


Why does the script stored in the table runs fine in QA but not when run
using EXEC? I did not think I would have to setup Remote servers. I have been
searching for several hours now but gotten nowhere. Any would be greatly
appreciated.

Thank you.

EXEC(@Script)

You forgot the parenthesis.

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"SQLGuptaSybase" <SQLGuptaSybase@discussions.microsoft.com> wrote in message
news:B289DA06-E554-418F-8D23-CABB8B89EC18@microsoft.com...
>I am trying to automate a scheduled restore of databases from several SQL
> Servers to my local development SQL Server (all SQL 2000 STD & Dev.). I can
> successfully create the RESTORE scripts and run them directly in QA. However,
> I want the scripts stored in a table on the local SQL Server and then run
> them using:
>
> USE SQLMaintenance
> GO
> DECLARE @Script VARCHAR(750)
> SELECT @Script = restorescript FROM dbo.DatabaseRestoreScripts WHERE
> restorecompleted = 0 AND databasename = 'somedatabasename'
> EXEC @Script
>
>
> QA returns:
>
> "Could not execute procedure on remote server 'RESTORE DATABASE
> somedatabasename FROM DISK =
> '\\SQLSERVER\d$\MSSQL\BACKUP\somedatabasename_db_2 00806142105' because SQL
> Server is not configured for remote access."
>
>
> Why does the script stored in the table runs fine in QA but not when run
> using EXEC? I did not think I would have to setup Remote servers. I have been
> searching for several hours now but gotten nowhere. Any would be greatly
> appreciated.
>
> Thank you.

Tibor,

Thank you for your but also I apologize for such an obvious error.

"Tibor Karaszi" wrote:

> EXEC(@Script)
>
> You forgot the parenthesis.
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
>
> "SQLGuptaSybase" <SQLGuptaSybase@discussions.microsoft.com> wrote in message
> news:B289DA06-E554-418F-8D23-CABB8B89EC18@microsoft.com...
> >I am trying to automate a scheduled restore of databases from several SQL
> > Servers to my local development SQL Server (all SQL 2000 STD & Dev.). I can
> > successfully create the RESTORE scripts and run them directly in QA. However,
> > I want the scripts stored in a table on the local SQL Server and then run
> > them using:
> >
> > USE SQLMaintenance
> > GO
> > DECLARE @Script VARCHAR(750)
> > SELECT @Script = restorescript FROM dbo.DatabaseRestoreScripts WHERE
> > restorecompleted = 0 AND databasename = 'somedatabasename'
> > EXEC @Script
> >
> >
> > QA returns:
> >
> > "Could not execute procedure on remote server 'RESTORE DATABASE
> > somedatabasename FROM DISK =
> > '\\SQLSERVER\d$\MSSQL\BACKUP\somedatabasename_db_2 00806142105' because SQL
> > Server is not configured for remote access."
> >
> >
> > Why does the script stored in the table runs fine in QA but not when run
> > using EXEC? I did not think I would have to setup Remote servers. I have been
> > searching for several hours now but gotten nowhere. Any would be greatly
> > appreciated.
> >
> > Thank you.
>

No need to apologize. We all do these things, and having more set of eyes to look at our code (like
in these groups) is a great way to spot these things. :-)

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi


"SQLGuptaSybase" <SQLGuptaSybase@discussions.microsoft.com> wrote in message
news:70647B92-43A7-45D5-B66E-56CEADEA8A46@microsoft.com...
> Tibor,
>
> Thank you for your but also I apologize for such an obvious error.
>
> "Tibor Karaszi" wrote:
>
>> EXEC(@Script)
>>
>> You forgot the parenthesis.
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>>
>> "SQLGuptaSybase" <SQLGuptaSybase@discussions.microsoft.com> wrote in message
>> news:B289DA06-E554-418F-8D23-CABB8B89EC18@microsoft.com...
>> >I am trying to automate a scheduled restore of databases from several SQL
>> > Servers to my local development SQL Server (all SQL 2000 STD & Dev.). I can
>> > successfully create the RESTORE scripts and run them directly in QA. However,
>> > I want the scripts stored in a table on the local SQL Server and then run
>> > them using:
>> >
>> > USE SQLMaintenance
>> > GO
>> > DECLARE @Script VARCHAR(750)
>> > SELECT @Script = restorescript FROM dbo.DatabaseRestoreScripts WHERE
>> > restorecompleted = 0 AND databasename = 'somedatabasename'
>> > EXEC @Script
>> >
>> >
>> > QA returns:
>> >
>> > "Could not execute procedure on remote server 'RESTORE DATABASE
>> > somedatabasename FROM DISK =
>> > '\\SQLSERVER\d$\MSSQL\BACKUP\somedatabasename_db_2 00806142105' because SQL
>> > Server is not configured for remote access."
>> >
>> >
>> > Why does the script stored in the table runs fine in QA but not when run
>> > using EXEC? I did not think I would have to setup Remote servers. I have been
>> > searching for several hours now but gotten nowhere. Any would be greatly
>> > appreciated.
>> >
>> > Thank you.
>>