Error executing SQL Script from VB:NET

Hi.

I have a complete script from a Database.

But when I try to execute this script (script.txt) from VB.NET shows me many errors. Basically, the special caracters (vbreturn, vbtab, etc.)

How execute this script or format the text to generate the database without problems?

Thanks 4all

IF it is a SQL script, you need a SQL interpreter to run the script.

You would use either OSQL.exe or SQLCmd.exe. (Refer to Books Online for complete syntax details.)

OSQL -S servername -U username -P password -i Scriptfile

Similar command line parameters for SQLCmd.exe

|||You could also tell us what the errors are, what VB object you are trying to use, etc. There are lots of different reasons why this won't work, but I am sure that it could be made to work. Using sqlcmd, osql, or isql would be the easiest way to go, (it changes from one version of SQL Server to the next for the past few versions but SQLCMD is just a .NET program running your batches, so we can make it happen if you will describe more about your issues.

Error during SQL 2000 SP3a upgrade

I received the following error while executing the
REPLCOM.SQL script during the upgrade.
[DBNETLIB]General network error. Check your network
documentation.
[DBNETLIB]ConnectionRead (recv()).
This was the only output in REPLCOM.OUT.
Any ideas, I upgraded other servers and this did not
happen. I was upgrading from SQL 2000 SP2.
Mark,
Is the SQL Server service pack directory you are installing from on the
network or on your local machine? Perhaps there was a network outage
whilst the upgrade was running?
Can you copy the install directory locally and run it again?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark Gozick wrote:
> I received the following error while executing the
> REPLCOM.SQL script during the upgrade.
> [DBNETLIB]General network error. Check your network
> documentation.
> [DBNETLIB]ConnectionRead (recv()).
> This was the only output in REPLCOM.OUT.
> Any ideas, I upgraded other servers and this did not
> happen. I was upgrading from SQL 2000 SP2.
sql

Error during SQL 2000 SP3a upgrade

I received the following error while executing the
REPLCOM.SQL script during the upgrade.
[DBNETLIB]General network error. Check your network
documentation.
[DBNETLIB]ConnectionRead (recv()).
This was the only output in REPLCOM.OUT.
Any ideas, I upgraded other servers and this did not
happen. I was upgrading from SQL 2000 SP2.Mark,
Is the SQL Server service pack directory you are installing from on the
network or on your local machine? Perhaps there was a network outage
whilst the upgrade was running?
Can you copy the install directory locally and run it again?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark Gozick wrote:
> I received the following error while executing the
> REPLCOM.SQL script during the upgrade.
> [DBNETLIB]General network error. Check your network
> documentation.
> [DBNETLIB]ConnectionRead (recv()).
> This was the only output in REPLCOM.OUT.
> Any ideas, I upgraded other servers and this did not
> happen. I was upgrading from SQL 2000 SP2.

Error during SQL 2000 SP3a upgrade

I received the following error while executing the
REPLCOM.SQL script during the upgrade.
[DBNETLIB]General network error. Check your network
documentation.
[DBNETLIB]ConnectionRead (recv()).
This was the only output in REPLCOM.OUT.
Any ideas, I upgraded other servers and this did not
happen. I was upgrading from SQL 2000 SP2.Mark,
Is the SQL Server service pack directory you are installing from on the
network or on your local machine? Perhaps there was a network outage
whilst the upgrade was running?
Can you copy the install directory locally and run it again?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
Mark Gozick wrote:
> I received the following error while executing the
> REPLCOM.SQL script during the upgrade.
> [DBNETLIB]General network error. Check your network
> documentation.
> [DBNETLIB]ConnectionRead (recv()).
> This was the only output in REPLCOM.OUT.
> Any ideas, I upgraded other servers and this did not
> happen. I was upgrading from SQL 2000 SP2.

Error creating programmatically a subscription with a parameter

