multiple Index files

1. Is there any benifit to creating a distinct index file for each column to be indexe
Index file 1 - Field
Index File 2 - Field
Index file 3 - Field
Index file 4 - Field
2. Is there a benifit to creating multiple groups of indexes ie
Index file 1 - Field A, Field B, Filed
Index File 2 - Field B, Field
Index file 3 - Field B, FieldHi Mark.
There are benefits / limitations in taking either approach.
Multi-column indexes may "cover" more queries or join predicates than single
column queries and provide better performance that way.
Distinct indexes provide compact index width for fast join or seek
operations where query predicates or filter arguments are on those columns.
SQL Server can also intersect indexes during optimization which recovers
some of the benefits that would have come from multi-column indexes.
If your application is relatively read only (data mart / warehouse) & not
bound by disk space limitations you might find that the more indexes you
have the better, as there are simply more options for the optimizer to suit
more queries.
If your application is more of an online transaction processinng
application, you probably want to limit which indexes you add as each index
needs updating as rows are inserted, updated & deleted.
The Index Tuning Wizard can be helpful in determining which indexes are the
best for the database & queries you're using.
HTH
Regards,
Greg Linwood
SQL Server MVP
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>|||Mark
It depends on what are you going to query and how.
If you issue SELECT Field A, Field B, Filed C FROM
SomeTable ORDER BY Field A, Field B, Filed C or
WHERE clause
so having covering index on these columns will gain perfomance improving.
"MarkFirth" <anonymous@.discussions.microsoft.com> wrote in message
news:406C0C47-5B5C-45EF-93C2-DD721278F61F@.microsoft.com...
> 1. Is there any benifit to creating a distinct index file for each column
to be indexed
> Index file 1 - Field A
> Index File 2 - Field B
> Index file 3 - Field B
> Index file 4 - Field H
> 2. Is there a benifit to creating multiple groups of indexes ie:
> Index file 1 - Field A, Field B, Filed C
> Index File 2 - Field B, Field C
> Index file 3 - Field B, Field H
>sql

multiple forms in one .rdl file

I am using report designer to generate database reports. I am wondering
if we can generate one .rdl file with multiple report forms according
to the multiple records in the data source. Any help will be
appreciated.
Amydidn't understand u but u can use subreports...
"amy" wrote:
> I am using report designer to generate database reports. I am wondering
> if we can generate one .rdl file with multiple report forms according
> to the multiple records in the data source. Any help will be
> appreciated.
> Amy
>|||Thanks for your response. I've found the way to do it already. I put
the whole form in a list, and then it works!
Amy

Multiple flat file import to corresponding SQL server tables

We are trying to use SSIS Import export wizrd to import the flat files (CSV format) that we have into MS SQL Server 2005 database tables. We have huge number of CSV files. Is there a way by which we can import these flat (CSV) files in to corresponding SQL server tables in a single shot. I would really appreciate this help as it is painful to convert each and every file using the Import Export wizard.

Thank you very much,

Regards,

Srinivas Raveendra

I am also facing the same problem.|||The simplest way I can see that is to create a package per every file-table destination pair. Each package could have a ForEach loop to process all files that have the same structure.|||

The way we have accomplished this task is to have a table that holds the names of all the files and the names of the tables each file is loaded into.

The SSIS package reads all the rows from this table and runs a foreach loop container for each record; The looping process firstly truncates the staging table and then bulk inserts based on the variable values for each iteration of the loop container. the syntax for the Bulk Insert would look something like this:

EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv''')

If the files you are importing are not exactly the same format as the tables you are importing them into. ie the columns are in a different order or there are more/less columns in the file than in the table, you will need to have format files set up and the syntax with the format file would look something like this:

EXEC('BULK INSERT ' + @.strTable + ' FROM ' + @.strFileDir + @.strFile + '.csv'' WITH (FORMATFILE=''' + @.strFormatDir + @.strFormat + '.fmt'')')

As a note we have the variables @.strFileDir and @.strFormatDir set at the package level, and dynamically controlled through a package configuration.

For more information on Bulk Insert see Books Online: http://msdn2.microsoft.com/en-us/library/ms188365.aspx

Hopefully this helps.

sql

Multiple files in FileGroup on RAID

We are putting out DBs on a RAID array and have the data and indexes in
their own file groups. My question is where can I find information about any
performance gain in I/O based on the number of files I have in the
FileGroup.
The array is made up of 14 drives with 1 hot spare (RAID 5). How many
files?
Thx"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
>
Spreading out the data to multiple files in a filegroup on top of a single
RAID 5 array is redundant. Each file will be spread out over all 14 drives
by the RAID striping to begin with. It's useless do do it again with
multiple files per filegroup.
In your setup you needn't even bother putting data and indexes into seperate
filegroups.
If you had your disks RAIDed into 7 seperate 2 drive RAID 1 mirrors, then
you might spread a filegroup over multiple volumes for performance and
capacity.
But a better configuration might be to bundle the mirrored sets into 4-drive
stripe and mirror sets.
David|||While there can be performance gains with having multiple files per file
group there is no chart etc. that exists to show what your after. The gains
are going to be greatest with lots of drive arrays and lots of processors
when you have multiple files. Chances are you will not max out the
capabilities of the way you have it now with that hardware setup.
--
Andrew J. Kelly
SQL Server MVP
"cw3" <cw@.3mc.com> wrote in message
news:%23yenkyNnDHA.3688@.TK2MSFTNGP11.phx.gbl...
> We are putting out DBs on a RAID array and have the data and indexes in
> their own file groups. My question is where can I find information about
any
> performance gain in I/O based on the number of files I have in the
> FileGroup.
> The array is made up of 14 drives with 1 hot spare (RAID 5). How many
> files?
> Thx
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?
Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com
|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:

> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?
|||> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files.
You can't do that without some free space. You can add a new file and shrink the original file.
Well, thinking about it, it is *possible* that the shrink can at the same time autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.

> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
The one with more free space.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.boroughkenaiaku s...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.com> said:
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE in this case. The
> database is mostly read intensive, with few writes/changes. There are two databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb used, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster database.
> What I'm trying to figure out is how to move from one database:one massive file to one
> database:multiple small files. I can't split to new databases without breaking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file group, how does SQL
> decide which one to write to?
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com|||On 2005-06-16 13:35:42 -0800, "David Gugick"
<david.gugick-nospam@.quest.com> said:

> Ben Hanson wrote:
> Is the file full of data or is there empty space? If there's sufficient
> empty space, you can use DBCC SHRINKFILE to recover disk space. It may
> take some time to run, so best to do this off-hours. If you just need
> to move tables, you can create the new database file and recreate the
> clustered indexes on the new data file for those tables you want to
> move.
> What's your drive array setup?
Details:
Win2k3 server running MSSQL server 2000 SP3
400gb RAID5, 4 x 146gb - Data
36gb RAID1, 2 x 36gb - System
This is a GIS server running ArcSDE. SQL is the database backend for
SDE in this case. The database is mostly read intensive, with few
writes/changes. There are two databases, one for vector data(300mb
used, 20gb allocated), and one for raster data(186gb used, 190gb
allocated). I just did a shrink database to free up 30gb of unused
space on the raster database.
What I'm trying to figure out is how to move from one database:one
massive file to one database:multiple small files. I can't split to
new databases without breaking current SDE connections which are
database specific.
This also begs the question, In a database with multiple files in one
file group, how does SQL decide which one to write to?|||> What I'm trying to figure out is how to move from one database:one massive file to one[vbc
ol=seagreen]
> database:multiple small files.[/vbcol]
You can't do that without some free space. You can add a new file and shrink
the original file.
Well, thinking about it, it is *possible* that the shrink can at the same ti
me autogrow the other
file. But I believe that shrink doesn't shrink the physical file size until
the very end, after
moving of the data. If that is the case, you need free space:
Add new file.
Shrink original file so that SQL server pushes the data to the new file.

> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
The one with more free space.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Ben Hanson" <benhanson@.borough.kenai.ak.us> wrote in message
news:2005061615392575249%benhanson@.borou
ghkenaiakus...
> On 2005-06-16 13:35:42 -0800, "David Gugick" <david.gugick-nospam@.quest.co
m> said:
>
>
> Details:
> Win2k3 server running MSSQL server 2000 SP3
> 400gb RAID5, 4 x 146gb - Data
> 36gb RAID1, 2 x 36gb - System
> This is a GIS server running ArcSDE. SQL is the database backend for SDE
in this case. The
> database is mostly read intensive, with few writes/changes. There are two
databases, one for
> vector data(300mb used, 20gb allocated), and one for raster data(186gb use
d, 190gb allocated). I
> just did a shrink database to free up 30gb of unused space on the raster d
atabase.
> What I'm trying to figure out is how to move from one database:one massive
file to one
> database:multiple small files. I can't split to new databases without bre
aking current SDE
> connections which are database specific.
> This also begs the question, In a database with multiple files in one file
group, how does SQL
> decide which one to write to?
>

Multiple Files for database

So, due to some oversights in planning, I've got a 200gb database that
is working from 1 file. Performance is remarkeably good(fast server &
disks), but we can't defrag the drive since there's not 200gb of free
space. Regardless of that problem, it seems it would be wise to have
this database spread across multiple files. Is there any way to do
this? I realize I can add files to the existing database, but how do I
get the first file to shrink?Ben Hanson wrote:
> So, due to some oversights in planning, I've got a 200gb database that
> is working from 1 file. Performance is remarkeably good(fast server &
> disks), but we can't defrag the drive since there's not 200gb of free
> space. Regardless of that problem, it seems it would be wise to have
> this database spread across multiple files. Is there any way to do
> this? I realize I can add files to the existing database, but how do
> I get the first file to shrink?
Is the file full of data or is there empty space? If there's sufficient
empty space, you can use DBCC SHRINKFILE to recover disk space. It may
take some time to run, so best to do this off-hours. If you just need to
move tables, you can create the new database file and recreate the
clustered indexes on the new data file for those tables you want to
move.
What's your drive array setup?
David Gugick
Quest Software
www.imceda.com
www.quest.com

Multiple Files

I have a job with a single step that executes a stored procedure that performs the following steps:

1. Checks for the existance of a file A in a folder A

2. If it exists,

a. executes the cmdshell to run a DTS package to drop a table, recreate it and load the data in the file A to table X

b. runs other stored procedures that use the data in table X to create other tables Y and Z

c. executes the cndshell to remove and rename the file A from Folder A into Folder B

What I'd like to do is use this same stored procedure if possible, but create a job or another store procedure that would loop thru and process multiple files in Folder A instead of just one.

Any suggestions would be greatly appreciated

My post in this thread should point you in a workable direction.

Multiple file import SQLExpress

