Question re: SSIS "Script Task" + VB.NET keyboard configuration

I was pretty excited when my first script ran in this type of task. But I soon noticed that I couldn't find the "watch" or "immediate" windows I was used to in standard VB.NET. Did I miss them or are they simply not available in the Script Task editor?

TIA,

barker

P.S. Also under the VB.NET environment, I can Import the VB.NET default keyboard settings (e.g. Step Into is F8; Step Over is Shift+F8) into the IDE. Is the same option available for the Script task editor?

They are just not visible by default. After you hit the breakpoint you can go to the menu "Debug>>>Windows>>Locals" for example.

The script task editor is just VS wired up for VSA and VB.net. So you should be able to accomplish this though I admit I have not explored that. I know I have re-assigned my own hot keys to things via "tools>>options>>keyboard"

sql

question re: maintenance

Ok, I want to write a script that

1. backups my sql database
2. commits the transactionlog
3. shrinks the db
4. shrinks the db log

does anyone have a script that will do this or can show me the light?

thank you! (sorry, i'm a programmer and not a very good dba regarding maintenance!)SQL Server 2000?

I would recommend creating a maintenance plan in Enterprise Manager, as it is easier than scripting the whole thing out. Open the database in question and set to TaskPad view is the easiest. You can then click on the dropdown second from the bottom and choose maintenance plan.

I will have to look, as I am not sure this does 100% of what you want. If not, you can set up a job with multiple steps and run the stored procedures necessary to backup, et al. The SQL Books Online (installed with client tools) is a great source of knowledge.

Question on VBScript connection issue and SQL 2005

I have a script that performs a number of reiterative tasks very quickly on a SQL database. After a few thousand requests (2-3 minutes) I get the following error:

Error: [Microsoft][ODBC SQL Server Driver][DBNETLIB]SQL Server does not exist or access denied.
Code: 80004005
Source: Microsoft OLE DB Provider for ODBC Drivers

The connection string looks like:

Driver={SQL Server};Server=45.22.11.33;Uid=test;Pwd=test;Database=Test

I have changed nothing and it always does this. It doesn't matter if in my script I open and close the connection on every request or leave it open the whole time.

Additionally, the script and the database are on the same machine.

Thanks for any help.There might be various reasons for this. How many concurrent user connections are made in your case? Are you sure you're not exceeding the limit per your license?|||

Is it possible that the IP address on the target machine was changed for some reasons? Can you try the following things?

1) ping 45.22.11.33.

2) telnet 45.22.11.33 1433

If any of this failed, you may have some network issue. Thanks.

Question on Restore Script

I am trying to write a generic restore script which could be used for
all the similar restores we do on a dialy baisis. Basically the only
thing that changes are the database names and all these backup are from
all different servers.
So I understand I would have to write a restore script with the MOVE
option and I am planning on passing the parameter
@.databasename,@.backupFileLocation.
However is there anyway to determine the names of the file in the
backup so that I could use those in my Restore Command without any user
intervention? Unless I am able to do that I cannot get the entire
process automated. From everthing I have read so far it suggests that I
would have to run RESTORE FILELISTONLY command to get the file names
and then edit my T_SQL command for each restore operation.
Is there a cool way of doing without any intervention?
Any help in this regard will be appreciated.
Thanks
Check the code in http://www.karaszi.com/SQLServer/uti...l_in_file.asp. That should give
you a good starting point.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"shub" <shubtech@.gmail.com> wrote in message
news:1137513510.543961.83750@.g44g2000cwa.googlegro ups.com...
>I am trying to write a generic restore script which could be used for
> all the similar restores we do on a dialy baisis. Basically the only
> thing that changes are the database names and all these backup are from
> all different servers.
> So I understand I would have to write a restore script with the MOVE
> option and I am planning on passing the parameter
> @.databasename,@.backupFileLocation.
> However is there anyway to determine the names of the file in the
> backup so that I could use those in my Restore Command without any user
> intervention? Unless I am able to do that I cannot get the entire
> process automated. From everthing I have read so far it suggests that I
> would have to run RESTORE FILELISTONLY command to get the file names
> and then edit my T_SQL command for each restore operation.
> Is there a cool way of doing without any intervention?
> Any help in this regard will be appreciated.
> Thanks
>
|||This is exactly the kind of script I was looking for. Thank you very
much. I really appreciate it.

