Bcp utility with stored procedure

Guys,

I have stored proc sp_generate_insert which will generate insert scripts for the tables. When I run the stored Proc
from the management studio it runs fine. But when I run through stored proc as part of BCP utility I get this error.

'SQLState = 42000, NativeError = 536
Error = [Microsoft][SQL Native Client][SQL Server]Invalid length parameter passed to the SUBSTRING function.'

Execute dev.dbo.sp_generate_inserts 'auth' runs fine from management studio and generates inserts for auth table.

When I run the same proc as part of the following stored proc with bcp utility I get the error.

alter PROCEDURE INSERTTEST2 ( @.FILEPATH NVARCHAR(50))
AS
DECLARE @.cmd varchar(2000)
BEGIN
set @.cmd = 'bcp.exe "EXEC dev.dbo.SP_GENERATE_INSERTS auth" '
+ 'QUERYOUT' + ' ' +@.filePath+ '.sql ' +'-S ' +
'NV-DEVSQL3' + ' -q ' + ' -c -T -e' + @.filePath+'.log -o '
+ @.filePath+ '_out.log'
select @.cmd -- + '...'
EXEC master.dbo.xp_cmdShell @.cmd
END

Any suggestions or inputs would help.

Thanks

The problem lies within the proc, so we need to see that code.

Though usually, this error comes from statements where the length parameter in SUBSTRING becomes negative.

If you're dynamically trying to set how large chunk substring should take, and that variable becomes negative, then this error happens.

Since the problem seems to occur or not depending on method of connecting, it may suggest that there are different settings that may be the root cause.. (ie ANSI DEFAULTS etc)

Could this be it perhaps?

/Kenneth

|||kenneth,

Thank you for you reply.

I dont know if the problem is setting defaults on the database or the connection, more so since the stored proc - sp_generate_scripts runs fine from the managment studio.

Anyways the code for stored proc is available at the following link

http://vyaskn.tripod.com/code/generate_inserts_2005.txt

Any suggestions/inputs would help

Thanks

|||

I played around a bit with the proc and found some 'interesting' stuff...

I think your problem may be that you don't use the -d parameter in your bcp command, so you're not ending up in the right db.

The reason this matters may be the same that I found, but didn't notice at first...

(I tried it on SQL Server 2000).
First when compiled, there was a msg about not finding sys.sp_MS_marksystemobject, but the proc compiled anyway, so I tried it out.

Got the same message as you a couple of times, but found that only if I was in a db other than master. Made a usertable in master, then it worked. =/