Hi,
I'm using a script (through rs utility) to automate the creation of a
lot of subscriptions. Everything works fine if I don't use a parameter
in my report, but when I use it I always get the Error Code
"rsInvalidParameters", with the Error Message
"The value of parameter 'Parameters' is not valid. Check the
documentation for information about valid values"
What I'm doing is:
...
Dim parameter As New ParameterValue()
parameter.Name = "storeCod"
parameter.Value = "318"
Dim parameters(1) As ParameterValue
parameters(0) = parameter
then, when I call rs.CreateSubscription(), I pass the "parameters"
array as the last parameter of the method.
It seems weird because if I debug using the GetReportParameters method
to see exactly the parameter name and value, it's right, the name is
storeCod and one of the valid values is "318".
What can be wrong?
Thanks in advance,
Vinicius BellinoTry setting the DefaultValue for the parameter to that value.
--
| From: vbellino@.uol.com.br (Vinicius Bellino)
| Newsgroups: microsoft.public.sqlserver.reportingsvcs
| Subject: Error creating programmatically a subscription with a parameter
| Date: 30 Mar 2005 08:43:34 -0800
| Organization: http://groups.google.com
| Lines: 30
| Message-ID: <93d12908.0503300843.7da9f4f5@.posting.google.com>
| NNTP-Posting-Host: 200.215.178.109
| Content-Type: text/plain; charset=ISO-8859-1
| Content-Transfer-Encoding: 8bit
| X-Trace: posting.google.com 1112201014 31851 127.0.0.1 (30 Mar 2005
16:43:34 GMT)
| X-Complaints-To: groups-abuse@.google.com
| NNTP-Posting-Date: Wed, 30 Mar 2005 16:43:34 +0000 (UTC)
| Path:
TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
ne.de!news.glorb.com!postnews.google.com!not-for-mail
| Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:46481
| X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
|
| Hi,
|
| I'm using a script (through rs utility) to automate the creation of a
| lot of subscriptions. Everything works fine if I don't use a parameter
| in my report, but when I use it I always get the Error Code
| "rsInvalidParameters", with the Error Message
| "The value of parameter 'Parameters' is not valid. Check the
| documentation for information about valid values"
|
| What I'm doing is:
| ...
| Dim parameter As New ParameterValue()
| parameter.Name = "storeCod"
| parameter.Value = "318"
|
| Dim parameters(1) As ParameterValue
| parameters(0) = parameter
|
| then, when I call rs.CreateSubscription(), I pass the "parameters"
| array as the last parameter of the method.
|
| It seems weird because if I debug using the GetReportParameters method
| to see exactly the parameter name and value, it's right, the name is
| storeCod and one of the valid values is "318".
|
| What can be wrong?
|
| Thanks in advance,
|
| Vinicius Bellino
||||Brad,
If I set the default value, it works, but I need to create
subscriptions with different parameter values for the same Report. I'm
reading a text file that defines which email must receive each
parameter value. This text file is something like:
ReportName;SharedScheduleName;
email1;StoreCode1;
email2;StoreCode2;
email3;StoreCode3;
So, I can't set the default parameter to StoreCode1 then create the
subscription to email1 then set to StoreCode2 and create a new
subscription to email2 because it won't work properly. Both
subscriptions will use the last default value set.
Do you happen to know what is going on? Because I think that setting
parameters programmatically is supposed to work well since it's a
"standard" need.
Thanks,
Vinicius Bellino
bradsy@.Online.Microsoft.com ("Brad Syputa - MS") wrote in message news:<o#jQ82UNFHA.2136@.TK2MSFTNGXA03.phx.gbl>...
> Try setting the DefaultValue for the parameter to that value.
> --
> | From: vbellino@.uol.com.br (Vinicius Bellino)
> | Newsgroups: microsoft.public.sqlserver.reportingsvcs
> | Subject: Error creating programmatically a subscription with a parameter
> | Date: 30 Mar 2005 08:43:34 -0800
> | Organization: http://groups.google.com
> | Lines: 30
> | Message-ID: <93d12908.0503300843.7da9f4f5@.posting.google.com>
> | NNTP-Posting-Host: 200.215.178.109
> | Content-Type: text/plain; charset=ISO-8859-1
> | Content-Transfer-Encoding: 8bit
> | X-Trace: posting.google.com 1112201014 31851 127.0.0.1 (30 Mar 2005
> 16:43:34 GMT)
> | X-Complaints-To: groups-abuse@.google.com
> | NNTP-Posting-Date: Wed, 30 Mar 2005 16:43:34 +0000 (UTC)
> | Path:
> TK2MSFTNGXA03.phx.gbl!TK2MSFTNGP08.phx.gbl!newsfeed00.sul.t-online.de!t-onli
> ne.de!news.glorb.com!postnews.google.com!not-for-mail
> | Xref: TK2MSFTNGXA03.phx.gbl microsoft.public.sqlserver.reportingsvcs:46481
> | X-Tomcat-NG: microsoft.public.sqlserver.reportingsvcs
> |
> | Hi,
> |
> | I'm using a script (through rs utility) to automate the creation of a
> | lot of subscriptions. Everything works fine if I don't use a parameter
> | in my report, but when I use it I always get the Error Code
> | "rsInvalidParameters", with the Error Message
> | "The value of parameter 'Parameters' is not valid. Check the
> | documentation for information about valid values"
> |
> | What I'm doing is:
> | ...
> | Dim parameter As New ParameterValue()
> | parameter.Name = "storeCod"
> | parameter.Value = "318"
> |
> | Dim parameters(1) As ParameterValue
> | parameters(0) = parameter
> |
> | then, when I call rs.CreateSubscription(), I pass the "parameters"
> | array as the last parameter of the method.
> |
> | It seems weird because if I debug using the GetReportParameters method
> | to see exactly the parameter name and value, it's right, the name is
> | storeCod and one of the valid values is "318".
> |
> | What can be wrong?
> |
> | Thanks in advance,
> |
> | Vinicius Bellino
> |

