I have a small project to be done in which I need to fetch the pdf file from a my system and save it in database and also fetch the name of it and save it in the database.
View 9 RepliesI have a folder with 1000 text(.txt) files in it.
I want to insert the filenames into a table in sql server.
Is it possible in SQL Server 2008..?
Convert 100 xml files individually to pdf's and zip them in a folder along with the source files.
Can it be possible in SQL server BI world?
If possible make this an automated process for every 100 files.
I've been struggling with this issue,
1) Test--FolderName (This Test folder name should use as a database name for below sub folders)
a)Create--Sub Foldername
i)create.sql
b)Alter---Sub FolderName
i)Alter.sql
c)Insert---Sub FolderName
i)Insert.sql
[Code] .....
The scripts need to be run in order. So script one needs to run first folder in that sub folders after that next second folders etc..
Is there a way to create a bat file that automatically runs all these scripts, in order against, the databases they need to?
The databases that they need to run against have the name of the database at the beginning of the name of the folder.
has anyone got any opinions on the pros/cons of storing mp3 files in the database instead of the file system ?
How could this be done ? Would it be a binary data type ?
I am asked to create 100 procedures to a database. Any best way to create them in a database one by one by calling the files and saving the execution output files in a folder?
View 9 Replies View RelatedCan someone provide information or a link to information regarding the pros and cons of saving files directly into a SQL 2005 database?
I'm actually for saving files to a database (cleaner implementation then just saving the location then having to get the file, etc), but my project manager is not convinced so I need to make an argument for (or against depending on what I actually find out) using varbinary data type.
Thanks.
Hello there,
I was thinking about saving some files in a SQL Server DB and then reading those back to the client for download. Any warnings that I should know about before doing so? The files will generally be at most 2mb in probably .pdf format. I've heard bad things about doing this in Access...but then again, SQL Server isn't access.
Someone:
I have the need to upload a file via a webpage and then save that file into the database. I would also like to retrieve it and show it to the user.
Can someone show me an example of how this should be done. Also I am concerned of the pros and cons of saving files in the database. Is there a performance hit on the server? Will it make the database unstable? Has anyone had problems doing this?
Any suggestions, samples, and/or help are welcome.
I'm trying to find the previous / next values of a field when it is having null values. I'm not able to use lag / lead as they don't ignore nulls.
declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')
[code]....
The next values are having nulls. i'm trying to get next value of 'v' ignoring nulls.
I can detach/attach SSAS database.But I have a software that protects(backs up) the files of the SSAS Database.
What the customer needs is to be able to take these backed up files and port it to a different server and attach it there.But the new server complains that these files have no corresponding detach-log files.
The customer doesn't want to backup and restore the SSAS databases.
Hi
I am trying to load a ssis package into a folder (lets say SSIS packages) that I created in MSDB but its geeting saved in the root (MSDB) not into the SSIS packages folder...I am doing this thru C#...
Hello,
My english is not the best, but i will try my best.
I want to change the saving folder for the database from my systempartition to my datapartition (from c:labla to d:mydata), but i dont know how. I can see the folder but it is in gray font and unchangeable in the server configuration tool.
Greetings Thomas
Hi !!
If i want to save, images, video clips or movies ......(multimedia stuff) in the SQL Server. Any idea how can i save them and call them. I also, want to know if that the effecient way to the task or there are other ways to di it???
Thanks!!!!
How to fetch records which throws error?
For example
I'm inserting records from a table to another table, having multiple columns
SampleQuery :
insert into tableB (col1,col2....col10)
select col1,col2....col10 from tableA
* while executing this query im getting error. (like varchar cant convert to numberic)
* Here I have no clue in which column it is
* also dont know which row causes it
In the first step of my SSIS package I need to get files from FTP and dump it/them in a local directory, but it's more than that, the logic is like this:
1. If no file(s) found, stop executing and send email saying no file(s) found;
2. If file(s) found, then compare it/them with existing files in our archive folder; if file(s) already exist in archive folder, stop executing and send email saying file(s) already existed, if file(s) not in archive folder yet, then transfer it/them to the local directory for processing.
I know i have to use a script task to do this and i did some research and found examples for each of the above 2 steps and not both combined, so that's why I need some help here to get the logic incorporated right.
Thanks for the help in advance and i apologize for the long lines of code!
example for step 1:
----------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Imports Microsoft.SqlServer.Dts.Pipeline.Wrapper
Imports Microsoft.SqlServer.Dts.Runtime.Wrapper
Imports Microsoft.VisualBasic.FileIO.FileSystem
Imports System.IO.FileSystemInfo
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Dim cDataFileName As String
Dim cFileType As String
Dim cFileFlgVar As String
WriteVariable("SCFileFlg", False)
WriteVariable("OOFileFlg", False)
WriteVariable("INFileFlg", False)
WriteVariable("IAFileFlg", False)
WriteVariable("RCFileFlg", False)
cDataFileName = ReadVariable("DataFileName").ToString
cFileType = Left(Right(cDataFileName, 4), 2)
cFileFlgVar = cFileType.ToUpper + "FileFlg"
WriteVariable(cFileFlgVar, True)
Dts.TaskResult = Dts.Results.Success
End Sub
Private Sub WriteVariable(ByVal varName As String, ByVal varValue As Object)
Try
Dim vars As Variables
Dts.VariableDispenser.LockForWrite(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
vars(varName).Value = varValue
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
End Sub
Private Function ReadVariable(ByVal varName As String) As Object
Dim result As Object
Try
Dim vars As Variables
Dts.VariableDispenser.LockForRead(varName)
Dts.VariableDispenser.GetVariables(vars)
Try
result = vars(varName).Value
Catch ex As Exception
Throw ex
Finally
vars.Unlock()
End Try
Catch ex As Exception
Throw ex
End Try
Return result
End Function
End Class
example for step 2:
-------------------------------------------------------------------------------------------------------
' Microsoft SQL Server Integration Services Script Task
' Write scripts using Microsoft Visual Basic
' The ScriptMain class is the entry point of the Script Task.
Imports System
Imports System.Data
Imports System.Math
Imports Microsoft.SqlServer.Dts.Runtime
Public Class ScriptMain
' The execution engine calls this method when the task executes.
' To access the object model, use the Dts object. Connections, variables, events,
' and logging features are available as static members of the Dts class.
' Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.
'
' To open Code and Text Editor Help, press F1.
' To open Object Browser, press Ctrl+Alt+J.
Public Sub Main()
Try
'Create the connection to the ftp server
Dim cm As ConnectionManager = Dts.Connections.Add("FTP")
'Set the properties like username & password
cm.Properties("ServerName").SetValue(cm, "ftp.name.com")
cm.Properties("ServerUserName").SetValue(cm, "username")
cm.Properties("ServerPassword").SetValue(cm, "password")
cm.Properties("ServerPort").SetValue(cm, "21")
cm.Properties("Timeout").SetValue(cm, "0") 'The 0 setting will make it not timeout
cm.Properties("ChunkSize").SetValue(cm, "1000") '1000 kb
cm.Properties("Retries").SetValue(cm, "1")
'create the FTP object that sends the files and pass it the connection created above.
Dim ftp As FtpClientConnection = New FtpClientConnection(cm.AcquireConnection(Nothing))
'Connects to the ftp server
ftp.Connect()
'ftp.SetWorkingDirectory("..")
ftp.SetWorkingDirectory("directoryname")
Dim folderNames() As String
Dim fileNames() As String
ftp.GetListing(folderNames, fileNames)
Dim maxname As String = ""
For Each filename As String In fileNames
' whatever operation you need to do to find the correct file...
Next
Dim files(0) As String
files(0) = maxname
ftp.ReceiveFiles(files, "C: emp", True, True)
' Close the ftp connection
ftp.Close()
'Set the filename you retreive for use in data flow
Dts.Variables.Item("FILENAME").Value = maxname
Catch ex As Exception
Dts.TaskResult = Dts.Results.Failure
End Try
Dts.TaskResult = Dts.Results.Success
End Sub
End Class
Where do I go in my computer to correct this problem so that I can complete the installation?
View 3 Replies View RelatedI wrote the below script to print all folders and files located in the share path. How to extend my script to mention by adding another column whether the file is a folder/file , sort of 0 or 1.
declare @chkdirectory1 varchar(4000) = 'shared_pathfolder';
declare @finalserver3 varchar(4000);
create table #tmp (directory_name varchar(4000))
SET @finalserver3 = '''"DIR ' + @chkdirectory1 + ' /B"''';
--select @finalserver3
--SELECT @finalServer
DECLARE @ExecCmd varchar(100)
--SELECT @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + char(50) + 'mkdir D:'+ CONVERT(varchar(8), getdate(), 112) + '' + char(50)
SET @ExecCmd = 'EXEC master.dbo.xp_cmdshell ' + @finalserver3
--SELECT @ExecCmd
exec(@ExecCmd)
drop table #tmp
I have the need to delete old backup files via TSQL job. Found this solution online:
PushD "
emoteservershareDIFF" &&(
forfiles -m *DIFF*.sqb -d -1 -c "cmd /c del /q @path"
) & PopD
It works remotely if I run it via command prompt. But when I add this to a TSQL job on my remote SQL instance, it runs without deleting anything. What I'm missing?
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
I need to load images from folders into a SQL Server table and I have done it successfully for individual images, however I need to load all the names of the folders and sub folders names in separate columns + load all images.
So the folders look like as in the screenshot and the final result of the table in SQL Server should look like the second screenshot.
I want to save every query executed from a given software, let's say Multi Script for example, and save in a table query text, execution time and rows count among other possible useful information. Right now I've created a sp and a job that runs every 1 milliseconds but I can't figure out how to get execution time and rows count. Another problem with this is that if the query takes too long I end up with several rows in my table.
View 5 Replies View RelatedI am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET
(
'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES',
'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR,
''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item'
COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns)
ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order)
DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
how to fetch data from oracle database in sql server 2014
example:
oracle schema :t1
sql server :t2
now am in t2 sql server database
now am executing below query
select * from t1.tablename ;
In my system, I generate invoices as PDF for my clients and I need to email them and save them on the database. What is the best approach for achieving that?!
Shall I save the files path in the SQL server 2000 as ( varchar ) and save files in a folder in the server or there is another approach?! is there any recommendation or advice about the procedure to send emails shall i save them first then send them or ...!??
Appreciate your advice!
I dont know the exact position of '/',',','--' or any special character.I have to fetch first name of the country.
create table abc
(country nvarchar(200))
go
INSERT INTO abc VALUES ('Tennessee/Virginia,USA')
GO
INSERT INTO abc VALUES ('Kansas,USA')
[code]...
Hi,
say I have th following in my post-deployment script:
:r ..ScriptsFolderScript1.sql
:r ..ScriptsFolderScript2.sql
:r ..ScriptsFolderScript3.sql
...
How can I do the equivalent of
:r ..ScriptsFolder*.sql
??
I've tried the above and the syntax is not supported.
Your help is much appreciated! =)
Hi All,I have a multiple files, but they are store in different directory onthe server. I want open those files and insert it into the databaseusing bcp.Example files structure dir:\xyz123abc ext1.txt\xyz123abc ext2.txt\zyz123999 ext2.txtbcp "dabase" in \xyz123abc ext1.txt -c -S"servername' -Usa-Ppassword -T".is there away to loop througth each dir, get the files, excecute thebcp, then go to next folder.Please help. Thanks in advance.Ted Lee
View 2 Replies View RelatedI've stepped into a new environment and have never dealt with multiple data files on user databases only with Temp db.What would be the best way to get all my data files in sync. I have done this on databases that aren't that big in size or off in size by a lot. Here is what I have
Mdf -- 69 GB
ndf -- 3
ndf -- 3
ndf -- 3
ndf -- 3
ndf -- 4
ndf --4
ndf -- 2
Hi!
I want to save and retreive some files on sqlserver. I want to save the files in binary format on sqlserver and I want to get back them in their format.Is it possible in c#? If possible please help me to do this. I already succeeded to save a file in binary format, but i am getting problem to retreive that in its format.
Thanks in adance
to utf8 format through BCP or throuhg the dos command prompt?
Cheers
Hi,
I'm tring to copy files from FTP address, the problem is that sometimes the FTP folder is empty, and then the FTP Task is failed.
Why is it failed if there are no files? Any suggestion how to avoid the error?
Thanks,
Hadar
Hello
I'm just starting with SQL and BIDS. I have created a xml-Import to SQL Server which works without any problem.
Now I like to import (daily) all xml-files of an appropriate Folder at once to the DB. The files will have different names each day.
I could not find any help in the internet to solve this problem.
Can someone give me an Idea how to import many xml-files out of the same folder to SQL?
Thanks & regards
Chaepp