Question on Restore Script

I am trying to write a generic restore script which could be used for
all the similar restores we do on a dialy baisis. Basically the only
thing that changes are the database names and all these backup are from
all different servers.
So I understand I would have to write a restore script with the MOVE
option and I am planning on passing the parameter
@.databasename,@.backupFileLocation.
However is there anyway to determine the names of the file in the
backup so that I could use those in my Restore Command without any user
intervention? Unless I am able to do that I cannot get the entire
process automated. From everthing I have read so far it suggests that I
would have to run RESTORE FILELISTONLY command to get the file names
and then edit my T_SQL command for each restore operation.
Is there a cool way of doing without any intervention?
Any help in this regard will be appreciated.
ThanksCheck the code in http://www.karaszi.com/SQLServer/ut...ver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"shub" <shubtech@.gmail.com> wrote in message
news:1137513510.543961.83750@.g44g2000cwa.googlegroups.com...
>I am trying to write a generic restore script which could be used for
> all the similar restores we do on a dialy baisis. Basically the only
> thing that changes are the database names and all these backup are from
> all different servers.
> So I understand I would have to write a restore script with the MOVE
> option and I am planning on passing the parameter
> @.databasename,@.backupFileLocation.
> However is there anyway to determine the names of the file in the
> backup so that I could use those in my Restore Command without any user
> intervention? Unless I am able to do that I cannot get the entire
> process automated. From everthing I have read so far it suggests that I
> would have to run RESTORE FILELISTONLY command to get the file names
> and then edit my T_SQL command for each restore operation.
> Is there a cool way of doing without any intervention?
> Any help in this regard will be appreciated.
> Thanks
>|||This is exactly the kind of script I was looking for. Thank you very
much. I really appreciate it.

Question on Restore Script

I am trying to write a generic restore script which could be used for
all the similar restores we do on a dialy baisis. Basically the only
thing that changes are the database names and all these backup are from
all different servers.
So I understand I would have to write a restore script with the MOVE
option and I am planning on passing the parameter
@.databasename,@.backupFileLocation.
However is there anyway to determine the names of the file in the
backup so that I could use those in my Restore Command without any user
intervention? Unless I am able to do that I cannot get the entire
process automated. From everthing I have read so far it suggests that I
would have to run RESTORE FILELISTONLY command to get the file names
and then edit my T_SQL command for each restore operation.
Is there a cool way of doing without any intervention?
Any help in this regard will be appreciated.
ThanksCheck the code in http://www.karaszi.com/SQLServer/util_restore_all_in_file.asp. That should give
you a good starting point.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"shub" <shubtech@.gmail.com> wrote in message
news:1137513510.543961.83750@.g44g2000cwa.googlegroups.com...
>I am trying to write a generic restore script which could be used for
> all the similar restores we do on a dialy baisis. Basically the only
> thing that changes are the database names and all these backup are from
> all different servers.
> So I understand I would have to write a restore script with the MOVE
> option and I am planning on passing the parameter
> @.databasename,@.backupFileLocation.
> However is there anyway to determine the names of the file in the
> backup so that I could use those in my Restore Command without any user
> intervention? Unless I am able to do that I cannot get the entire
> process automated. From everthing I have read so far it suggests that I
> would have to run RESTORE FILELISTONLY command to get the file names
> and then edit my T_SQL command for each restore operation.
> Is there a cool way of doing without any intervention?
> Any help in this regard will be appreciated.
> Thanks
>|||This is exactly the kind of script I was looking for. Thank you very
much. I really appreciate it.

