How to translate oracle decode

Hi,

How to translate oracle Decode without changed code I mean:

I have one application and instead to change all decode to case when I would like just replace decode for dbo.decode, so I

Wrote this function

select dbo.fnDecode( 1 , 2 , 3 )

first parameter always int, and the others parameters could be char, int or float.

I would like to test first value and return second or third value

-> sql_variant for all parameters, ok

but I can use + or -

I can't do this

select dbo.fnDecode( 1 , 2 , 3 ) + dbo.fnDecode( 1 , 2 , 3 )

If I put cast ok, but I is there another way, overload this call?

With clr doesn't work, because Sql Server doesn't accept function overload calls from C#

Any ideia?

cheers,

Any ideia?

Use explicit cast/convert for both operands?

|||There is no easy way to do what you are trying i.e., replacing Oracle's built-in DECODE function with TSQL/SQLCLR equivalent. The sql_variant approach will work to some extent but you will run into issues when you try to use operators on the value as you experienced. It will require explicit CASTING in lot of cases. And using TSQL UDF will introduce performance problems especially in DMLs if used incorrectly. SQLCLR approach doesn't work because we don't support overloaded methods. Best is to convert uses of DECODE to CASE expression. Note that CASE expression is actually ANSI SQL standard and more portable way to write SQL code. In fact, Oracle supports it from 7.3 or 8i so there is no reason to use DECODE in Oracle too. As stop gap measure, you could write different set of TSQL UDFs (decode, decode1, decode2 etc) each handling different data types. And when you hit performance problems with your queries/DML that uses these UDFs you can convert to CASE expression i.e., inline the code.|||I can't use cast/convert, because I never know what kind of value will be, so I dont know what kind of cast if to char or to int,float ....

how to transform an .MDF database into a permanent database in sql server express?

Hi, i have a MDF file used by an asp.net application (with sql server 2005 express).

I want to make it to a permanent database in sql server express.

I know that i can attach it from Management Studio, but is there no better way?

Does it exist a way to 'import' or 'migrate' or copy' a .MDF.database into the central sql server, as if created directly in sql server express? After that, i can delete the .MDF file.

Thanks

Tartuffe

Hello,

if you would create a new database with SQL Server, it will also create an .mdf-file. You can see this for yourself in the following location:

C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

So, I'm not sure what your problem is with the mdf files, but they are perfectly normal SQL Database files and attaching is therefore a good way to bring your database online. If you want, you can first also create a brand new database first, and then import the date. In Management Studio you can right click the new database in the Object Explorer, select tasks > import data... and it should start the SQL Server Import and Export Wizard. Since it's a wizard, I think you will be able to perform the task as you see fit.

Good luck!

|||

Hi, thanks for replying.

Unfortunately, i have Management Studio express and when rightclicking on the new created db, i can't see Import, only backup/restore shrink ...

|||

Hi tartuffe2,

I my opinion, using attach database is the best way for this scenario.

SQL Server 2005 has many ways to implement move, copy, and update database, such as detach/attach, backup/restore, import/export, database mirroring, log shipping, replication. But some of them do not support in Express Edition. For your request, attach is the fastest and simplest way.

Thanks.

How To Transfer databse ..

Hi,

I am doing one E-commerce Application , throgh C#. & sql server express editon. Both my Local & Web Hosting servers are in sql server express 2005. I designed Databse Throghly, to my local server. Now I want to Transfer tables to my web server. I have already Sql server mangement studio Express. I can not do.. I spent, more than 5 yrs time..

Please help asasp..

Thanks

Partha
SSIS is not a component of SQL Server Express. You should be able to script the table structures and run the resulting scripts against the remote server. As far as data, there are some utilities available for scripting your data to INSERT statements if you search the internet. Or you can do it yourself by using the management views in SQL Server 2005.

How to transfer database to mobile device?

Hi all, I'm new to mobile devices.

I have a desktop application that uses SQL Server Compact Edition. I will soon have a mobile application that will use the same database. How can I (at runtime of the desktop app, say when the user clicks a button) transfer the sdf file to the mobile device? I'm not necessarily interested in synchronizing the databases, simply overwriting the database on the mobile device with the one on the desktop would suffice.

Is something like this possible?

Thanks!

You can copy the SDF file from the desktop to the device using RAPI.|||Thanks, I'll check that out.

How to transfer binary data from Server side to the client side through ASP Page ?

