Hi all,
I bring data from 15 different files into several SQL Server tables every
night. I first bring data from CSV file and store them into 15 different
intermediary tables. Then I execute stored procedure that imports data from
intermediary tables into the tables that our users use.
My question is about clearing the intermediary tables. Right now I am
just truncating the tables before importing new data from CSV files. So I
use Insert Into...Select query. But this can create a problem if any of the
columns in the CSV files are removed. So I am thinking if it is worthwhile
droping the tables and then import data using Select...Into query. This way
even if a column is removed, the data will still be imported from the CSV
files. My question is are there any drawbacks in droping and recreating 15
tables everynight instead of truncating them?
Thanks.Actually, if you're database recovery model is not FULL, a SELECT INTO is
going to run significantly faster than an INSERT SELECT, because it's
considered a BULK operation.
Can't think of any drawbacks.
BG, SQL Server MVP
www.SolidQualityLearning.com
"Nikhil Patel" <nikhil0100@.aol.com> wrote in message
news:O9DvE8GNFHA.3512@.TK2MSFTNGP15.phx.gbl...
> Hi all,
> I bring data from 15 different files into several SQL Server tables
> every
> night. I first bring data from CSV file and store them into 15 different
> intermediary tables. Then I execute stored procedure that imports data
> from
> intermediary tables into the tables that our users use.
> My question is about clearing the intermediary tables. Right now I am
> just truncating the tables before importing new data from CSV files. So I
> use Insert Into...Select query. But this can create a problem if any of
> the
> columns in the CSV files are removed. So I am thinking if it is worthwhile
> droping the tables and then import data using Select...Into query. This
> way
> even if a column is removed, the data will still be imported from the CSV
> files. My question is are there any drawbacks in droping and recreating 15
> tables everynight instead of truncating them?
> Thanks.
>|||I can see some drawbacks to creating the tables each time. If columns
are missing or renamed then won't your SPs that access them fail
anyway? You should avoid using SELECT * in production code. Plus,
you'll have to give DDL admin rights to whatever process performs the
load.
One possible approach is to create views that only expose certain
columns in the base table(s) then use a Dynamic Properties task in DTS
to load the data to the appropriate view each time. This does assume
you can read some metadata that determines what columns should be
present. If that metadata isn't available then I would have thought it
desirable to fail the load whenever some data was missing. What if the
file contains zero columns - just empty rows?
David Portas
SQL Server MVP
--sql
Showing posts with label files. Show all posts
Showing posts with label files. Show all posts
Tuesday, March 27, 2012
Sunday, March 11, 2012
Create PDF File
I have an application in Power Builder 6.5 and I use SQL Server 2000.
How Can I create pdf files?
I want to create pdf files using Power or SQL.
Is it possible?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I guess you cannot create PDF files using SQL Server 2000. I don't know about
Powerbuilder. Adobe has a PDF file creation services. Please take a look at
http://www.adobe.com/products/server/docgen.html
Did you mean storing PDF files in SQL Server 2000?
"Karla Gomez" wrote:
> I have an application in Power Builder 6.5 and I use SQL Server 2000.
> How Can I create pdf files?
> I want to create pdf files using Power or SQL.
> Is it possible?
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Vikas,
While you may not be able to directly create Adobe PDF files directly with
T-SQL (and I'm still researching this), you can use a 3rd party tool and a
T-SQL query and generate a PDF file as the output. Specificly, see
ceTesoftware at http://www.dynamicpdf.com/ and their product DynamicPDF
ReportWriter and code example at:
http://www.dynamicpdf.com/Products/R...r/Examples.csp
The basic program flow for a script using DynamicPDF? ReportWriter is as
follows:
Create a report object.
Define the database connection and query.
Add report elements (i.e. RecordBox) to the header, section header, body,
section footer and/or footer.
Generate (Draw) the PDF report.
ASP Example Script: <!-- METADATA TYPE="typelib"
UUID="{567E47DC-8220-403F-B348-EB9A035648BB}" -->
<%
Dim objReport
Set objReport = Server.CreateObject("DPDF_RptWtr.Report")
With objReport.Command
'An ADO Connection String (Northwind DB in this example)
.ActiveConnection = "File Name = " & Server.MapPath("Northwind.udl")
'A SQL Query
.CommandText = "SELECT CompanyName FROM Customers"
End With
With objReport.Header
.Height = 24
.AddLabel "Header goes here", 0, 0, 504, 12
End With
With objReport.Body
.Height = 12
.AddRecordBox "CompanyName", 0, 0, 504, 12
End With
objReport.DrawToASP
Set objReport = Nothing
%>
And the following link demostrates a dynamiclly built PDF file:
http://www.dynamicpdf.com/Products/R...t_Verdana.aspx
I'm sure there are other such products as SQL Server 2000 Reporting Services
supports writing the output to the PDF format.
Regards,
John
"Vikas Ahuja [MSFT]" wrote:
[vbcol=seagreen]
> I guess you cannot create PDF files using SQL Server 2000. I don't know about
> Powerbuilder. Adobe has a PDF file creation services. Please take a look at
> http://www.adobe.com/products/server/docgen.html
> Did you mean storing PDF files in SQL Server 2000?
> "Karla Gomez" wrote:
|||Karla & Vikas,
I knew I had seen how to do this somewhere...
Creating a PDF from a Stored Procedure
http://www.sqlservercentral.com/colu...dprocedure.asp
Regards,
John
"John Kane" <JohnKane@.discussions.microsoft.com> wrote in message
news:DC2A1EB6-DEF1-47DA-919C-22A7336C5638@.microsoft.com...
> Vikas,
> While you may not be able to directly create Adobe PDF files directly with
> T-SQL (and I'm still researching this), you can use a 3rd party tool and a
> T-SQL query and generate a PDF file as the output. Specificly, see
> ceTesoftware at http://www.dynamicpdf.com/ and their product DynamicPDF
> ReportWriter and code example at:
> http://www.dynamicpdf.com/Products/R...r/Examples.csp
> The basic program flow for a script using DynamicPDFT ReportWriter is as
> follows:
> Create a report object.
> Define the database connection and query.
> Add report elements (i.e. RecordBox) to the header, section header, body,
> section footer and/or footer.
> Generate (Draw) the PDF report.
> ASP Example Script: <!-- METADATA TYPE="typelib"
> UUID="{567E47DC-8220-403F-B348-EB9A035648BB}" -->
> <%
> Dim objReport
> Set objReport = Server.CreateObject("DPDF_RptWtr.Report")
> With objReport.Command
> 'An ADO Connection String (Northwind DB in this example)
> .ActiveConnection = "File Name = " & Server.MapPath("Northwind.udl")
> 'A SQL Query
> .CommandText = "SELECT CompanyName FROM Customers"
> End With
> With objReport.Header
> .Height = 24
> .AddLabel "Header goes here", 0, 0, 504, 12
> End With
> With objReport.Body
> .Height = 12
> .AddRecordBox "CompanyName", 0, 0, 504, 12
> End With
> objReport.DrawToASP
> Set objReport = Nothing
> %>
> And the following link demostrates a dynamiclly built PDF file:
>
http://www.dynamicpdf.com/Products/R...t_Verdana.aspx
> I'm sure there are other such products as SQL Server 2000 Reporting
Services[vbcol=seagreen]
> supports writing the output to the PDF format.
> Regards,
> John
>
>
>
> "Vikas Ahuja [MSFT]" wrote:
about[vbcol=seagreen]
at[vbcol=seagreen]
|||hello Karla,
did you find a solution to this problem? Did you use the acrobat writer or stored procedure? how difficult is it to create pdf's on the fly?
bev
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi Bev,
Yes, I did find a solution to this problem. You can use T-SQL and therefore
stored procedures via the methods documented at:
Creating a PDF from a Stored Procedure
http://www.sqlservercentral.com/colu...dprocedure.asp
Regards,
John
"bev harris" wrote:
> hello Karla,
> did you find a solution to this problem? Did you use the acrobat writer or stored procedure? how difficult is it to create pdf's on the fly?
> bev
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>
|||Karla
This company provide COM and .NET components.
http://websupergoo.com/products.htm
I have used version 3 which was a COM component with .NET 1.0 and it worked
very well. Tickets generated on www.easyCinema.com use this technology.
I have never used this product...
http://www.tallcomponents.com/
"Karla Gomez" wrote:
> I have an application in Power Builder 6.5 and I use SQL Server 2000.
> How Can I create pdf files?
> I want to create pdf files using Power or SQL.
> Is it possible?
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
How Can I create pdf files?
I want to create pdf files using Power or SQL.
Is it possible?
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
I guess you cannot create PDF files using SQL Server 2000. I don't know about
Powerbuilder. Adobe has a PDF file creation services. Please take a look at
http://www.adobe.com/products/server/docgen.html
Did you mean storing PDF files in SQL Server 2000?
"Karla Gomez" wrote:
> I have an application in Power Builder 6.5 and I use SQL Server 2000.
> How Can I create pdf files?
> I want to create pdf files using Power or SQL.
> Is it possible?
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
|||Vikas,
While you may not be able to directly create Adobe PDF files directly with
T-SQL (and I'm still researching this), you can use a 3rd party tool and a
T-SQL query and generate a PDF file as the output. Specificly, see
ceTesoftware at http://www.dynamicpdf.com/ and their product DynamicPDF
ReportWriter and code example at:
http://www.dynamicpdf.com/Products/R...r/Examples.csp
The basic program flow for a script using DynamicPDF? ReportWriter is as
follows:
Create a report object.
Define the database connection and query.
Add report elements (i.e. RecordBox) to the header, section header, body,
section footer and/or footer.
Generate (Draw) the PDF report.
ASP Example Script: <!-- METADATA TYPE="typelib"
UUID="{567E47DC-8220-403F-B348-EB9A035648BB}" -->
<%
Dim objReport
Set objReport = Server.CreateObject("DPDF_RptWtr.Report")
With objReport.Command
'An ADO Connection String (Northwind DB in this example)
.ActiveConnection = "File Name = " & Server.MapPath("Northwind.udl")
'A SQL Query
.CommandText = "SELECT CompanyName FROM Customers"
End With
With objReport.Header
.Height = 24
.AddLabel "Header goes here", 0, 0, 504, 12
End With
With objReport.Body
.Height = 12
.AddRecordBox "CompanyName", 0, 0, 504, 12
End With
objReport.DrawToASP
Set objReport = Nothing
%>
And the following link demostrates a dynamiclly built PDF file:
http://www.dynamicpdf.com/Products/R...t_Verdana.aspx
I'm sure there are other such products as SQL Server 2000 Reporting Services
supports writing the output to the PDF format.
Regards,
John
"Vikas Ahuja [MSFT]" wrote:
[vbcol=seagreen]
> I guess you cannot create PDF files using SQL Server 2000. I don't know about
> Powerbuilder. Adobe has a PDF file creation services. Please take a look at
> http://www.adobe.com/products/server/docgen.html
> Did you mean storing PDF files in SQL Server 2000?
> "Karla Gomez" wrote:
|||Karla & Vikas,
I knew I had seen how to do this somewhere...
Creating a PDF from a Stored Procedure
http://www.sqlservercentral.com/colu...dprocedure.asp
Regards,
John
"John Kane" <JohnKane@.discussions.microsoft.com> wrote in message
news:DC2A1EB6-DEF1-47DA-919C-22A7336C5638@.microsoft.com...
> Vikas,
> While you may not be able to directly create Adobe PDF files directly with
> T-SQL (and I'm still researching this), you can use a 3rd party tool and a
> T-SQL query and generate a PDF file as the output. Specificly, see
> ceTesoftware at http://www.dynamicpdf.com/ and their product DynamicPDF
> ReportWriter and code example at:
> http://www.dynamicpdf.com/Products/R...r/Examples.csp
> The basic program flow for a script using DynamicPDFT ReportWriter is as
> follows:
> Create a report object.
> Define the database connection and query.
> Add report elements (i.e. RecordBox) to the header, section header, body,
> section footer and/or footer.
> Generate (Draw) the PDF report.
> ASP Example Script: <!-- METADATA TYPE="typelib"
> UUID="{567E47DC-8220-403F-B348-EB9A035648BB}" -->
> <%
> Dim objReport
> Set objReport = Server.CreateObject("DPDF_RptWtr.Report")
> With objReport.Command
> 'An ADO Connection String (Northwind DB in this example)
> .ActiveConnection = "File Name = " & Server.MapPath("Northwind.udl")
> 'A SQL Query
> .CommandText = "SELECT CompanyName FROM Customers"
> End With
> With objReport.Header
> .Height = 24
> .AddLabel "Header goes here", 0, 0, 504, 12
> End With
> With objReport.Body
> .Height = 12
> .AddRecordBox "CompanyName", 0, 0, 504, 12
> End With
> objReport.DrawToASP
> Set objReport = Nothing
> %>
> And the following link demostrates a dynamiclly built PDF file:
>
http://www.dynamicpdf.com/Products/R...t_Verdana.aspx
> I'm sure there are other such products as SQL Server 2000 Reporting
Services[vbcol=seagreen]
> supports writing the output to the PDF format.
> Regards,
> John
>
>
>
> "Vikas Ahuja [MSFT]" wrote:
about[vbcol=seagreen]
at[vbcol=seagreen]
|||hello Karla,
did you find a solution to this problem? Did you use the acrobat writer or stored procedure? how difficult is it to create pdf's on the fly?
bev
************************************************** ********************
Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
|||Hi Bev,
Yes, I did find a solution to this problem. You can use T-SQL and therefore
stored procedures via the methods documented at:
Creating a PDF from a Stored Procedure
http://www.sqlservercentral.com/colu...dprocedure.asp
Regards,
John
"bev harris" wrote:
> hello Karla,
> did you find a solution to this problem? Did you use the acrobat writer or stored procedure? how difficult is it to create pdf's on the fly?
> bev
> ************************************************** ********************
> Sent via Fuzzy Software @. http://www.fuzzysoftware.com/
> Comprehensive, categorised, searchable collection of links to ASP & ASP.NET resources...
>
|||Karla
This company provide COM and .NET components.
http://websupergoo.com/products.htm
I have used version 3 which was a COM component with .NET 1.0 and it worked
very well. Tickets generated on www.easyCinema.com use this technology.
I have never used this product...
http://www.tallcomponents.com/
"Karla Gomez" wrote:
> I have an application in Power Builder 6.5 and I use SQL Server 2000.
> How Can I create pdf files?
> I want to create pdf files using Power or SQL.
> Is it possible?
>
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
>
Thursday, March 8, 2012
Create odbc / sql
It is possible to create odbc through files? what is the code?
Thanks FrancescoDo you mean create a DSN? If so, check the following
examples:
HOWTO: Create and Remove a DSN in Visual Basic
http://support.microsoft.com/defaul...b;EN-US;q171146
HOWTO: Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/defaul...b;EN-US;q184608
-Sue
On Tue, 24 Feb 2004 10:23:41 GMT, "Francesco Pellegatta"
<francesco@.pegasopoint.it> wrote:
>It is possible to create odbc through files? what is the code?
>Thanks Francesco
>
Thanks FrancescoDo you mean create a DSN? If so, check the following
examples:
HOWTO: Create and Remove a DSN in Visual Basic
http://support.microsoft.com/defaul...b;EN-US;q171146
HOWTO: Programmatically Create a DSN for SQL Server with VB
http://support.microsoft.com/defaul...b;EN-US;q184608
-Sue
On Tue, 24 Feb 2004 10:23:41 GMT, "Francesco Pellegatta"
<francesco@.pegasopoint.it> wrote:
>It is possible to create odbc through files? what is the code?
>Thanks Francesco
>
Friday, February 24, 2012
Create File
Hi
Is it possible to create file on server side with sql server 2005.
lets say
I have table and PDF files stored in this table (binary)
No i want to save this file on server side to specific folder?
Best regards
Mex
Sure as long as the SQL Server service account has the permission to do so.
Simplest example is EXEC xp_cmdshell 'dir > c:\temp\junk.txt'
Linchi
"Mex" wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
>
> Best regards
> Mex
>
>
|||On Jun 15, 8:32 am, "Mex" <redivi...@.hot.ee> wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
> Best regards
> Mex
Sounds like a good place to use a CLR stored procedure if your app/
middle tier won't provide this.
Is it possible to create file on server side with sql server 2005.
lets say
I have table and PDF files stored in this table (binary)
No i want to save this file on server side to specific folder?
Best regards
Mex
Sure as long as the SQL Server service account has the permission to do so.
Simplest example is EXEC xp_cmdshell 'dir > c:\temp\junk.txt'
Linchi
"Mex" wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
>
> Best regards
> Mex
>
>
|||On Jun 15, 8:32 am, "Mex" <redivi...@.hot.ee> wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
> Best regards
> Mex
Sounds like a good place to use a CLR stored procedure if your app/
middle tier won't provide this.
Create File
Hi
Is it possible to create file on server side with sql server 2005.
lets say
I have table and PDF files stored in this table (binary)
No i want to save this file on server side to specific folder?
Best regards
MexSure as long as the SQL Server service account has the permission to do so.
Simplest example is EXEC xp_cmdshell 'dir > c:\temp\junk.txt'
Linchi
"Mex" wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
>
> Best regards
> Mex
>
>|||On Jun 15, 8:32 am, "Mex" <redivi...@.hot.ee> wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
> Best regards
> Mex
Sounds like a good place to use a CLR stored procedure if your app/
middle tier won't provide this.
Is it possible to create file on server side with sql server 2005.
lets say
I have table and PDF files stored in this table (binary)
No i want to save this file on server side to specific folder?
Best regards
MexSure as long as the SQL Server service account has the permission to do so.
Simplest example is EXEC xp_cmdshell 'dir > c:\temp\junk.txt'
Linchi
"Mex" wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
>
> Best regards
> Mex
>
>|||On Jun 15, 8:32 am, "Mex" <redivi...@.hot.ee> wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
> Best regards
> Mex
Sounds like a good place to use a CLR stored procedure if your app/
middle tier won't provide this.
Create File
Hi
Is it possible to create file on server side with sql server 2005.
lets say
I have table and PDF files stored in this table (binary)
No i want to save this file on server side to specific folder?
Best regards
MexSure as long as the SQL Server service account has the permission to do so.
Simplest example is EXEC xp_cmdshell 'dir > c:\temp\junk.txt'
Linchi
"Mex" wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
>
> Best regards
> Mex
>
>|||On Jun 15, 8:32 am, "Mex" <redivi...@.hot.ee> wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
> Best regards
> Mex
Sounds like a good place to use a CLR stored procedure if your app/
middle tier won't provide this.
Is it possible to create file on server side with sql server 2005.
lets say
I have table and PDF files stored in this table (binary)
No i want to save this file on server side to specific folder?
Best regards
MexSure as long as the SQL Server service account has the permission to do so.
Simplest example is EXEC xp_cmdshell 'dir > c:\temp\junk.txt'
Linchi
"Mex" wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
>
> Best regards
> Mex
>
>|||On Jun 15, 8:32 am, "Mex" <redivi...@.hot.ee> wrote:
> Hi
> Is it possible to create file on server side with sql server 2005.
> lets say
> I have table and PDF files stored in this table (binary)
> No i want to save this file on server side to specific folder?
> Best regards
> Mex
Sounds like a good place to use a CLR stored procedure if your app/
middle tier won't provide this.
Tuesday, February 14, 2012
Create database on NAS
I am using SQL 2005 std edtition. Everyone time I create a new database with
files being located to say \\nas\db2. I get 5110 error.(5110 (File
'file_name' Is On A Network Device Not Supported For Database Files).
I read about traceon and other MS KBs but no luck. Anyone ?
thanks(a) I strongly recommend NOT using a NAS for SQL Server... this is not
exactly a robust environment to be running a high performance database
application.
(b) if you really want to go through with it, then you should be mapping a
driver letter for the SQL Server service account user (and any other user
that will need to access it). Ideally, SQL Server uses a local drive (even
if we are talking SAN / direct attached storage), but barring that, it at
least needs to *look* like a local drive.
A
"andyoye" <andyoye@.nospam.com> wrote in message
news:%23RA0%23q04HHA.1484@.TK2MSFTNGP06.phx.gbl...
>I am using SQL 2005 std edtition. Everyone time I create a new database
>with files being located to say \\nas\db2. I get 5110 error.(5110 (File
>'file_name' Is On A Network Device Not Supported For Database Files).
> I read about traceon and other MS KBs but no luck. Anyone ?
> thanks
>
>|||And here's a KB on the topic:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uiZtE104HHA.4476@.TK2MSFTNGP06.phx.gbl...
> (a) I strongly recommend NOT using a NAS for SQL Server... this is not exactly a robust
> environment to be running a high performance database application.
> (b) if you really want to go through with it, then you should be mapping a driver letter for the
> SQL Server service account user (and any other user that will need to access it). Ideally, SQL
> Server uses a local drive (even if we are talking SAN / direct attached storage), but barring
> that, it at least needs to *look* like a local drive.
> A
>
> "andyoye" <andyoye@.nospam.com> wrote in message news:%23RA0%23q04HHA.1484@.TK2MSFTNGP06.phx.gbl...
>>I am using SQL 2005 std edtition. Everyone time I create a new database with files being located
>>to say \\nas\db2. I get 5110 error.(5110 (File 'file_name' Is On A Network Device Not Supported
>>For Database Files).
>> I read about traceon and other MS KBs but no luck. Anyone ?
>> thanks
>>
>
files being located to say \\nas\db2. I get 5110 error.(5110 (File
'file_name' Is On A Network Device Not Supported For Database Files).
I read about traceon and other MS KBs but no luck. Anyone ?
thanks(a) I strongly recommend NOT using a NAS for SQL Server... this is not
exactly a robust environment to be running a high performance database
application.
(b) if you really want to go through with it, then you should be mapping a
driver letter for the SQL Server service account user (and any other user
that will need to access it). Ideally, SQL Server uses a local drive (even
if we are talking SAN / direct attached storage), but barring that, it at
least needs to *look* like a local drive.
A
"andyoye" <andyoye@.nospam.com> wrote in message
news:%23RA0%23q04HHA.1484@.TK2MSFTNGP06.phx.gbl...
>I am using SQL 2005 std edtition. Everyone time I create a new database
>with files being located to say \\nas\db2. I get 5110 error.(5110 (File
>'file_name' Is On A Network Device Not Supported For Database Files).
> I read about traceon and other MS KBs but no luck. Anyone ?
> thanks
>
>|||And here's a KB on the topic:
http://support.microsoft.com/default.aspx?scid=kb;en-us;304261
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uiZtE104HHA.4476@.TK2MSFTNGP06.phx.gbl...
> (a) I strongly recommend NOT using a NAS for SQL Server... this is not exactly a robust
> environment to be running a high performance database application.
> (b) if you really want to go through with it, then you should be mapping a driver letter for the
> SQL Server service account user (and any other user that will need to access it). Ideally, SQL
> Server uses a local drive (even if we are talking SAN / direct attached storage), but barring
> that, it at least needs to *look* like a local drive.
> A
>
> "andyoye" <andyoye@.nospam.com> wrote in message news:%23RA0%23q04HHA.1484@.TK2MSFTNGP06.phx.gbl...
>>I am using SQL 2005 std edtition. Everyone time I create a new database with files being located
>>to say \\nas\db2. I get 5110 error.(5110 (File 'file_name' Is On A Network Device Not Supported
>>For Database Files).
>> I read about traceon and other MS KBs but no luck. Anyone ?
>> thanks
>>
>
Create Database ... For Attach
Has anyone ever used the CREATE DATABASE statement with the FOR ATTACH clause?
I have a database with something like 128 physical data files which I have to move (detach, move the physical files and then re-attach). Since the limit for sp_attach_db is 16 files, I am forced to use the FOR ATTACH option. I was wondering if anyone had ever done this and if there were any hints and/or recommendations you might be willing to share...
Regards,
hmscott128 files?
Dude...how big is this thing?
what's the hardware config?
No I haven't done this...|||Sigh, it's another of my "inherited" databases. Worse, it's supposedly a "vendor supported" solution. Let's just not go there.
There are actually two databases on this one server (one has a mere 75 files, the other has 128). The bigger of the two is used to store images of...um...people.
The bigger database is ~240 GB. According to the vendor it will grow by approximately 50 - 80 GB per year. We are in the process of moving it from an "all on board" disk solution to a CX 500 SAN.
Oh, well, I am going to create a sandbox version of the db with ~32 files and play around with detaching an re-attaching it. As always thanks for your response.
Regards,
hmscott
128 files?
Dude...how big is this thing?
what's the hardware config?
No I haven't done this...|||um...people...just images...no mpgs?|||The images are of people you would probably rather not routinely associate with.
hmscott
PS. I just test the process on a bunch of empty files; it's a cinch. Here's the SQL..
Create the initial database on 32 data files and two log files:
-- =============================================
-- Create database on multiple data and transaction log files
-- =============================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'MultiFile')
DROP DATABASE MultiFile
GO
CREATE DATABASE MultiFile
ON PRIMARY
( NAME = FileName1,
FILENAME = N'e:\MSSQL\Data\FileName1.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName2,
FILENAME = N'e:\MSSQL\Data\FileName2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName3,
FILENAME = N'e:\MSSQL\Data\FileName3.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName4,
FILENAME = N'e:\MSSQL\Data\FileName4.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName5,
FILENAME = N'e:\MSSQL\Data\FileName5.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName6,
FILENAME = N'e:\MSSQL\Data\FileName6.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName7,
FILENAME = N'e:\MSSQL\Data\FileName7.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName8,
FILENAME = N'e:\MSSQL\Data\FileName8.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName9,
FILENAME = N'e:\MSSQL\Data\FileName9.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName10,
FILENAME = N'e:\MSSQL\Data\FileName10.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName11,
FILENAME = N'e:\MSSQL\Data\FileName11.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName12,
FILENAME = N'e:\MSSQL\Data\FileName12.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName13,
FILENAME = N'e:\MSSQL\Data\FileName13.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName14,
FILENAME = N'e:\MSSQL\Data\FileName14.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName15,
FILENAME = N'e:\MSSQL\Data\FileName15.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName16,
FILENAME = N'e:\MSSQL\Data\FileName16.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName17,
FILENAME = N'e:\MSSQL\Data\FileName17.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName18,
FILENAME = N'e:\MSSQL\Data\FileName18.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName19,
FILENAME = N'e:\MSSQL\Data\FileName19.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName20,
FILENAME = N'e:\MSSQL\Data\FileName20.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName21,
FILENAME = N'e:\MSSQL\Data\FileName21.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName23,
FILENAME = N'e:\MSSQL\Data\FileName23.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName24,
FILENAME = N'e:\MSSQL\Data\FileName24.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName25,
FILENAME = N'e:\MSSQL\Data\FileName25.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName26,
FILENAME = N'e:\MSSQL\Data\FileName26.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName27,
FILENAME = N'e:\MSSQL\Data\FileName27.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName28,
FILENAME = N'e:\MSSQL\Data\FileName28.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName29,
FILENAME = N'e:\MSSQL\Data\FileName29.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName30,
FILENAME = N'e:\MSSQL\Data\FileName30.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName31,
FILENAME = N'e:\MSSQL\Data\FileName31.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName32,
FILENAME = N'e:\MSSQL\Data\FileName32.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
LOG ON
( NAME = FileName_Log1,
FILENAME = N'e:\MSSQL\Data\FileName_Log1.ldf',
SIZE = 512KB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName_Log2,
FILENAME = N'e:\MSSQL\Data\FileName_Log2.ldf',
SIZE = 512KB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
GO
Now detach the database and use windows explorer to move the data files and log files to a new directory (in this case e:\test\data).
Now use CREATE DATABASE ... FOR ATTACH to re-attach the database:
CREATE DATABASE MultiFile
ON PRIMARY
(FILENAME = N'e:\Test\Data\FileName1.mdf'),
(FILENAME = N'e:\Test\Data\FileName2.ndf'),
(FILENAME = N'e:\Test\Data\FileName3.ndf'),
(FILENAME = N'e:\Test\Data\FileName4.ndf'),
(FILENAME = N'e:\Test\Data\FileName5.ndf'),
(FILENAME = N'e:\Test\Data\FileName6.ndf'),
(FILENAME = N'e:\Test\Data\FileName7.ndf'),
(FILENAME = N'e:\Test\Data\FileName8.ndf'),
(FILENAME = N'e:\Test\Data\FileName9.ndf'),
(FILENAME = N'e:\Test\Data\FileName10.ndf'),
(FILENAME = N'e:\Test\Data\FileName11.ndf'),
(FILENAME = N'e:\Test\Data\FileName12.ndf'),
(FILENAME = N'e:\Test\Data\FileName13.ndf'),
(FILENAME = N'e:\Test\Data\FileName14.ndf'),
(FILENAME = N'e:\Test\Data\FileName15.ndf'),
(FILENAME = N'e:\Test\Data\FileName16.ndf'),
(FILENAME = N'e:\Test\Data\FileName17.ndf'),
(FILENAME = N'e:\Test\Data\FileName18.ndf'),
(FILENAME = N'e:\Test\Data\FileName19.ndf'),
(FILENAME = N'e:\Test\Data\FileName20.ndf'),
(FILENAME = N'e:\Test\Data\FileName21.ndf'),
(FILENAME = N'e:\Test\Data\FileName23.ndf'),
(FILENAME = N'e:\Test\Data\FileName24.ndf'),
(FILENAME = N'e:\Test\Data\FileName25.ndf'),
(FILENAME = N'e:\Test\Data\FileName26.ndf'),
(FILENAME = N'e:\Test\Data\FileName27.ndf'),
(FILENAME = N'e:\Test\Data\FileName28.ndf'),
(FILENAME = N'e:\Test\Data\FileName29.ndf'),
(FILENAME = N'e:\Test\Data\FileName30.ndf'),
(FILENAME = N'e:\Test\Data\FileName31.ndf'),
(FILENAME = N'e:\Test\Data\FileName32.ndf')
LOG ON
(FILENAME = N'e:\Test\Data\FileName_Log1.ldf'),
(FILENAME = N'e:\Test\Data\FileName_Log2.ldf')
FOR ATTACH
GO
um...people...just images...no mpgs?|||Very nice...so it's not an adult site?|||Nope, not an adult site (heehee).
Things went well this weekend. Moved both databases successfully and then re-attached them with the FOR ATTACH option. It was surprisingly painless all considered.
Regards,
hmscott
Very nice...so it's not an adult site?
I have a database with something like 128 physical data files which I have to move (detach, move the physical files and then re-attach). Since the limit for sp_attach_db is 16 files, I am forced to use the FOR ATTACH option. I was wondering if anyone had ever done this and if there were any hints and/or recommendations you might be willing to share...
Regards,
hmscott128 files?
Dude...how big is this thing?
what's the hardware config?
No I haven't done this...|||Sigh, it's another of my "inherited" databases. Worse, it's supposedly a "vendor supported" solution. Let's just not go there.
There are actually two databases on this one server (one has a mere 75 files, the other has 128). The bigger of the two is used to store images of...um...people.
The bigger database is ~240 GB. According to the vendor it will grow by approximately 50 - 80 GB per year. We are in the process of moving it from an "all on board" disk solution to a CX 500 SAN.
Oh, well, I am going to create a sandbox version of the db with ~32 files and play around with detaching an re-attaching it. As always thanks for your response.
Regards,
hmscott
128 files?
Dude...how big is this thing?
what's the hardware config?
No I haven't done this...|||um...people...just images...no mpgs?|||The images are of people you would probably rather not routinely associate with.
hmscott
PS. I just test the process on a bunch of empty files; it's a cinch. Here's the SQL..
Create the initial database on 32 data files and two log files:
-- =============================================
-- Create database on multiple data and transaction log files
-- =============================================
IF EXISTS (SELECT *
FROM master..sysdatabases
WHERE name = N'MultiFile')
DROP DATABASE MultiFile
GO
CREATE DATABASE MultiFile
ON PRIMARY
( NAME = FileName1,
FILENAME = N'e:\MSSQL\Data\FileName1.mdf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName2,
FILENAME = N'e:\MSSQL\Data\FileName2.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName3,
FILENAME = N'e:\MSSQL\Data\FileName3.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName4,
FILENAME = N'e:\MSSQL\Data\FileName4.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName5,
FILENAME = N'e:\MSSQL\Data\FileName5.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName6,
FILENAME = N'e:\MSSQL\Data\FileName6.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName7,
FILENAME = N'e:\MSSQL\Data\FileName7.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName8,
FILENAME = N'e:\MSSQL\Data\FileName8.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName9,
FILENAME = N'e:\MSSQL\Data\FileName9.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName10,
FILENAME = N'e:\MSSQL\Data\FileName10.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName11,
FILENAME = N'e:\MSSQL\Data\FileName11.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName12,
FILENAME = N'e:\MSSQL\Data\FileName12.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName13,
FILENAME = N'e:\MSSQL\Data\FileName13.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName14,
FILENAME = N'e:\MSSQL\Data\FileName14.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName15,
FILENAME = N'e:\MSSQL\Data\FileName15.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName16,
FILENAME = N'e:\MSSQL\Data\FileName16.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName17,
FILENAME = N'e:\MSSQL\Data\FileName17.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName18,
FILENAME = N'e:\MSSQL\Data\FileName18.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName19,
FILENAME = N'e:\MSSQL\Data\FileName19.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName20,
FILENAME = N'e:\MSSQL\Data\FileName20.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName21,
FILENAME = N'e:\MSSQL\Data\FileName21.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName23,
FILENAME = N'e:\MSSQL\Data\FileName23.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName24,
FILENAME = N'e:\MSSQL\Data\FileName24.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName25,
FILENAME = N'e:\MSSQL\Data\FileName25.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName26,
FILENAME = N'e:\MSSQL\Data\FileName26.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName27,
FILENAME = N'e:\MSSQL\Data\FileName27.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName28,
FILENAME = N'e:\MSSQL\Data\FileName28.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName29,
FILENAME = N'e:\MSSQL\Data\FileName29.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName30,
FILENAME = N'e:\MSSQL\Data\FileName30.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName31,
FILENAME = N'e:\MSSQL\Data\FileName31.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName32,
FILENAME = N'e:\MSSQL\Data\FileName32.ndf',
SIZE = 1MB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
LOG ON
( NAME = FileName_Log1,
FILENAME = N'e:\MSSQL\Data\FileName_Log1.ldf',
SIZE = 512KB,
MAXSIZE = 10MB,
FILEGROWTH = 10%),
( NAME = FileName_Log2,
FILENAME = N'e:\MSSQL\Data\FileName_Log2.ldf',
SIZE = 512KB,
MAXSIZE = 10MB,
FILEGROWTH = 10%)
GO
Now detach the database and use windows explorer to move the data files and log files to a new directory (in this case e:\test\data).
Now use CREATE DATABASE ... FOR ATTACH to re-attach the database:
CREATE DATABASE MultiFile
ON PRIMARY
(FILENAME = N'e:\Test\Data\FileName1.mdf'),
(FILENAME = N'e:\Test\Data\FileName2.ndf'),
(FILENAME = N'e:\Test\Data\FileName3.ndf'),
(FILENAME = N'e:\Test\Data\FileName4.ndf'),
(FILENAME = N'e:\Test\Data\FileName5.ndf'),
(FILENAME = N'e:\Test\Data\FileName6.ndf'),
(FILENAME = N'e:\Test\Data\FileName7.ndf'),
(FILENAME = N'e:\Test\Data\FileName8.ndf'),
(FILENAME = N'e:\Test\Data\FileName9.ndf'),
(FILENAME = N'e:\Test\Data\FileName10.ndf'),
(FILENAME = N'e:\Test\Data\FileName11.ndf'),
(FILENAME = N'e:\Test\Data\FileName12.ndf'),
(FILENAME = N'e:\Test\Data\FileName13.ndf'),
(FILENAME = N'e:\Test\Data\FileName14.ndf'),
(FILENAME = N'e:\Test\Data\FileName15.ndf'),
(FILENAME = N'e:\Test\Data\FileName16.ndf'),
(FILENAME = N'e:\Test\Data\FileName17.ndf'),
(FILENAME = N'e:\Test\Data\FileName18.ndf'),
(FILENAME = N'e:\Test\Data\FileName19.ndf'),
(FILENAME = N'e:\Test\Data\FileName20.ndf'),
(FILENAME = N'e:\Test\Data\FileName21.ndf'),
(FILENAME = N'e:\Test\Data\FileName23.ndf'),
(FILENAME = N'e:\Test\Data\FileName24.ndf'),
(FILENAME = N'e:\Test\Data\FileName25.ndf'),
(FILENAME = N'e:\Test\Data\FileName26.ndf'),
(FILENAME = N'e:\Test\Data\FileName27.ndf'),
(FILENAME = N'e:\Test\Data\FileName28.ndf'),
(FILENAME = N'e:\Test\Data\FileName29.ndf'),
(FILENAME = N'e:\Test\Data\FileName30.ndf'),
(FILENAME = N'e:\Test\Data\FileName31.ndf'),
(FILENAME = N'e:\Test\Data\FileName32.ndf')
LOG ON
(FILENAME = N'e:\Test\Data\FileName_Log1.ldf'),
(FILENAME = N'e:\Test\Data\FileName_Log2.ldf')
FOR ATTACH
GO
um...people...just images...no mpgs?|||Very nice...so it's not an adult site?|||Nope, not an adult site (heehee).
Things went well this weekend. Moved both databases successfully and then re-attached them with the FOR ATTACH option. It was surprisingly painless all considered.
Regards,
hmscott
Very nice...so it's not an adult site?
Subscribe to:
Posts (Atom)