Error converting datatypes

Hi,

This script gets run by a job every 3 mins, and it's falling over with an "Error converting varchar value... to column of datatype int", and I think it's on this line:

select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'

...where I'm trying to build up a string by dropping an ID number (datatype int) into it.

So I tried:

select @.sbj1='New ICNA Forum Post (ThreadID='+CAST(@.existingID AS varchar(100))+')'

and:

select @.sbj1='New ICNA Forum Post (ThreadID='+CONVERT(varchar(100), @.existingID)+')'

Both of these result in the job running successfully, but no emails get sent and the job history shows the error "Incorrect syntax near 'Forum'." On the good side, it's supposed to be looping through the email-sending bit 4 times (there are currently 4 users) and sure enough, it repeats that error message 4 times.

The full script follows below. I'd be hugely grateful if anyone could point out what I'm doing wrong, and how to do it right.

Cheers.

Declare @.hMessage varchar(255),@.msg_id varchar(255)
Declare @.MessageText varchar(8000),@.message varchar(8000)
Declare @.MessageSubject varchar(8000),@.subject varchar(8000)
Declare @.Origin varchar (8000), @.originator_address varchar(8000)

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT

WHILE @.hMessage IS NOT NULL
BEGIN

exec master.dbo.xp_readmail
@.msg_id=@.hMessage,
@.message=@.MessageText OUT,
@.subject=@.MessageSubject OUT,
@.originator_address=@.Origin OUT

IF ((SELECT COUNT(*) FROM forum_users WHERE email = @.Origin) = 1) -- IF email from forum-recognised address
BEGIN
IF (CHARINDEX('(ThreadID=', @.MessageSubject)>0) -- IF email has a thread ID
BEGIN
DECLARE @.existingID int, @.em1 varchar(100), @.bdy1 varchar(8000), @.sbj1 varchar(500)
SELECT @.existingID=CAST(SUBSTRING(@.MessageSubject, (CHARINDEX('=', @.MessageSubject)+1), (CHARINDEX(')', @.MessageSubject)-(CHARINDEX('=', @.MessageSubject)+1))) AS int)
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.existingID)

-- Do mailing

declare em_cursor1 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor1
fetch next from em_cursor1
into @.em1

while @.@.FETCH_STATUS=0
begin
select @.bdy1='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj1='New ICNA Forum Post (ThreadID='+@.existingID+')'
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
fetch next from em_cursor1
into @.em1
end
close em_cursor1
deallocate em_cursor1