Hi,
I am designing a 4-tier application. It is like this.
Client - Business Logic Client Side (UI Validation) - ASP Page - Business
Logic Server Side (Database related) - SQL server database.
In this scenario, As of now I am passing a XML string from Server side
(Using ADO 2.6) to the client side through ASP. But I believe, compared to
transfer of huge XML strings from the server side to the client side,
transfer of binary data would be fast.
Can any one tell me, how to transfer binary data from Server side to the
client side through ASP Page ?
Thanks and Regards,
Peri
"Peri" <lperi@.CSPL.com> wrote in message
news:u4SyKPxPEHA.3220@.TK2MSFTNGP09.phx.gbl...
[snip]
> Can any one tell me, how to transfer binary data from Server side to the
> client side through ASP Page ?
You would need to figure out what the binary data would look like and how to
transfer it. That is the beauty of XML, you don't have to do either (but you
do pay the package tax).
Bryant

how to test before putting in production

Hi all,
I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000.
The application is in production and everytime I want to do some changes, i do the changes on my
development machine then I copy the application dll on the server.
The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements.
These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.

How can I do modifications without affecting the production server and the users ?
thanks.

The simplest solution is to have a local copy of your database and test against that. You can download the MSDE from Microsoft for free.

A more complicated solution would be to create the test SPs in a different schema from dbo, and reference them that way. (I've had good luck doing that with Oracle using synonyms. Everything was referenced via synonym, and if a developer wanted to override the object, he'd create a new one with the same name as the synonym in his own schema. I'm not sure how well this would work in SQL Server.)

|||Thanks for your reply. As you said the first solution seems easier.

I still have a few questions if you don't mind:
When I download MSDE, do they have utilities to copy my whole database from SQL server to MSDE ?|||

I use enterprise manager to back up the production DB, and then restore it on MSDE.

Yes. Just change the connection string to point to the appropriate db. It you have it in your web.config, it should be easy to do.

You can do a simple copy/paste. I use SqlDiff to copy schema/procedure changes from one database to another.

|||thank you , seems good i'll try that.

how to sync PDA data (in XML format)with remote database (SQL)

hello everyone,
i am developing a application using C#and .NEt compact framework.
for that i need to sync pda data (in xml format) with the remote database server(SQL) and vice versa.
please help me out
thanks in advance.I think using CE version of windows & SQL you can workaround this issue. I suggest you to refer to http://www.microsoft.com/sql (SQL homepage) website for more details about CE products.

HTH|||satya i am using pocket pc 2002/2003 not win ce and on pocket pc i am using xml as a database and on my central server i am using sql serve.
and thanks for the reply. please guide me in this direction.
regards|||May try this SDK download (http://www.microsoft.com/windowsmobile/information/devprograms/default.mspx?gssnb=1) site for any reference.

Have you gone thru MSSQL homepage earlier?|||no i havent tried mssql before that. should i go through with or not.
one more problem how can i embed the IE (internet explorer) in my application so that from within my application i would be able to see reports generated by the server.
please|||I think you would be better of to go thru the referred websites to get more information.

I don't have exp. with other tools so cannot offer any discussion.:(

How to sync data in multiple instances of a database?

Hi,
I have a question about how to keep the data in multiple instances of a
database in sync.
Our application is a stand alone desktop application, being used in all
of our branches - the exact same app running on the exact same
database, the only difference being the data in the database. The
backend is a SQL Server 2000 database, which is relatively small with
about 50 tables. Each branch would have its own data, and they would
generate reports at regular intervals and send it across to the main
office, and everything was going great. Now, a requirement that the
data of all branches be available to all other branches has come up.
One central database, though very much preferred, is ruled out because
of various reasons - operational, financial... but more importantly to
us developers, the application was designed to talk to the database
very frequently, so remoting will slow down the app which is not
acceptable to the accounting guys who use the app.
I would like to know the options available to us for getting the data
in one database to another. PK columns are all made of UUIDs, so the
problem of duplicate PKs won't be there while importing data of one
branch into another.
I read about publishing/subscription, but can't quite figure out how to
implement: each database needs to be a publisher as well as a
subscriber...
I also thought of using the bcp utility to export the data from each
branch and send it to all other branches, where we could use bcp to
import the data from all other branches, but some problems there: can
only new data be exported, or if not, can bcp be adviced to not copy
duplicate rows while importing? And then there's the question of
getting the exported file across to all the branches automatically...
Any ideas, suggestions are greatly appreciated. Additional info:
branches are quite far away from one another, worst case is about 2100
kms! And there's not a great deal of data: I would guess about a few
hundred rows of data a day.
- RameshThis sounds like a merge replication scenario. Each branch could be a
publisher to the central DB. The central DB could then re-publish to the
branches. You'd have a filter to ensure that a branch is not subscribing to
its own data.
You may want to post this in the .replication newsgroup. Ask for Hilary.
;-)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Ramesh" <dramesh@.rushmorent.com> wrote in message
news:1141154664.627611.186750@.v46g2000cwv.googlegroups.com...
Hi,
I have a question about how to keep the data in multiple instances of a
database in sync.
Our application is a stand alone desktop application, being used in all
of our branches - the exact same app running on the exact same
database, the only difference being the data in the database. The
backend is a SQL Server 2000 database, which is relatively small with
about 50 tables. Each branch would have its own data, and they would
generate reports at regular intervals and send it across to the main
office, and everything was going great. Now, a requirement that the
data of all branches be available to all other branches has come up.
One central database, though very much preferred, is ruled out because
of various reasons - operational, financial... but more importantly to
us developers, the application was designed to talk to the database
very frequently, so remoting will slow down the app which is not
acceptable to the accounting guys who use the app.
I would like to know the options available to us for getting the data
in one database to another. PK columns are all made of UUIDs, so the
problem of duplicate PKs won't be there while importing data of one
branch into another.
I read about publishing/subscription, but can't quite figure out how to
implement: each database needs to be a publisher as well as a
subscriber...
I also thought of using the bcp utility to export the data from each
branch and send it to all other branches, where we could use bcp to
import the data from all other branches, but some problems there: can
only new data be exported, or if not, can bcp be adviced to not copy
duplicate rows while importing? And then there's the question of
getting the exported file across to all the branches automatically...
Any ideas, suggestions are greatly appreciated. Additional info:
branches are quite far away from one another, worst case is about 2100
kms! And there's not a great deal of data: I would guess about a few
hundred rows of data a day.
- Ramesh|||Does each brach really need a complete copy of transactional data from all
other branches, or do they simply need summary data for inclusion in
soncolidated reports?
"Ramesh" <dramesh@.rushmorent.com> wrote in message
news:1141154664.627611.186750@.v46g2000cwv.googlegroups.com...
> Hi,
> I have a question about how to keep the data in multiple instances of a
> database in sync.
> Our application is a stand alone desktop application, being used in all
> of our branches - the exact same app running on the exact same
> database, the only difference being the data in the database. The
> backend is a SQL Server 2000 database, which is relatively small with
> about 50 tables. Each branch would have its own data, and they would
> generate reports at regular intervals and send it across to the main
> office, and everything was going great. Now, a requirement that the
> data of all branches be available to all other branches has come up.
> One central database, though very much preferred, is ruled out because
> of various reasons - operational, financial... but more importantly to
> us developers, the application was designed to talk to the database
> very frequently, so remoting will slow down the app which is not
> acceptable to the accounting guys who use the app.
> I would like to know the options available to us for getting the data
> in one database to another. PK columns are all made of UUIDs, so the
> problem of duplicate PKs won't be there while importing data of one
> branch into another.
> I read about publishing/subscription, but can't quite figure out how to
> implement: each database needs to be a publisher as well as a
> subscriber...
> I also thought of using the bcp utility to export the data from each
> branch and send it to all other branches, where we could use bcp to
> import the data from all other branches, but some problems there: can
> only new data be exported, or if not, can bcp be adviced to not copy
> duplicate rows while importing? And then there's the question of
> getting the exported file across to all the branches automatically...
> Any ideas, suggestions are greatly appreciated. Additional info:
> branches are quite far away from one another, worst case is about 2100
> kms! And there's not a great deal of data: I would guess about a few
> hundred rows of data a day.
> - Ramesh
>|||Hi JT,
Each branch needs a complete copy of all the data from all the
branches, please don't ask me why. It is as if there is one central
database and every branch is connected to it, except that there are
multiple copies of the data, one at each branch. (:
- Ramesh|||Hi Tom,
May be you are right; I should post this question in the other group.
Let me read about merge replication a bit before posting it there.
Thanks anyway.
- Ramesh

How to store Resume and cover letter into SQL Server?

Hi,

I am trying to create an asp.net web recruiting application for HR, which will give the users the ability to both copy/paste the Resume and cover letter in textbox and upload resume and cover letter, then submit it (which will be saved into SQL Server 2000 table).

I am thinking to save Resume and cover_letter as Image data type columns in SQL Server.

. Can someone give me a direction about how to save the uploaded resume and cover letter to table and if it's the easiest way to do it?

. What to deal with different formats of uploaded resumes? I hope to limit to only Word or HTML

. Since I also give user another option - copy/paste the resume and coverletter into a textboxes. Can I simply save the copy/paste resume and cover letter into text field column? Later, say, if any HR recruiter retrieve the text from database, will it concatonates everything together without line break?

Any ideas is appreciated.

You have two options use NChar 4000 or NVarchar 4000 and limit the size of the uploaded file so you can use ANSI SQL %Like% in your search or use NText and use Full Text index which is an add on to SQL Server dependent on Microsoft Search service with the Microsoft Catalog populated all the time to get search results. Full Text index uses Microsoft proprietry key word search as CONTAINS, CONTAINSTABLE, FREETEXT and FREETEXTTABLE. Run a search for Full Text index implementation in SQL Server BOL(books online) Hope this helps.|||

You can save the resume in the database in an image field

//Create a Buffer Stream to Hold the uploaded file
string FileName = GetFileName(txtFileUpload.PostedFile.FileName.ToString());
string FileType = txtFileUpload.PostedFile.ContentType.ToString();
int FileSize = int.Parse(txtFileUpload.PostedFile.ContentLength.ToString());
byte[] DocBuffer = new byte[FileSize];
Stream objSream;
objSream = txtFileUpload.PostedFile.InputStream;
objSream.Read(DocBuffer,0,FileSize);
//Pass this buffer as a paramter to a Stored procedure which will insert a new record into ur table
SqlParameter pcvContent = new SqlParameter("@.cvContent",SqlDbType.Image);
pcvContent.Direction = ParameterDirection.Input;
pcvContent.Value = cvContent;
dbCommand.Parameters.Add(pcvContent);
//======================================================================
to retrive the saved file from the database you have to do the following
do your select statment
Response.AddHeader("Content-Disposition","attachment; filename='"+ dr["FileName"].ToString() +"'");
Response.ContentType = dr["FileType"].ToString();
Response.BinaryWrite( (byte[]) dr["CvContent"]);
//=========================================================================
this the first solution.
The secound one is to create a resume builder.

|||Personally, I would consider storing BOTH the raw document in an IMAGEcolumn, and ripping the text of the document and storing that in a TEXTcolumn. This will allow you to set up a full-text index forsearching the resumes, while at the same time making it easy for usersto get the resumes back in whatever format they were submitted in.

|||

hi,

I need to do a keyword search in word and pdf documents. I tried using Index Server linked with SQL Server but its giving me many problems. So i'm planning to rip off the text from the word or pdf docs and store the text in a TEXT field as u said in one of the threads so that i can do a search easily.

Is it possible to read from a word or a pdf file and store it in a string. Sorry about contacting u directly

Thanks in Advance

How to stop reporting services deploy

I am using visual studio 2003 with sql reporting services for sql
server 2000.
I have a solution with a web application project and a reporting
services project.
When I start the web application project in the IDE, the reports are
automatically deployed to the report server.
I would like to stop this for two reasons: it takes too long, and my
reports are actually not ready to deploy.
Does anyone know how I can stop my reporting services project from
deploying when I run another project in the same solution?On May 15, 3:03 pm, wozza <Woz...@.gmail.com> wrote:
> I am using visual studio 2003 with sql reporting services for sql
> server 2000.
> I have a solution with a web application project and a reporting
> services project.
> When I start the web application project in the IDE, the reports are
> automatically deployed to the report server.
> I would like to stop this for two reasons: it takes too long, and my
> reports are actually not ready to deploy.
> Does anyone know how I can stop my reporting services project from
> deploying when I run another project in the same solution?
Does anyone have an answer?|||On May 15, 3:03 pm, wozza <Woz...@.gmail.com> wrote:
> I am using visual studio 2003 with sql reporting services for sql
> server 2000.
> I have a solution with a web application project and a reporting
> services project.
> When I start the web application project in the IDE, the reports are
> automatically deployed to the report server.
> I would like to stop this for two reasons: it takes too long, and my
> reports are actually not ready to deploy.
> Does anyone know how I can stop my reporting services project from
> deploying when I run another project in the same solution?
I can't believe I'm the only person who has ever encountered this.
Does anyone know how to work around this?|||On May 15, 3:03 pm, wozza <Woz...@.gmail.com> wrote:
> I am using visual studio 2003 with sql reporting services for sql
> server 2000.
> I have a solution with a web application project and a reporting
> services project.
> When I start the web application project in the IDE, the reports are
> automatically deployed to the report server.
> I would like to stop this for two reasons: it takes too long, and my
> reports are actually not ready todeploy.
> Does anyone know how I can stop my reporting services project from
> deploying when I run another project in the same solution?
OK, I've found the answer, which I'll document here for prosperity.
Right click the solution and go properties.
go configuration properties/configuration
reporting services projects have a "deploy" check box. Uncheck to stop
deployment.