So, fixed the 'sys.sp_MS_marksystemobject' to 'sp_MS_marksystemobject' and recompiled (since the former doesn't exist in 2000, only in 2005) and tried again. Now all is smooth, and it works like it's supposed to.

Apparently, the proc needs to be marked as a systemobject, else you may get these 'db-scope' issues, so check out if this is the problem.

/Kenneth

BCP using ODBC - problem with unique identifier

Hi guys
I'm having a nasty problem with bulk copying into a table that has
unique identifier column. I'm coding on C++, using ODBC driver.
I'm coping from a file containing UID description like this:
{43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
(I also tried a non-bracket version)
and in the DB table I get:
4233347B-4235-4433-452D-353238302D34
which seems random at first sight, but it is:
[B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.
and my question is: what the hell?
my code look like this:
if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
ret = -1;
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
SQLUNIQUEID, colNo) == FAIL){
ret = -1;
}
(I also tried a VARLEN version:)
if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
(LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
ret = -1;
}
and then stuff like sendrow ans save:
if (bcp_sendrow(m_hDbproc) == FAIL)
return -1;
if (bcp_batch (m_hDbproc) == -1)
return -1;
I also tried specyfiling the column type in the m_hDbproc handle as
SQLUNIQUEID, but either I'm doing something wrong, or this just isn't
the way of a bulk copy samurai:
INT * pValue=new INT;
INT *pLen=new INT;
*pValue=0x24;
bcp_setcolfmt(m_hDbproc,1,BCP_FMT_TYPE,pValue,4);
So like, PLEASE help me on this. I need to get this working by last
monday :]
Thanx, M.(mpietrzyk@.autograf.pl) writes:
> I'm having a nasty problem with bulk copying into a table that has
> unique identifier column. I'm coding on C++, using ODBC driver.
> I'm coping from a file containing UID description like this:
> {43B5B3DE-5280-4CBF-B357-D9E57651F0D1}
> (I also tried a non-bracket version)
> and in the DB table I get:
> 4233347B-4235-4433-452D-353238302D34
> which seems random at first sight, but it is:
> [B34{]-[B5]-[D3]-[E-]-[5280-4] - with chars read binary as hex.
> and my question is: what the hell?
> my code look like this:
> if (bcp_init (m_hDbproc,tableName, NULL, NULL, DB_IN) == FAIL)
> ret = -1;
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, 16, (LPCBYTE)NULL, 0,
> SQLUNIQUEID, colNo) == FAIL){
> ret = -1;
> }
> (I also tried a VARLEN version:)
> if (bcp_bind (m_hDbproc, (LPCBYTE)data, 0, SQL_VARLEN_DATA,
> (LPCBYTE)delimiter, 1, SQLVARCHAR, colNo) == FAIL){
> ret = -1;
> }
First you need to decide in which format is the UID? It if is in text,
you should specify SQLVARCHAR for the data type. Only if you have the
UID as binary, you should specify SQLUNIQUEID.
Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
It depends on what's in delimiter, but since a GUIO is always 36
characters (without braces), you could just as well specify 36 for the
length.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx|||> First you need to decide in which format is the UID? It if is in text,
> you should specify SQLVARCHAR for the data type. Only if you have the
> UID as binary, you should specify SQLUNIQUEID.
> Even if you use SQLVARCHAR, I don't think SQL_VARLEN_DATA is correct.
> It depends on what's in delimiter, but since a GUIO is always 36
> characters (without braces), you could just as well specify 36 for the
> length.
>
Thanx Erland,
I tried the approaches you mentioned:
1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
SQL Server Driver]Invalid character value for cast specification". What
do you meas format of the UID? You mean in the input file? in the input
file it is in "text format", like presented in my first post.
2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
result in bcp_bind.
Setting different then 16 for SQLVARCHAR still results in
"[Microsoft][ODBC SQL Server Driver]Invalid character value for cast
specification" error
Still no good.|||tha_mihau (mpietrzyk@.autograf.pl) writes:
> 1. Using SQLVARCHAR instead of SQLUNIQUEID results in "[Microsoft][ODBC
> SQL Server Driver]Invalid character value for cast specification". What
> do you meas format of the UID? You mean in the input file? in the input
> file it is in "text format", like presented in my first post.
The error message means that the string does not convert to a GUID.
This could be because you have not specified the appropriate length or
delimiter. I would try with 36 in length and no terminator.
> 2. Setting length (for SQLUNIQUEID) different then 16 results in FAIL
> result in bcp_bind.
Since you have text input, you should not use SQLUNIQUEID, unless you
convert the value in your program prior to passing it to BCP.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

BCP question

