Instr function for SQL - Database Discussion Boards

Rob Graham8-May-06 16:48

8-May-06 16:48

Yea, "\n" is an "escape sequence" that the compiler understands to replace with the non-printing newline character(s), but embedded in a db string it is just \n...

You might be able to pass the db string through a format function and get the newlines replaced. If you're using C# try String.Format(dbString). C++ CString.Format(dbstring).

I haven't tried this, and I'm just guessing, but that is what I would try first.

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

Re: Using ROUND

myNameIsRon8-May-06 21:34

myNameIsRon

8-May-06 21:34

Hey Rob,
The string.Format didn't seem to work so I googled "\n not working" and found this:

http://www.codeproject.com/cs/miscctrl/MessageBoxEx.asp?df=100&forumid=155440&exp=0&select=1246013#xx1246013xx[\n Not Working^]

Which gave me this to try, and it works.
dbString.Replace(@"\n",Environment.NewLine);
or
dbString.Replace(@"\n","\n");

Cheers!
Ron

Reporting Service 2005

devvvy5-May-06 14:38

devvvy

5-May-06 14:38

Hey, "Formula Fields" in Reporting Service (SQL 2005), is it in VBScript?

Norman Fung

Export a table to a CSV file

Alvin Ortiz5-May-06 12:46

Alvin Ortiz

5-May-06 12:46

I have a table on Microsoft Access and I need to export it to a CSV file when I press a button on an Access form. I'm using some Transact-SQL code inside VBA, but nothing yet. Any commentaries?

Re: Export a table to a CSV file

Rob Graham7-May-06 6:07

Rob Graham

7-May-06 6:07

Microsoft Access does not use TRANSACT-SQL. That dialect is specific to SQL server.
If you post the relevant parts of the VBA code, someone might be able to help...otherwise you're asking us to guess, which most of us won't wate our time on Big Grin | :-D

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

Re: Export a table to a CSV file

Alvin Ortiz15-May-06 12:27

Alvin Ortiz

15-May-06 12:27

ok...let me re-phrase the question...this is the statement that I'm using to export the table to a excel file...
DoCmd.TransferSpreadsheet acExport,8, "Table2", "C:\Table2", True

This is after making this query...
DoCmd.RunSQL "SELECT [Fields] INTO Table2 FROM Table1;"

All I want is to export it to csv, or using the excel file that I already have...thanks for your time Smile | :)

Outputting RTF from SQL Reporting?

dxben5-May-06 5:54

dxben

5-May-06 5:54

Using SQL Server 2000, does the SQL Reporting tools allow you to output in RTF format?

Instr function for SQL

MatthysDT5-May-06 1:54

MatthysDT

5-May-06 1:54

I'm looking for a function in SQL Server to find the first occurence of a given character within a given string and return the position as int.

Really don't feel like re-inventing the wheel today, help would be appreciated!

you can't forget something you never knew...

Re: Instr function for SQL

sathish s5-May-06 2:07

sathish s

5-May-06 2:07

You can use Patindex or Charindex in sqlserver

SQL or TSQL Help Needed

Naveed Kamboh5-May-06 0:42

Naveed Kamboh

5-May-06 0:42

Hello Friends,

I have a field of description which holds data like this

F001 - xyz
H001 - abcd
FG0003 - lmn
...
...
...
...

I want to remove that par of data before this hyphen sign including hyphen "-" and want to represent data after hyphen sign.

Help will be appriciated.

Naveed Kamboh

Re: SQL or TSQL Help Needed

sathish s5-May-06 1:36

sathish s

5-May-06 1:36

query

use pubs
select substring (phone,charindex('-',phone)+1, Len(phone)-charindex('-',phone)+1) 
from 
authors

Re: SQL or TSQL Help Needed

Naveed Kamboh5-May-06 2:03

Naveed Kamboh

5-May-06 2:03

Thank you verey much its 100%.

Naveed Kamboh

Reg Stored Proc Parameter usage & performance

Pradeep Shamarao4-May-06 23:15

Pradeep Shamarao

4-May-06 23:15

I have to create stored procs for insertions and updates for a new project with lots of parameter (around 20 to 35), i wanted to know the advantange and disadvantages of the two methods below

Method 1. --- the usual way

Create Procedure SaveNewSettings
@param1 varchar(20),@param2 varchar(20),@param3 varchar(20),
@param4 varchar(20),....etc
---- insert into table values (

Method 2. -- the xml way
Create Procedure SaveNewSettings
@XMLParam As varchar(200)
AS

EXEC sp_xml_preparedocument @hDoc OUTPUT , @XMLParam
--- Do the openxml stuff and update

Which method of using parameters is better wrt performance and usability.

Please suggest.

I am using SQL Server 2000 & ASP.Net (VB.net as code behind) for my project.

MSDE and MS SQL 2005 Express Edition inquiry

Kuira4-May-06 13:35

Kuira

4-May-06 13:35

I would like to know if I can install both MSDE and MS SQL 2005 Express together such that I have an MSDE Database and also a MS SQL 2005 database to use.

Re: MSDE and MS SQL 2005 Express Edition inquiry

Paul Conrad4-May-06 14:37

Paul Conrad

4-May-06 14:37

Kuira,

You can run both side by side.

Paul

Clone rows based on existing rows

Ravi Bhavnani4-May-06 11:42

Ravi Bhavnani

4-May-06 11:42

Can you SQL gurus help me craft an UPDATE statement that will insert a modified clone of a set of rows into a table? For example, my table FIELDS_IN_VIEW has 2 columns VIEW and FIELD and contains several thousand rows:

 Row  VIEW  FIELD
 ---  ----  -----
   1   23    100
   2   23    701
   4   23    459
 ...
6500   99    804

I'd like to clone all the rows that apply to view 23 for a new view (eg: 24). View 24 is guaranteed to not already exist in the table.

Thanks,

/ravi

My new year's resolution: 2048 x 1536
Home | Music | Articles | Freeware | Trips
ravib(at)ravib(dot)com

Re: Clone rows based on existing rows

goyal manish4-May-06 20:12

goyal manish

4-May-06 20:12

try it
insert into FIELDS_IN_VIEW select 24, field from FIELDS_IN_VIEW where view = 23

Re: Clone rows based on existing rows

Ravi Bhavnani5-May-06 8:47

Ravi Bhavnani

5-May-06 8:47

Thanks, Manish!

/ravi

My new year's resolution: 2048 x 1536
Home | Music | Articles | Freeware | Trips
ravib(at)ravib(dot)com

CheckBox Control and RowID

karinb4-May-06 5:41

karinb

4-May-06 5:41

as a newbie, I have developed a simple administrative app that lists members of our mailing list. ON the viewing page, checkboxes are dynamically listed along with the member info. When one checks a box and clicks 'edit', i want the item checked to open into the edit page with it's appropriate data. Problem: I can get the checkbox associated to an id number on the viewing page. I just can't seem to figure out how to send that id and data to the edit page. any help or suggestions would be great. can provide code examples if needed.

karinb

-- modified at 11:42 Thursday 4th May, 2006

Re: CheckBox Control and RowID

Paddy Boyd4-May-06 5:56

Paddy Boyd

4-May-06 5:56

Cross posting - very annoying... Mad | :mad:

Re: CheckBox Control and RowID

goyal manish4-May-06 20:18

goyal manish

4-May-06 20:18

just put that id in session state or pass it as query string to the edit page.

Ole DB for OLAP

Tarek Jabri4-May-06 4:50

Tarek Jabri

4-May-06 4:50

I am trying to connect to MDP provider using
OLE DB For OLAP following these steps:
1-i'v got the List of providers..and choose one of them
2-i called ParseDisplayName() ,..the i called BindMoniker() method as written in the code below...
3-When i Call ppIDBInitialize->Initialize(); ..i got Access voilation exception
can any one help me please....
Here is the code:

ULONG chEaten = 0;
hr = pIParseDisplayName->ParseDisplayName(pIBindCtx,rgEnumInfo[i].wszParseName, &chEaten, &pIMoniker);
ppIDBInitialize=NULL;
hr = BindMoniker(pIMoniker, 0, IID_IUnknown,(void**)&ppIDBInitialize);

rgProps[0].dwPropertyID = DBPROP_INIT_LOCATION;
rgProps[0].vValue.vt  = VT_BSTR;
rgProps[0].vValue.bstrVal  =  SysAllocString(L"server");

rgProps[1].dwPropertyID = DBPROP_INIT_DATASOURCE;
rgProps[1].vValue.vt  = VT_BSTR;
rgProps[1].vValue.bstrVal  = SysAllocString(L"FoodMart 2000");

rgProps[2].dwPropertyID = DBPROP_AUTH_INTEGRATED;
rgProps[2].vValue.vt  = VT_BSTR;
rgProps[2].vValue.bstrVal  = SysAllocString(L"SSPI");

// Create the structure containing the properties.
DBPROPSET PropSet;
PropSet.rgProperties      = rgProps;
PropSet.cProperties       = 3;
PropSet.guidPropertySet   = DBPROPSET_DBINIT;

IDBProperties *pIDBProperties=NULL;

// create properties pointer
hr=ppIDBInitialize->QueryInterface(IID_IDBProperties,(void**)&pIDBProperties);

// Set properties
hr=pIDBProperties->SetProperties(1, &PropSet);

// Initialize the data source object.
 hr = ppIDBInitialize->Initialize();   // Access voilation exception

ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.sysconstraints'

thomasa4-May-06 3:39

thomasa

4-May-06 3:39

When I try to open a table Design view (SQL Server 2000) I
get the following error:

Table 'xlbsUsers' could not be loaded.
ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name 'dbo.sysconstraints'

I get this on all the tables on this SQL Server, thus other SQL Severs in this SQL Server Group works fine.

There is no problem oppening the 'xlbsUsers' table in 'return all rows' and adding, deleting or edeting records.

Does someone know how to fix this, or why I get this problem?

Thanks
Thomas

-- modified at 5:51 Friday 5th May, 2006

Fixed

thomasa4-May-06 23:50

thomasa

4-May-06 23:50

the 'dbo.sysconstraints' is a View that all SQL server needs, and it is automatic created when you create
a SQL Server.

I used the program "ApexSQL Diff" to update my Database from a another database, and in this event the
'sysconstraints' and 'syssegments' view somehow disappeard.
Lucky for me I had a backup and could get the views from there.
( actually I had to make a view with some bogus contents like "Select * from xlbsUsers", save it and close it. Then
copy the content of my backuped view and open the 'sysconstraints' which I created and paste it in. Did the same with 'syssegments' )

Thomas

SQL 2005 Analysis Services - pivot table

alex_kl4-May-06 3:22

alex_kl

4-May-06 3:22

Hi,

I work in an organization that has a new SQL 2005 server

when a person trys to view a pivot table report he get an error message "server is too busy"' but when the SQL 2005 is installed on the computer the user has no problem viewing the reports.
every computer got win2k+SP4+office 2003+frame work 1.1

which package need to be installed in the computers that get to user to connect to sql olap 2005 threw excel or frontpage 2003 without installing the full package of SQL2005.

thx Alex

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