Question on getting the date value into a filename

I am running the following sqllitespeed backup script on Windows 2003 Server
against SQL Server 2000

sqllitespeed -Bdatabase -T -DNorthwind -FH:\MicrosoftSQLServer\BACKUPS\DATABACKUPS\NorthWi nd\northwind.bak

How could I place the date variable into the output filename?

I've tried variations of %date% etc but nada.

Any thoughts?

Thanks in advance.

Gerryhow do you executye this? Command line?, xp_cmdshell?|||It would be in a script run by an external controller.
A batch file.|||OK, batch file

I would use T-SQL to create said batch fiile, and as a matter of fact I would have a sql server job lauch said batch file

Question on generating SQL scripts using Enterprise Manager

Hi,

I was using enterprise manager to generate a script for my DB. I
scripted only my tables and views and in Options I picked all the
options EXCEPT "script Primary Keys, Foreign Keys and Constraits " (
which I was going to script seperately ). I noticed that the the
generated file still had all FKs and PKs scripted. When I additionally
unchecked the "script Full-Text indexes" option, it worked as expected.
Any idea why the full-text option causes all constraints to be
scripted. Using SQL server 2000.

Thanksdrdeadpan (vkat01-nospam@.yahoo.com) writes:
> I was using enterprise manager to generate a script for my DB. I
> scripted only my tables and views and in Options I picked all the
> options EXCEPT "script Primary Keys, Foreign Keys and Constraits " (
> which I was going to script seperately ). I noticed that the the
> generated file still had all FKs and PKs scripted. When I additionally
> unchecked the "script Full-Text indexes" option, it worked as expected.
> Any idea why the full-text option causes all constraints to be
> scripted. Using SQL server 2000.

Sounds like a bug.

It would be interesting to see a repro. That is a complete database script
with at most three tables with all these features, and when scripted in
EM displays all these problems. I doubt that the bug will ever be fixed
in Enterprise Manager, but since I'm on the SQL 2005 beta, I would like
to test if the problem is there as well.

By the way, did your tables actually have any full-text indexes?

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Erland for your response.

No, we have NO full text indexes defined. Our tables have a rather
large number of columns so rather than pasting the script here ,I
tested it again. This time I picked 3 tables to be scripted with the
following options.

Script Database
Script database users and database roles
Script object-level permissions
Script indexes
Script full-text indexes.

The above options once again scripted all PKs and FKs even though it
was not requested.

I reran the script without the full-text scripting option and it works
fine i.e no Pks and FKs. SO, I guess it is prefectly reproducable on
Sql Server 2000. I just wanted to make sure I was'nt seeing things.
Great website BTW.

DrD|||drdeadpan (vkat01-nospam@.yahoo.com) writes:
> No, we have NO full text indexes defined. Our tables have a rather
> large number of columns so rather than pasting the script here ,I
> tested it again. This time I picked 3 tables to be scripted with the
> following options.
> Script Database
> Script database users and database roles
> Script object-level permissions
> Script indexes
> Script full-text indexes.
> The above options once again scripted all PKs and FKs even though it
> was not requested.

You don't have to post your actual tables. It's enough to post a few
tables for which the problem appears.

Anyway, I was able to reproduce the problem in SQL 2000, but when I did
a quick test in SQL 2005, no constraints were brought it.

As I mentioned earlier, the likelyhood that this will be fixed in SQL2000
is about nil.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question on generating SQL scripts using Enterprise Manager

Hi,

I was using enterprise manager to generate a script for my DB. I
scripted selected my tables and views and in Options I picked all the
options. I noticed that the
generated file does not include FKs, EXTs or PKs scripted.
Any idea why the full-text options are not scripting the constraints?
Using SQL server 2000.