END
ELSE -- IF email has no thread ID
BEGIN
DECLARE @.newID int, @.em2 varchar(100), @.bdy2 varchar(8000), @.sbj2 varchar(500) -- Create a new thread record and use the resulting ID to add a thread_post record
INSERT INTO forum_threads (subject) VALUES (@.MessageSubject)
SELECT @.newID=@.@.IDENTITY
INSERT INTO forum_posts (body, thread_id) VALUES (@.MessageText, @.newID)

-- Do mailing

declare em_cursor2 cursor for
SELECT email FROM forum_users WHERE email_option='yes'
open em_cursor2
fetch next from em_cursor2
into @.em2

while @.@.FETCH_STATUS=0
begin
select @.bdy2='New ICNA Forum Post:'+CHAR(13)+CHAR(10)+CHAR(13)+CHAR(10)+@.Messag eText
select @.sbj2='New ICNA Forum Post (ThreadID='+@.newID+')'
exec master.dbo.xp_sendmail @.em2,@.bdy2,@.sbj2
fetch next from em_cursor2
into @.em2
end
close em_cursor2
deallocate em_cursor2

END
END

SET @.hMessage = NULL

EXEC master.dbo.xp_findnextmsg @.unread_only='true',@.msg_id=@.hMessage OUT
ENDHave you tried substituing the xp_sendmail with SELECT just to see if you have formatted very thing correctly.|||Ah. Did I mention that my grasp of SQL and its debugging techniques was a little sparse?

Thanks very much for your help, but could you possibly explain how do do that?

Cheers.|||OK;

In your script you have a line like:
exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1
RewriteSELECT 'master.dbo.xp_sendmail', @.em1,@.bdy1,@.sbj1
Do this for all xp_sendmail. This may shine some light.|||Aha! Well, at least I've learnt how to get some debugging output. Unfortunately I'm none the wiser as to why sendmail isn't working.

It loops 4 times, once for each email address in my forum_users table. I'm sending the "trigger" email from a hotmail address (needs to be external to our network) and thus for each email it tries to send the subject and body look like:

@.sbj1:
New ICNA Forum Post (ThreadID=24)

@.bdy1:
New ICNA Forum Post: Friday morning test 1 __________________________________________________ _______________ Join the worlds largest e-mail service with MSN Hotmail. http://www.hotmail.com

Which are pretty well exactly what I was expecting - so why on earth is it falling over? :(|||At last, got it working! It didn't like this line:

exec master.dbo.xp_sendmail @.em1,@.bdy1,@.sbj1

when I replaced it with:

exec master.dbo.xp_sendmail
@.recipients=@.em1,
@.message=@.bdy1,
@.subject=@.sbj1

it worked fine. I don't know why, and I don't care :) It works...

Error attempting restore of full/differential backup

I am running the following script to attempt a restore of a differential backup:

RESTORE DATABASE AdventureWorks
FROM DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorks.bak'
WITH
NORECOVERY
GO
RESTORE DATABASE AdventureWorks
FROM DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH RECOVERY
GO

I thought this was the way to do it. It does restore the full backup, but on the attempt to restore the differential backup, I get the following error:

Msg 3136, Level 16, State 1, Line 1

This differential backup cannot be restored because the database has not been restored to the correct earlier state.

Msg 3013, Level 16, State 1, Line 1

RESTORE DATABASE is terminating abnormally.

Does anyone know what this means? Do I have to use "with recovery" on the first restore? (The sample I took this from used "with norecovery")

The original backups were done with SQL Agent scheduled jobs. The script for the full backup is:

BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorks.bak'

The script for the differential backup is:

BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT

All I can say is, it's a good thing I am testing this out with non-critical data, because I obviously don't know what I am doing. (Sorry, I'm primarily a programmer, not a DBA) Can anyone help?

Thanks

Are you running SQL 2000 or 2005, because you can't restore AdventureWorks on SQL 2000?|||I am running SQL 2005 -- sorry, forgot to mention.|||I discovered the problem. In my differential backup

BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT

I should not be using "INIT".

I thought that since you just use the last differential backup file, it would make sense to overwrite it every time, so I put in the INIT. However, it doesn't work. You have to have all the differential files saved, even though you only use the last one.

It doesn't make sense to me, but it appears to be the answer, because when I do it that way the restore works.

error after renaming server hosting SQL Server 2005

STEPS LEADING UP TO THE ERROR

1. renamed server
2. rebooted
3. ran the following script:

-- delete the server name currently stored in SQL Server
EXEC sp_dropserver @.@.servername
GO

-- set the server name stored in SQL Server to match the server name
DECLARE @.srvr sysname
SET @.srvr = CAST(SERVERPROPERTY('ServerName') AS sysname)
EXEC sp_addserver @.srvr, local
GO

-- Enable a loopback linked server after renaming the server.
DECLARE @.srvr sysname
SET @.srvr = CAST(SERVERPROPERTY('ServerName') AS sysname)
EXEC sp_serveroption @.srvr, 'data access', true
GO

4. rebooted

ERROR DESCRIPTION

Upon starting SSMS, the following error dialog appeared (Shift+Ctrl-C was used to capture the dialog box as text):

Microsoft SQL Server Management Studio

OK

As you can see the error dialog box is empty. It appears over what appears to be a blank SSMS. After clicking OK, SSMS appeared as it should.

I actually have the same problem and I haven't renamed anything. When I start SQL Server Managemnt Studio I get the blank message with the OK button as well.

Any suggestions?

Error adding a new job

I'm creating a new job and getting a failure when applying it to the server. When I script it is seems to work fine via SQL management studio. I haven't added any steps or a schedule yet, could this be the cause of the error the errors are a bit nebulous to say the least (hopefully the sql server errors will get passed through in next service pack)! Anyway heres the code, its a mixture of smo and my apps code:

//refresh jobs from server, releasing objects..

this.JobServer().Jobs.Refresh(true);

//create new job

m_oJob = new Agent.Job();

//loop through checking name (non case specific)

while(bExists==true)

{

n++;

bExists = false;

foreach(Agent.Job oJob in this.JobServer().Jobs)

{

if (oJob.Name.ToLower()==string.Format("[{0}] {1}",strName.ToLower() ,n.ToString()).ToLower() )

bExists = true;

}

}

m_oJob.Parent = this.JobServer();

//set name

m_oJob.Name = string.Format("[{0}] {1}",strName,n.ToString());

m_oJob.Description = String.Format ("Schedule for analytics job {0}",m_oScheduleJob.Name);

//set job owner to login

m_oJob.OwnerLoginName = m_oScheduleJob.Owner;

m_oJob.IsEnabled = true;

m_oJob.StartStepID = 1;

m_oJob.EventLogLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.OnFailure;

m_oJob.EmailLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

m_oJob.NetSendLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

m_oJob.PageLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

m_oJob.OperatorToEmail = "";

m_oJob.OperatorToNetSend = "";

m_oJob.OperatorToPage = "";

m_oJob.DeleteLevel = Microsoft.SqlServer.Management.Smo.Agent.CompletionAction.Never;

if (!((Agent.JobServer)this.JobServer()).JobCategories.Contains("Analytics Job"))

{

Agent.JobCategory c = new Agent.JobCategory(this.JobServer(), "Analytics Job");

c.Create();

}

m_oJob.Category = "Analytics Job";

//assign to server

m_oJob.ApplyToTargetServer("myserver"); --> fails

m_oJob.ApplyToTargetServer("(local)"); --> fails

m_oJob.ApplyToTargetServer("myserver.dnssuffix"); --> fails

Ok figured out that you need to call the create before targeting server (should have looked at previous posts more closely, informative errors would have been good though - resorting to scripting and checking errors from sql for clues). Once saved need to call alter to change the objects however calling targetserver twice triggers an error anyone know how I can tell if its been called already?

|||I'm shooting from the hip here, so I will taking a closer look if I have a little more time, but I think that Job.EnumTargetServers() will do the trick. Let me know if that worked...|||Yes thats correct, thanks.