I am trying to use System.IO.Compression to compress/decompress the buffer. I have the data in byte format in sql server and I need to create an CLR user defined function. I have everything running except that for some of the files, the compressed size is larger than the actual file size. I tried to use both Deflate and GZip compression types. Did anyone face the similar situation. Thanks in advance for your help.
Hi
I need to have a second copy of our databse on another server as backup if something will happen to the first one.
I couldnīt get the sql replication or database mirror to work ( I think the problem i that both servers are running in a workgroup an dnot in a domaine) and now I try to solved my problem with an automatic restore of the full backup (a new full backup is running every night) but now I need a function to check if todays date is the same as a part of the backupfilename and is it possible to do that as a step in the sqlagentjob i have create?
Thanks for any help how I can do that
I have built a Table Valued SqlFunction which streams out filtered results from a table. The way that I have implemented this is by using a custom class which implements the IEnumerator interface.
The class internally stores a SqlDataReader and a SqlConnection as private member variables. The Class initializer (ie: the New function) creates a SqlCommand which is executed with ExecuteReader into the SqlDataReader and returns.
The IEnumerator.MoveNext method loops through the SqlDataReader until it finds a result which matches the heuristic filter, and the IEnumerator.Current method returns the current result from the SqlDataReader.
Unfortunately as soon as the initializer returns the SqlDataReader is automatically closed, and I can't figure out why. I've debugged through this and at the end of the Initializer the SqlDataReader is definately open, and as soon as first call to MoveNext is run it is closed.
Can anyone offer any suggestions on how I can fix this.
In the interim i've had to load all of the filtered results into a temporary ArrayList in the Initializer, which defeats the purpose of streaming out the results.
There is a SqlFunction in my SQL Server Project:
[Microsoft.SqlServer.Server.SqlFunction(DataAccess=DataAccessKind.Read)]
public static SqlBoolean GetRelation(SqlGuid ID)
{
using (SqlConnection conn = new SqlConnection("context connection=true"))
{
conn.Open();
SqlCommand cmd = new SqlCommand("SELECT * FROM MemberRelation WHERE ID='" + ID + "'", conn);
//SqlCommand cmd = new SqlCommand("EXEC GetMemberRelation '" + ID + "'", conn);
/*SqlCommand cmd = new SqlCommand("GetMemberRelation", conn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add(new SqlParameter("@ID", SqlDbType.UniqueIdentifier));
cmd.Parameters["@ID"].Value = ID;*/
using (SqlDataReader sdr = cmd.ExecuteReader())
{
sdr.Read();
//...
sdr.Close();
}
conn.Close();
}
}
When I try to call a stored procedure by using one of the two marked code segments instead of concatenating string in GetRelation, I can deploy the project to my SQL server without problem. But when I call GetRelation from Query Analyzer, I get an error: "Invalid use of side-effecting or time-dependent operator in 'SET ON/OFF' within a function."
What does the error message mean? How could I correct it? Thanks for answering.
I'd need to create a file and then populate it. Any link/advice would be very appreciated.
Thanks in advance
Using BULK Insert with a format file I am receiving the following message:
Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'STREAMS' reported an error. The provider did not give any information about the error.
The statement has been terminated.
I am running SQL Server 7.0 w/ SP1 applied. The same data file and format files work fine if I use bcp.
The data file contains fixed length records.
Any ideas what the problem is?
The following is a list of questions that I have not been able to obtain concrete answers. I am probably missing something:
1) ReadWriteVariables -- can the updated value for a ReadWriteVariable be accessed within the same data flow? It appears not as I think the PostExecute() fires at the completion of the data flow not the end of the Script Component. Secondarily, the Script Component is a non-blocking transformation so the component does not "see" the end of the pipeline prior to sending data down stream.
2) Record Count -- Because of #1 above, How could you calculate a record count for a data stream? It does not appear that one can calculate the number of records for a data stream within a data flow and then access the count from within the same data flow.
3) FinishOutputs() -- Is the concept of FinishOutputs() applicable to Script Component Destinations? Asked another way, is FinishOutputs() executed at the end of the data stream regardless of whether there are "real" outputs for the component? I can create a "Dummy" output to create FinishOutputs() but is this ok?
4) Script Component -- It appears that the Script Component Source, Transformation or Destination are really defined based on the columns defined in "Inputs and Outputs". Can you convert an Source script component to a transformation script component by simply adding an Output?
Sorry for these basic questions but I am not getting it completely. As you can tell...
More of a general "Streams" question than broker specific, but this is all being done in the context of Broker passing the messages around. The use of Streams & Encoding seems to be my problem and I'm not as familiar with Streams as I am with other areas of the Framework... Any advice would be appreciated.
At this point, I've created my own objects/stored procedures based loosely on the ServiceBrokerInterface class provided in the SQL Server samples. Some of this was done for simplification and as a learning exercise, but also to ensure that all of the SQL operations are being done via Stored Procedures and not inline SQL. This was done to adhere to our existing security policy used on this project.
In this "interface" I've built, I have a [BrokerMessage.cs] class which is meant to have a few additional pieces of functionality beyond what the MS provided version had supplied.
1st... A constructor for accepting either String or XmlDocument as the "content"
2nd... Methods to return either a XmlDocument or a simple String.
Since all of the Broker functionality is defined as using VARBINARY(MAX) in my stored procedures, I don't believe I have any problems at that level. It's simply a binary blob to Broker.
In my constructor for accepting String or XmlDocuments, I attempted to use the following...
public BrokerMessage(string type, XmlDocument contents)
{
m_type = type;
m_contents = new MemoryStream(EncodingProvider.GetBytes(contents.InnerXml));
}
My understanding was that MemoryStream is derived from Stream so I can implicitly cast it. The "EncodingProvider" is a static member set as follows:
public static Encoding EncodingProvider = Encoding.Unicode;
This way I ensure that internal & external code can all be set to use the same encoding and easily changed if necessary. I was hoping to avoid using Unicode since the rest of the application does not require it, but from my understanding all Xml documents in SQL Server are Unicode based, so this should be a better encoding choice for any processing that may potentially occur within SQL Server itself.
In my methods to return the various forms of the "Stream", I have the following code... The ToBytes() method is what is used to pass intot he stored procedure parameter that is defined as VarBinary and expecting a byte array. One area of concern is that the Read method accepts an INT for the length, but the actual Length property is a LONG. I'm sure there's a better way to handle this and I would welcome any advise there.
/// <summary>
/// Used to convert from a Stream back to a simple Byte array.
/// </summary>
/// <returns></returns>
public virtual byte[] ToBytes()
{
byte[] results = new byte[this.Contents.Length];
this.Contents.Read(results, 0, (int)this.Contents.Length);
return results;
}
/// <summary>
/// Used to convert from a Stream back to a simple String.
/// </summary>
/// <returns></returns>
public new string ToString()
{
byte[] buffer = this.ToBytes();
String results = EncodingProvider.GetString(buffer);
return results;
}
/// <summary>
/// Used to convert from a Stream back to a simple XmlDocument.
/// </summary>
/// <returns></returns>
public virtual XmlDocument ToXmlDocument()
{
XmlDocument results = new XmlDocument();
results.InnerText = this.ToString();
return results;
}