Thanks(chawes40@.yahoo.com) writes:
> I was using enterprise manager to generate a script for my DB. I
> scripted selected my tables and views and in Options I picked all the
> options. I noticed that the
> generated file does not include FKs, EXTs or PKs scripted.
> Any idea why the full-text options are not scripting the constraints?
> Using SQL server 2000.

Did the database actually have any full-text indexes? I tried to reproduce
the problem according your description, and my script included PKs and
FKs. But I don't even have full-text installed on my machine.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Question on @sql return value

hi,
I have a t-sql script as follows,
Declare @.sql varchar(1000)
set @.sql = select count(*) from table1
exec (@.sql)
I want to know if recordcount = 0
then go line1
else go line2
How do I assign the return value(recordcount)
ThanksDECLARE @.rc INT;
Declare @.sql varchar(1000)
set @.sql = 'select count(*) from table1';
exec (@.sql);
SET @.rc = @.@.ROWCOUNT;
PRINT @.rc
IF @.rc = 0
BEGIN
-- do this
END
ELSE
BEGIN
-- do that
END
--
Aaron Bertrand
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
> hi,
> I have a t-sql script as follows,
> Declare @.sql varchar(1000)
> set @.sql = select count(*) from table1
> exec (@.sql)
> I want to know if recordcount = 0
> then go line1
> else go line2
> How do I assign the return value(recordcount)
> Thanks
>|||That's what I want...
Thanks a lot
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:e%23qP6vQ1HHA.4712@.TK2MSFTNGP04.phx.gbl...
> DECLARE @.rc INT;
> Declare @.sql varchar(1000)
> set @.sql = 'select count(*) from table1';
> exec (@.sql);
> SET @.rc = @.@.ROWCOUNT;
> PRINT @.rc
> IF @.rc = 0
> BEGIN
> -- do this
> END
> ELSE
> BEGIN
> -- do that
> END
> --
> Aaron Bertrand
> SQL Server MVP
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
>> hi,
>> I have a t-sql script as follows,
>> Declare @.sql varchar(1000)
>> set @.sql = select count(*) from table1
>> exec (@.sql)
>> I want to know if recordcount = 0
>> then go line1
>> else go line2
>> How do I assign the return value(recordcount)
>> Thanks
>>
>

Question on @sql return value

hi,
I have a t-sql script as follows,
Declare @.sql varchar(1000)
set @.sql = select count(*) from table1
exec (@.sql)
I want to know if recordcount = 0
then go line1
else go line2
How do I assign the return value(recordcount)
ThanksDECLARE @.rc INT;
Declare @.sql varchar(1000)
set @.sql = 'select count(*) from table1';
exec (@.sql);
SET @.rc = @.@.ROWCOUNT;
PRINT @.rc
IF @.rc = 0
BEGIN
-- do this
END
ELSE
BEGIN
-- do that
END
Aaron Bertrand
SQL Server MVP
"mecn" <mecn2002@.yahoo.com> wrote in message
news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
> hi,
> I have a t-sql script as follows,
> Declare @.sql varchar(1000)
> set @.sql = select count(*) from table1
> exec (@.sql)
> I want to know if recordcount = 0
> then go line1
> else go line2
> How do I assign the return value(recordcount)
> Thanks
>|||That's what I want...
Thanks a lot
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in mess
age
news:e%23qP6vQ1HHA.4712@.TK2MSFTNGP04.phx.gbl...
> DECLARE @.rc INT;
> Declare @.sql varchar(1000)
> set @.sql = 'select count(*) from table1';
> exec (@.sql);
> SET @.rc = @.@.ROWCOUNT;
> PRINT @.rc
> IF @.rc = 0
> BEGIN
> -- do this
> END
> ELSE
> BEGIN
> -- do that
> END
> --
> Aaron Bertrand
> SQL Server MVP
>
> "mecn" <mecn2002@.yahoo.com> wrote in message
> news:Of9BwtQ1HHA.1484@.TK2MSFTNGP06.phx.gbl...
>