Dear all,
I was trying to create filegroup and files on Mount Point, e.g. C:MOUNTFG1D1
If I try this:
.......
FILENAME = 'C:MOUNTFG1D1FG1F1.ndf',
It doesn't work, no matter what ACL I set on C:, C:MOUNT, or C:MOUNTFG1D1
But if I do this:
......
FILENAME = 'C:MOUNTFG1D1DATAFG1F1.ndf',
It will work, no need to set any ACL at all.
Any ideas?
dong
When restoring a database where the data file(s) are located under a mount point, seems to be a problem with reporting free space available.
DB Size - Say 30GB (25 Data, 5 Log)
E: Drive 20GB with 15GB Free
Restoring database file to
E:SQLMountPoint
- This points to separate disk with more than enough free space
SQL prevents the restore by stating there is not enough free space.
A long work around is
assign a drive letter to the large disk
Restore database using temp drive letter - F:SQLDATAMyDB_Data.mdf
Update sysfiles - changing drive & path - E:SQLMountPointSQLDATAMyDB_Data.mdf
Stop & restart database...
Is there a better way???
Wishlist - Restore only warns about free space - but allows continue...
Regards
Replication in SQL2K required a common file share between publisher and subsriber.
Our security policy will not allow our DB servers to communicate via net bios across the firewall.
Can SQL2K5 perform replication using the normal database communication ports (1433 or 2433)?
hi
my application is applocation1 i kept a file in this folder i want to read it from application how can i get the path of that file in my application.
i want to read the file in my root folder excluding the bin path
Please any one know the answer let me know ..
thanks and regards
Ravishanker Maduri
I've a fresh installation of SQL Server 2014 Enterprise on Windows Server 2012R2. I've setup de Windows Server Failover Cluster and the validation test has been successfully passed. I use mountpoint so I've disk M: that is the host drive for mounted volume m:Isql2014A that is the base folder for mounted volume
On that folder I've 5 mountpoint:
1) m:isql2014a empdbdata
2) m:isql2014a empdblog
3) m:isql2014auserdata
4) m:isql2014auserlog
5) m:isql2014auserlog
into these 5 mountpoint I've placed the SQL Server files.
After the installation of the first FCI I've re-run the cluster validation wizard and again it has passed successfully. When I try to failover the instance from one node to the other I have 2 problem:
1) every time I do failover, some disks fail to come online for a while and then succeed :
Cluster resource 'CLD - IstA - TempDB Data' of type 'Physical Disk' in clustered role 'SQL Server (ISQL2014A)' failed. The error code was '0xaa' ('The requested resource is in use.').
2) sometimes the SQL Server service fails to start, in the error log I see that the master database is not accessible. The master database in into the m:isql2014abinbackup.
Im backing up to a network directory thats actually a mount point on a different server.My backup was slower than usual so i opened up perfmon to have a look.
When selecting the mount point from the Logical Disks section in perfmon i can see that writes/sec & write bytes/sec both show zero for a long period of time, even though the backup percent complete is increasing.Then all of a sudden the writes to the network share jump massively.
Is there some caching mechanism for backups in sql where during a backup data is only flushed to the disk periodically during backup?
The problem that I am having is that with Visual Web Developer I am creating a webpage and having it directly put online, so for example when I start a new ASP.NET page, I select the location to be HTTP, with the location http://MYWEBSERVER/Website and for the language and Visual BasicI notice a couple of things, first that there is no longer a a link under the Main toolbar "Website" selection called the ASP.NET configuration. So how can I configure what I want to have users be able to do? It seems that this choice is only available if I am building the ASP.NET page on my "localhost". So that is the first problem. So I am able to get the pages to work, atleast the things such as the textboxes to show up etc, (Even things as advanced as the "Login" box). How ever when I try to get someone to try to login I am taken to a page that has an server error. The error is:
"The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. "
The Stack Trace Errors are at the bottom. I think this is happening because the automatically generated databases are not getting built online as they are on my computer. On my computer I have MSSQL express. So either the databases are not getting built for some reason, (and I think that is the case as I don't see any in the folder). So I think that somehow I have to create a database on my server, and then somehow configure the ASP.NET file, perhaps in the Web.Config file to look for that new database. Is this the correct methodology? Is there some simpler way that I can just somehow upload things as they are and have them work correctly on my server? The error says that either the Server did not find the database or that the trust level was insufficient. I don't think that is it as I just looked again and I don't see any .MDF files. So how would I go about getting this to work right? Is there a way to do this with MySQL also? So that I don't have to use MSSQL? My server only allows 1 DataBase for that.
Thanks and I hope my question makes sense. It is basically how can I get it to be able to create and check users etc. online?
Brian
[ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.] System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2555237 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
Below is a file that I have loaded into a Sql table:
<btb-root xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" btb-num-trans="2" btb-date="2006-11-09" btb-time="22:40:03" btb-sender="BTB" btb-receipient="BPO-USR">
- <btb-request req-method="Asynchronous">
- <req-header>
<req-btb-id>68790</req-btb-id>
<req-client-id>1133</req-client-id>
<req-product>BPO-Exterior</req-product>
<req-loan-number>00000</req-loan-number>
</req-header>
- <req-property-address>
<addr1>1115 TEST DR</addr1>
<city>TEST</city>
<state>TEstate>
<zip>30044</zip>
</req-property-address>
- <req-borrowers borr-type="Borrower">
<first-name>Test</first-name>
<last-name>TE</last-name>
</req-borrowers>
</btb-request>
My goal is to take the btb-date and store it in the same table I loaded the seperate nodes to. Currently I am loading the req-header, req-property-address, and req-borrowers.
This date will be static in that it will remain the same for every record. My goal is to read it in and store it along with each record. Hope someone can give me some help.
Thanks.
Hi,
I want to create a text file and write to text it by calling its assembly from Stored Procedure. Full Detail is given below
I write a code in class to create a text file and write text in it.
1) I creat a class in Visual Basic.Net 2005, whose code is given below:
Imports System
Imports System.IO
Imports Microsoft.VisualBasic
Imports System.Diagnostics
Public Class WLog
Public Shared Sub LogToTextFile(ByVal LogName As String, ByVal newMessage As String)
Dim w As StreamWriter = File.AppendText(LogName)
LogIt(newMessage, w)
w.Close()
End Sub
Public Shared Sub LogIt(ByVal logMessage As String, ByVal wr As StreamWriter)
wr.Write(ControlChars.CrLf & "Log Entry:")
wr.WriteLine("(0) {1}", DateTime.Now.ToLongTimeString(), DateTime.Now.ToLongDateString())
wr.WriteLine(" :")
wr.WriteLine(" :{0}", logMessage)
wr.WriteLine("---------------------------")
wr.Flush()
End Sub
Public Shared Sub LotToEventLog(ByVal errorMessage As String)
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessage)
End Sub
End Class
2) Make & register its assembly, in SQL Server 2005.
3)Create Stored Procedure as given below:
CREATE PROCEDURE dbo.SP_LogTextFile
(
@LogName nvarchar(255), @NewMessage nvarchar(255)
)
AS EXTERNAL NAME
[asmLog].[WriteLog.WLog].[LogToTextFile]
4) When i execute this stored procedure as
Execute SP_LogTextFile 'C:Test.txt','Message1'
5) Then i got the following error
Msg 6522, Level 16, State 1, Procedure SP_LogTextFile, Line 0
A .NET Framework error occurred during execution of user defined routine or aggregate 'SP_LogTextFile':
System.UnauthorizedAccessException: Access to the path 'C:Test.txt' is denied.
System.UnauthorizedAccessException:
at System.IO.__Error.WinIOError(Int32 errorCode, String maybeFullPath)
at System.IO.FileStream.Init(String path, FileMode mode, FileAccess access, Int32 rights, Boolean useRights, FileShare share, Int32 bufferSize, FileOptions options, SECURITY_ATTRIBUTES secAttrs, String msgPath, Boolean bFromProxy)
at System.IO.FileStream..ctor(String path, FileMode mode, FileAccess access, FileShare share, Int32 bufferSize, ileOptions options)
at System.IO.StreamWriter.CreateFile(String path, Boolean append)
at System.IO.StreamWriter..ctor(String path, Boolean append, Encoding encoding, Int32 bufferSize)
at System.IO.StreamWriter..ctor(String path, Boolean append)
at System.IO.File.AppendText(String path)
at WriteLog.WLog.LogToTextFile(String LogName, String newMessage)
We have a hierarchical table of some 2-3k of rows that grows slowly at only 3-5 rows a month, and is never likely to be above 5k, holding file/directory names with an IdParent int value pointing at that items immediate parent or NULL if root
Below is a sample:
Id Name IdParent
1 C: NULL
2 D: NULL
3 ProgramFiles 1
4 DataFiles 2
5 Excel.EXE 3
6 MyDataList.CSV 4
iterative code required to achieve the result I am looking for is best handled in the Application layer, but in this case the design brief is that we must get our results back as a View. Speed of execution and code clarity are secondary 'icing on the cake' issues.
I have tried recursive CTE's and XML FOR solutions but without success, and while I feel the latter is probably the best all rounder the searches I have made and the examples I have read do not seem to solve this particular issue.
Even to find the starting point of the search tree (i.e. the .EXE and the .CSV items) was difficult because the final slash is optional in some of the rows (.e. row 4)
The end result of my view on the above data should return:
Id FilePath
5 C:ProgramFilesExcel.EXE
6 D:DataFilesMyDataList.CSV (note the adding of the missing delimiter between DataFiles and MyDataList)
Assuming all windows servers belonging to the WSFC are on the same subnet, will the AG listener become a single failure point in system if all Application servers connect to the AG through the listener? If the answer is yes, what are the options to resolve this issue?
View 3 Replies View RelatedHow could i restore a filegroup to a certain point of time (not to apply all
transaction log up to the latest one) ??
Hello all,
First off, I appreciate the time that those of you reading and responding to this request are offering. My quesiton is a theoretical and hopefully simple one, and yet I have been unable to find an answer to it on other searches or sources.
Here's the situation. I am working with SQL Server 2005 on a Windows Server 2003 machine. I have a series of databases, all of which are in Full recovery mode, using a backup device for the full database backups and a separate device for the log backups. The full backups are run every four days during non-business hours. The log backups are run every half hour.
Last week, one of my coworkers found that some rarely-used data was unavailable, and wanted to restore a database to a point in time where the data was available. He told me that point in time was some time back in November.
To accomplish this, I restored the database (in a separate database, as to not overwrite my production database) using the Point in Time Recovery option. I selected November from the "To a point in time" window (I should note that this window is always grey, never white like most active windows, it seems), and the full database backup and the subsequent logs all became available in the "Select the backup sets to restore" window.
I then tried a bevy of different options from the "Options" screen. However, every restore succeeds (ie: it doesn't error out), but seems to be bringing the database back to a current point in time. It's never actually going back to the point in time I specify.
My questions are as follows:
a) Is it possible to do a point in time recovery to a point in time BEFORE the last full database backup?
b) If so, what options would you recommend I use? (ie: "Overwrite the existing database", restore with recovery, etc etc).
I again appreciate any and all advice I receive, and I look forward to hearing from anyone and everyone on this topic. Thank you.
Ryan
Hi, all experts here,
Thank you very much for your kind attention.
I am having a question about document map on SQL Server 2005 Reporting Services. I found one problem with the values retrived for the document level. That is, the values are not distinct, they are duplicate for the document level. Is it possible for us to get the distinct values for a particular document level? Hope my question is clear for your help.
Thank you very much in advance for your help and advices. I am looking forward to hearing from you.
With best regards,
Yours sincerely,
I know how to create mount point in windows 20003 cluster, I am not sure how
to set it up with SQL 2005 running on the cluster.
Does sql need to be dependant on any of the disks? I have tried looking for
a guide, but cannot find.
current setup active active cluster running. I need to add san space which
will hold the databases. The san will be carved up into drive letters. each
drive letter will hold 3 mount points.
ie.
node 1
J:-2 mount point
k:2 mount point
l:2 mount point
node 2-
r:-2 mount point
s:2 mount point
t:2 mount point
each node would be able to own the disk if the other node failed over.
any help is appreciate. I have tried books online etc.. cannot find a good
step by step.;
thanks
I've been through a number of tutorials on how to enable row-level security based on a userID, but my problem is more complicated and I do not have sufficient understanding of report models to guess.
My security information is defined in a table within my database. It contains a username and an account mask. An account mask maps to 1 or more account codes contained in the other data tables in my report model. A user may have more than one account mask defined for his account.
I understand the concept of directly mapping the logged-in user to a field containing a matching username. Is it possible to do a two-step mapping, so that based on the user ID I can get the account mask(s) and then evaluate which account codes match the mask(s)?
Or is there a different/better way to set this up? Defining SQL roles/groups is not an option, because of some compatibility issues with external systems.
So far, because my security table has no defined relationship with the data tables, I have not even been able to get it into my report model (Would love any suggestions on that one, too.)
I'd appreciate any ideas or suggestions - even if only something to investigate.
Thanks,
Sarah
I understand mount points help scalability in easier maintenance.
By scalability do we mean more than 26 drive letters or it means adding more space to the same mount point letter on with more ease .
Can I add more space to a mountpoint if required later on by adding hard disks .
Also if one can give some pointers to good file group configuration guidelines / storage align partitions , it will be very much helpful
Further I my server CPU has 4 cores , will having 4 filegroups help me in improving system performance.
If SAN has 2 controllers , is it preferred to run data file partition on one controller and log file partition on another.
Thanks in advance.
Hello,
We have a requirement to be able to monitor mount points. The xp_fixeddrives does not support mount point monitoring. Is there another way to do it. Does microsoft working on updating the xp_fixeddrives. Let me know if anyone has any ideas how to monitor mount points.
Thanks
Hello,
To implement the new SQL 2005, I plan to make the environment easy to manage. The environment should be simple to document and be automated via scripts. Therefore I plan to use mount points as described below.
On a typical SQL server with multiple drives like C, D, E, F, G, H. Where each drive will have various folders to hold SQL code, data files, transaction log files, tempdb files, snapshot files, and other types of files. This typical environment is not pretty and is hard to write scripts for.
So I plan to standardize on one standard directory structure via volume mount point. On all new SQL 2005 servers, we should see drive E as the one and only SQL Server directory. Other drives will be mounted to drive E as shown.
E:
SQLSERVER local folder -sql code for each db instance
SQLSHARED local folder -sql shared tools for all db instances
SQLTLOG1 Drive H -db transaction log
SQLSNAP1 Drive F -db snapshot files
SQLTEMPDB1 Drive H -tempdb main data file
SQLWORK Drive D - DBA work area
SQLDATA1 Drive G -db data files
SQLDATA2 Future Drive -if SQLDATA1 is too large for any direct attached drive, or to get more I/O throughput.
With this implementation, I can easily write scripts to manage the environment. Also if any mounted volume is out of space, we can swap the based drive without doing any change to database configuration. We can also switch from direct attached drive to SAN in the future.
Do you think mount point is safe to use with SQL 2005? I know it is supported.
Do you have a standard directory structure for your environment? How do you do it?
Thanks,
KTMD
Does sql server 2012 mount points support FileStream feature?
View 1 Replies View RelatedI have to check for existence of mount points on drives and retrieve the Space information for those mount points. Is this even possible with t-sql??
View 2 Replies View RelatedHello,
Is there any ability to do database-level triggers in SQL 2000? I have a SQL 2000 database, and I was asked if we could create a trigger that whenever anyone touches the data in a database, to create an entry in an event log? If not, I have a main table I can put a trigger on; however, my question is how do you write to a file in a trigger as well?
Thanks.
I have designed an XML schema in which defines the data structures expected in an xml file that our software will import. This is used to validate the files.The current problem is that the validation passes any correctly formatted xml file that does not contain any of our data structures. Is it possible to put something in the schema that says that at least one instance of our toplevel datatype must be included in the file?. I know using minOccurs="1" but I can only see how to use with a definition of a datatype - not the xml file itself.
View 5 Replies View RelatedI have a problem where I need to select all top level file paths from a string value in SQL
So I have a column "Locations"
Example Data:
X:folderanotherfolder
X:folderyet another folder
X:foldernameanother folder
X:foldernameyet another folder
I'd want to return only:
X:folder
X:foldername
I need to somehow parse the sting and capture anything before the second ''?
I'm using the import wizard to create a new table from a flat file source. The table gets created but no data gets copied. What's wrong? Here's the report:
View 87 Replies View RelatedHello,
I have read previous threads on this, still not working.
New installation of SQL Server 2005 on a Windows Server 2003 box. I have installed SP1 on both the server & the client, do I have to re-create the package?
Thanks in advance,
Dave
Hi all,
I have created a dts (.dtsx file). which gets the data from my log file and insert into my sql 2005 table. In dts i am using 'Flat File Source' and 'OLE DB Destination' (becoz i need to acess my db remotely). When i execute this through 'SQL Server Business Intelligence Development Studio' it's working fine but when i execute this through my c# code, it throws me an error 'The product level is insufficient for component Flat File Source (1)'.
My c# code :
string strLocalPath = @"d:FTPServiceMyPackage.dtsx";
Application app = new Application();
Package package2 = app.LoadPackage(strLocalPath, null);
// Now that the package is loaded, we can query on
// its properties.
int n = package2.Configurations.Count;
DTSProtectionLevel pl = package2.ProtectionLevel;
DTSExecResult result2 = package2.Execute();
string strErrorDesc = "";
if (result2 == DTSExecResult.Failure)
{
foreach (DtsError local_DtsError in package2.Errors)
{
strErrorDesc = local_DtsError.Description;
}
}
Output of all objects :
n = 0
p1 = EncryptSensitiveWithUserKey
result2 = Failure
strErrorDesc = The product level is insufficient for component "Flat File Source" (1).
Please help me to solve this problem ASAP.
Thanks
Harsh
Hi all
I am trying to create xml file from the data, everything works fine cept I can't get the format that I need.
This is my SP:
WITH XMLNAMESPACES (
'urn:http://base.google.com/cns/1.0' as "c"
, 'urn:http://base.google.com/ns/1.0' as "g"
)
SELECT top 2
TITLE "item/title",
description "item/description",
employer "item/g:employer",
job_type "item/g:job_type",
link "item/link",
location "item/g:location",
salary "item/g:salary",
education "item/g:education"
FROM JobExport
FOR XML PATH ('chanel'), ROOT('rss')
I need to get:
<rss version="2.0" xmlns:g="http://base.google.com/ns/1.0" xmlns:c="http://base.google.com/cns/1.0">
- <channel>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education></g:education>
</item>
- <item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type />
<link></link>
<g:location></g:location>
<g:salary />
<g:education>High School</g:education>
</item>
</channel>
</rss>
but I get this:
<rss xmlns:g="urn:http://base.google.com/ns/1.0" xmlns:c="urn:http://base.google.com/cns/1.0">
<chanel>
<item></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
<g:education></g:education>
</item>
</chanel>
<chanel>
<item>
<title></title>
<description></description>
<g:employer></g:employer>
<g:job_type></g:job_type>
<link></link>
<g:location></g:location>
<g:salary></g:salary>
</item>
</chanel>
</rss>
The problem is that the "chanel" need to be a subroot (not sure what else to call it)
Hello,
How can I get a XML with a root node from a FOR XML ?:
Now I have this:
The SQL:
SELECT * FROM Table_Projects Project
FOR XML AUTO
and the XML output:
<Project>
<ItemID>1</ItemID>
.....
</Project>
<Project>
<ItemID>2</ItemID>
.....
</Project>
...
and I want this:
<Projects>
<Project>
<ItemID>1</ItemID>
.....
</Project>
<Project>
<ItemID>2</ItemID>
.....
</Project>
...
</Projects>
thanks and regards,
Edu
Hi,
I am looking for tutorials about how to create dts et dtsx files.
Thanks for your help.
Arioule.
Greetings:
I have a database that is 1.7terabyte in size with 136gb free and throws a "transport level error" telling me to discard the results when I run dbccshrinkfile ('DBNAME', size). I have tried various increments of size, from truncateonly to 1MB below its current value, and nothing works. I have tried to detach and reattach the db, restart the service, restart the server, and none have provided a solution. Any ideas?
Regards,
Derek
I have used the copy database wizard, but I realized I had forgotten to shrink the transaction log file. So I canceled the wizard. My database, detached by the wizard, has now disappeared. The mdf file is still there, but when I try to attach it manually I get the "create file encountered operating system error 5 while attempting to open the physical file..." error.
Any way I can recover it?
Thanks.
Logged on local Administrator on one of my sqlserver 2000 "ABC" and registered 5 sql groups and linked 50 sql2k servers from MSDE to Standare (all sp3).
Now, I need a different login on this machine "ABC", how could I get back all the SQL Enterprise Manager Console Root Setting and connections like before?
thanks
David