PRC file extensions in MSSQL Management Studio 2005

Most of our files were scripted out to our VSS as PRC files. In MSSQL
Management Studio 2005 these file extensions are not recognized by the
compiler as SQL text. Now I could go into VSS and change all my
extensions to SQL, but I feel like that would be a very time consuming
task. Is there a way to have MSSQL Management Studio 2005 recognize
this as executable SQL text?
Thanks,
MikeMike,
I think you can change the File Type Association in Windows - sounds
like you have give Windows a gentle nudge in the right direction.
HTH
Barry|||I tried that and it didn't work : - (|||What do you mean by "recognized by the compiler as SQL text"? SQL Server (th
e database engine)
doesn't open any files (containing source code), so it couldn't care less wh
at extensions you have
for the files on the files systems on your machines. So your problem has to
be with the tools you
are using. Perhaps you can elaborate a bit?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mike" <mikepmyers@.hotmail.com> wrote in message
news:1136388956.187342.158280@.g14g2000cwa.googlegroups.com...
> Most of our files were scripted out to our VSS as PRC files. In MSSQL
> Management Studio 2005 these file extensions are not recognized by the
> compiler as SQL text. Now I could go into VSS and change all my
> extensions to SQL, but I feel like that would be a very time consuming
> task. Is there a way to have MSSQL Management Studio 2005 recognize
> this as executable SQL text?
> Thanks,
> Mike
>|||I found the answer to my own question. In MSSQL Management Studio 2005
you have to associate the extension to the program by:
* Tools - Options - Text Editor - File Extnesion - Extension:
"PRC" - Editor: "SQL Query Editor"
It's a pain that MSSQL Management Studio 2005 doesn't recognize the
"PRC" file extension automatically, but as long as there is a
work-around I guess I'll live.sql

PRC file extensions in MSSQL Management Studio 2005

Most of our files were scripted out to our VSS as PRC files. In MSSQL
Management Studio 2005 these file extensions are not recognized by the
compiler as SQL text. Now I could go into VSS and change all my
extensions to SQL, but I feel like that would be a very time consuming
task. Is there a way to have MSSQL Management Studio 2005 recognize
this as executable SQL text?
Thanks,
Mike
I found the answer to my own question. In MSSQL Management Studio 2005
you have to associate the extension to the program by:
* Tools - Options - Text Editor - File Extnesion - Extension:
"PRC" - Editor: "SQL Query Editor"
It's a pain that MSSQL Management Studio 2005 doesn't recognize the
"PRC" file extension automatically, but as long as there is a
work-around I guess I'll live.

PRC file extensions in MSSQL Management Studio 2005

Most of our files were scripted out to our VSS as PRC files. In MSSQL
Management Studio 2005 these file extensions are not recognized by the
compiler as SQL text. Now I could go into VSS and change all my
extensions to SQL, but I feel like that would be a very time consuming
task. Is there a way to have MSSQL Management Studio 2005 recognize
this as executable SQL text?
Thanks,
MikeI found the answer to my own question. In MSSQL Management Studio 2005
you have to associate the extension to the program by:
* Tools - Options - Text Editor - File Extnesion - Extension:
"PRC" - Editor: "SQL Query Editor"
It's a pain that MSSQL Management Studio 2005 doesn't recognize the
"PRC" file extension automatically, but as long as there is a
work-around I guess I'll live.

PRC file extensions in MSSQL Management Studio 2005

Most of our files were scripted out to our VSS as PRC files. In MSSQL
Management Studio 2005 these file extensions are not recognized by the
compiler as SQL text. Now I could go into VSS and change all my
extensions to SQL, but I feel like that would be a very time consuming
task. Is there a way to have MSSQL Management Studio 2005 recognize
this as executable SQL text?
Thanks,
MikeI found the answer to my own question. In MSSQL Management Studio 2005
you have to associate the extension to the program by:
* Tools - Options - Text Editor - File Extnesion - Extension:
"PRC" - Editor: "SQL Query Editor"
It's a pain that MSSQL Management Studio 2005 doesn't recognize the
"PRC" file extension automatically, but as long as there is a
work-around I guess I'll live.

