Hi All,
I am writing a automated backup script using job scheduler of the management studio and would like to know how can i copy the output messages given by the SQL Server to an external file automatically.
Thanks in Advance
Mitesh
I want to select all threads even though the count(Messages.TitleID) is zero.. Is like when u start a new thread with no messages currently posted. How can i modify the codes to do it?SELECT Thread.ThreadTitle, COUNT(Messages.TitleID) AS NoOfMessage, Thread.ThreadID FROM (Thread INNER JOIN Messages ON Thread.ThreadID = Messages.ThreadID) GROUP BY Thread.ThreadTitle, Thread.ThreadID
View 2 Replies View RelatedCan i get a login name of the user that execute the store procedure that have open the connection to the DB?
thanks, Paolo giusti
I passed .net datatable from a .net app to a store procedure. From this store procedure, how to code to bulk insert (or another way) to SQL table?
View 7 Replies View RelatedI have a ssis package where I need to have excel destination. In the Excel file, I need to have few rows with some text and then populate data below the text. One the text is like this:
Data as of: 08/25/2015
if the report ran today, then Data as of will have Yesterday. So, if the user opens that excel file after a week, then user should see same Data as of: 08/25/2015. not today()-day(1).
I was planing to handle on excel side with today()-day(1). but it only works the day it was run. Then the excel file is open after few days later, then it might as Data as of: 08/30/2015 which is not true. It should still stay Data as of:
08/25/2015 on what ever date the excel file is open. The SSIS package runs only once.
How do I handle this so that whenever user open the file, they will see Data as of: 08/25/2015. This is not a column in excel. It is like a description of data in excel.
Hi
View 7 Replies View RelatedHow to write the error messages generated in a job to a file?Thanks.
View 1 Replies View RelatedLooking for a script which allows me to define a message
and place that message in a txt file.
Thanks for help!!
Hello all
I have a sql file that I want execute by using the cmdsql command line. But when I create a text file I receive two Informational Messages:
1. "Changed database context to 'DataBaseName'."
2. (2 row(s) affected)
How can I ignore these messages in my text file? there is a parametter or something elese to configure to avoir these Informational Messages?
As we know there are two output windows in sql server management studio like 'Results' and 'Messages', I want to capture that 'Messages' window into a log file using powershell program . like into a log file it should display like :
ex:-
(1 row(s) affected)
(1 row(s) affected)
is there limitation for size of file to store in db by filestream in sql server 2008?or it accept all sizes?
View 1 Replies View RelatedBelow is the test data and my query. Basically i am trying to get the File records along with if any error messages occurred. I am getting the output as expected but the message column has "," at the front and sometime at the end of the column. How do i avoid that.
declare @Files table (ID int identity(1,1) primary key,Filename varchar(50),Date_Created datetime)
insert into @Files (Filename,Date_Created)
select 'File1',GETDATE()-1 union all
select 'File2',GETDATE()-1 union all
select 'File3',GETDATE()-1 union all
[Code] .....
Dear all I create this html file on the fly in my asp.net application abd what i would like to do is to store it inside my sql2005 database. What would be the best way?The html file itself is not really big. Probably not more then 600 - 800 characters most. I was thinking the text type fields of the database and then when retreiving it dump it inside in a file and save the file with html extention. Are there any better sugestions? Thank you for your time
View 3 Replies View RelatedI am getting an error about "Cannot perform a shrinkfile operation inside a user transaction", but I don't have a shrinkfile command in my procedure. Does SQL hang on to that command if it was received earlier in a different procedure?
View 5 Replies View RelatedFor inserting current date and time into the database, is it more efficient and performant and faster to do getDate() inside SQL Server and insert the value
OR
to do System.DateTime.Now in the application and then insert it in the table?
I figure even small differences would be magnified if there is moderate traffic, so every little bit helps.
Thanks.
Hi All,
I am not sure whether this is the right place to post this question. But I am unable to figure out what is the best solution to retrieve and display an image in a html file(stored in varbinary(max) column). I have a list of images in the file and I am supposed to display them. Can anybody please let me know what is the best way to do this?
Thanks a lot!!
I'm trying to execute a stored procedure within the case clause of select statement.
The stored procedure returns a table, and is pretty big and complex, and I don't particularly want to copy the whole thing over to work here. I'm looking for something more elegant.
@val1 and @val2 are passed in
CREATE TABLE #TEMP(
tempid INT IDENTITY (1,1) NOT NULL,
myint INT NOT NULL,
mybool BIT NOT NULL
)
INSERT INTO #TEMP (myint, mybool)
SELECT my_int_from_tbl,
CASE WHEN @val1 IN (SELECT val1 FROM (EXEC dbo.my_stored_procedure my_int_from_tbl, my_param)) THEN 1 ELSE 0
FROM dbo.tbl
WHERE tbl.val2 = @val2
SELECT COUNT(*) FROM #TEMP WHERE mybool = 1
If I have to, I can do a while loop and populate another temp table for every "my_int_from_tbl," but I don't really know the syntax for that.
Any suggestions?
please tell the steps
How to store File in SQL DataBase
Hi frds,
My question is how should i store a .DOC file in sql 2005 as image field and how to retrive the Doc stored in Image format
Thanks & regards
Sithender.s
Can someone please direct me to a discussion on which version of SQL Server allows a file to be stored in a db field and how this is accomplished as well as how the file is then retrieved with asp.net VB from the DBthanksMilton
View 16 Replies View RelatedDear all,,
I used a program that convert from powerpoint to flash
the output of this program is a folder that contain 2 folders and a HTML page
How can I store those in my database?
note: I have to use all of these folders to preview the flash
Thanks in advance
Hi!
I want to store doc/pdf/image file in my SQL server2000 database. I have taken the field datatype in SQL SErver2k as "text".
Now I want to select a file in my project & want to store it in the DB using asp.net commands.
Can anybody tell me the procedure with a small example?
I have used FileInput to select the file .
thnx.....
How to store the html file in sql server data.
Hi There,
My requirement is to read from a table in SQL 2000 and need to export the
data in a XML format with .XML in a flat file.
I don't want to do to use ADO or VB or any other external tools to do this. I want it to be purely done using SQL server within enviroment.
How to generate this kind of XML files from
SQL server?.
Kindest regards,
Chris.G
I am creating some applications for Windows Mobile 5.0 devices to be used internally at our company. Each device will run several home grown applications that will all share the same common SQL Mobile database.
What I am wondering is whether or not there is a standard path where such a database is typically stored in Windows Mobile 5.0. I see a folder called Application Data. Is that what it is for, or is that one for something else? I'm not real familiar with the layout of the Pocket PC in general. Could someone tell me where vendors normally put their database file(s)?
Thanks,
Brad
Hello, please help!!
I have spent days searching the web and forums for an answer to this simple question and cannot find an example.
I have built a service broker application on sql server 2005. The application puts some xml on an incoming queue which is basically a few parameters to be used in a query. This queue will then call a stored proc which does some business logic and puts the resulting results in another queue also in xml.
I have written a test harness in SQL to put messages on the inbound queue and then some sql to retrieve the returned code from the outbound queue.
What I want to do is be able to convert the SQL which does this into .net code to be used by an application. i.e. write in .net some code to put xml on a queue and then write some .net code to retrieve xml from another queue.
I wouldn't have thought this would be a difficult thing to do and would have been done hundreds of times, but unable to find anything to simply send and retrieve XML to service broker queues....
thanks for your help.. its really needed. I found some links, but they are really vague and often doing select statments in service broker or something like this. I don't want to call any sql, just send and recieve XML on the queues.
any example code that does this, would be really helpfull
kind regards,
David Weeden
Database Developer
how to use class iDataReader's function getbytes(...............) to read binary data?
using System;using System.Data;using System.Configuration;using System.Collections;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts;using System.Web.UI.HtmlControls;using System.Data.SqlClient;using System.Data.Common;using System.IO;
public partial class uploadFileToDatabase : System.Web.UI.Page{ string strUser; protected void Page_Load(object sender, EventArgs e) { strUser=Request.QueryString["download"]; if(strUser!=null) { string strConn="Data Source=hao-pc;Initial Catalog=simpleBBS;Persist Security Info=True;User ID=sa;Password=123;Pooling=False"; SqlDataAdapter sda=new SqlDataAdapter(); DbProviderFactory dbProviderFactory=DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbConn=dbProviderFactory.CreateConnection(); dbConn.ConnectionString=strConn; dbConn.Open(); DbCommand dbComm=dbProviderFactory.CreateCommand(); dbComm.Connection=dbConn; dbComm.CommandText="select * from userFiles2 where fileisn='"+strUser+"'"; IDataReader myReader=dbComm.ExecuteReader(); int strFileLen=0; if(myReader.Read()) { strFileLen=Convert.ToInt32(myReader.GetString(3)); FileStream afs; string fPath; fPath = Server.MapPath(Request.ApplicationPath) + "\upLoadFiles\" + myReader.GetString(1); afs = new FileStream(fPath, FileMode.OpenOrCreate, FileAccess.Write); Byte[] filedata = new Byte[strFileLen]; afs.Write(filedata, 0, strFileLen); BinaryWriter abw; int iBufferSize=1000; Byte[] outbyte=new Byte[iBufferSize-1]; long retval; long starIndex=0; abw=new BinaryWriter(afs); starIndex=0; int itemNum=4; retval=myReader.GetBytes(itemNum,starIndex,outbyte,0,iBufferSize); //when i store large data into a file it display an error such as "Buffer offset '0' plus the bytes '82452' is greater than the length of the passed in buffer. abw.Write(outbyte); starIndex+=iBufferSize; retval=myReader.GetBytes(itemNum,starIndex,outbyte,0,iBufferSize); } abw.Write(outbyte); abw.Flush(); abw.Close(); afs.Close(); myReader.Close(); dbComm.Dispose(); } } } protected void Button1_Click(object sender, EventArgs e) { string strFileName; string strFileType; int strFileLen; Random ran=new Random(); string strFileISN=DateTime.Now.ToString("yyyyMMddhhmmss")+ran.Next(0,999); if(FileUpload1.PostedFile.FileName!=null) { strFileName=FileUpload1.PostedFile.FileName; strFileType=strFileName.Substring(strFileName.LastIndexOf(".")+1); strFileName=strFileName.Substring(strFileName.LastIndexOf("\")+1); strFileLen=FileUpload1.PostedFile.ContentLength; string strConn="Data Source=hao-pc;Initial Catalog=simpleBBS;Persist Security Info=True;User ID=sa;Password=123;Pooling=False"; SqlDataAdapter sda=new SqlDataAdapter(); DbProviderFactory dbProviderFactory=DbProviderFactories.GetFactory("System.Data.SqlClient"); DbConnection dbConn=dbProviderFactory.CreateConnection(); dbConn.ConnectionString=strConn; dbConn.Open(); DbCommand dbComm=dbProviderFactory.CreateCommand(); dbComm.Connection=dbConn; string dateTime=DateTime.Now.ToString(); Stream fs=FileUpload1.PostedFile.InputStream; Byte[] filedata=new Byte[strFileLen]; fs.Read(filedata,0,strFileLen); dbComm.CommandText="insert into userFiles2 (fileisn,filename,filetype,filesize,filecontent,uploadTime) values (@fileisn,@filename,@filetype,@filesize,@filecontent,@uploadTime)"; dbComm.Parameters.Add(new SqlParameter("@fileisn", (object)strFileISN)); dbComm.Parameters.Add(new SqlParameter("@filename",(object)strFileName)); dbComm.Parameters.Add(new SqlParameter("@filetype",(object)strFileType)); dbComm.Parameters.Add(new SqlParameter("@filesize",(object)strFileLen.ToString())); dbComm.Parameters.Add(new SqlParameter("@filecontent",(object)filedata)); dbComm.Parameters.Add(new SqlParameter("@uploadTime",(object)dateTime)); dbComm.ExecuteNonQuery(); fs.Close(); dbConn.Close(); dbComm.Dispose(); dbConn.Close(); } } }
Hi,
Can some one give me some suggest?
Thanks!
I would like to store and read .doc file in a table of sql server using C#.
Can some body help me on this? my id is padam76@gmail.com.
Thanks & Regard,Padam Kumar Tripathi
I was wondering if anybody can help me in a storeProc for SQL 7 which can get the realational tables frm a database and put them into filesystem...
Cheers
--Nik
Hi,
I need to export data from sql server into flat file. The file sould be generated with current date and table anem.
for example yyyymmddhhmmsstablename.txt.
Do you have any solution for this.
Thanks,
or in registry? where?
View 8 Replies View RelatedHi,
I have never use sql server to store binary data. Now, I need to store a .doc file into it. Which column type I should adopt?
In addition, could you provide me related articles to study?
Thanks,
Ricky.