Solved

SQL script not working.

Posted on 2006-11-05
20
707 Views
Last Modified: 2008-03-04
@echo off

IF "%1"=="" GOTO Usage

SET DatabaseName=%1
SET Server=sql2005
SET User=sa
SET Password=Password123


echo.
echo About to create the tables for Study database %DatabaseName%
echo on server %Server%
echo.
pause

sqlcmd -S %Server% -U %User% -P %Password% -d %DatabaseName% -i ..\Database\RG10_Study_Create.sql > %Server%_%DatabaseName%_CreateTables.txt
echo.
echo See %Server%_%DatabaseName%_CreateTables.txt log file.
echo.

GOTO End

:Usage
echo ***************************************************
echo Invalid parameter!
echo.
echo Please provide database name.
echo.
echo CreateStudyTables [Database name]
echo.
echo Ex: CreateStudyTables RG_Study_Oct19
echo.
echo ***************************************************

:End


pause



C:\Voxco.ReportGenerator\Setup>createstudydb RG_StudyMatt_05Nov

About to create a Study database called RG_StudyMatt_05Nov
on server FULLBLOWN

Press any key to continue . . .
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

See FULLBLOWN_RG_StudyMatt_05Nov_CreateDB.txt log file.


About to create the tables for Study database RG_StudyMatt_05Nov
on server sql2005

Press any key to continue . . .
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

See sql2005_RG_StudyMatt_05Nov_CreateTables.txt log file.
0
Comment
Question by:mathieu_cupryk
  • 9
  • 7
  • 4
20 Comments
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17878408
Have you verified, via client tools, that you can indeed connect to a server called FULLBLOWN using those user credentials?

Why is your script output saying the server is 'FULLBOWN' when inside the script it is defined as SQL2005?

Have you gone to the surface are configuration tool and enabled TCP/IP connections?
0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17878450
1) have u enabled sql authentication. and is passowrd correct.
2) have u enabled remote connectoins. right click server-> properties goto connectios page and select remote connections.
0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17878452
i tink SQL2005 is the Server Name and the database in it is called FULLBOWN
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17878638
In your output, this message here:

About to create a Study database called RG_StudyMatt_05Nov
on server FULLBLOWN

Implies that your script is trying to connect to a server called FULLBLOWN



However this line here:

SET Server=sql2005


Has set the server to be sql2005.




Anyway we'll get back to that once we verify that you have enabled remote connections as per my and Wizilling's posts.

0
 

Author Comment

by:mathieu_cupryk
ID: 17878908
This is how I connect:

Database Engine

FULLBLOWN\SQL2005

SQL Server Authentication

sa

Password123

Is there a way to check the server.

I created the database SQL2005

0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17878951
There is a way to check. Do you have SQL Sevrer Management studio installed? Start it and verify that you can connect using the parameters that you've just posted.




There is a fair bit of confusion here...

1. This line of your batch (not SQL) script sets the %server% variable to SQL2005:

SET Server=sql2005

2. This line of your output says that actually your %server% variable is FULLBLOWN:

About to create a Study database called RG_StudyMatt_05Nov
on server FULLBLOWN

3. Your last post said that you need to connect to FULLBLOWN\SQL2005 so in fact neither of the above are correct, you need to be connecting to a server called FULLBLOWN\SQL2005. Therefore this is what you should be setting your variable to and this is what you should be seeing in your output.


4. Lastly, your SQLCMD parameters imply that you are trying to connect to a database that you haven't actually created yet. This won't work either.




Anyway, first verify that you can connect to the database server then we will adress the rest of the issues.
0
 

Author Comment

by:mathieu_cupryk
ID: 17878975
First I have to connect to FULLBLOWN\SQL2005

then there is a database  RG_StudyMatt_05Nov.

C:\Voxco.ReportGenerator\Setup>createstudydb RG_StudyMatt_05Nov

About to create a Study database called RG_StudyMatt_05Nov
on server FULLBLOWN

Press any key to continue . . .
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

See FULLBLOWN_RG_StudyMatt_05Nov_CreateDB.txt log file.


About to create the tables for Study database RG_StudyMatt_05Nov
on server FULLBLOWN

Press any key to continue . . .
Sqlcmd: Error: Microsoft SQL Native Client : An error has occurred while establi
shing a connection to the server. When connecting to SQL Server 2005, this failu
re may be caused by the fact that under the default settings SQL Server does not
 allow remote connections..
Sqlcmd: Error: Microsoft SQL Native Client : Login timeout expired.