Hi Guys
I have a client who was having issues importing from a text file (Created by
there mainframe ) into SQL server, using DTS it would only import a certain
number of the rows (like 10 000 out of a million) we hare now trying to use
BCP and see the following problem below:
C:\>bcp CARD..AVAAF_ABSA_Employee in
c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
netjie -Usa -Ppassword
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
format file
This is the format of the clients format file:
8.0
25
1 SQLCHAR 2 2 "\0" 0 num1
SQL_Latin1_General_Cp437_BIN
2 SQLCHAR 2 7 "\0" 10 Employee_no
SQL_Latin1_General_Cp437_BIN
3 SQLCHAR 2 7 "\0" 1 AB_Number
SQL_Latin1_General_Cp437_BIN
4 SQLCHAR 2 30 "\0" 3 Surname
SQL_Latin1_General_Cp437_BIN
5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
6 SQLCHAR 2 10 "\0" 2 Initials
SQL_Latin1_General_Cp437_BIN
7 SQLCHAR 2 20 "\0" 0 num2
SQL_Latin1_General_Cp437_BIN
8 SQLCHAR 2 50 "\0" 11 EmailAddress
SQL_Latin1_General_Cp437_BIN
9 SQLCHAR 2 12 "\0" 0 num3
SQL_Latin1_General_Cp437_BIN
10 SQLCHAR 2 10 "\0" 0 num4
SQL_Latin1_General_Cp437_BIN
11 SQLCHAR 2 40 "\0" 0 num5
SQL_Latin1_General_Cp437_BIN
12 SQLCHAR 2 4 "\0" 0 num6
SQL_Latin1_General_Cp437_BIN
13 SQLCHAR 2 40 "\0" 0 num7
SQL_Latin1_General_Cp437_BIN
14 SQLCHAR 2 20 "\0" 5 TelephoneNumber SQL_Latin1_General_Cp437_BIN
15 SQLCHAR 2 20 "\0" 7 FaxNumber SQL_Latin1_General_Cp437_BIN
16 SQLCHAR 2 12 "\0" 6 CellularNumber SQL_Latin1_General_Cp437_BIN
17 SQLCHAR 2 56 "\0" 0 num8
SQL_Latin1_General_Cp437_BIN
18 SQLCHAR 2 40 "\0" 9 DomicileAdddress SQL_Latin1_General_Cp437_BIN
19 SQLCHAR 2 40 "\0" 0 num9
SQL_Latin1_General_Cp437_BIN
20 SQLCHAR 2 10 "\0" 0 num10
SQL_Latin1_General_Cp437_BIN
21 SQLCHAR 2 4 "\0" 0 num11
SQL_Latin1_General_Cp437_BIN
22 SQLCHAR 2 40 "\0" 0 num12
SQL_Latin1_General_Cp437_BIN
23 SQLCHAR 2 40 "\0" 0 num13
SQL_Latin1_General_Cp437_BIN
24 SQLCHAR 2 2 "\0" 0 num14
SQL_Latin1_General_Cp437_BIN
25 SQLCHAR 2 16 "\r\n" 15 Province SQL_Latin1_General_Cp437_BIN
I cannot find any info on this error message , has anyone got any ideas,
this is SQL server 2000 SP3
Thanks
DylanHave you checked the event log to verify you are not getting Disk I/O
errors?
--
----
----
--
Need SQL Server Examples check out my website at
http://www.geocities.com/sqlserverexamples
"Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> Hi Guys
> I have a client who was having issues importing from a text file (Created
by
> there mainframe ) into SQL server, using DTS it would only import a
certain
> number of the rows (like 10 000 out of a million) we hare now trying to
use
> BCP and see the following problem below:
>
> C:\>bcp CARD..AVAAF_ABSA_Employee in
> c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
>
> netjie -Usa -Ppassword
>
> SQLState = S1000, NativeError = 0
>
> Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> format file
>
> This is the format of the clients format file:
>
> 8.0
> 25
> 1 SQLCHAR 2 2 "\0" 0 num1
> SQL_Latin1_General_Cp437_BIN
> 2 SQLCHAR 2 7 "\0" 10 Employee_no
> SQL_Latin1_General_Cp437_BIN
> 3 SQLCHAR 2 7 "\0" 1 AB_Number
> SQL_Latin1_General_Cp437_BIN
> 4 SQLCHAR 2 30 "\0" 3 Surname
> SQL_Latin1_General_Cp437_BIN
> 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> 6 SQLCHAR 2 10 "\0" 2 Initials
> SQL_Latin1_General_Cp437_BIN
> 7 SQLCHAR 2 20 "\0" 0 num2
> SQL_Latin1_General_Cp437_BIN
> 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> SQL_Latin1_General_Cp437_BIN
> 9 SQLCHAR 2 12 "\0" 0 num3
> SQL_Latin1_General_Cp437_BIN
> 10 SQLCHAR 2 10 "\0" 0 num4
> SQL_Latin1_General_Cp437_BIN
> 11 SQLCHAR 2 40 "\0" 0 num5
> SQL_Latin1_General_Cp437_BIN
> 12 SQLCHAR 2 4 "\0" 0 num6
> SQL_Latin1_General_Cp437_BIN
> 13 SQLCHAR 2 40 "\0" 0 num7
> SQL_Latin1_General_Cp437_BIN
> 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
SQL_Latin1_General_Cp437_BIN
> 15 SQLCHAR 2 20 "\0" 7 FaxNumber
SQL_Latin1_General_Cp437_BIN
> 16 SQLCHAR 2 12 "\0" 6 CellularNumber
SQL_Latin1_General_Cp437_BIN
> 17 SQLCHAR 2 56 "\0" 0 num8
> SQL_Latin1_General_Cp437_BIN
> 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
SQL_Latin1_General_Cp437_BIN
> 19 SQLCHAR 2 40 "\0" 0 num9
> SQL_Latin1_General_Cp437_BIN
> 20 SQLCHAR 2 10 "\0" 0 num10
> SQL_Latin1_General_Cp437_BIN
> 21 SQLCHAR 2 4 "\0" 0 num11
> SQL_Latin1_General_Cp437_BIN
> 22 SQLCHAR 2 40 "\0" 0 num12
> SQL_Latin1_General_Cp437_BIN
> 23 SQLCHAR 2 40 "\0" 0 num13
> SQL_Latin1_General_Cp437_BIN
> 24 SQLCHAR 2 2 "\0" 0 num14
> SQL_Latin1_General_Cp437_BIN
> 25 SQLCHAR 2 16 "\r\n" 15 Province
SQL_Latin1_General_Cp437_BIN
>
>
> I cannot find any info on this error message , has anyone got any ideas,
> this is SQL server 2000 SP3
>
> Thanks
> Dylan
>|||I have not as i thought it would be unlikely that it owuld be that , but i
will check it anyways ,
Thanks
"Gregory A. Larsen" <greg.larsen@.netzero.com> wrote in message
news:u6mlGkOaEHA.1764@.TK2MSFTNGP10.phx.gbl...
> Have you checked the event log to verify you are not getting Disk I/O
> errors?
> --
> ----
--
> ----
--
> --
> Need SQL Server Examples check out my website at
> http://www.geocities.com/sqlserverexamples
> "Dylan Kruger" <carmellobear1@.hotmail.com> wrote in message
> news:ev0WvtNaEHA.2892@.TK2MSFTNGP10.phx.gbl...
> > Hi Guys
> >
> > I have a client who was having issues importing from a text file
(Created
> by
> > there mainframe ) into SQL server, using DTS it would only import a
> certain
> > number of the rows (like 10 000 out of a million) we hare now trying to
> use
> > BCP and see the following problem below:
> >
> >
> >
> > C:\>bcp CARD..AVAAF_ABSA_Employee in
> > c:\1ECLS_EMP.txt -fc:\1formatfile.fmt -Sman
> >
> >
> >
> > netjie -Usa -Ppassword
> >
> >
> >
> > SQLState = S1000, NativeError = 0
> >
> >
> >
> > Error = [Microsoft][ODBC SQL Server Driver]I/O error while reading BCP
> > format file
> >
> >
> >
> > This is the format of the clients format file:
> >
> >
> >
> > 8.0
> >
> > 25
> >
> > 1 SQLCHAR 2 2 "\0" 0 num1
> > SQL_Latin1_General_Cp437_BIN
> >
> > 2 SQLCHAR 2 7 "\0" 10 Employee_no
> > SQL_Latin1_General_Cp437_BIN
> >
> > 3 SQLCHAR 2 7 "\0" 1 AB_Number
> > SQL_Latin1_General_Cp437_BIN
> >
> > 4 SQLCHAR 2 30 "\0" 3 Surname
> > SQL_Latin1_General_Cp437_BIN
> >
> > 5 SQLCHAR 2 20 "\0" 4 Name SQL_Latin1_General_Cp437_BIN
> >
> > 6 SQLCHAR 2 10 "\0" 2 Initials
> > SQL_Latin1_General_Cp437_BIN
> >
> > 7 SQLCHAR 2 20 "\0" 0 num2
> > SQL_Latin1_General_Cp437_BIN
> >
> > 8 SQLCHAR 2 50 "\0" 11 EmailAddress
> > SQL_Latin1_General_Cp437_BIN
> >
> > 9 SQLCHAR 2 12 "\0" 0 num3
> > SQL_Latin1_General_Cp437_BIN
> >
> > 10 SQLCHAR 2 10 "\0" 0 num4
> > SQL_Latin1_General_Cp437_BIN
> >
> > 11 SQLCHAR 2 40 "\0" 0 num5
> > SQL_Latin1_General_Cp437_BIN
> >
> > 12 SQLCHAR 2 4 "\0" 0 num6
> > SQL_Latin1_General_Cp437_BIN
> >
> > 13 SQLCHAR 2 40 "\0" 0 num7
> > SQL_Latin1_General_Cp437_BIN
> >
> > 14 SQLCHAR 2 20 "\0" 5 TelephoneNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 15 SQLCHAR 2 20 "\0" 7 FaxNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 16 SQLCHAR 2 12 "\0" 6 CellularNumber
> SQL_Latin1_General_Cp437_BIN
> >
> > 17 SQLCHAR 2 56 "\0" 0 num8
> > SQL_Latin1_General_Cp437_BIN
> >
> > 18 SQLCHAR 2 40 "\0" 9 DomicileAdddress
> SQL_Latin1_General_Cp437_BIN
> >
> > 19 SQLCHAR 2 40 "\0" 0 num9
> > SQL_Latin1_General_Cp437_BIN
> >
> > 20 SQLCHAR 2 10 "\0" 0 num10
> > SQL_Latin1_General_Cp437_BIN
> >
> > 21 SQLCHAR 2 4 "\0" 0 num11
> > SQL_Latin1_General_Cp437_BIN
> >
> > 22 SQLCHAR 2 40 "\0" 0 num12
> > SQL_Latin1_General_Cp437_BIN
> >
> > 23 SQLCHAR 2 40 "\0" 0 num13
> > SQL_Latin1_General_Cp437_BIN
> >
> > 24 SQLCHAR 2 2 "\0" 0 num14
> > SQL_Latin1_General_Cp437_BIN
> >
> > 25 SQLCHAR 2 16 "\r\n" 15 Province
> SQL_Latin1_General_Cp437_BIN
> >
> >
> >
> >
> >
> > I cannot find any info on this error message , has anyone got any ideas,
> > this is SQL server 2000 SP3
> >
> >
> >
> > Thanks
> >
> > Dylan
> >
> >
>

