SQL Server 2008 :: Stripping Part Of A File Name
Oct 16, 2015I have a filename as follows:
123_20151016_3152_AIRHtest1.txt
What I would like to extract from this fill name is the "3152" only. What is the correct way to do this?
ADVERTISEMENT
SQL Server 2008 :: How To Extract Part Of A String
May 11, 2015Here is a sample order # we used for one of our shipments: BL-53151-24954-1-0001-33934
I need to extract the "24954" portion of that order # while within an INNER JOIN, but not sure how.
My problem is we have 2 order tables: OrderTable1 contains a field with the full order #. OrderTable2 contains a field with only the "24954" portion. I need to JOIN on these 2 fields somehow.
So an example would be the following:
OrderTable1.Full_Order_No: BL-53151-24954-1-0001-33934
OrderTable2.Order_No: 24954
SELECT
ot1.Full_Order_No
, ot2.Order_No
FROM
OrderTable1 ot1
INNER JOIN OrderTable2 ot2 ON ot2.Order_No = [do something here to truncate ot1.Full_Order_No]
How can I do this?
Few notes:
-the 1st part of the order number, "BL-53151-" will ALWAYS be the same. It's our client # which will never change for the purpose of this query.
-The portion I need (24954) can be more or less than the 5 current digits.
-There will always be 6 portions to the order number, split up between 5 dashes.
SQL Server 2008 :: Display A Column Alias As Part Of The Result Set Column Labels?
Feb 26, 2015Is there a way to display a column alias as part of the result set column labels?
View 9 Replies View RelatedSQL Server 2008 :: SSIS Copy File From SharePoint Library Using File System Task Permissions?
Jun 19, 2015Historically I've always written a VB script to copy a file from a sharepoint library. I don't like this method because I have to input a username & password in the script and maintain a config file.
Yesterday I was playing around with using a file system task. The sharepoint file has a UNC path so why not? I created a simple test package with a single file system task that copies the sharepoint file (addressed via UNC) to another network location. Package runs fine locally.
I try running on our utility server but am getting a "The file name [SHAREPOINT UNC PATH] specified in the connection was not valid" error. Package is running with a proxy on the server and the proxy account has the same permissions to the sharepoint site (so far as I can tell) as me.
SQL Server 2008 :: Log File Space Is 5 Times The Data File
Mar 16, 2015one of my database data file is 100 GB and the log file is 500 GB.DB is in full recovery model and the transaction logs happen once in 6 hours.Even then, the Database log file isn't reducing in size.
View 9 Replies View RelatedIntegration Services :: Renaming / Replacing Part Of A File In File System Task
May 14, 2015I'm copying files to a folder with the naming convention as follows in the source folder:
CM_ABC_MY_TEST.txt
In the destination folder, this filename needs to appear as:
CM_XYZ_MY_TEST.txt
In my File System Task, I'm pretty sure I'm going to need an expression with a replace, substring, etc. But am having a hard time nailing down the exact syntax.
SQL Server 2008 :: MDF And NDF File Sizes
Feb 26, 2015I have one .mdf and two .ndf files on the same drive. The .mdf file size =275GB, one .ndf file size = 300GB and other .ndf file size = 135GB. Is this normal to have 3 different file size? if not what can I do to fix this? I don't have option to make all files to initial size equal to 300GB as a .ndf.If I have to add a .ndf file (in case of running out the above drive), what initial file size should I set up for new file on new drive? And how data gets distributed across all 4 files (including new .ndf on different drive)?
View 2 Replies View RelatedSQL Server 2008 :: Load Latest CSV Files From File Server Automatically
Feb 4, 2015I need to load the latest csv files from file server , The files are placed in a folder called -
Posted 02022015- --> csv files .
I am able to copy the csv files from filserver using bulk insert (manually) , giving the file location
I am having difficulty picking up the latest folder which is posted on the server and import it into database using a stored proc .
SQL Server 2008 :: Can't Access File Because It Is Being Used By Another Process
Feb 4, 2015I am working with SSIS package. It executes everyday.
It has the file system task. It moves the production backup from one server to the different server. In today's execution the package failed with the following error
Error Description:An error occurred with the following error message: "The process cannot access the file 'ECOSQLDumpsTest_backup_2015_02_03_230004_1557700.bak' because it is being used by another process.".
How to find which process is using that test backup file?
SQL Server 2008 :: Log File Rotation Script
Apr 20, 2015log file rotation and cleanup script and how to remove the log file older then x days.
View 4 Replies View RelatedSQL Server 2008 :: How To Reduce MDF File Size
Apr 30, 2015I have issue with my DATA file ( MDF) .. The usage is 99.87% for database DB1 . File size is 3 GB . How do I reduce it ?
I have tried to shrink it by changing the recovery from FULL to SIMPLE and set it back to FULL .
I notice the index Defragmentation is high ..
Can I change the Initial Size into 1 GB for example ?
SQL Server 2008 :: Insert The File In Other Database?
May 21, 2015I work with sql server 2008 on a database.we have export schema and datas with the command export datas
click rigth on database => tasks => generate scripts => select all object => click advanced => select type of data to script => schema and data
Now we have a file with all datas and schema That's perfect ...But how i can insert the file in a other database?ok i can copy paste all datas in management studio and press f5 but when i do this the management studio fail because the size of the file is > 200 mega !
SQL Server 2008 :: Import Xml File From Web Page
Jun 12, 2015I want to import xml file directly from web page into microsoft sql table. At the moment the import is done after the XML file is downloaded local.I want to skip this step to manualy download the file.It can be done in SQL? when i change the path i get this error: Cannot bulk load because the file URL... could not be opened. Operating system error code 123(The filename, directory name, or volume label syntax is incorrect.)
below is the code
DECLARE @idoc INT
DECLARE @doc XML
SET @Doc = (SELECT * FROM OPENROWSET(BULK 'F:Folderbrfxrates.xml', SINGLE_CLOB) AS xmlData) -- 1 LOCAL works
--SET @Doc = (SELECT * FROM OPENROWSET(BULK 'http://www.bnr.ro/nbrfxrates.xml', SINGLE_CLOB) AS xmlData) -- from web i get error
SELECT @Doc
[code]...
SQL Server 2008 :: MDF File Growth Datewise
Jun 30, 2015Script to find DATEWISE MDF FILE GROWTH Like
30/6/2015 10%
1/7/2015 8%
SQL Server 2008 :: Monitor File Access
Aug 27, 2015I know i could use Process explorer to find processes which are accessing a drive or a folder, i need exact same thing to be recorded/monitored. Basically i need list of all processes accessing a drive/folder.
View 0 Replies View RelatedSQL Server 2008 :: Parsing Unstructured CSV File?
Oct 1, 2015I have a CSV file with roughly 6 million rows. The file is unstructured; that is, some rows have 5 fields, others have 15, and there are as many 50 fields in one row.
I am using bulk insert to read the entire file into a table in database, with each row being a database record. With that, I have one column that contains a row of comma delimited fields. All fields are character string and I want to find a quick way of parsing each row and placing each comma-delimited value in a column. For example:
CREATE TABLE MyTable
(
CSVString varchar(1000),
C1 varchar(20),
C2 varchar(20),
...
C50 varchar(20),
)
Column CSVString contains the a CSV row (I don't know how many filelds (no. of commas + 1) in the row, but if the row contains 10 fields, I need to populate columns C1-C10. If the row has 15 fields, I populate columns C1-C15.
How can I do this in a very efficient way? I tried CTE but performance was not very good.
SQL Server 2008 :: Exporting BCP To A Compressed File (Rar)
Nov 2, 2015I have this bcp
EXEC xp_cmdshell 'bcp "SELECT top 10 * FROM [db].dbo.[u_activity]" queryout "C:bcpCustomers.csv" -c -b 10000 -t~ -S 10.20.8.149 -U user-P password'
which throws the data into a csv, but the issue is I also need to compress the file down in a .rar file,
can this be done in a SP while still executing the above bcp ?
SQL Server 2008 :: Transaction Log File Drive Is Missing From SAN
Feb 17, 2015We had some SAN issues and we dont have Transaction Log files for some databases.. The drive which was holding this Tlog files were missing.. How to bring back databases.
View 1 Replies View RelatedSQL Server 2008 :: Bulk Insert With A Format File?
Mar 5, 2015I have to perform a bulk Import on a regular Basis and have created a script to do this. The Problem is that the .csv file has 12 Columns and the table to Import into has 14. To Workaround this discrepancy I have decided to use a Format file. The Problem is that how to create one.
View 3 Replies View RelatedSQL Server 2008 :: Moving Tables To New File Group
Mar 16, 2015I'm presented with an issue where by I need to reclaim a fair bit of unused space currently sat in the primary data file for my database. I don't want to run DBCC SHRINKDATABASE as we all know this could potentially have a some serious negative effects relating to index fragmentation.
So, how do I get the free space out of the data file? - I've decided to:
1. Add new new file group
2. Add a clustered index for all tables on the new file group
3. Shrink the primary file group as much as possible (hopefully giving me the free space back)
4. Drop the newly created clustered indexes for all tables
There are no clustered indexes currently for any of the tables!, so me temporarily creating/dropping one shouldn't be an issue. Are there any other ways I can get the free space back to the OS?
SQL Server 2008 :: How To Find Which Backup File Belongs To Which LSN
Mar 18, 2015I am getting following error:
"The log in this backup set terminates at LSN 9566000024284900001, which is too early to apply to the database. A more recent log backup that includes LSN 986000002731000001 can be restored."
If I am missing to restore previous log backup file, how to find which LSN mapped to which log backup file (.trn file name)? Another word looking at LSN can we tell which log backup file is missing?
SQL Server 2008 :: Loading Data From 5 GB Flat File
Mar 27, 2015Designing a solution for loading data into SQL destination from a single 5/10 GB flat file? If yes, what kind of performance measures you have taken while designing the solution ?
View 3 Replies View RelatedSQL Server 2008 :: Renaming Logical File Names
Apr 30, 2015Is there any danger with renaming the LOGICAL file names behind the database?
There are a bunch of databases that were restored copies and all of them have the same logical database file name. I'm trying to get some growth data so I want the logical files to be different (prefer them to match the actual database name) so I can more easily identify them.
For instance:
database_id name type_desc name physical_name
1 DLMdb1 ROWS DLMDB1 D:dlmdb1.mdf
1 DLMdb1 LOG DLMDB1_log E:dlmdb1.ldf
2 DLMdb2 ROWS DLMDB1 D:dlmdb2.mdf
2 DLMdb2 LOG DLMDB1_log E:dlmdb2.ldf
3 DLMdb3 ROWS DLMDB1 D:dlmdb3.mdf
3 DLMdb3 LOG DLMDB1_log E:dlmdb3.ldf
Am I safe to rename the logical names? I can't think of anything that references those logical file names that I would be breaking [backups, applications].
SQL Server 2008 :: Unable To Open Database File (MDF)
May 6, 2015I have a SQL Server 2005 database file (a .mdf file) that I am trying to open in SQL Server Management Studio so I can add a field to a table, but I am unable to open the file. I can't say that I really understand how SQL Server handles these databases. I especially don't understand the "attaching" and "detaching" operations.
I am working in VB.Net 2008 now, but my background is with VB6 using the JET engine. It was just so straightforward using the JET engine, but seems so much more complicated with SQL Server. I am trying to use .mdf files the same way I used .mdb files. I want my application to find the database file, "attach to it", and let me manipulate it.
At one time I was able to open my database in Sql Server Management Studio and edit the design of the database. The application still works but I am no longer able to modify the database because I cannot attach to it in Management Studio.
Here is a big clue: the files are no longer in the special folder anymore, which is c:Program Files (x86)Microsoft SQL ServerMSSQL.2MSSQLDATA. I might have deleted the files, stupidly, thinking they were redundant. I still have the .mdf file (and the _log.ldf file) on my hard drive and the application can still open it, but when I try to use Management Studio to attach to the .mdf file, I get this error:
Microsoft SQL Server Management Studio Express
An error occurred when attaching the database(s). Click the hyperlink in the Message column for details.
here are the details:
Unable to open the physical file "c:Program Files (x86)Microsoft SQL ServerMSSQL.2MSSQLDATAHEALSExamsSQL.mdf". Operating system error 2: "2(The system cannot find the file specified.)". (.Net SqlClient Data Provider)
Indeed the file is not there, but why is Mgmt Studio looking there? Why doesn't it just open the file where it is? What can I do to get it back? I even copied the files to c:Program Files (x86)Microsoft SQL ServerMSSQL.2MSSQLDATA? I get the same error.
Here is another clue: In Management Studio, when I right-click Databases, and chose Attach, it brings up a dialog. There I click "Add" to get another dialog in which I will select a database, but before the dialog appears I get this error:
Locate Database Files - KURANT-WIN7SQLEXPRESS
C:UsersJason KurantDesktop
Cannot access the specified path or file on the server. Verify that you have the necessary security privileges and that the path or file exists.
If you know that the service account can access a specific file, type in the full path for the file in the File Name control in the Locate dialog box.
SQL Server 2008 :: Creation Of XML File With Data In Table
Jun 1, 2015I want to create a XML file with data in my table. I have a question about tags.
SELECT
-- Root element attributes
'http://tempuri.org/Form.xsd' AS 'xmlns',
'http://www.w3.org/2001/XMLSchema-instance' AS 'xmlns:xsd',
(
SELECT
-- Creating a default element
[Code] ....
This is my query. When I use 'xmlns' namespace the result is below:
<Form xmlns="http://tempuri.org/Form.xsd" xmlns:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri xmlns="">
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>
I dont want to see 'xmlns' in 'Veri' tag. When I use xmlna or another word instead of 'xmlns' the result is below:
<Form xmlns="http://tempuri.org/Form.xsd" xmlna:xsd="http://www.w3.org/2001/XMLSchema-instance">
<Veri>
<Satir>
<Id>1</Id>
</Satir>
</Veri>
</Form>
How can I remove 'xmlns' from 'Veri' tag while using it in 'Form' tag
SQL Server 2008 :: ALTER Database To Modify Log File
Jun 25, 2015We are running into the following error while changing a column data type from nvarchar (1200) to varchar(8000)
"Msg 1105, Level 17, State 2, Line 1
Could not allocate space for object 'dbo.TBL1 '.'PK_CL_ID' in database 'Client01' because the 'PRIMARY' filegroup is full. Create disk space by deleting unneeded files, dropping objects in the filegroup, adding additional files to the filegroup, or setting autogrowth on for existing files in the filegroup.
The statement has been terminated."
Now tried to change the filegrowth of the log file to unlimited
ALTER DATABASE Client01
MODIFY FILE ( NAME = Client01_log, MAXSIZE = unlimited);
The query executes without error but I do not see the auto growth as unrestricted. It's still 2GB
SQL Server 2008 :: Easy Way To Compress Backup File?
Jul 15, 2015easy way to compress backup file. I use SQL Server 2008 R2
View 4 Replies View RelatedSQL Server 2008 :: Code To Generate XML File Based In XSD
Aug 2, 2015I have data in Sql table , I want to convert it to xml using xsd using script component in ssis.
View 0 Replies View RelatedSQL Server 2008 :: Excel File Generation Through Backend
Sep 8, 2015I have a requirement of generating an "Sales Report" excel file and mailing that file to a particular email id. I have generated the excel file through bcp command and mail is working fine.
However formatting of excel file is required by end user for eg. Cells to be merged and borders to be given for cells for which i think bcp command will not work.
SQL Server 2008 :: Logical And Physical File Names Of All Databases
Apr 3, 2011Is there a query or sp that I can pull all databases on the server along with their logical file names and physical file names?
View 5 Replies View RelatedSQL Server 2008 :: Initial Size For TempDB Data And Log File?
Sep 12, 2011We have installed SQL Server 2008 R2 SP1 instance and it's having Share Point 2010 databases.
We have 2 dedicated drives for Tempdb on SAN with 50 GB space. Both tempdb data & log files are created with default size. I would like to presize them.
What are the best values to start with?
U ->Tempdbdata having tempdb.mdf file
V->Tempdblog having templog.ldf file
SQL Server 2008 :: Unable To Open Step Output File
Aug 27, 2013I am running sql script inside job.But sql is not saving the results in the file which i specified('Unable to open Step output file'). I tried same thing on different server, it works fine.Both are are 2008 R2.Is there any server level setting that i have to look at.On both servers i logged in as sal. Why is it working on server1 ,but not on server2?
View 6 Replies View RelatedSQL Server 2008 :: Importing Excel File Stored In Sharepoint
Feb 23, 2015I have an excel file that is stored in a sharepoint document library. I am trying to use SSIS to import it into a SQL database. I use the excel connection manager with a sharepoint UNC path. When I run it from BIDS, it runs successfully. When I run it from a job, I get an error
"It is already opened exclusively by another user, or you need permission to view and write its data"
It is definitely not open by anyone else, and I have full permissions to the file. ALSO, the SQL Agent and Service acct which the job runs under, has full permissions to the file. I have tried running it under a proxy account with my user account, but it fails with the same message. Further, I can run a DIR command from a command prompt to list the sharepoint directory contents successfully, but when I run the same command from SSMS using xp_cmdshell, it fails with access denied. Again, the SQL Service acct has full rights to the sharepoint site.
I notice when i browse to the sharepoint document library and try to open the folder with "Open with Windows Explorer", it always asks me to login and I'm thinking that is related to the problem I am having - that it doesn't automatically pick up the windows authentication.