See FULLBLOWN_RG_StudyMatt_05Nov_CreateTables.txt log file.

0
 

Author Comment

by:mathieu_cupryk
ID: 17878990
C:\Voxco.ReportGenerator\Setup>createstudydb RG_StudyMatt_06Nov

About to create a Study database called RG_StudyMatt_06Nov
on server FULLBLOWN\SQL2005

Press any key to continue . . .
The system cannot find the path specified.

See FULLBLOWN\SQL2005_RG_StudyMatt_06Nov_CreateDB.txt log file.


About to create the tables for Study database RG_StudyMatt_06Nov
on server FULLBLOWN\SQL2005

Press any key to continue . . .
The system cannot find the path specified.

See FULLBLOWN\SQL2005_RG_StudyMatt_06Nov_CreateTables.txt log file.

Press any key to continue . . .

About to create the Procedures for Study database RG_StudyMatt_06Nov
on server FULLBLOWN\SQL2005

Press any key to continue . . .
The system cannot find the path specified.

See FULLBLOWN\SQL2005_RG_StudyMatt_06Nov_CreateSPs.txt log file.

Press any key to continue . . .
Press any key to continue . . .
0
 
LVL 30

Expert Comment

by:nmcdermaid
ID: 17879044
So in the second one you changed the server to be the correct server name and it connected OK.


Now, this line:

>> The system cannot find the path specified.

Is probably because it can't find ..\Database\RG10_Study_Create.sql

Try using a full path instead of a relative one.

i.e. C:\Folder\Database\RG10_Study_Create.SQL (or whatever is valid) just to get it working.

Then think about the best way to make this path independent.
0
 

Author Comment

by:mathieu_cupryk
ID: 17882114
@echo off

IF "%1"=="" GOTO Usage

SET DatabaseName=%1
SET Server=FULLBLOWN\SQL2005
SET User=sa
SET Password=Password123


echo.
echo About to create a Study database called %DatabaseName%
echo on server %Server%
echo.
pause

echo CREATE DATABASE %DatabaseName% > CreateDatabase.sql
sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %Server%_%DatabaseName%_CreateDB.txt
echo.
echo See %Server%_%DatabaseName%_CreateDB.txt log file.
echo.

CALL CreateStudyTables %DatabaseName%
CALL CreateStudySPs %DatabaseName%

GOTO End

:Usage
echo ***************************************************
echo Invalid parameter!
echo.
echo Please provide database name.
echo.
echo CreateStudyDB [Database name]
echo.
echo Ex: CreateStudyDB RG_Study_Oct19
echo.
echo ***************************************************

:End

pause

Where should I fix this?
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 

Author Comment

by:mathieu_cupryk
ID: 17882778
HResult 0x35, Level 16, State 1
Named Pipes Provider: Could not open a connection to SQL Server [53].
0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17885553
can u check throught sqlcmd.
start a command prompt and type cmd
in the prompt window type
sqlcmd -S FULLBLOWN\SQL2005 -U sa -P Password1
press entere

type a select statement to check if u actuall in. type this
sp_helpdb
go

what does that return

0
 

Author Comment

by:mathieu_cupryk
ID: 17885643
name
                                                 db_size       owner

                                dbid   created     status







            compatibility_level
--------------------------------------------------------------------------------
------------------------------------------------ ------------- -----------------
--------------------------------------------------------------------------------
------------------------------- ------ ----------- -----------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------- -------------------
AdventureWorks
                                                     165.94 MB FULLBLOWN\Matt

                                     8 Nov  5 2006 Status=ONLINE, Updateability=
                                     8 Nov  5 2006 Status=ONLINE, Updateability=
READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_L
atin1_General_CP1_CI_AS, SQLSortOrder=52, IsAnsiNullsEnabled, IsAnsiPaddingEnabl
ed, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsA
utoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled



                             90
AdventureWorksDW
                                                      70.50 MB FULLBLOWN\Matt

                                     7 Nov  5 2006 Status=ONLINE, Updateability=
READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_L
atin1_General_CP1_CI_AS, SQLSortOrder=52, IsAnsiNullsEnabled, IsAnsiPaddingEnabl
ed, IsAnsiWarningsEnabled, IsArithmeticAbortEnabled, IsAutoCreateStatistics, IsA
utoUpdateStatistics, IsFullTextEnabled, IsNullConcat, IsQuotedIdentifiersEnabled



                             90
master
                                                       4.50 MB sa

                                     1 Apr  8 2003 Status=ONLINE, Updateability=
READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=611, Collation=SQL_L
atin1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateSt
atistics




                             90