BCP

Hi Guys,
I have data that I need BCP'd out of a database into a file. The BCP
process works, however there is always an additional line at the end of
the created file. This line contains no data, and is caused (I assume),
but a <CR> at the end of the previous line (Row delimiter). This
however causes major problems for the UNIX import application.
Is there a way to remove this last line?
Thanks,
JustinAfter that BCP command, create a new task (if you are within a DTS) which
open the file through FSO and drop that line.
regards
--
current location: alicante (es)
"justin.drennan@.gmail.com" wrote:

> Hi Guys,
> I have data that I need BCP'd out of a database into a file. The BCP
> process works, however there is always an additional line at the end of
> the created file. This line contains no data, and is caused (I assume),
> but a <CR> at the end of the previous line (Row delimiter). This
> however causes major problems for the UNIX import application.
> Is there a way to remove this last line?
> Thanks,
> Justin
>|||And if im not using DTS? Any ideas?|||if you're using a stored procedure you could use the set of system stored
procedures such as sp_OACreate, sp_OAMethod, in order to work with FSO.
Something like that:
DECLARE @.object int
DECLARE @.hr int
EXEC @.hr = sp_OACreate 'Scripting.FileSystemObject', @.object OUT
Hope that helps.
current location: alicante (es)
"justin.drennan@.gmail.com" wrote:

> And if im not using DTS? Any ideas?
>

Batch Inserts from DataTable

Please help guys,
I have a DataTable filled from the parsing of a csv file by the OleDb text driver.
This DataTable could on occassion contain in excess of 2000 rows.
I want to be able to batch the inserts to my backend sql table and be able to recorver on errors during the insert.
i.e, maybe send the first 500 rows to insert via an insert dynamic text... really don't know the optimal insert technic to use.
but, if I get an error on say the third batch, I want to be able to recorver, and not have to start all over again and continue the inserts from the batch that failed....
Please help... what is the best way to perform the inserts and how can I track these inserts and recorver on errors like power failures or sql server unavailable etc.
Please help...could I batch the reading of data from a csv file, I mean, if the csv file contains hundreds of thousand of records, how do I read say 500 at a time, using the Microsoft Text Driver - please help...|||

You could run dynamic selects

dim i as int32

for each row in datatable.rows

sql += "insert into table;"
i += 1

if i = 500 then

'do connection and things

i = 0

end if

next

Nick

|||

You could use DTS package to move the CVS file but if you want an alternative, try the links below for options. Hope this helps.

http://www.sqldts.com/?220

http://www.users.drew.edu/skass/sql/TextDriver.htm

Basic SQL Cluster Questions

Ok my job is done. I have built a fully tested Windows and SQL active/passive
cluster. Not the software guys take over but as an inquiring IT guy I can't
call it done withoout some more education.
Please feel free to answer these questions or send me to more information
please.
1. I understand that the virtual clusters "CompanyCluster IP address
x.x.x.x and SqlCluster IP Address x.x.x.x" are used to map databases and
point to as the reference for websites, databases and apps. Do you perform
any tasks on these virtual machines llike servicee packs or database installs
and maintenance?
1a. If you do not use the Virtual machines then I assume you use the active
node. Would that install the database to the other node automatically?
2. Where are the Virtual machines stored and should you back them up or are
they just basically cached on the active node?
3. How about backup software any recomendations. We currently use Acronis
and is seems to work on all of our non-clustered servers.
I have not built a cluster since Server 2000 and I can tell you that this
time out with Server 2003 R2 and Sql 2005 it was much better.
Regards, emagidson
Comments Inline
"EMagidson" <EMagidson@.discussions.microsoft.com> wrote in message
news:991EA41E-F915-4A6C-9379-7BB85BE8CC44@.microsoft.com...
> Ok my job is done. I have built a fully tested Windows and SQL
> active/passive
> cluster. Not the software guys take over but as an inquiring IT guy I
> can't
> call it done withoout some more education.
> Please feel free to answer these questions or send me to more information
> please.
> 1. I understand that the virtual clusters "CompanyCluster IP address
> x.x.x.x and SqlCluster IP Address x.x.x.x" are used to map databases and
> point to as the reference for websites, databases and apps. Do you perform
> any tasks on these virtual machines llike servicee packs or database
> installs
> and maintenance?
OS service packs are applied to physical machines. SQL Service packs and
hotfixes are applied to individual instances. The installer is
cluster-aware and updates all nodes at once. From the point of view of a
client connection, there is no difference between a clustered installation
and a non-clustered system.

> 1a. If you do not use the Virtual machines then I assume you use the
> active
> node. Would that install the database to the other node automatically?
Nodes are the physical layer. Instances are the virtual abstraction. They
are independent entities. An instance can move from one node to another in
order to stay online. An instance can exist on only a single node at a
time.

> 2. Where are the Virtual machines stored and should you back them up or
> are
> they just basically cached on the active node?
SQL Data is stored on the cluster disks. The cluster configuration is
stored in teh cluster registry, an abstraction that is managed by the
cluster service on each node.

> 3. How about backup software any recomendations. We currently use Acronis
> and is seems to work on all of our non-clustered servers.
Personally, I backup the SQL servers just as I would a stand-alone system.
I do a compressed backup to disk and archive that to tape. I don't really
worry about backups. I worry about restores. Nobody got fired or rewarded
for a backup. Many folks have had that happen because they either could or
could not restore the system. One final note on restores. Test them. An
untested recoery plan is merely a recovery hope.

> I have not built a cluster since Server 2000 and I can tell you that this
> time out with Server 2003 R2 and Sql 2005 it was much better.
>
Yes, it is. That is one of the things the Server and SQL teams got right.

> Regards, emagidson
>
Good luck,
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP