Allow Zero Length - Database Discussion Boards

Rein Hillmann23-Jul-03 6:01

23-Jul-03 6:01

Adding spaces can help to isolate the complete word but it won't (for example) pick up the word "sentence" in this sentence.

Re: Search the database;

Ian Darling23-Jul-03 6:14

Ian Darling

23-Jul-03 6:14

True, but I thought I made that reasonably clear - the code was illustrative, not complete. You could modify the LIKE clause to handle those cases.

--
Ian Darling

Assign One ConnObj Pointer to another

Johan_yo17-Jul-03 11:27

Johan_yo

17-Jul-03 11:27

Do I need to call AddRef() when I assign one Connection pointer object to the other? or Do I just need to call "=" operator, nothing else?

eg: _ConnectionPtr pCn1,
pCn2;
//create instance for both pCn1 and pCn2
//pCn1 open connection
pCn2 = pCn1; //is just it, no need anything else?
//do I need to call AddRef() here?

I want to have just one Connection to Database to conserve DbServer's resource.

Do I need to call Release() or can I just set the pointer to NULL? I read somewhere that smartpointer supposedly call release() everytime we set the pointer to NULL. Is this true?

Is _RecordsetPtr the same thing as Connection pointer (same Qs as above but replace Conn with Recordset)?

How about _FieldPtr?

cheers,
Jo

Crystal Reports question

lustuyck16-Jul-03 22:09

lustuyck

16-Jul-03 22:09

Hi,

I'm working on a remoting project with Visual Studio .NET. What I want is, to build the ReportDocument on the server (so the dataset is filled, attached to the ReportDocument and also the rpt file is read server-side); and then to send the ReportDocument to the client and attach it there to the crystalreportviewer.

However, because ReportDocument is nog serializable, this is not possible.

Is there a way that I can build the report server-side (filling dataset, attaching to rpt) and then send the result to the client? I don't want to distribute the rpt files to the client...

Help with first Stored Procedure

totig16-Jul-03 6:14

totig

16-Jul-03 6:14

Hi. This is my first attempt at a stored procedure. Ok, I have three columns (name, date, cost) in my table called inventory. I am trying to find a way of getting the following out:
If the date is less than 12 months to the current date, then output a new column with cost/12
else it should be divided by 3.

Basically the output I am trying to get is:
Name Date Cost AdjCost

Please help if you can

Re: Help with first Stored Procedure

Ian Darling17-Jul-03 1:03

Ian Darling

17-Jul-03 1:03

This sounds like a candidate for a user defined function.

eg:

select Name, Date, Cost, dbo.adjust(Cost, Date) from inventory

where adjuct() is the function, and the SQL code is your stored procedures contents.

--
Ian Darling

Re: Help with first Stored Procedure

basementman17-Jul-03 5:05

basementman

17-Jul-03 5:05

create procedure GetAdjCost
AS

select
  Name,
  Date,
  Cost,
  case 
    when DateDiff(mm,Date,GetDate()) < 12 then Cost / 12
    else Cost / 3
  end 'AdjCost'
from
  Inventory

Hope this helps..

onwards and upwards...

Allow Zero Length

Anthony988716-Jul-03 5:43

Anthony9887

16-Jul-03 5:43

I'm having troubles setting the field to Allow Zero Length to True using ADO for C++. Any ideas or code samples?

Thanks

Re: Allow Zero Length

Giles16-Jul-03 6:04

Giles

16-Jul-03 6:04

Using ADOX?

"Je pense, donc je mange." - Rene Descartes 1689 - Just before his mother put his tea on the table.

Shameless Plug - Distributed Database Transactions in .NET using COM+

Re: Allow Zero Length

Anthony988716-Jul-03 6:49

Anthony9887

16-Jul-03 6:49

Yes

submitting a ' in a string to sql

DxSolo15-Jul-03 17:03

DxSolo

15-Jul-03 17:03

i have a problem i have a vb6 program which at one point calls a stored procedure that accepts two strings as arguments but there are certain times when on of the strings contains a ' for example

execute mystoredproc 'blahblah', 'jame's'

this of course generates an error because it thinks that the letters jame is the whole string being submitted so how can i change this so it accepts it

Re: submitting a ' in a string to sql

Nino_115-Jul-03 17:22

Nino_1

15-Jul-03 17:22

DX,
It sounds like your string in VB is reading the special character as the beginning of a comment. ie: ' Comments go here.

I don't know of a workaround for this, I have had the same problem with ASP Pages doing data inserts into SQL Server.

Tony

Re: submitting a ' in a string to sql

DxSolo16-Jul-03 3:18

DxSolo

16-Jul-03 3:18

no its not vb that has the problem its doesnt think its a comment its when its submitted to the stored procedure that it gets the problem

like the stored procedure would accept this

execute mystoreproc 'blah','blah'

no problem

but when you have something like this

execute mystoreproc 'blah's','blah'

the stored proc will interpet it like this
execute mystoreproc 'blah' s ,'blah'

because sql uses the ' character to enclose strings that are inserted or otherwise used in queries

Re: submitting a ' in a string to sql

DxSolo16-Jul-03 3:56

DxSolo

16-Jul-03 3:56

i found the solution if you want to use something like

'jake's bigtime' in an insert statement then you would have to simply put two apostrophies and sql will then put the one apostrophe character in the db

so the query would look like this

insert into test values('jake''s bigtime')

and a this query select * from test
would return

jake's bigtime

Re: submitting a ' in a string to sql

Simon_uk16-Jul-03 3:58

Simon_uk

16-Jul-03 3:58

Hello,
All you need to do is use two single quotes instead.
e.g. 'o''neill'.

HTH,
Simon.

[EDIT]
Ahh, I see you posted the correct answer 2 minutes before me! Smile | :)

[/EDIT]

Re: submitting a ' in a string to sql

Arjan Einbu17-Jul-03 0:40

Arjan Einbu

17-Jul-03 0:40

You should use the parameters collection to submit your parameters to the stored proc.

<br />
SqlCommand cmd = new SqlCommand("mystoredproc");<br />
cmd.CommandType = CommandType.StoredProcedure;<br />
cmd.Parameters.Add("@param1", "blahblah");   //replace @param1 with the name of your parameter<br />
cmd.Parameters.Add("@param2", "jamie's");    //replace @param2 with the name of your parameter<br />
cmd.ExecuteNonQuery();<br />

Then the necessary convertions will be done automagically for you.

Transaction Log File Growing Out Of Control !!

Nino_115-Jul-03 16:30

Nino_1

15-Jul-03 16:30

Has anyone had the same experience?

I have multiple databases on a SQL Server 2000 Enterprise Box.

OS: Windows 2000 Advanced Server.

SQL: Enterprise SQL Server 2000

I checked the disk space cause the space was getting a little low, and then D'OH Eek! | :eek:

The Log Files were 14.9GB in size.

I ran a script through QA and shrunk the log files to an acceptable size, but before, I had to set the database recovery option to Simple from Full.

Script:

USE DATABASENAME

-- FileName as it is in sysdevices, Size in MB's

DBCC SHRINKFILE (FILENAME_LOG, 1000)

GO

Is this sort of thing a normal occurence in SQL Server 2000?

Thanks.

Tony

Re: Transaction Log File Growing Out Of Control !!

Arjan Einbu17-Jul-03 0:46

Arjan Einbu

17-Jul-03 0:46

Yes, it is, if you don't regularly empty your log files. (Usually when taking a full backup)
You should set a max size for your logs, so that they don't grow this much again. (It's a pain discovering your disks are full, and ALL your databases halt...)

Re: Transaction Log File Growing Out Of Control !!

Mike Dimmick17-Jul-03 3:10

Mike Dimmick

17-Jul-03 3:10

Perfectly normal.

When Recovery Mode is set to Full or Bulk-Logged, all operations are recorded in the transaction log, which is only truncated when the transaction log is backed up.

This enables you to roll back the database to any point in time, even if the database wasn't backed up at that time.

You should either set the mode back to Simple, or back up the transaction log regularly.

Simple only allows you to restore from the most recent backup, IIRC.

The Bulk-Logged mode behaves like Full for normal transactions, but like Simple for bulk insert operations via the bcp tool or the BULK INSERT statement.

Need _easily deployable_ C#/.NET Oracle connectivity

dratti15-Jul-03 2:43

dratti

15-Jul-03 2:43

I am writing an application that needs to connect to an Oracle 9i database, select rows from one table, and insert some in another. It needs to be easily deployable. The goal is to have an installation program that is able to setup anything the user needs, coming from a fresh install of windows (win2k and XP are the only ones I’m worrying about supporting). This includes setting up the .NET runtime, Oracle data provider, and oracle client if necessary. This is just a simple application that automates some daily tasks people do around the office here.

I started trying to use Microsoft's Oracle data provider that ships with the 1.1 SDK. This works fine on a machine with an oracle9 client installed, but I need to be able to run this without installing a client - or be able to automatically install the client when installing my application. I'm not sure if this is possible - can anyone help?

Thinking the above might not be feasible, I looked into Data Tech's Data Providers: http://www.datadirect-technologies.com/products/dotnet/dotnetrelhigh.asp this worked great and you didn’t need an oracle client installed on the client PC. HOWEVER, the licensing scheme for this product is less than ideal, and you have to pay per client license. That just isn’t something we want to worry about (and they don’t offer unlimited use). Furthermore, you still need to install their data providers through their installation program, so really I'm not gaining what I really wanted from their product.

Are there any other options? Am I making myself clear? I just want to write a program that connects to an oracle database, without having to worry about what the client has or doesn’t have on their pc (though if I can automate a client install of oracle, that would work). Like Oracle's thin driver for java. Any help would be appreciated.

Thank you
Dave Ratti

Re: Need _easily deployable_ C#/.NET Oracle connectivity

kev_in25-Jul-03 13:49

kev_in

25-Jul-03 13:49

If you have the oracle client installed, you can peruse the Oracle Universal Installer Concepts Guide Release 2.2 (Part Number A96697-01), particularly the section in Chapter 3 on creating and using a response file. Essentially, you can run through the installer, having oracle installer keep track of your responses, then create an silent install using those parameters. You can start with a simple hardcoded install, then move toward customizing the response file. I've used installshield installs to let the user decide where to install oracle, then modify the RSP file accordingly. But it's definitely best to let Oracle handle the guts of the install.

HTH

On the road of life, there are passengers, drivers, and those guys with steamrollers.

Enumerating MSDE servers using SQLDMO

apferreira14-Jul-03 13:32

apferreira

14-Jul-03 13:32

In Windows XP Home Edition, when trying to list MSDE servers using C# and SQLDMO the list is empty. For instance, when doing

SQLDMO.ApplicationClass DMOApp= new SQLDMO.ApplicationClass();

SQLDMO.NameList names=DMOApp.ListAvailableSQLServers();

I get names.Count=0.

Can anybody point me in the right direction?

bcp command problem

si_6914-Jul-03 0:05

si_69

14-Jul-03 0:05

i have a declared a string which i want to write to a text file
using the bcp command

i have so far

declare @String varchar(300)
exec master..xp_cmdshell 'bcp @String queryout @String -o z:\callparent\text.txt'

which is not working Cry | :((

does ne1 have any idea's im not that familar with bcp

thanks

si

Re: bcp command problem

basementman14-Jul-03 7:05

basementman

14-Jul-03 7:05

If you are trying to use the contents of the string @String in your BCP command, you need to do the following:

declare @cCmd varchar(400)
declare @String varchar(300)

-- populate @String....

select @cCmd = 'bcp ' + @String + ' queryout ' + @String + ' -o z:\callparent\text.txt'

exec master..xp_cmdshell @cCmd

onwards and upwards...

Get databse name

Hoang Dung12-Jul-03 7:54

Hoang Dung

12-Jul-03 7:54

Hi,

How do I get all database name in SQL server 7?

Thanks.
H.Dung

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.