Posting an Image to SQL Server 2005

Hello Everyone I am trying to write image files to sql server using the following code. I have recieved the following error message

Failed to convert parameter value from a String to a Byte[]. Please help. I am sure it is a problem gathering the iostream. I am not very familiar. Any help is gretaly appreciated.

Thanks in advance

Here is the code:

'Dim User As MembershipUser = Membership.GetUser(CreateUserWizard1.UserName)

'Dim t As TextBox = DirectCast(FormView1.FindControl("aspnet_UserID"), TextBox)

Dim objConnAs SqlConnection

Dim objComAs SqlCommand

IfMe.FileUpload1.HasFileThen

Dim fileExtensionAsString

Dim fileOKAsBoolean =False

fileExtension = System.IO.Path. _

GetExtension(FileUpload1.FileName).ToLower()

Dim allowedExtensionsAsString() = _

{".jpg",".jpeg",".png",".gif",".mwv"}

For iAsInteger = 0To allowedExtensions.Length - 1

If fileExtension = allowedExtensions(i)Then

fileOK =True

EndIf

Next

'Try

Dim imagestreamAs System.IO.Stream = FileUpload1.FileContent

Dim data()AsByte

ReDim data(imagestream.Length - 1)

imagestream.Read(data, 0, imagestream.Length)

imagestream.Close()

objConn =New SqlConnection(strNewConnection)

objCom =New SqlCommand("insert into ProfileImagesAndDocs(aspnet_userid,img_name,img_data,img_contenttype)values(@.aspnet_userid,@.imagename,@.Picture,@.CategoryName)", objConn)

'--------------

'this is the aspnet_userid

Dim useridparameterAs SqlParameter =New SqlParameter("@.aspnet_userid", SqlDbType.VarChar)useridparameter.Value =Me.aspnet_userid.Text

objCom.Parameters.Add(useridparameter)

'--------------

'This is the image name

Dim imagenameparameterAs SqlParameter =New SqlParameter("@.imagename", SqlDbType.VarChar)imagenameparameter.Value =Me.FileUpload1.FileName

objCom.Parameters.Add(imagenameparameter)

'--------------

'this is the picture data

Dim pictureParameterAs SqlParameter =New SqlParameter("@.Picture", SqlDbType.Image)

pictureParameter.Value = data

objCom.Parameters.Add(pictureParameter)

'--------------

'this is the profile area or category i.e. video intro

Dim categorynameParameterAs SqlParameter =New SqlParameter("@.CategoryName", SqlDbType.VarChar)

pictureParameter.Value ="IntroVideo"

objCom.Parameters.Add(categorynameParameter)

objConn.Open()

objCom.ExecuteNonQuery()

objConn.Close()

'bookmark

Label1.Text ="File uploaded!"

'Catch ex As Exception

Label1.Text ="File could not be uploaded."

EndIf

Hi,

You may use FileStream to read the image content and save it as SqlDbType.Image into your database. See the following sample.

Private Function GetFile()As String''' GetFileDim fileAs HttpPostedFile = File1.PostedFilefileName = file.FileNameReturn fileNameEnd Function''' ReadFilePrivate Function ReadFile()As Byte()Dim fileAs FileStream = File.OpenRead(GetFile())Dim contentAs Byte() =New Byte(file.Length - 1) {}file.Read(content, 0, content.Length)file.Close()Return contentEnd FunctionPrivate Sub WriteImage()Dim commAs SqlCommand = conn.CreateCommand()comm.CommandText ="insert into images(image,type) values(@.image,@.type)"comm.CommandType = CommandType.TextDim paramAs SqlParameter = comm.Parameters.Add("@.image", SqlDbType.Image)param.Value = ReadFile()param = comm.Parameters.Add("@.type", SqlDbType.NVarChar)param.Value = GetContentType(New FileInfo(fileName).Extension.Remove(0, 1))If comm.ExecuteNonQuery() = 1ThenResponse.Write("Successful")ElseResponse.Write("Fail")End Ifconn.Close()End SubPrivate Function GetContentType(ByVal extensionAs String)As StringDim typeAs String =""If extension.Equals("jpg")OrElse extension.Equals("JPG")Thentype ="jpeg"Elsetype = extensionEnd IfReturn"image/" + typeEnd Function
 
Thanks.

|||

Thanks the code above worked very well. I am having one other difficulty when I select a file with a *.wmv extension the file is not read and the script halts. Any Ideas?! and thanks again for the solution.

possible uses and impact of using xml datatype

I'm looking at this for an application I'm working on right now that is currently using SQL 2000 and a huge number of meta data files.

We synchronize externally using different technologies to items which cannot be pre-defined at all. We don't know what they will look like, what attributes they will have, or even if a new item might pop up.

So. we have a database just to record that "x" exists, and there is an applicaiton layer to interpret with the meta data what x actually means and looks like, then display it to the user. We track changes to "x" once we know it is there, over time. IT's attribute values will change over time.

I'm thinking with 2005, we could stuff the fact that "x" exists into a row, and it's corresponding definition in an XML column. It seems that this is the exact situation the XML data type was invented for.

My question is: am I right in the above assumption, and what would we really gain by moving that information from the filesystem into the database? Better performance? Easier to manipulate the XML? Easier association of a particular XML file to database data? Would it degrade performance of a system that is currently kind of slow but working?

I think if we used this correctly and in a limited way, we could have something pretty spiffy.

How easy is it to read and manipulate the elements in the XML using SQL?

I want to stay away from CLR and continue using the application layer, just have the attributes for the object available to the application layer.

Could someone give me an example of the ideal situation this datatype was invented for? I believe it would be wrong to invent the whole "database-in-a-database" thing, but for our purposes the datatype might work, since we have no control over the entities or attributes but need to store their existence for the UI.

Obviously, I have a lot of research to do but I thought I might ask if it's worth my time at this point.

Yes, it's worth your time to investigate, but it's hard to know what the impact might be on your particular situation.

Tagged data formats and markup languages generally, of which XML is a member, are ideal for metadata situations.

But the thing is, you must already *have* a metadata language in your app, so it's hard to say what benefit would come from changing it to XML ... except for exactly this point, that the xpath and xquery capabilities in XML generally, and the excellent integration with SQL in SQL Server 2005, are very likely to be helpful. Also that XML as a language, is simple, straightforward, and very widespread.

Possible DTS File Import Bug

Env: SQL Server 2000 on in WIN NT 5.x
Job: import mutiple flat files into several tables daily.
Catch: one or two of the several flat files might be empty.

First thought/test:
Use [first row as fields] option for the import process.
Problem, DTS can't complete (as a package).

As an alternative, I could probably detect if a file is empty then
decide what to do with it, with VB activeX, it might be feasible,
question, VB has a command for "FileExist", how about "FileLen" or the
like for determining the length of a file?

TIA.Hi

See http://www.sqldts.com/default.aspx?292 and
http://www.sqldts.com/default.aspx?246

John

"NickName" <dadada@.rock.com> wrote in message
news:1102971903.347486.145350@.f14g2000cwb.googlegr oups.com...
> Env: SQL Server 2000 on in WIN NT 5.x
> Job: import mutiple flat files into several tables daily.
> Catch: one or two of the several flat files might be empty.
> First thought/test:
> Use [first row as fields] option for the import process.
> Problem, DTS can't complete (as a package).
> As an alternative, I could probably detect if a file is empty then
> decide what to do with it, with VB activeX, it might be feasible,
> question, VB has a command for "FileExist", how about "FileLen" or the
> like for determining the length of a file?
> TIA.|||Very helpful. Thank you.|||Very helpful. Thank you. However, activeX problem, error obj "string
C:\myDir\file1.csv" required, the code seems to be correct.

' File Size
' check file size if empty | 0 quit

Option Explicit

Function Main()

Dim oFSO
Dim oFile
Dim sSourceFile

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set sSourceFile = "C:\myDir\file1.csv"

Set sSourceFileV = sSourceFile.Value

Set oFile = oFSO.GetFile(sSourceFileV)

If oFile.Size > 0 Then
Main = DTSTaskExecResult_Success
Else
Main = DTSTaskExecResult_Failure
End If
' Clean Up
Set oFile = Nothing
Set oFSO = Nothing
End Function|||Never mind about the activeX error, the objFile.Value attribute was
unnecessary. But the "connectors" does not seem to have an option to
connect this ActiveX script with a package and making sure run the
ActiveX script first, instead of last.

TIA.|||Hi

You can use workflow to govern the order of the steps, if you had one or
more files to process then you can use the looping example
http://www.sqldts.com/default.aspx?246, although I would expect a three way
split for in the shouldILoop procedure or a second comparison step to cater
for files with size, files with no size and no files to process.

In your code sSourceFileV is not needed, use
Set oFile = oFSO.GetFile(sSourceFile)

John

"NickName" <dadada@.rock.com> wrote in message
news:1103038306.437005.86870@.f14g2000cwb.googlegro ups.com...
> Very helpful. Thank you. However, activeX problem, error obj "string
> C:\myDir\file1.csv" required, the code seems to be correct.
> ' File Size
> ' check file size if empty | 0 quit
> Option Explicit
> Function Main()
> Dim oFSO
> Dim oFile
> Dim sSourceFile
> Set oFSO = CreateObject("Scripting.FileSystemObject")
> Set sSourceFile = "C:\myDir\file1.csv"
> Set sSourceFileV = sSourceFile.Value
> Set oFile = oFSO.GetFile(sSourceFileV)
> If oFile.Size > 0 Then
> Main = DTSTaskExecResult_Success
> Else
> Main = DTSTaskExecResult_Failure
> End If
> ' Clean Up
> Set oFile = Nothing
> Set oFSO = Nothing
> End Function|||Hi

You can use workflow to determine the order of execution.

John

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Positive Pay Files in SSRS

I have been tasked with creating a Positive Pay (also known as Safe Pay) file
using SSRS. I was wondering if anyone here has had any experience doing this
or has any idea on the feasibility of this project. I am specifically
wondering whether SSRS is suitable for this type of task.
Thanks
Zack GallingerFirst, I haven't ever heard of Positive Pay so I googled it:"Positive Pay Is
a Way To Successfully Combat Check Fraud
Banks and large businesses are protecting themselves and are promoting a
technique called positive pay as a method of fighting check fraud artists.
Generally speaking, positive pay works like this:
a.. 1. Using accounting and database software, a company regularly sends
its bank a positive pay file that lists all the checks written against that
company's account(s). That file includes a record of each check's issue
date, amount, check number/account and payee name.
b.. 2. When a check reaches the bank for payment, the bank compares the
check against the positive pay file. Any discrepancies in a check's
information trigger a flag that the check in question may have been altered.
c.. 3. The bank notifies its corporate customer that the discrepancy has
been noted and asks the company to verify the authenticity of that check. "
Assuming you can create the query that gets the data then the issue is what
format the bank wants the data. It is very easy to create a CSV file.
You might find a SSIS job would be easier if the data format is complicated.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Zack" <Zack@.discussions.microsoft.com> wrote in mes
sage news:90685B31-1551-4F34-BF3A-A63D0DF181F5@.microsoft.com...
>I have been tasked with creating a Positive Pay (also known as Safe Pay)
>file
> using SSRS. I was wondering if anyone here has had any experience doing
> this
> or has any idea on the feasibility of this project. I am specifically
> wondering whether SSRS is suitable for this type of task.
> Thanks
> Zack Gallinger