model
                                                       1.69 MB sa

                                     3 Apr  8 2003 Status=ONLINE, Updateability=
READ_WRITE, UserAccess=MULTI_USER, Recovery=FULL, Version=611, Collation=SQL_Lat
in1_General_CP1_CI_AS, SQLSortOrder=52, IsAutoCreateStatistics, IsAutoUpdateStat
istics

keeps going...

0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17885844
ok thats shows that ur connection is ok.


try this
sqlcmd-S FULLBLOWN\SQL2005 -U sa -P Password1 -i CreateDatabase.sql

your problem might be here    > %Server%_%DatabaseName%_CreateDB.txt

%Server% cannot take a \ value

try this:

sqlcmd-S FULLBLOWN\SQL2005 -U sa -P Password1 -i CreateDatabase.sql   > %DatabaseName%_CreateDB.txt
0
 

Author Comment

by:mathieu_cupryk
ID: 17885900
Msg 1801, Level 16, State 3, Server FULLBLOWN\SQL2005, Line 1
Database 'RG_studyMATT_NOV07' already exists.
0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17885975
good .. try a different database name.. or drop that db if u dont use it.
0
 
LVL 13

Accepted Solution

by:
Wizilling earned 500 total points
ID: 17886007
Oh .. this CreateDatabase.sql script might have the database name hard coded into it.
check and rename as neccessary
0
 

Author Comment

by:mathieu_cupryk
ID: 17886030
This command created the database that was hard coded in CreateDatabase.sql


D:\Voxco.ReportGenerator\Setup>sqlcmd -S FULLBLOWN\SQL2005 -U sa -P Password123
-i CreateDatabase.sql   > %DatabaseName%_CreateDB.txt

Now what should I do about the following:

@echo off

IF "%1"=="" GOTO Usage

SET DatabaseName=%1
SET Server=FullBlown\sql2005
SET User=sa
SET Password=Password123


echo.
echo About to create a Study database called %DatabaseName%
echo on server %Server%
echo.
pause

echo CREATE DATABASE %DatabaseName% > CreateDatabase.sql
sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %Server%_%DatabaseName%_CreateDB.txt
echo.
echo See %Server%_%DatabaseName%_CreateDB.txt log file.
echo.

CALL CreateStudyTables %DatabaseName%
CALL CreateStudySPs %DatabaseName%

GOTO End

:Usage
echo ***************************************************
echo Invalid parameter!
echo.
echo Please provide database name.
echo.
echo CreateStudyDB [Database name]
echo.
echo Ex: CreateStudyDB RG_Study_Oct19
echo.
echo ***************************************************

:End

pause
0
 

Author Comment

by:mathieu_cupryk
ID: 17886154
I removed %Server%_

and now after it works.

0
 
LVL 13

Expert Comment

by:Wizilling
ID: 17886179
change the line sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %Server%_%DatabaseName%_CreateDB.txt
TO --> sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %DatabaseName%_CreateDB.txt

ie remove the %Server% bit in the filenam because its tries to create afilename like FULLBROWN\SQL2005_ db name _ createdb.txt

\ is an illegal character in filename.


or u can use this little modified one.. i added SET ServerAddress = FullBlown_sql2005

@echo off

IF "%1"=="" GOTO Usage

SET DatabaseName=%1
SET ServerAddress =FullBlown_sql2005
SET Server=FullBlown\sql2005
SET User=sa
SET Password=Password123


echo.
echo About to create a Study database called %DatabaseName%
echo on server %Server%
echo.
pause

echo CREATE DATABASE %DatabaseName% > CreateDatabase.sql
sqlcmd -S %Server% -U %User% -P %Password% -i CreateDatabase.sql > %ServerAddress%_%DatabaseName%_CreateDB.txt
echo.
echo See %Server%_%DatabaseName%_CreateDB.txt log file.
echo.

CALL CreateStudyTables %DatabaseName%
CALL CreateStudySPs %DatabaseName%

GOTO End

:Usage
echo ***************************************************
echo Invalid parameter!
echo.
echo Please provide database name.
echo.
echo CreateStudyDB [Database name]
echo.
echo Ex: CreateStudyDB RG_Study_Oct19
echo.
echo ***************************************************

:End

pause
0

Featured Post

Complete Microsoft Windows PC® & Mac Backup

Backup and recovery solutions to protect all your PCs & Mac– on-premises or in remote locations. Acronis backs up entire PC or Mac with patented reliable disk imaging technology and you will be able to restore workstations to a new, dissimilar hardware in minutes.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now