Hi everyone one,
I'm very new to SQLExpress and I'm having difficulty with a procedure I
found on the net
to import multiple files into a table:
http://www.databasejournal.com/feat...cle.php/3325701
The method I'm using is method one.
I've done everything as outlined, however when running the procedure within
SQLexpress I receive the error message
Msg 102, Level 15, State 1, Line 2
Incorrect syntax near ','.
I'm presuming it has something to do with the Fieldterminator remark, the
double quote and comma specified in the bulk insert statement:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH
( FIELDTERMINATOR = "," , ROWTERMINATOR = "\n")'
I've tried running the bulk insert with single quotes and it runs fine..with
double quotes not so good I get the same error so thats why I think its a
syntax error...but when I replace single quotes in the procedure, the
procedure doesn't compile...i.e.
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
WITH
( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')'
....I'm at a loss as to how to fix it!
Could someone help please, thanks so much.Try using single quotes instead of double quotes to delimit literals so that
you aren't sensitive to your QUOTED_IDENTIFIER setting. When you need a
quote within a literal, specify 2 single quotes:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+ @.Filepath+@.Filename+'''
WITH
( FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'
Happy Holidays
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:es09z0vDGHA.472@.TK2MSFTNGP12.phx.gbl...
> Hi everyone one,
> I'm very new to SQLExpress and I'm having difficulty with a procedure I
> found on the net
> to import multiple files into a table:
> http://www.databasejournal.com/feat...cle.php/3325701
> The method I'm using is method one.
> I've done everything as outlined, however when running the procedure
> within SQLexpress I receive the error message
> Msg 102, Level 15, State 1, Line 2
> Incorrect syntax near ','.
> I'm presuming it has something to do with the Fieldterminator remark, the
> double quote and comma specified in the bulk insert statement:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
> WITH
> ( FIELDTERMINATOR = "," , ROWTERMINATOR = "\n")'
> I've tried running the bulk insert with single quotes and it runs
> fine..with double quotes not so good I get the same error so thats why I
> think its a syntax error...but when I replace single quotes in the
> procedure, the procedure doesn't compile...i.e.
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM "'+ @.Filepath+@.Filename+'"
> WITH
> ( FIELDTERMINATOR = ',' , ROWTERMINATOR = '\n')'
> ....I'm at a loss as to how to fix it!
> Could someone help please, thanks so much.
>
>|||Thanks Dan, that worked, now on to my "real" data...
the fields are separated by a comma but enclosed in a double quote i.e.
"12345","67899"
How do I get the field terminator set correctly? I just know you're going
to
tell me to remove the double quotes...(sigh)...theres a ton of files...
Thanks again
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OX$4aOwDGHA.344@.TK2MSFTNGP11.phx.gbl...
> Try using single quotes instead of double quotes to delimit literals so
> that you aren't sensitive to your QUOTED_IDENTIFIER setting. When you
> need a quote within a literal, specify 2 single quotes:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
> @.Filepath+@.Filename+'''
> WITH
> ( FIELDTERMINATOR = '','' , ROWTERMINATOR = ''\n'')'
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Dale" <dale@.nospam.com> wrote in message
> news:es09z0vDGHA.472@.TK2MSFTNGP12.phx.gbl...
>|||You need to create a format file when you have a more complicated file
format. Sample SQL 2000 format file:
8.0
3
1 SQLCHAR 0 1 "\"" 0 quote ""
2 SQLCHAR 0 10 "\",\"" 1 Col1 ""
3 SQLCHAR 0 10 "\"\r\n" 2 Col2 ""
Specify the format file using FORMATFILE in your BULK INSERT statement
instead of FIELDTERMINATOR and ROWTERMINATOR:
set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
@.Filepath+@.Filename+'''
WITH
( FORMATFILE = ''' + @.FormatFilename + ''')'
See the Books Online for format file details.
Happy Holidays
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:OtBJ2LxDGHA.3064@.TK2MSFTNGP10.phx.gbl...
> Thanks Dan, that worked, now on to my "real" data...
> the fields are separated by a comma but enclosed in a double quote i.e.
> "12345","67899"
> How do I get the field terminator set correctly? I just know you're going
> to
> tell me to remove the double quotes...(sigh)...theres a ton of files...
> Thanks again
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:OX$4aOwDGHA.344@.TK2MSFTNGP11.phx.gbl...
>|||Thanks Dan...this certainly got complicated fast!! I'm guessing this format
file is likened to a specifications file in msaccess.
At any rate, when trying to run the bcp utility:
bcp adventureworks.humanresources.department format nul -T -n -f
importtest-f-n.txt
I receive an error, unable to open a connection...that remote access may not
be enabled? I've checked using the surface configuration tool and remote
looks to be enabled.
Secondly if I get this bcp utililty to work..can it be run against a "txt"
file (haven't been able to get this to work either)..it doesn't make sense
to me to run the utility against the table rec'ving the data if it isn't the
source of the import?...I can't see any other way to create this format file
other than manually?
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:eqAaAwxDGHA.3528@.TK2MSFTNGP12.phx.gbl...
> You need to create a format file when you have a more complicated file
> format. Sample SQL 2000 format file:
> 8.0
> 3
> 1 SQLCHAR 0 1 "\"" 0 quote ""
> 2 SQLCHAR 0 10 "\",\"" 1 Col1 ""
> 3 SQLCHAR 0 10 "\"\r\n" 2 Col2 ""
> Specify the format file using FORMATFILE in your BULK INSERT statement
> instead of FIELDTERMINATOR and ROWTERMINATOR:
> set @.Query ='BULK INSERT '+ @.Tablename + ' FROM '''+
> @.Filepath+@.Filename+'''
> WITH
> ( FORMATFILE = ''' + @.FormatFilename + ''')'
> See the Books Online for format file details.
> --
> Happy Holidays
> Dan Guzman
> SQL Server MVP
> "Dale" <dale@.nospam.com> wrote in message
> news:OtBJ2LxDGHA.3064@.TK2MSFTNGP10.phx.gbl...
>|||> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
Since you haven't specified a server, BCP will connect to the default
instance on the local machine. Is that your intent?

> it doesn't make sense to me to run the utility against the table rec'ving
> the data if it isn't the source of the import?
A format file describes the format of your text file (source or target).
When you run BCP using the 'format' option, the table is neither a source
nor target; the table schema is used to facilitate creating a format file
with one field per column. Since you have specified '-n', the generated
format file will be appropriate only for native file format rather than the
comma-delimited/quoted field format you specified in your original post.

> I can't see any other way to create this format file other than manually?
Although BCP can't directly create a format file for a
comma-delimited/quoted field format, you can use BCP to create a default
character format file and then change the generated to match your actual
file. That way, you at least won't need to enter all the columns. For
example:
bcp adventureworks.humanresources.department format
nul -T -c -fimporttest-f-n.txt -SMyServer
Hope this helps.
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:uMsEFo7DGHA.516@.TK2MSFTNGP15.phx.gbl...
> Thanks Dan...this certainly got complicated fast!! I'm guessing this
> format file is likened to a specifications file in msaccess.
> At any rate, when trying to run the bcp utility:
> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
> I receive an error, unable to open a connection...that remote access may
> not be enabled? I've checked using the surface configuration tool and
> remote looks to be enabled.
> Secondly if I get this bcp utililty to work..can it be run against a "txt"
> file (haven't been able to get this to work either)..it doesn't make sense
> to me to run the utility against the table rec'ving the data if it isn't
> the source of the import?...I can't see any other way to create this
> format file other than manually?
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:eqAaAwxDGHA.3528@.TK2MSFTNGP12.phx.gbl...
>|||Thanks again Dan
I agree it would be easier to run the bcp utility w/correct syntax for the
purposes intended. However it seems...I cannot get it to run:
C:\>bcp adventureworks.humanresources.department format nul -T -n -f
importtest-
f-n.txt -<servername>
Warning: Packetsize size must be between 512 and 65535 -- defaulting to
4096.
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open a
con
nection to SQL Server [2].
SQLState = HYT00, NativeError = 0
Error = [Microsoft][SQL Native Client]Login timeout expired
SQLState = 08001, NativeError = 2
Error = [Microsoft][SQL Native Client]An error has occurred while
establishing a
connection to the server. When connecting to SQL Server 2005, this failure
may
be caused by the fact that under the default settings SQL Server does not
allow
remote connections.
I have checked everything that a newbie can think of, it looks to me like
remote connections are enabled, connect via tcp/ip or named pipes, I've even
specified a port 3308 to connect on via firewall...
Ok as silly as it sounds, does bcp work with SQLexpress?...|||Hi Dale
You are missing -S<servername> is this a typo?
If you are using the default server then you can miss out specifying
completely
John
"Dale" <dale@.nospam.com> wrote in message
news:ejWme09DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Thanks again Dan
> I agree it would be easier to run the bcp utility w/correct syntax for the
> purposes intended. However it seems...I cannot get it to run:
> C:\>bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-
> f-n.txt -<servername>
> Warning: Packetsize size must be between 512 and 65535 -- defaulting to
> 4096.
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open
> a con
> nection to SQL Server [2].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]An error has occurred while
> establishing a
> connection to the server. When connecting to SQL Server 2005, this failure
> may
> be caused by the fact that under the default settings SQL Server does not
> allow
> remote connections.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
>|||In addition to John's response, the instance needs to be restarted after you
enable remote connections. You might try restarting the instance to make
sure the change isn't pending.

> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
Is this the default or named instance? How did you configure port 3308?
Note that for a named instance, you'll need to enable/start the SQL Browser
service in order to remotely connect by server\instance.
Hope this helps.
Dan Guzman
SQL Server MVP
"Dale" <dale@.nospam.com> wrote in message
news:ejWme09DGHA.2956@.TK2MSFTNGP14.phx.gbl...
> Thanks again Dan
> I agree it would be easier to run the bcp utility w/correct syntax for the
> purposes intended. However it seems...I cannot get it to run:
> C:\>bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-
> f-n.txt -<servername>
> Warning: Packetsize size must be between 512 and 65535 -- defaulting to
> 4096.
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]Named Pipes Provider: Could not open
> a con
> nection to SQL Server [2].
> SQLState = HYT00, NativeError = 0
> Error = [Microsoft][SQL Native Client]Login timeout expired
> SQLState = 08001, NativeError = 2
> Error = [Microsoft][SQL Native Client]An error has occurred while
> establishing a
> connection to the server. When connecting to SQL Server 2005, this failure
> may
> be caused by the fact that under the default settings SQL Server does not
> allow
> remote connections.
> I have checked everything that a newbie can think of, it looks to me like
> remote connections are enabled, connect via tcp/ip or named pipes, I've
> even specified a port 3308 to connect on via firewall...
> Ok as silly as it sounds, does bcp work with SQLexpress?...
>|||Xref: TK2MSFTNGP08.phx.gbl microsoft.public.sqlserver.programming:574887
Dale (dale@.nospam.com) writes:
> Thanks Dan...this certainly got complicated fast!! I'm guessing this
> format file is likened to a specifications file in msaccess. At any
> rate, when trying to run the bcp utility:
> bcp adventureworks.humanresources.department format nul -T -n -f
> importtest-f-n.txt
> I receive an error, unable to open a connection...that remote access may
> not be enabled? I've checked using the surface configuration tool and
> remote looks to be enabled
You probably need to add:
-S .\SQLEXPRESS
if you leave out the server name, BCP tries to connect to the default
instance. But by default SQL Express installs as a *named* instance with
the name SQLEXPRESS. (You can have several instances of SQL Server on
your machine, but only one can be the default instance.)
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Multiple Expressions in Precedence Constraints

Is it possible in a precedence constraint to have multiple expressions evaluated? IE

Operation set to Expression and expression to evaluate is: @.File = x or @.File = y

Thanks

(@.File == "x" || @.File == "y")|||

I am an idiot!

Thanks

|||If this was correct for you, please mark this thread as answered.

Multiple Environments and Configuration File settings in packages.

Suppose 2 environments on a single machine.

Each environment has different configuration settings....different

databases etc.

All the packages in the first environment have a hardcoded config files

referencing the local drive.

In order to create the second environment do I have to

go into each package and manually change the location of the

hardcoded config files. If I don't it will it not use the config files

from the first environment.

Thanks

What you can do is store the config file reference in a environment variable and change it between executions. You might have to use the command line mode for that.
Creat a batch file like this:

- pseudo code
Set env_variable = config1

dtexec.exe package.dtsx (not the correct syntax but to give you an idea)

Set env_variable = config2

dtexec.exe package.dtsx

-

Also, check ProjectREAL stuff. It'll give you a better idea.

Hope that helps.

Multiple databases

I'm developing a website in a hosted environment and using SQL server 2000.
The hosting company restricts the file growth to 100mb. Because of the numbe
r
of views I have several system tables have caused the size to exceed the
100mb. To increase the capacity I have to setup another database (size max
100 mb) then a 3rd and so on.
This is new territory for me and I'm not sure how to set up multiple files
across the databases. Is this the area of filegroups and can multiple
databases share one set of system tables? How do I do this?
--
Always learning hobbyist.Wouldn't it be easier to find a new web hosting firm that doesn't limit file
size to 100Mb? If it is possible for you to do this, let me know and I can
pass you some info I have on a company that I know the owner of.
"Merv Harvey" wrote:

> I'm developing a website in a hosted environment and using SQL server 2000
.
> The hosting company restricts the file growth to 100mb. Because of the num
ber
> of views I have several system tables have caused the size to exceed the
> 100mb. To increase the capacity I have to setup another database (size max
> 100 mb) then a 3rd and so on.
> This is new territory for me and I'm not sure how to set up multiple files
> across the databases. Is this the area of filegroups and can multiple
> databases share one set of system tables? How do I do this?
> --
> Always learning hobbyist.|||Rolaaus, I agree 100mb is a bit lousy and you are absolutely right. I expect
the DB to grow to gb's eventually and 100mb files will certainly be out of
the question. For now I will do it once (for the learning). If the site
eventually makes any $$$$ I will look a dedicated hosting.
"rolaaus" wrote:
[vbcol=seagreen]
> Wouldn't it be easier to find a new web hosting firm that doesn't limit fi
le
> size to 100Mb? If it is possible for you to do this, let me know and I ca
n
> pass you some info I have on a company that I know the owner of.
> "Merv Harvey" wrote:
>|||File groups is not about size restriction. You could add files to a database
(.ndf) and you could then use file groups to logicall groups this files
together.
As for the shared system file, i don't have an idea at this moment.
Cheers
Gill
.
you start with a mdf file and ndf. create you user tables in the ndf file
rather then mdf. . when it becomes 100mb
"Merv Harvey" <MervHarvey@.discussions.microsoft.com> wrote in message
news:DB4EA409-4259-47B1-92E9-61A880185564@.microsoft.com...
> I'm developing a website in a hosted environment and using SQL server
> 2000.
> The hosting company restricts the file growth to 100mb. Because of the
> number
> of views I have several system tables have caused the size to exceed the
> 100mb. To increase the capacity I have to setup another database (size max
> 100 mb) then a 3rd and so on.
> This is new territory for me and I'm not sure how to set up multiple files
> across the databases. Is this the area of filegroups and can multiple
> databases share one set of system tables? How do I do this?
> --
> Always learning hobbyist.

Multiple databases

I'm developing a website in a hosted environment and using SQL server 2000.
The hosting company restricts the file growth to 100mb. Because of the number
of views I have several system tables have caused the size to exceed the
100mb. To increase the capacity I have to setup another database (size max
100 mb) then a 3rd and so on.
This is new territory for me and I'm not sure how to set up multiple files
across the databases. Is this the area of filegroups and can multiple
databases share one set of system tables? How do I do this?
Always learning hobbyist.
Wouldn't it be easier to find a new web hosting firm that doesn't limit file
size to 100Mb? If it is possible for you to do this, let me know and I can
pass you some info I have on a company that I know the owner of.
"Merv Harvey" wrote:

> I'm developing a website in a hosted environment and using SQL server 2000.
> The hosting company restricts the file growth to 100mb. Because of the number
> of views I have several system tables have caused the size to exceed the
> 100mb. To increase the capacity I have to setup another database (size max
> 100 mb) then a 3rd and so on.
> This is new territory for me and I'm not sure how to set up multiple files
> across the databases. Is this the area of filegroups and can multiple
> databases share one set of system tables? How do I do this?
> --
> Always learning hobbyist.
|||Rolaaus, I agree 100mb is a bit lousy and you are absolutely right. I expect
the DB to grow to gb's eventually and 100mb files will certainly be out of
the question. For now I will do it once (for the learning). If the site
eventually makes any $$$$ I will look a dedicated hosting.
"rolaaus" wrote:
[vbcol=seagreen]
> Wouldn't it be easier to find a new web hosting firm that doesn't limit file
> size to 100Mb? If it is possible for you to do this, let me know and I can
> pass you some info I have on a company that I know the owner of.
> "Merv Harvey" wrote:
|||File groups is not about size restriction. You could add files to a database
(.ndf) and you could then use file groups to logicall groups this files
together.
As for the shared system file, i don't have an idea at this moment.
Cheers
Gill
..
you start with a mdf file and ndf. create you user tables in the ndf file
rather then mdf. . when it becomes 100mb
"Merv Harvey" <MervHarvey@.discussions.microsoft.com> wrote in message
news:DB4EA409-4259-47B1-92E9-61A880185564@.microsoft.com...
> I'm developing a website in a hosted environment and using SQL server
> 2000.
> The hosting company restricts the file growth to 100mb. Because of the
> number
> of views I have several system tables have caused the size to exceed the
> 100mb. To increase the capacity I have to setup another database (size max
> 100 mb) then a 3rd and so on.
> This is new territory for me and I'm not sure how to set up multiple files
> across the databases. Is this the area of filegroups and can multiple
> databases share one set of system tables? How do I do this?
> --
> Always learning hobbyist.

Multiple databases

I'm developing a website in a hosted environment and using SQL server 2000.
The hosting company restricts the file growth to 100mb. Because of the number
of views I have several system tables have caused the size to exceed the
100mb. To increase the capacity I have to setup another database (size max
100 mb) then a 3rd and so on.
This is new territory for me and I'm not sure how to set up multiple files
across the databases. Is this the area of filegroups and can multiple
databases share one set of system tables? How do I do this?
--
Always learning hobbyist.Wouldn't it be easier to find a new web hosting firm that doesn't limit file
size to 100Mb? If it is possible for you to do this, let me know and I can
pass you some info I have on a company that I know the owner of.
"Merv Harvey" wrote:
> I'm developing a website in a hosted environment and using SQL server 2000.
> The hosting company restricts the file growth to 100mb. Because of the number
> of views I have several system tables have caused the size to exceed the
> 100mb. To increase the capacity I have to setup another database (size max
> 100 mb) then a 3rd and so on.
> This is new territory for me and I'm not sure how to set up multiple files
> across the databases. Is this the area of filegroups and can multiple
> databases share one set of system tables? How do I do this?
> --
> Always learning hobbyist.|||Rolaaus, I agree 100mb is a bit lousy and you are absolutely right. I expect
the DB to grow to gb's eventually and 100mb files will certainly be out of
the question. For now I will do it once (for the learning). If the site
eventually makes any $$$$ I will look a dedicated hosting.
"rolaaus" wrote:
> Wouldn't it be easier to find a new web hosting firm that doesn't limit file
> size to 100Mb? If it is possible for you to do this, let me know and I can
> pass you some info I have on a company that I know the owner of.
> "Merv Harvey" wrote:
> > I'm developing a website in a hosted environment and using SQL server 2000.
> > The hosting company restricts the file growth to 100mb. Because of the number
> > of views I have several system tables have caused the size to exceed the
> > 100mb. To increase the capacity I have to setup another database (size max
> > 100 mb) then a 3rd and so on.
> >
> > This is new territory for me and I'm not sure how to set up multiple files
> > across the databases. Is this the area of filegroups and can multiple
> > databases share one set of system tables? How do I do this?
> > --
> > Always learning hobbyist.|||File groups is not about size restriction. You could add files to a database
(.ndf) and you could then use file groups to logicall groups this files
together.
As for the shared system file, i don't have an idea at this moment.
Cheers
Gill
.
you start with a mdf file and ndf. create you user tables in the ndf file
rather then mdf. . when it becomes 100mb
"Merv Harvey" <MervHarvey@.discussions.microsoft.com> wrote in message
news:DB4EA409-4259-47B1-92E9-61A880185564@.microsoft.com...
> I'm developing a website in a hosted environment and using SQL server
> 2000.
> The hosting company restricts the file growth to 100mb. Because of the
> number
> of views I have several system tables have caused the size to exceed the
> 100mb. To increase the capacity I have to setup another database (size max
> 100 mb) then a 3rd and so on.
> This is new territory for me and I'm not sure how to set up multiple files
> across the databases. Is this the area of filegroups and can multiple
> databases share one set of system tables? How do I do this?
> --
> Always learning hobbyist.

Multiple Data Sources for 1 Dataset?

I have to create a join between tables on SQL Server & a table/file on an
AS400(DB2).
I can create the 2 different data sources just fine, but I cannot figure out
how to join the data into 1 dataset, since you can only use 1 data source.
Has anyone been able to do this? Help!Hod did you go with this question Lori?
I have the same problem.
Thanks
Todd
"Lori" wrote:
> I have to create a join between tables on SQL Server & a table/file on an
> AS400(DB2).
> I can create the 2 different data sources just fine, but I cannot figure out
> how to join the data into 1 dataset, since you can only use 1 data source.
> Has anyone been able to do this? Help!|||I ended up having to create a linked server (on SQL Server) to connect to the
AS/400. I then used a SP to do the query statement. Let me know if you can
find any better way. The linked server does work, it's just not pretty. :-)
"Tango" wrote:
> Hod did you go with this question Lori?
> I have the same problem.
> Thanks
> Todd
> "Lori" wrote:
> > I have to create a join between tables on SQL Server & a table/file on an
> > AS400(DB2).
> > I can create the 2 different data sources just fine, but I cannot figure out
> > how to join the data into 1 dataset, since you can only use 1 data source.
> > Has anyone been able to do this? Help!|||A painfully learned suggestion. Even though with 4 part naming you can do a
heterogenous join, don't. It will pull on the data from the tables locally
to do the join. Use openquery, bring the appropriate data into a temp table
and then join with the temp table. If you have a query with a join (regular
join, not heterogenous) or you have a parameter then even with all the
tables in the query going against the same database it is likely to bring
over all the data locally to process the result. Hence my strong suggestion
to stay away from 4 part naming and use openquery (even though a pain)
instead.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Lori" <Lori@.discussions.microsoft.com> wrote in message
news:39D2A369-C0EC-44E1-B5AB-48A78DA74DB9@.microsoft.com...
>I ended up having to create a linked server (on SQL Server) to connect to
>the
> AS/400. I then used a SP to do the query statement. Let me know if you
> can
> find any better way. The linked server does work, it's just not pretty.
> :-)
> "Tango" wrote:
>> Hod did you go with this question Lori?
>> I have the same problem.
>> Thanks
>> Todd
>> "Lori" wrote:
>> > I have to create a join between tables on SQL Server & a table/file on
>> > an
>> > AS400(DB2).
>> > I can create the 2 different data sources just fine, but I cannot
>> > figure out
>> > how to join the data into 1 dataset, since you can only use 1 data
>> > source.
>> > Has anyone been able to do this? Help!

multiple data flows

Easy: read a SQL table with 500 fields, transform, write to flat file using SSIS.

But, I have hundreds of transformations to define using Lookups and Aggregates, Derived Column transformations. I wan to group the data flow transformations in usable (reasonable size) groups (packages, containers, subroutines, whatever you want to call it).

I cannot figure out a simple easy way of doing this most "simple" obvious thing.

Am I the only one on the planet who needs to do this?

Thx.

Newbie.

Hi Paul,

No you are not the only one that has seen a need to modularize the data flows. The current product is the version 1.0 and unfortunately it does not give you an intuitive way to do this.

Currently, you may be able to achieve some level of modularization by breaking your data flows and connecting them through staging objects (raw files, flat files, temporary tables, etc).

We are actively looking into improving this experience, though.

It will be there in one of the future releases.

Thanks,

Bob

|||I think your question is how to break up what would ordinarily be a very large data flow (hundreds of transformations?!), into multiple smaller data flows.

If so, you would use Raw Files to save the data pipeline to the disk so one data flow can end and another can begin. The Raw File destination will write to a file on the disk at the end of one data flow and the Raw File source will read from it to begin the next data flow. The raw file contains the pipeline metadata, so you don't have to worry about defining your columns (500?!) each time.
|||Okay, this makes sense and I was looking to do exactly what the two responses above suggest. Just thought this should be a LOT easier than this... and it sounds like it will be some day...

In case anyone is listening, what I would really like is to highlight a set of (connected) data flows and right-click and select Save As Sub Data Flow... and then give it a name... That's all. I understand that the mechanics behind the scene makes this not so easy -but, whatever, it should be possible to do this. MS: please make it so.

Thanks !
|||Thanks!|||

Paul Des wrote:

Okay, this makes sense and I was looking to do exactly what the two responses above suggest. Just thought this should be a LOT easier than this... and it sounds like it will be some day...

In case anyone is listening, what I would really like is to highlight a set of (connected) data flows and right-click and select Save As Sub Data Flow... and then give it a name... That's all. I understand that the mechanics behind the scene makes this not so easy -but, whatever, it should be possible to do this. MS: please make it so.

Thanks !

Paul,

I don't think I'm giving too much away by saying this will appear in the product one day. I'm hoping beyond hope that it will be in katmai.

Note to MSFT, whatever it takes to get this into katmai - do it!!!!

-Jamie

|||

Paul - thanks for the feedback. We have been working on this as a key enhancement to the product and the experience is somewhat similar to what you describe above. As Jamie knows, not everything we're working on are for the next release, but it will not be very long before you have this available.

Multiple Data Files

We have a large Database (91 GB) that is currently in one large data file. Now that we have muliple disk arrays I can split that up on I would like to have a couple data files. My question is, what is the best way to split this up? Should I keep one primary file group and just create another file, or should I create a file group for indexes and put those on that? This database is used for reporting only so it doesn't really have any writes being done on it.

Thanks much.do you have a lot aof medium size tables, or several very large ones?

The answer is always..."it depends"|||Unfortunately it is pretty much one big table. It is horribly designed.|||What's the DDL of the table?

How many physical arrarys do you have?

Let's say you have 3

Create 9 partitions for a partitioned view

put file 1,4,7 on array 1
put 2,5,8 on array 2
put 3,6,9 on array 3

Arrange the partitions so they are approximatley 1 GB each

The more files on to independant drives the better..and I think I would keep the indexes with the tables...

Anyone?|||Well, here is the DDL, it is a beast though.

CREATE TABLE [dbo].[CLARITY_TDL] (
[TDL_ID] [numeric](19, 0) NOT NULL ,
[DETAIL_TYPE] [int] NOT NULL ,
[TYPE] [int] NOT NULL ,
[POST_DATE] [datetime] NULL ,
[ORIG_POST_DATE] [datetime] NULL ,
[ORIG_SERVICE_DATE] [datetime] NULL ,
[PERIOD] [varchar] (6) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TX_ID] [int] NULL ,
[TRAN_TYPE] [int] NULL ,
[ALLOWED_AMOUNT] [numeric](12, 2) NULL ,
[CHARGE_SLIP_NUMBER] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TYPE_OF_SERVICE] [int] NULL ,
[MATCH_TRX_ID] [int] NULL ,
[MATCH_TX_TYPE] [int] NULL ,
[MATCH_PROC_ID] [int] NULL ,
[MATCH_PROV_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MATCH_LOC_ID] [int] NULL ,
[ACCOUNT_ID] [int] NULL ,
[PAT_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AMOUNT] [numeric](12, 2) NULL ,
[PATIENT_AMOUNT] [numeric](12, 2) NULL ,
[INSURANCE_AMOUNT] [numeric](12, 2) NULL ,
[RELATIVE_VALUE_UNIT] [numeric](12, 2) NULL ,
[CUR_CVG_ID] [int] NULL ,
[CUR_PLAN_ID] [int] NULL ,
[CUR_PAYOR_ID] [int] NULL ,
[CUR_FIN_CLASS] [int] NULL ,
[PERFORMING_PROV_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BILLING_PROVIDER_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORIGINAL_CVG_ID] [int] NULL ,
[ORIGINAL_PLAN_ID] [int] NULL ,
[ORIGINAL_PAYOR_ID] [int] NULL ,
[ORIGINAL_FIN_CLASS] [int] NULL ,
[PROC_ID] [int] NULL ,
[PROCEDURE_QUANTITY] [int] NULL ,
[CPT_CODE] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MODIFIER_ONE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MODIFIER_TWO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MODIFIER_THREE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[MODIFIER_FOUR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DX_ONE_ID] [int] NULL ,
[DX_TWO_ID] [int] NULL ,
[DX_THREE_ID] [int] NULL ,
[DX_FOUR_ID] [int] NULL ,
[DX_FIVE_ID] [int] NULL ,
[DX_SIX_ID] [int] NULL ,
[SERV_AREA_ID] [int] NULL ,
[LOC_ID] [int] NULL ,
[DEPT_ID] [int] NULL ,
[POS_ID] [int] NULL ,
[CUSTOMER_ITEM_ONE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMER_ITEM_TWO] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMER_ITEM_THREE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMER_ITEM_FOUR] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CUSTOMER_ITEM_FIVE] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[BILL_CLAIM_AMOUNT] [numeric](12, 2) NULL ,
[INVOICE_NUMBER] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ABF_FORM_ID] [int] NULL ,
[CLM_CLAIM_ID] [int] NULL ,
[BILL_HOLD_AMOUNT] [numeric](12, 2) NULL ,
[PAT_AGING_DAYS] [int] NULL ,
[INS_AGING_DAYS] [int] NULL ,
[ACTION_CVG_ID] [int] NULL ,
[ACTION_PLAN_ID] [int] NULL ,
[ACTION_PAYOR_ID] [int] NULL ,
[ACTION_FIN_CLASS] [int] NULL ,
[DEBIT_GL_NUM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CREDIT_GL_NUM] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REASON_CODE_ID] [int] NULL ,
[USER_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[COPAY_INDICATOR] [int] NULL ,
[TX_NUM] [int] NULL ,
[ORIG_PRICE] [numeric](12, 2) NULL ,
[PRICE_CONTRACT_ID] [int] NULL ,
[CONTRACT_DISCOUNT] [numeric](12, 2) NULL ,
[INT_PAT_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ORIG_AMT] [numeric](12, 2) NULL ,
[PAT_TYPE_C] [int] NULL ,
[PRIM_CARE_PROV] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[REFERRAL_ID] [int] NULL ,
[REFERRAL_SOURCE_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PRIM_LOCATION] [int] NULL ,
[RVU_WORK] [numeric](12, 2) NULL ,
[RVU_OVERHEAD] [numeric](12, 2) NULL ,
[RVU_MALPRACTICE] [numeric](12, 2) NULL ,
[POSTING_BATCH_NUM] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VOID_USER_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[R_NEW_CHG_TX_ID] [int] NULL ,
[R_ORIG_CHG_TX_ID] [int] NULL ,
[R_ORIG_POST_DATE] [datetime] NULL ,
[R_ORIG_AMOUNT] [numeric](12, 2) NULL ,
[R_ORIG_PAT_AMOUNT] [numeric](12, 2) NULL ,
[R_ORIG_FIN_CLASS] [int] NULL ,
[R_ORIG_PAYOR_ID] [int] NULL ,
[R_ORIG_PLAN_ID] [int] NULL ,
[R_ORIG_CVG_ID] [int] NULL ,
[REFERENCE_NUMBER] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAYMENT_SOURCE_C] [int] NULL ,
[TX_COMMENT] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ANESTHESIA_TYPE_C] [int] NULL ,
[EMERGENCY_STATUS_C] [int] NULL ,
[PHYSICAL_STATUS_C] [int] NULL ,
[CONCURRENCY_CALC] [numeric](6, 2) NULL ,
[CONCURRENCY_OVER] [numeric](6, 2) NULL ,
[BASE_UNITS] [numeric](6, 2) NULL ,
[TIMED_UNITS] [numeric](6, 2) NULL ,
[PHY_STATUS_UNITS] [numeric](6, 2) NULL ,
[EMERGENCY_UNITS] [numeric](6, 2) NULL ,
[AGE_UNITS] [numeric](6, 2) NULL ,
[MED_SUP_MOD_OVER_C] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[ANESTHESIA_CERT] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TECH_CHARGE_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[NIA_OUTCOME_CODE_C] [int] NULL ,
[PROC_MED_NEC_FLAG] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRD_CHARGE_SLIP_NO] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[VISIT_NUMBER] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[CRNA_CHARGE_ID] [int] NULL ,
[ORIG_ANES_CHG_ID] [int] NULL ,
[CONTESTED_FLAG_YN] [char] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PAT_ENC_CSN_ID] [int] NULL ,
[ACTION_USER_ID] [varchar] (12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TDL_EXTRACT_DATE] [datetime] NULL ,
[INSURANCE_ID] [varchar] (17) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[SPECIMEN_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TEST_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LAB_ID] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PANEL_ID] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[PROV_SPECIALTY_C] [int] NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[CLARITY_TDL] WITH NOCHECK ADD
CONSTRAINT [PK__CLARITY_TDL__746F28F1] PRIMARY KEY CLUSTERED
(
[TDL_ID]
) WITH FILLFACTOR = 80 ON [PRIMARY]
GO

CREATE INDEX [CLARITY_TDL__DETAIL_TYPE] ON [dbo].[CLARITY_TDL]([DETAIL_TYPE]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY]
GO

CREATE INDEX [CLARITY_TDL__POST_DATE] ON [dbo].[CLARITY_TDL]([POST_DATE]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY]
GO

CREATE INDEX [CLARITY_TDL__PERIOD] ON [dbo].[CLARITY_TDL]([PERIOD]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY]
GO

CREATE INDEX [CLARITY_TDL__SERV_AREA_ID] ON [dbo].[CLARITY_TDL]([SERV_AREA_ID]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY]
GO

CREATE INDEX [CLARITY_TDL__ORIG_POST_DATE] ON [dbo].[CLARITY_TDL]([ORIG_POST_DATE]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY]
GO

CREATE INDEX [CLARITY_TDL__TX_ID] ON [dbo].[CLARITY_TDL]([TX_ID]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY]
GO

CREATE INDEX [CLARITY_TDL__ORIG_SERVICE_DATE] ON [dbo].[CLARITY_TDL]([ORIG_SERVICE_DATE]) WITH FILLFACTOR = 80, PAD_INDEX ON [PRIMARY]
GO

We do have 3 physical disk arrays for this.

What is the advantage of putting multiple files on one array? That wouldn't decrease the load at all.|||I think Brett's idea of splitting out the filegroups is to increase the odds that subsequent reads will come from a different array. It doesn't decrease your load, but it should improve the distribution of your load across your arrays. I don't know what the optimum number is...

The only thing that jumps out at me, other than the fact most of your columns are NULL, is that you are leaving 20% of your pages empty because you don't really have any writes. My guess is your writes are done based on your PK, TDL_ID, which I'm also guessing is a monotonically increasing value that will result in all your inserts being done at the end of your page chain. Thus, your pages stay 20% empty.

If these assumptions are correct, changing your FILLFACTOR to 100% will decrease your i/o's by ~ 20%.|||I left the pages at 20% empty because we get a fairly large load on the database every night (about 50 MB). TDL_ID is however monotonically increasing, so you are right, the inserts would be at the end of the chain, I never thought of that. Thanks for the help.|||It'll increase the number of threads due to parallelism AND will reduce contention because the file will be on different arrays. And by placing the "next of kin" data on separate arrays, I'm assuming that a particular request for data that is "close" will also thread out.

I was just reading about Federated Servers (http://vyaskn.tripod.com/what_are_federated_databases.htm), but I've never used them, and I suspect that not many people do.

Since they're also using partitioned views as well, I don't see how dragging the network in to the equation is a plus.

I would just try the partitioning on the single box first.

Actually, I would consider normalizing the table first.|||Great, thanks for all of your help on this.

I would love to normalize that table, but unfortunately it is out of my hands. That table is created and used by a third party product. I know it is ugly, but there isnt much I can do about it.|||I wonder how many level you can do partitioning...I'll have to play with that..

BUT...if you could, you could partition out the data, AND create another view that is a join to normalized tables and a partition view.

You would just have the final view as the exact same name as your messy table.

AND, can someone PLEASE tell me why all 3rd party stuff looks like crap all the time?|||That is a good idea. I have been wanting to normalize that thing since the first time I saw it.

Wouldn't that have a problem with inserts though? The data is also loaded through a third party product.

If I partition the table and use a view to UNION them all together, the load would have to be changed, correct? That is why I originally just thought I would put all the indexes on a seperate data file.

multiple connection to the same data base file: is it possible?

Hi,

Please tell me if my query is suitable for this group or should I post this to the appropriate one. I am trying to find out if it is possible to create a new connection to an already opened database. I am using vb to create the user interface for my application.

I have programmed in a connection to a database file, which will display the file in the main window of the app. Through another form, the user can key in certain criteria which will be used generate a new qeury result of the already opened database file and display the query result in a new window. For this purpose, I have tested this by creating a new connection for the diplay form but I receive this error message :

'C:\MITalong\Project\test'. It is already opened exclusively by another user, or you need permission to view its data."

What is the correct way of doing this, ie. doing queries on an opened database file to generate result on another window. I am utilising the oledb as I wish for my app to work with microsoft access files.

Thank you.

Hi,

are you sure you are in the right group ? That sounds like a Access database file ratehr than a SQL Server file ? if you are using a Access database file you will have to tweak in the settings page, the lock status of an access database file which shouldn′t be then exclusivly locked if someone opens it.

Or are you using SQl Server Express userInbstance functionality ?

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

|||

Oops,

Thanks for the feed back. I final realised I have posted this to the wrong group.

Once again thank you.

|||

Hi again,

I have rechecked all the groups desription in this forum, and this is where queries on oledb connections can be directed.

Yes, I am using access for my project using the oledb thing. I forgot to mention I am quite new to vb.net way of things so some terms used might be lost to me. Regarding my starter thread, does anybody have any ideas? btw, I am working on a window app, so there is no web usage.

Thanks.

Multiple backslashes in physical file name

Accidentally, I've managed to have this as part of a Create Database:

... FILENAME = N'C:\MSSQL\Data\\\testdb_Log.LDF' ...

, notice the triple backslash. The Create Database statement works fine,
and sp_helpdb says the log file name is:

C:\MSSQL\Data\\\testdb_Log.LDF

I noticed the MSDOS command prompt also allows multiple backslashes,
they're reduced to one when performing the command and I guess
SQL Server does the same thing, so no problem so far really.

But is it supposed to work this way? Quite confusing, isn't it?Not sure if it supposed to work that way, but it would get quite confusing after a while. It may even give you "unexpected results" if you detach and try to reattach the files. If you can get away with it, I would heartily suggest getting the files renamed.|||This sounds like a question for Old Man Phelan.|||It gets better... This actually goes back to the Unix days, and has to do with how pathing is logically based. Just for jolly factors, try to explain the difference between:dir c:\windows\\system32
dir c:\\windows\system32Why does one work, and one fail? What causes the time lag? What did you really do?

Answers will follow, but I'd love to hear folks try to talk this fiasco out a bit first.

-PatP|||Hmnmm...I got a network path not found message from the second one. I am going to guess that the system is interpreting this as "Using the protocol 'C', go to the machine called 'Windows', and get the file called 'System32'". The delay is caused by waiting for all of the DNS servers to chime in saying "Nope. No server called 'Windows' here." Still no idea why the first example works, though.|||Hello Pat I can proof both of your lines correct ;)

check this
for
dir c:\windows\\system32

c:\windows\ md (alt+092)system32

dir c:\\windows\system32

c:\md (alt+092)windows

Now both are valid folders and path too... ;)
Put your Numlock on and type numbers from there|||Hmnmm...I got a network path not found message from the second one. I am going to guess that the system is interpreting this as "Using the protocol 'C', go to the machine called 'Windows', and get the file called 'System32'". The delay is caused by waiting for all of the DNS servers to chime in saying "Nope. No server called 'Windows' here." Still no idea why the first example works, though.Bingo! Full marks for that half of the problem!

Now to give a few more clues on the first half of the problem...

dir c:\windows\system32\.\
dir c:\windows\system32\..\
dir c:\windows\system32\..\.\
dir c:\windows\system32\..\..\What do "dot" and "double dot" refer to? Based on that answer, why is an empty reference logically the same as a "dot" reference?

NOTE: If you have only worked with Microsoft Operating Systems, you are at a sore disadvantage here. This is another clue.

-PatP|||It may even give you "unexpected results" if you detach and try to reattach the files. If you can get away with it, I would heartily suggest getting the files renamed.

That's how I noticed it, I was trying to attach a database
file when I got an error message because the referred Log file didn't exist. Then I happened
to notice the double backslash in the Log file name. This wasn't the cause of the error though, the file
was simply missing. But, no idea how the double backslash got there!|||But, no idea how the double backslash got there!My first guess would be a typo (fat fingers, flying furiously, flubbing fiendishly). My next guess would be a keyboard problem (key bounce). Next would be a numeric directory name typed with Num Lock turned off... After that, I'd give up!

-PatP|||Bingo! Full marks for that half of the problem!

Now to give a few more clues on the first half of the problem...

dir c:\windows\system32\.\
dir c:\windows\system32\..\
dir c:\windows\system32\..\.\
dir c:\windows\system32\..\..\What do "dot" and "double dot" refer to? Based on that answer, why is an empty reference logically the same as a "dot" reference?

NOTE: If you have only worked with Microsoft Operating Systems, you are at a sore disadvantage here. This is another clue.

-PatP

. refers to the current directory you are in (i.e. c:\windows\system32\.\ is the c:\windows\system32 directory.)

.. refers to the parent directory of the one you are in (i.e c:\windows\system32\.. refers to c:\windows directory.)

so the ..\..\ would put you at the c:\WINDOWS folder.
[EDIT ... I typed too fast] ... ..\..\ puts you at the root of the C drive!|||Hmm. I am still a touch confused about the empty reference. When you type in

dir \

or

cd \

You end up referring to the root of the current drive. dir \\ gives you

C:\Documents and Settings\mcrowley>dir \\
The filename, directory name, or volume label syntax is incorrect.

Hmm...It may be time to dust off the Linux test machine...|||\\ on some systems means referring to a network
computer.|||My first guess ...

It's a SP who makes up the filename I think.
I'll have to trace myself through the code. Funny it
hasn't occurred when we've been using the same SP
before.

Multiple <Create>...</Create> in one file Problem

I'm trying to create multiple dimensions (almost 800) in an SSAS 2005 database through one XMLA script file in an XMLA Query window in Management Studio. I have generated the <Create>...</Create> commands and tested that a representative sample of them execute correctly individually. However, when I paste them all (or just 2 of them) into the query window one right after the other and attempt to run the file, I get an error that claims only one <Create> command can appear:

"The 'Create' element at line 58, column 76 ('http://schemas.microsoft.com/analysisservices/2003/engine' namespace) appears more than once under Envelope/Body/Execute/Command."

If I understand the documentation on the Create element correctly, you can have more than one such element within the <Command> </Command> element:

"0-n: Optional element that can occur more than once."

Anyone have an ideas on how to make this work? I'm trying to create 790+ dimensions, so I'd sure like to avoid executing each script manually.

hello Bob,

i think you could use <Batch> and specify all your create elements underneath. Books Online describe Batch in more details.

hope this helps,

|||

Hi, Mary! Thanks for your response.

I've actually tried <Batch><Create>...</Create><Create>...</Create></Batch> and it did not work. It complains that the Batch element may not occur within the <Command> element, although the documentation does say you can have <Batch> within <Command>.

Other ideas?

Bob

|||

i think something like the following should work:

<Envelope xmlns="http://schemas.xmlsoap.org/soap/envelope/">
<Body>
<Execute xmlns="urn:schemas-microsoft-com:xml-analysis">
<Command>
<Batch xmlns="http://schemas.microsoft.com/analysisservices/2003/engine">
<Create>..1st create.. .</Create>
<Create>..2nd create...</Create>
</Batch>
</Command>
<Properties>
<PropertyList>... properties list if any .... </PropertyList>
</Properties>
</Execute>
</Body>
</Envelope>

actually, SSMS can accept just the <batch> part itself i think (since it adds envelope/body .. etc. when needed).

hope this helps,

|||

Aha! I had not included the xmlns namespace attribute. When I added the namespace attribute xmlns="http://schemas.microsoft.com/analysisservices/2003/engine", it worked like a charm.

Thanks, Mary!