finding length of text field

I need to find the length of a text field.The len function doesn't work with
text fields.
Is there an alternative?
Thanks!DATALENGTH()
HP wrote:
> I need to find the length of a text field.The len function doesn't work wi
th
> text fields.
> Is there an alternative?
> Thanks!|||Look in the BOL for DATALENGTH
HTH, jens Suessmeyer.|||HP (HP@.discussions.microsoft.com) writes:
> I need to find the length of a text field.The len function doesn't work
> with text fields.
As Jens and Trey said there is datalength().
In case you are using ntext, beware that datalength() returns the length
in bytes, and includes trailing blanks. For instance:
SELECT len(N'August '), datalength(N'August ')
Returns (6, 18).
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Look in the BOL for DATALENGTH.
HTH, Jens Suessmeyer.

finding first record ... !?

I have a table as follows:

- user_id (key)
- user_email_address (text)
- user_request_date (text)
- user_sent_date (time/date)

An automated macro will run every 5 minutes and needs to run a SQL UPDATE
that:

- finds the FIRST (ONE ROW ONLY) (youngest) record where
- user_request_date is the most recent
- AND user_sent_date is NULL (i.e. blank)

- THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
current system date/time)

For example:

- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL

SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)

- update record 33 - with NOW() time and date
THEN record 34, then 35, then 36, then 37
ONE ROW at a time per each SQL UPDATE

Any ideas!?
Thanks!
Richard

richard @. rcbuchanan . comRichard,

Try something like

update Richard set
user_sent_date = getdate()
where user_id = (
select top 1 user_id from Richard R
where R.user_sent_date is null
order by user_request_date
)

Your narrative and your example don't agree on whether
you want the most recent (youngest), or least recent (oldest)
user_request_date, so you may need to add DESC to the
order by clause.

Steve Kass
Drew University

Richard C Buchanan wrote:

>I have a table as follows:
>- user_id (key)
>- user_email_address (text)
>- user_request_date (text)
>- user_sent_date (time/date)
>An automated macro will run every 5 minutes and needs to run a SQL UPDATE
>that:
>- finds the FIRST (ONE ROW ONLY) (youngest) record where
> - user_request_date is the most recent
> - AND user_sent_date is NULL (i.e. blank)
> - THEN UPDATES the user_sent_date for THAT record to NOW() (i.e. the
>current system date/time)
>
>For example:
>- record 33 - request_date 01-JUL-03 14:55:03 - user_sent_date = NULL
>- record 34 - request_date 01-JUL-03 15:03:46 - user_sent_date = NULL
>- record 35 - request_date 01-JUL-03 16:08:08 - user_sent_date = NULL
>- record 36 - request_date 01-JUL-03 16:57:31 - user_sent_date = NULL
>- record 37 - request_date 01-JUL-03 17:11:41 - user_sent_date = NULL
>SO - the above NEEDED SQL UPDATE would update ONLY row 33 (the FIRST ROW)
>- update record 33 - with NOW() time and date
>THEN record 34, then 35, then 36, then 37
>ONE ROW at a time per each SQL UPDATE
>
>Any ideas!?
>Thanks!
>Richard
>richard @. rcbuchanan . com
>

finding digit on text

Hello there
is there a simple way to know if some text has digits on it?Sounds like you want PATINDEX
DECLARE @.mytext VARCHAR(100)
SET @.mytext='abc123xyz'
IF PATINDEX('%[0-9]%',@.mytext) > 0
PRINT 'Digits found'
ELSE
PRINT 'No digits found'|||One method is LIKE '%[0-9]%'.
Hope this helps.
Dan Guzman
SQL Server MVP
"Roy Goldhammer" <roy@.hotmail.com> wrote in message
news:OUhWShkVGHA.1564@.TK2MSFTNGP11.phx.gbl...
> Hello there
> is there a simple way to know if some text has digits on it?
>

Finding Correct Primary Key

Hello,
I could not reach a conclusion on this. Maybe someone helps me to understand
better.
I have a consolidated text extract from other systems and want to upload
the data to an SQL table.
Below is the fields in extract.
OrderNo
LineNumber
SellingCompany
PurcCtry
FinalizeDt
ItemNo
CustomerID
SalesRep
Quantity
Sales_USD
At first analysis, I thought OrderNo and LineNumber are the PK of the table
and designed the table.
When I receive the data I realized that since the data is coming from
different sources. Some of them has duplicate Orderno LineNumber
combination.
Now my data has two different of type of data one has unique key of Orderno
LineNumber combination. On the other hand second part of data needs OrderNo
LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
Question is : Should I keep these data in two different table, or should I
keep them in a single table by adding more columns on PK ?
What is the affect of defining larger PK even it is not needed for some of
the data?
My table has data has 1.5 million record and increasing 75000 each month.
70 % of data has Unique Orderno Line Number
Data is used by sql server reporting services, I have also some plans to use
analysis server in the future.
Thanks in advance.
erdal,You can use up to 16 columns for a composite primary key, however, make sure
you only use as many as you actually need, based on your data.
If you expect frequent and large increases in table size, make the primary
key nonclustered, and find a more appropriate candidate for the clustered
index. An ideal candidate for this kind of situation is a column with
non-nullable unique values, that increase for each new inserted row (e.g. an
identity column).
Using several tables is an option, yet it may complicate querying for the
reporting purposes.
ML|||> Question is : Should I keep these data in two different table, or should I
> keep them in a single table by adding more columns on PK ?
It seems to me that the data from the 2 sources represents different
entities so you should store these in different tables.

> On the other hand second part of data needs OrderNo
> LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
You need to do some analysis to determine the appropriate PK. What does the
data from this data source actually represent (e.g. order details history or
different order entry systems)? Simply adding columns until you come up
with a unique combination isn't the right approach. Data may be unique
based on your current data sample but not necessarily in the future.
Perhaps the data is bad and you are only masking an underlying problem.
Maybe some data scrubbing is needed before import. These are all issues
that need to be addressed in order to make intelligent schema design
decisions.
Hope this helps.
Dan Guzman
SQL Server MVP
"Erdal Akbulut" <erdalim21@.yahoo.com> wrote in message
news:ukME7Fv1FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I could not reach a conclusion on this. Maybe someone helps me to
> understand
> better.
> I have a consolidated text extract from other systems and want to upload
> the data to an SQL table.
> Below is the fields in extract.
> OrderNo
> LineNumber
> SellingCompany
> PurcCtry
> FinalizeDt
> ItemNo
> CustomerID
> SalesRep
> Quantity
> Sales_USD
>
> At first analysis, I thought OrderNo and LineNumber are the PK of the
> table
> and designed the table.
> When I receive the data I realized that since the data is coming from
> different sources. Some of them has duplicate Orderno LineNumber
> combination.
> Now my data has two different of type of data one has unique key of
> Orderno
> LineNumber combination. On the other hand second part of data needs
> OrderNo
> LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
> Question is : Should I keep these data in two different table, or should I
> keep them in a single table by adding more columns on PK ?
> What is the affect of defining larger PK even it is not needed for some of
> the data?
> My table has data has 1.5 million record and increasing 75000 each month.
> 70 % of data has Unique Orderno Line Number
> Data is used by sql server reporting services, I have also some plans to
> use
> analysis server in the future.
> Thanks in advance.
>
> erdal,
>|||On Sat, 22 Oct 2005 13:15:06 +0300, Erdal Akbulut wrote:

>Hello,
>I could not reach a conclusion on this. Maybe someone helps me to understan
d
>better.
>I have a consolidated text extract from other systems and want to upload
>the data to an SQL table.
>Below is the fields in extract.
>OrderNo
>LineNumber
>SellingCompany
>PurcCtry
>FinalizeDt
>ItemNo
>CustomerID
>SalesRep
>Quantity
>Sales_USD
>
>At first analysis, I thought OrderNo and LineNumber are the PK of the table
>and designed the table.
>When I receive the data I realized that since the data is coming from
>different sources. Some of them has duplicate Orderno LineNumber
>combination.
(snip)
>Question is : Should I keep these data in two different table, or should I
>keep them in a single table by adding more columns on PK ?
Hi Erdal,
It seems to me that you're approaching this from the wrong way. You
should start with how the data is supposed to be related in the
business.
Not knowing your business and making some wild assumptions about the
meaning of some columns names, my first guess is that (OrderNo,
LineNumber) might be a good candidate key for this table - but that's
noway sure! If your business assigns one and only one salesrep toe ach
customer, than SalesRep should be in the Customers table and can be
removed from this table. Similar, if each customer makes all it's
purchases in one country, then PurcCtry should be moved to the Customers
table as well. There might be more similar dependencies between the
columns that only someone with knowledge of the business model of your
company can find.
WRT the duplicated order numbers: if these sources each have their own
system for assigning order numbers (as might be the case after the
merger of two companies, or in a company with independent branches) and
duplicates are to be expected, then you should add a column to indicate
the source (not the data source, but the source of the order - e.g. the
name or ID of the branch where the order comes from), and add that
column to the primary key. OTOH, if the order numbers really should be
unique in the complete collection of data, then you'll have to hunt down
the reason why duplicates were entered in the first place, fix the
problem, clean up the erronous data before proceeding with this
consolidation project.
(previously snipped text:)
>Now my data has two different of type of data one has unique key of Orderno
>LineNumber combination. On the other hand second part of data needs OrderNo
>LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
I'm not sure if I understand you right. Maybe you're just trying to find
a set of columns that would technically (but not logically) qualify as
primary key - in that case see above: either add a column or fix the
real problem.
But if you're saying that the business model logically implies that the
data from one source has (OrderNo, LineNumber, FinalizeDt, ItemNo,
CustomerID) as the "real" primary key, then
a) You have a very unusual business model (but of course, mine is not to
judge), and
b) You can't combine the data from the two sources into one table, since
they have a different logical structure - it would be like trying to
combine ledger entries and personnel records into one table.

>What is the affect of defining larger PK even it is not needed for some of
>the data?
You'll get corrupted data in your table. Would you consider defining the
primary key on your Personnel table as (EmployeeID, EmpName)? I'd hope
not - since it's not the question IF you'll end up with duplicated data,
but WHEN you'll get it. And the answer is "sooner than you think".
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||> When I receive the data I realized that since the data is coming from
> different sources. Some of them has duplicate Orderno LineNumber
> combination.
>
It sounds as if what you need is something like SourceID, OrderNo,
LineNumber. If SourceID is not included in the extract files, then populate
it during the transform/load step.
.|||Thanks for the answers,
Let me try to clarify the issue.
we have 7 companies running on the same application, and 2 more using
different systems. All applications run on a mainframe environment and the
data is also consolidated in mainframe.
For the first 7 companies. OrderNo and Orderline are the candidates. No
problem at this part.
For the other 2 companies, we do not have the data in orderline level. The
data is summed by item, customer etc by month, and they have dummy ordenos
as 999999 and line no as 001
Currently I have two tables. one for the first 7 companies having Orderno
and Linenumber as PK. and another table for the other 2 companies having PK
with multiple columns.
I was thinking to merge the data in one table and define a larger PK having
PK columns from first and second table.
Seems it is not recommended in this case.
regards,
"Erdal Akbulut" <erdalim21@.yahoo.com> wrote in message
news:ukME7Fv1FHA.2348@.TK2MSFTNGP15.phx.gbl...
> Hello,
> I could not reach a conclusion on this. Maybe someone helps me to
understand
> better.
> I have a consolidated text extract from other systems and want to upload
> the data to an SQL table.
> Below is the fields in extract.
> OrderNo
> LineNumber
> SellingCompany
> PurcCtry
> FinalizeDt
> ItemNo
> CustomerID
> SalesRep
> Quantity
> Sales_USD
>
> At first analysis, I thought OrderNo and LineNumber are the PK of the
table
> and designed the table.
> When I receive the data I realized that since the data is coming from
> different sources. Some of them has duplicate Orderno LineNumber
> combination.
> Now my data has two different of type of data one has unique key of
Orderno
> LineNumber combination. On the other hand second part of data needs
OrderNo
> LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
> Question is : Should I keep these data in two different table, or should I
> keep them in a single table by adding more columns on PK ?
> What is the affect of defining larger PK even it is not needed for some of
> the data?
> My table has data has 1.5 million record and increasing 75000 each month.
> 70 % of data has Unique Orderno Line Number
> Data is used by sql server reporting services, I have also some plans to
use
> analysis server in the future.
> Thanks in advance.
>
> erdal,
>|||On Mon, 24 Oct 2005 18:30:15 +0300, Erdal Akbulut wrote:

>Thanks for the answers,
>Let me try to clarify the issue.
>we have 7 companies running on the same application, and 2 more using
>different systems. All applications run on a mainframe environment and the
>data is also consolidated in mainframe.
>For the first 7 companies. OrderNo and Orderline are the candidates. No
>problem at this part.
>For the other 2 companies, we do not have the data in orderline level. The
>data is summed by item, customer etc by month, and they have dummy ordenos
>as 999999 and line no as 001
>
>Currently I have two tables. one for the first 7 companies having Orderno
>and Linenumber as PK. and another table for the other 2 companies having P
K
>with multiple columns.
>I was thinking to merge the data in one table and define a larger PK having
>PK columns from first and second table.
>Seems it is not recommended in this case.
Hi Erdal,
Seems to me that you have two options to chose from:
A) Use two tables. One keyed on (OrderNo, LineNumber) to store the data
from the first 7 companies, and the second one with a smaller amount of
columns, and keyed on (Item, Customer, etc, Month) for the last 2
companies. Don't introducce fake order and line numbers in the last
table, they'll only confuse people.
B) Use a staging table to accept the data from the first 7 companies.
Aggregate this data to the same level as the data you receive from the
other 2 companies. Then combine the data from the latter 2 companies
with the aggregated data from the first 7 companies into one table.
Which of these solutions suits your problem best is for you to decide,
since I don't know what you're planning to do with the data, once it's
in the database.
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

Finding Correct Primary Key

Hello,
I could not reach a conclusion on this. Maybe someone helps me to understand
better.
I have a consolidated text extract from other systems and want to upload
the data to an SQL table.
Below is the fields in extract.
OrderNo
LineNumber
SellingCompany
PurcCtry
FinalizeDt
ItemNo
CustomerID
SalesRep
Quantity
Sales_USD
At first analysis, I thought OrderNo and LineNumber are the PK of the table
and designed the table.
When I receive the data I realized that since the data is coming from
different sources. Some of them has duplicate Orderno LineNumber
combination.
Now my data has two different of type of data one has unique key of Orderno
LineNumber combination. On the other hand second part of data needs OrderNo
LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
Question is : Should I keep these data in two different table, or should I
keep them in a single table by adding more columns on PK ?
What is the affect of defining larger PK even it is not needed for some of
the data?
My table has data has 1.5 million record and increasing 75000 each month.
70 % of data has Unique Orderno Line Number
Data is used by sql server reporting services, I have also some plans to use
analysis server in the future.
Thanks in advance.
erdal,Hello Erdal,
I usually add a source system key when combining data sets from
different sources. If you add a source system key to Orderno,
LineNumber combination does that give you a unique key?
If the answer is yes then you can have a common table. I normally have
different staging tables and one common table for reporting and star
schema generation.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/|||Hello Erdal,
I just re-read my post and forgot to mention that I hard code or
generate the source system key. It helps me trace the source of records
in the systems I build.
Hope this is a bit clearer now,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/

Finding Correct Primary Key

Hello,
I could not reach a conclusion on this. Maybe someone helps me to understand
better.
I have a consolidated text extract from other systems and want to upload
the data to an SQL table.
Below is the fields in extract.
OrderNo
LineNumber
SellingCompany
PurcCtry
FinalizeDt
ItemNo
CustomerID
SalesRep
Quantity
Sales_USD
At first analysis, I thought OrderNo and LineNumber are the PK of the table
and designed the table.
When I receive the data I realized that since the data is coming from
different sources. Some of them has duplicate Orderno LineNumber
combination.
Now my data has two different of type of data one has unique key of Orderno
LineNumber combination. On the other hand second part of data needs OrderNo
LineNumber ,FinalizeDt,ItemNo and CustomerID for a unique key.
Question is : Should I keep these data in two different table, or should I
keep them in a single table by adding more columns on PK ?
What is the affect of defining larger PK even it is not needed for some of
the data?
My table has data has 1.5 million record and increasing 75000 each month.
70 % of data has Unique Orderno Line Number
Data is used by sql server reporting services, I have also some plans to use
analysis server in the future.
Thanks in advance.
erdal,
Hello Erdal,
I usually add a source system key when combining data sets from
different sources. If you add a source system key to Orderno,
LineNumber combination does that give you a unique key?
If the answer is yes then you can have a common table. I normally have
different staging tables and one common table for reporting and star
schema generation.
Hope this helps,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
|||Hello Erdal,
I just re-read my post and forgot to mention that I hard code or
generate the source system key. It helps me trace the source of records
in the systems I build.
Hope this is a bit clearer now,
Myles Matheson
Data Warehouse Architect
http://bi-on-sql-server.blogspot.com/
sql

finding connection string

to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
Thanks
It was UDL, I remember now.
"me" wrote:

> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks

finding connection string

to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:

> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks

finding connection string

to find a connection string I use to create a text file and change the
extension to ?
and then I could connect to the database and find the connection string. I
forgot what the file extension was. any one knows?
ThanksIt was UDL, I remember now.
"me" wrote:
> to find a connection string I use to create a text file and change the
> extension to ?
> and then I could connect to the database and find the connection string. I
> forgot what the file extension was. any one knows?
> Thanks

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).
Use DATALENGTH.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>
|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).Use DATALENGTH.
--
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>> is this possible in T-SQL? (SQL Server 2000)
>> I want to find if the field is not null but empty (has no text in it).
>>
>>
>

find the LENgth of a TEXT/NTEXT field

is this possible in T-SQL? (SQL Server 2000)
I want to find if the field is not null but empty (has no text in it).Use DATALENGTH.
Adam Machanic
Pro SQL Server 2005, available now
http://www.apress.com/book/bookDisplay.html?bID=457
--
"John Richardson" <j3richardson@.hotmail.com> wrote in message
news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
> is this possible in T-SQL? (SQL Server 2000)
> I want to find if the field is not null but empty (has no text in it).
>
>|||thanks. can't believe I didn't see that.
"Adam Machanic" <amachanic@.hotmail._removetoemail_.com> wrote in message
news:ehe4oXWFGHA.344@.TK2MSFTNGP11.phx.gbl...
> Use DATALENGTH.
> --
> Adam Machanic
> Pro SQL Server 2005, available now
> http://www.apress.com/book/bookDisplay.html?bID=457
> --
>
> "John Richardson" <j3richardson@.hotmail.com> wrote in message
> news:eLQoiVWFGHA.4088@.TK2MSFTNGP10.phx.gbl...
>sql

find text string in database

Hello,
I'd like to find a specific text string searching in all tables within same database.
Is there a way to make only one query to all tables at the same time?
Thank you very much for your attention.
QslxNo. You would have to write a procedure that looped through all the tables and checked every column.

This question makes me suspect that there are some design issues with your database schema.

blindman|||Ya mean like:

USE Northwind
GO

CREATE TABLE myTable99 (TABLE_NAME sysname, COLUMN_NAME sysname, Occurs int)
GO

SET NOCOUNT ON

DECLARE @.SQL varchar(8000), @.TABLE_NAME sysname, @.COLUMN_NAME sysname, @.Sargable varchar(80), @.Count int

SELECT @.Sargable = 'Beer'

DECLARE insaneCursor CURSOR FOR
SELECT c.TABLE_NAME, c.COLUMN_NAME
FROM INFORMATION_SCHEMA.Columns c INNER JOIN INFORMATION_SCHEMA.Tables t
ON t.TABLE_SCHEMA = c.TABLE_SCHEMA AND t.TABLE_NAME = c.TABLE_NAME
WHERE c.DATA_TYPE IN ('char','nchar','varchar','nvarchar','text','ntext ')
AND t.TABLE_TYPE = 'BASE TABLE'

OPEN insaneCursor

FETCH NEXT FROM insaneCursor INTO @.TABLE_NAME, @.COLUMN_NAME

WHILE @.@.FETCH_STATUS = 0
BEGIN
SELECT @.SQL = 'INSERT INTO myTable99 (TABLE_NAME, COLUMN_NAME, Occurs) SELECT '
+ '''' + @.TABLE_NAME + '''' + ','
+ '''' + @.COLUMN_NAME + '''' + ','
+ 'COUNT(*) FROM [' + @.TABLE_NAME
+ '] WHERE [' + @.COLUMN_NAME + '] Like '
+ ''''+ '%' + @.Sargable + '%' + ''''
--SELECT @.SQL
EXEC(@.SQL)
IF @.@.ERROR <> 0
BEGIN
SELECT @.SQL
SELECT * FROM INFORMATION_SCHEMA.Columns WHERE TABLE_NAME = @.TABLE_NAME
GOTO Error
END
FETCH NEXT FROM insaneCursor INTO @.TABLE_NAME, @.COLUMN_NAME
END

SELECT * FROM myTable99 WHERE Occurs <> 0

Error:
CLOSE insaneCursor
DEALLOCATE insaneCursor

GO

DROP TABLE myTable99
GO

SET NOCOUNT OFF|||Hi Brett,
Thanks a lot for you help.
It works great!

Cheers,|||brett, don't you have any hobbies? :p|||Yeah...SQL

That was a cut and paste from my toolbox...

You kidding...I'm writting a sql server version of the window explorer find function...using xp_cmdshell, because it's tooooooooooooo painful to deal with server ops...|||I can't tell when you're kidding and when you're not!|||I'd say I'm an Enigma...but that's taken already...8-)

And I finally got server ops to give me clearence, so I won't have to build the explorer

Are Margarittas a hobby?

Find text in collapsed drilldown report

I have a drilldown report. Without opening a drilldown item, is there a way
to search for a text string using the "Find" link when viewing a report? If
not, what would be the best recommendation to accomplish this.Hello Parker,
Without opening the drilldown item, you could not use the "Find" button to
find the text in the drilldown item.
This is by design because the find operation only search the HTML which is
appearanced.
My suggestion is that add a parameter in the report to control to expanded
all the items.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi ,
How is everything going? Please feel free to let me know if you need any
assistance.
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.

Find specific text in a string

Hai ,
in a textbox am having text as
"(20/100)+pay1*pay2" .it's a formula. and stored in a particular
variable.
string strformula="(20/100)+pay1*pay2" ;
i've to substitute the value of the variable 'pay1' & 'pay2' and
finding the value of that strformula.
can any onr tell me how to find 'pay1' and 'pay2' in the variable
strformula. it's urgent and reply immediately.
Thanks in advance.Hi
I am not sure what this has to do with SQL Server!
If the strings are unique then you could use the replace function.
John
<ksrajalakshmi@.gmail.com> wrote in message
news:1139642884.262422.60580@.g14g2000cwa.googlegroups.com...
> Hai ,
> in a textbox am having text as
> "(20/100)+pay1*pay2" .it's a formula. and stored in a particular
> variable.
> string strformula="(20/100)+pay1*pay2" ;
> i've to substitute the value of the variable 'pay1' & 'pay2' and
> finding the value of that strformula.
> can any onr tell me how to find 'pay1' and 'pay2' in the variable
> strformula. it's urgent and reply immediately.
> Thanks in advance.
>|||If am having the value as
string strvalue ="(12.23+233.56)*12/100";
i've to find the value.so that am converting to double. but it throws
error. tel me how to find value?|||Hi
It is still not clear if you are using SQL Server! If you are then you can
do something like:
DECLARE @.strformula nvarchar(60)
DECLARE @.nparams nvarchar(80)
DECLARE @.output decimal(10,4)
DECLARE @.pay1 decimal(10,4)
DECLARE @.pay2 decimal(10,4)
SET @.strformula = N'SELECT @.output_val = (20.0/100)+(@.pay_1*@.pay_2)'
SET @.nparams = N'@.output_val decimal(10,4) OUTPUT, @.pay_1 decimal(10,4),
@.pay_2 decimal(10,4)'
SET @.pay1 = 233.56
SET @.pay2 = 12.0/100
SELECT @.strformula
SELECT @.nparams
EXEC sp_executesql @.strformula, @.nparams, @.output_val = @.output OUTPUT,
@.pay_1 = @.pay1, @.pay_2 = @.pay2
SELECT @.output
John
<ksrajalakshmi@.gmail.com> wrote in message
news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
> If am having the value as
> string strvalue ="(12.23+233.56)*12/100";
> i've to find the value.so that am converting to double. but it throws
> error. tel me how to find value?
>|||Actually I meant that your syntax wasn't SQL syntax:

Not mine. It is T-SQL :)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> wrote in message news:...
> First, this clearly isn't SQL Syntax, but that notwithstanding, you can
> evaluate a function like this, (as long as it fits SQL Syntax of course)
> declare @.value decimal (10,8)
> declare @.formula varchar(200), @.query nvarchar(2000)
> set @.formula = '(12.23+233.56)*12/100'
> set @.query = 'select @.value = (' + @.formula + ')'
> EXEC sp_executesql @.query,
> N'@.Value decimal(10,8) output',
> @.value output
> select @.value
> I would strongly suggest against it, since this is really not SQL's
> strongpoint. This is one of the rare cases where I would probably suggest
> you storing the expression and the answer in two columns (using the middle
> tier layer to calculate the value, or this method could be used in a a
> singleton insert.)
>
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
> "Arguments are to be avoided: they are always vulgar and often
> convincing."
> (Oscar Wilde)
> <ksrajalakshmi@.gmail.com> wrote in message
> news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
>|||First, this clearly isn't SQL Syntax, but that notwithstanding, you can
evaluate a function like this, (as long as it fits SQL Syntax of course)
declare @.value decimal (10,8)
declare @.formula varchar(200), @.query nvarchar(2000)
set @.formula = '(12.23+233.56)*12/100'
set @.query = 'select @.value = (' + @.formula + ')'
EXEC sp_executesql @.query,
N'@.Value decimal(10,8) output',
@.value output
select @.value
I would strongly suggest against it, since this is really not SQL's
strongpoint. This is one of the rare cases where I would probably suggest
you storing the expression and the answer in two columns (using the middle
tier layer to calculate the value, or this method could be used in a a
singleton insert.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
<ksrajalakshmi@.gmail.com> wrote in message
news:1139650655.809064.217710@.f14g2000cwb.googlegroups.com...
> If am having the value as
> string strvalue ="(12.23+233.56)*12/100";
> i've to find the value.so that am converting to double. but it throws
> error. tel me how to find value?
>

Find People Names in Long Text

Thank you for taking the time to read this, I need all the advise and help I can get on this ... so please post anything you think would work ... A little confused I am:

Have a database table called "people" with "person name" and "ID" field. My ASP.NET application mainly stores articles inarticle table. An article's Article text mentions various people's names in different combinations (e.g. John, Smith, John Smith, Smith John, etc)

Is there any way, I could compare the article text stored in article table with people table and get the people from people table along with their ID's who have been mentioned in that article? ... so in an article "i love john smith ... and i think Mr smith has always been helpful", I get John Smith back...

Not too sure being honest, what is the best way of implementing this, looking for the most efficient way, probably using XML? SQL Query or may be ASP.NET's code behind?

Thanks once again for taking the time.

Cheers,

Tyro

I think that the best way is stored procedure on SQL server side. The question is

Would you like to do it for single article or for all articles in your database at one run?

If for all names in all articles you will kill you server.

Thanks

|||

Thanks for your reply Jpazgier,

Killing the server is what I am worried about, plus the fact that the people's table is highly likely to grow in the future. I was thinking SQL too. doing some sort of lookup and compare, but think that would be very resource intensive.

I was thinking along the lines of probably being able to compare two XML files ... ?, writing the XML files overnight and then comparing with article text before saving the article in db ... just an idea ... what do you think? please feel free to correct me ..

Tyro

|||

I would create stored procedure which will grabperson namefrom your peopletable and will look for each components of this person name in article field, if it will find all components in article I would just report this person as existing inside article and report its ID. The question is you would like to report the person exists in article if one component of his name is in it or all elements should exists in article to report person as connected to it.

I would run it one time for all articles and next every time you add new person or new article, so maybe you need two or three procedures one for searching multiple article with one person , second to search single article with multiple persons and maybe 3rd for startup and maintenance to search all articles with all persons which are currently in database.

I would store results in table with 3 columns ID, articleID and personID so will be easy to insert results and manipulate it in the future.

Thanks

|||

Thanks for all your replies and helping me out with this Jpazgier, Appreciate it. Hopefully what you said should work well.

Tyro

Find Partial Text From Return Of Subquery

I don't even know if this is even possible but I figured it'd make my life a lot easier if it was. I have an organizational table with departments and subdepartments that has a chain of command listing in it. (I didn't design it, don't shoot me, I'm just having to fix it). What I need to do is find a department and all departments beneath it based on the passed in value of the department's ID. I'm right now keying off the chain of command since the chain of command is from the top down.

SELECT *
FROM Department
WHERE (COC LIKE
(SELECT COC
FROM Department
WHERE DeptID = '12345'))

Though of course this doesn't work. It will only return those identical to the returned value. If I were to just "do it" without a subquery it'd be
SELECT * FROM Department WHERE COC LIKE '1;14;16;232;12345;' and it would return everything from this department downward.

Is there a way to do a partial like on a subquery results?Not sure if I understood you correctly but try this:

Select *
from Department a
join Department b on a.COC = b.DeptID
where a.DeptID = '12345'

find order by date range or order id

hi basically what i have is 3 text boxes. one for start date, one for end date and one for order id, i also have this bit of SQL

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order]WHERE (([Date_ordered] >= @.Date_ordered OR @.Date_ordered IS NULL) AND ([Date_ordered] <= @.Date_ordered2 OR @.Date_ordered2 IS NULL OR (Order_ID=ISNULL(@.OrderID_ID,Order_ID) OR @.Order_ID IS NULL))">

but the problem is it does not seem to work! i am not an SQL guru but i cant figure it out, someone help me please!

Thanks

Jez

If you use SqlDataSource, you can add this to the SqlDataSource:CancelSelectOnNullParameter="false"

And change your SelectCommand to:

SelectCommand="SELECT [Order_ID], [Customer_Id], [Date_ordered], [status] FROM [tbl_order] WHERE [Date_ordered] >= ISNULL(@.Date_ordered, Date_ordered) AND ([Date_ordered] <= ISNULL(@.Date_ordered2, Date_ordered) AND Order_ID=ISNULL(@.OrderID_ID,Order_ID)">

|||

hi thanks for the code, right near the end you put OrderID_ID instead of Order_ID, and missed one ) at the end, but thank you very much for your help!

Jez

|||

Thank you for catching the typos. Glad that you got the idea.

|||

hey i just wondered if i could pick your brains again quickly, i wanted to have the same statement, but also see if it would also take input from a querystring, so could i just do the same thing, put all the WHERE clause in brackets, put a "OR" then just put where order_ID = ? and in the select perameters just have where ? = querystringorderID ?

Jez

Find number of records in text file.

Hi All,
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
[vbcol=seagreen]
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry
key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:[vbcol=seagreen]
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;H
DR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL serve
r.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com

Find number of records in text file.

Hi All,
Is there a way to find out how many records (rows) in the text file in
sql. ( It is a fix lengh record)
Example: I have a text file in my local machine
c:\test\t1.txt
c:\test\t2.txt
in t1.txt I have these rows:
AAAAAAAAAAAAAAAAAAAAAAAAAAA
BBBBBBBBBBBBBBBBBBBBBBBBBBB
CCCCCCCCCCCCCCCCCCCCCCCCCCC
and t2.txt have this rows:
XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
so t1.txt should give me 3 rows and t2.txt should give me 1 row.
Please let me know. Thanks in advance
Teed Leentuyen01@.yahoo.com wrote:
> Hi All,
> Is there a way to find out how many records (rows) in the text file in
> sql. ( It is a fix lengh record)
> Example: I have a text file in my local machine
> c:\test\t1.txt
> c:\test\t2.txt
> in t1.txt I have these rows:
> AAAAAAAAAAAAAAAAAAAAAAAAAAA
> BBBBBBBBBBBBBBBBBBBBBBBBBBB
> CCCCCCCCCCCCCCCCCCCCCCCCCCC
> and t2.txt have this rows:
> XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> Please let me know. Thanks in advance
> Teed Lee
>
One way would be to use OPENROWSET, documented in Books Online, and lots
of samples online.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Thank,
I did try it
SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
I got these errors:
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver] Disk or network error.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
OLE DB provider "MSDASQL" for linked server "(null)" returned message
"[Microsoft][ODBC Text Driver]General error Unable to open registry key
'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
0x482c06b4 Text'.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL"
for linked server "(null)".
I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
but all of them give me error.
Please help. Thanks
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Hi All,
> >
> > Is there a way to find out how many records (rows) in the text file in
> > sql. ( It is a fix lengh record)
> >
> > Example: I have a text file in my local machine
> >
> > c:\test\t1.txt
> > c:\test\t2.txt
> >
> > in t1.txt I have these rows:
> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >
> > and t2.txt have this rows:
> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >
> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >
> > Please let me know. Thanks in advance
> >
> > Teed Lee
> >
> One way would be to use OPENROWSET, documented in Books Online, and lots
> of samples online.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||It will work with the Jet driver. Something along the lines
of the following works with a text file located at
C:\Test.txt:
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=C:\;HDR=NO','select * from Test.txt')
-Sue
On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
>Thank,
>I did try it
>SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
>Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
>I got these errors:
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver] Disk or network error.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>OLE DB provider "MSDASQL" for linked server "(null)" returned message
>"[Microsoft][ODBC Text Driver]General error Unable to open registry key
>'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
>0x482c06b4 Text'.".
>Msg 7303, Level 16, State 1, Line 1
>Cannot initialize the data source object of OLE DB provider "MSDASQL"
>for linked server "(null)".
>
>I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
>but all of them give me error.
>Please help. Thanks
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > Hi All,
>> >
>> > Is there a way to find out how many records (rows) in the text file in
>> > sql. ( It is a fix lengh record)
>> >
>> > Example: I have a text file in my local machine
>> >
>> > c:\test\t1.txt
>> > c:\test\t2.txt
>> >
>> > in t1.txt I have these rows:
>> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
>> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
>> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
>> >
>> > and t2.txt have this rows:
>> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
>> >
>> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
>> >
>> > Please let me know. Thanks in advance
>> >
>> > Teed Lee
>> >
>> One way would be to use OPENROWSET, documented in Books Online, and lots
>> of samples online.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||Thank You,
I try this statement
select count(*)
from OpenRowset('Microsoft.Jet.OLEDB.4.0',
'Text;Database=\\testingserver\filedir;HDR=NO','select * from
Test.txt')
but when I try to run your statement I got this error:
OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
returned message "Unspecified error".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider
"Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
The different between your statement vs. mine is "my file is on the
network"
Do I have to setup ODBC connect on the server where the file locate or
somehting.
Would you please tell me what else I need to setup or what do I do
wrong here.
Thanks again,
Teed Lee
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||Hi,
I also try this:
EXEC sp_addlinkedserver filedir, 'Jet 4.0',
'Microsoft.Jet.OLEDB.4.0', '\\testserver\filedir', NULL, 'Text'
Sue Hoegemeier wrote:
> It will work with the Jet driver. Something along the lines
> of the following works with a text file located at
> C:\Test.txt:
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=C:\;HDR=NO','select * from Test.txt')
> -Sue
> On 14 Aug 2006 15:13:18 -0700, ntuyen01@.yahoo.com wrote:
> >Thank,
> >
> >I did try it
> >SELECT count(*) FROM OpenRowset('MSDASQL', 'Driver={Microsoft Text
> >Driver (*.txt; *.csv)};DefaultDir=c:\;','select * from testing.txt')
> >
> >I got these errors:
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver] Disk or network error.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >OLE DB provider "MSDASQL" for linked server "(null)" returned message
> >"[Microsoft][ODBC Text Driver]General error Unable to open registry key
> >'Temporary (volatile) Jet DSN for process 0xe0 Thread 0x8dc DBC
> >0x482c06b4 Text'.".
> >Msg 7303, Level 16, State 1, Line 1
> >Cannot initialize the data source object of OLE DB provider "MSDASQL"
> >for linked server "(null)".
> >
> >
> >I try different provider : Microsoft.Jet.OLEDB.4.0, SQLOLEDB ...
> >but all of them give me error.
> >
> >Please help. Thanks
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > Hi All,
> >> >
> >> > Is there a way to find out how many records (rows) in the text file in
> >> > sql. ( It is a fix lengh record)
> >> >
> >> > Example: I have a text file in my local machine
> >> >
> >> > c:\test\t1.txt
> >> > c:\test\t2.txt
> >> >
> >> > in t1.txt I have these rows:
> >> > AAAAAAAAAAAAAAAAAAAAAAAAAAA
> >> > BBBBBBBBBBBBBBBBBBBBBBBBBBB
> >> > CCCCCCCCCCCCCCCCCCCCCCCCCCC
> >> >
> >> > and t2.txt have this rows:
> >> > XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX
> >> >
> >> > so t1.txt should give me 3 rows and t2.txt should give me 1 row.
> >> >
> >> > Please let me know. Thanks in advance
> >> >
> >> > Teed Lee
> >> >
> >>
> >> One way would be to use OPENROWSET, documented in Books Online, and lots
> >> of samples online.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> Thank You,
> I try this statement
> select count(*)
> from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> Test.txt')
> but when I try to run your statement I got this error:
> OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> returned message "Unspecified error".
> Msg 7303, Level 16, State 1, Line 1
> Cannot initialize the data source object of OLE DB provider
> "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> The different between your statement vs. mine is "my file is on the
> network"
> Do I have to setup ODBC connect on the server where the file locate or
> somehting.
> Would you please tell me what else I need to setup or what do I do
> wrong here.
>
Does the SQL service account have permission to access that network
share? Does this work if you put the text file on a local drive?
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||I work fine on the local account. On the local account I have to setup
the "SQL Server Surface Area Configuration"
The problem I have is:
my sql server is on different machine Server "MachineA"
my files is on difrrent server "MachineB"
I am login as my account and I am able to get in (permission) to both
server.
On the "MachineA" do I have to set something to connect to "MachineB"
or
On the MachineB server do I have to set something
('Microsoft.Jet.OLEDB.4.0')?
I am not sure. Please help.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > Thank You,
> > I try this statement
> >
> > select count(*)
> > from OpenRowset('Microsoft.Jet.OLEDB.4.0',
> > 'Text;Database=\\testingserver\filedir;HDR=NO','select * from
> > Test.txt')
> >
> > but when I try to run your statement I got this error:
> >
> > OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "(null)"
> > returned message "Unspecified error".
> > Msg 7303, Level 16, State 1, Line 1
> > Cannot initialize the data source object of OLE DB provider
> > "Microsoft.Jet.OLEDB.4.0" for linked server "(null)".
> >
> > The different between your statement vs. mine is "my file is on the
> > network"
> > Do I have to setup ODBC connect on the server where the file locate or
> > somehting.
> > Would you please tell me what else I need to setup or what do I do
> > wrong here.
> >
> Does the SQL service account have permission to access that network
> share? Does this work if you put the text file on a local drive?
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||ntuyen01@.yahoo.com wrote:
> I work fine on the local account. On the local account I have to setup
> the "SQL Server Surface Area Configuration"
> The problem I have is:
> my sql server is on different machine Server "MachineA"
> my files is on difrrent server "MachineB"
> I am login as my account and I am able to get in (permission) to both
> server.
> On the "MachineA" do I have to set something to connect to "MachineB"
> or
> On the MachineB server do I have to set something
> ('Microsoft.Jet.OLEDB.4.0')?
> I am not sure. Please help.
> Teed
Huh? I repeat my question - "Does the SQL service account have
permission to access that network share?"
If I understand your response, it sounds like you have SQL running under
the "Local System" context. If so, you cannot access network resources
this way - SQL Server MUST run under a domain account, and that domain
account MUST have permission to access the desired network resources.
This has nothing to do with the login that you use to connect to SQL server.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi,
Yes,
I login (domain\username) is part of the group (domain\groupname) in
the SQL service account and also my login (domain\username) also have
permission to access that network share.
Teed
Tracy McKibben wrote:
> ntuyen01@.yahoo.com wrote:
> > I work fine on the local account. On the local account I have to setup
> > the "SQL Server Surface Area Configuration"
> >
> > The problem I have is:
> > my sql server is on different machine Server "MachineA"
> > my files is on difrrent server "MachineB"
> >
> > I am login as my account and I am able to get in (permission) to both
> > server.
> >
> > On the "MachineA" do I have to set something to connect to "MachineB"
> > or
> > On the MachineB server do I have to set something
> > ('Microsoft.Jet.OLEDB.4.0')?
> > I am not sure. Please help.
> >
> > Teed
> Huh? I repeat my question - "Does the SQL service account have
> permission to access that network share?"
> If I understand your response, it sounds like you have SQL running under
> the "Local System" context. If so, you cannot access network resources
> this way - SQL Server MUST run under a domain account, and that domain
> account MUST have permission to access the desired network resources.
> This has nothing to do with the login that you use to connect to SQL server.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com|||Okay...but that doesn't make sense. Looks like we're losing
things in translation here.
Try this. Open up Enterprise Manager. Select the server.
Right click and select properties. Then click on the
security tab. In the bottom of the screen on the security
tab, there is a section named Start up service account.
Which one of the two is selected - System Account or This
Account? It can only be one of the two.
-Sue
On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
>Hi,
>Yes,
>I login (domain\username) is part of the group (domain\groupname) in
>the SQL service account and also my login (domain\username) also have
>permission to access that network share.
>Teed
>
>Tracy McKibben wrote:
>> ntuyen01@.yahoo.com wrote:
>> > I work fine on the local account. On the local account I have to setup
>> > the "SQL Server Surface Area Configuration"
>> >
>> > The problem I have is:
>> > my sql server is on different machine Server "MachineA"
>> > my files is on difrrent server "MachineB"
>> >
>> > I am login as my account and I am able to get in (permission) to both
>> > server.
>> >
>> > On the "MachineA" do I have to set something to connect to "MachineB"
>> > or
>> > On the MachineB server do I have to set something
>> > ('Microsoft.Jet.OLEDB.4.0')?
>> > I am not sure. Please help.
>> >
>> > Teed
>> Huh? I repeat my question - "Does the SQL service account have
>> permission to access that network share?"
>> If I understand your response, it sounds like you have SQL running under
>> the "Local System" context. If so, you cannot access network resources
>> this way - SQL Server MUST run under a domain account, and that domain
>> account MUST have permission to access the desired network resources.
>> This has nothing to do with the login that you use to connect to SQL server.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, if I login I login as "sa", then I am able
to run the query find.
I get my NT login does not have enough persimision on the server
somewhere right?
Thanks for your time and help.
Teed
Sue Hoegemeier wrote:
> Okay...but that doesn't make sense. Looks like we're losing
> things in translation here.
> Try this. Open up Enterprise Manager. Select the server.
> Right click and select properties. Then click on the
> security tab. In the bottom of the screen on the security
> tab, there is a section named Start up service account.
> Which one of the two is selected - System Account or This
> Account? It can only be one of the two.
> -Sue
> On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >Hi,
> >Yes,
> >
> >I login (domain\username) is part of the group (domain\groupname) in
> >the SQL service account and also my login (domain\username) also have
> >permission to access that network share.
> >
> >Teed
> >
> >
> >Tracy McKibben wrote:
> >> ntuyen01@.yahoo.com wrote:
> >> > I work fine on the local account. On the local account I have to setup
> >> > the "SQL Server Surface Area Configuration"
> >> >
> >> > The problem I have is:
> >> > my sql server is on different machine Server "MachineA"
> >> > my files is on difrrent server "MachineB"
> >> >
> >> > I am login as my account and I am able to get in (permission) to both
> >> > server.
> >> >
> >> > On the "MachineA" do I have to set something to connect to "MachineB"
> >> > or
> >> > On the MachineB server do I have to set something
> >> > ('Microsoft.Jet.OLEDB.4.0')?
> >> > I am not sure. Please help.
> >> >
> >> > Teed
> >>
> >> Huh? I repeat my question - "Does the SQL service account have
> >> permission to access that network share?"
> >>
> >> If I understand your response, it sounds like you have SQL running under
> >> the "Local System" context. If so, you cannot access network resources
> >> this way - SQL Server MUST run under a domain account, and that domain
> >> account MUST have permission to access the desired network resources.
> >> This has nothing to do with the login that you use to connect to SQL server.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com|||I did not see the option you said in the sql 2005. Do you where it is
on 2005.
Ok, here is another thing, at my local machine I bring up the "SQL
Server Management Studio" then connect to that dabase as "sa", then I
am able to run the query fine. Also if I remote login that server as as
Administrator, then I am able to run the query fine, but at my local
machine I bring up the "SQL Server Management Studio" and login that
database as my "NT domain username" then I got error message above.
Thanks for your time and help. I am new to this.
ntuyen01@.yahoo.com wrote:
> Teed
>
> Sue Hoegemeier wrote:
> > Okay...but that doesn't make sense. Looks like we're losing
> > things in translation here.
> > Try this. Open up Enterprise Manager. Select the server.
> > Right click and select properties. Then click on the
> > security tab. In the bottom of the screen on the security
> > tab, there is a section named Start up service account.
> > Which one of the two is selected - System Account or This
> > Account? It can only be one of the two.
> >
> > -Sue
> >
> > On 15 Aug 2006 08:42:39 -0700, ntuyen01@.yahoo.com wrote:
> >
> > >Hi,
> > >Yes,
> > >
> > >I login (domain\username) is part of the group (domain\groupname) in
> > >the SQL service account and also my login (domain\username) also have
> > >permission to access that network share.
> > >
> > >Teed
> > >
> > >
> > >Tracy McKibben wrote:
> > >> ntuyen01@.yahoo.com wrote:
> > >> > I work fine on the local account. On the local account I have to setup
> > >> > the "SQL Server Surface Area Configuration"
> > >> >
> > >> > The problem I have is:
> > >> > my sql server is on different machine Server "MachineA"
> > >> > my files is on difrrent server "MachineB"
> > >> >
> > >> > I am login as my account and I am able to get in (permission) to both
> > >> > server.
> > >> >
> > >> > On the "MachineA" do I have to set something to connect to "MachineB"
> > >> > or
> > >> > On the MachineB server do I have to set something
> > >> > ('Microsoft.Jet.OLEDB.4.0')?
> > >> > I am not sure. Please help.
> > >> >
> > >> > Teed
> > >>
> > >> Huh? I repeat my question - "Does the SQL service account have
> > >> permission to access that network share?"
> > >>
> > >> If I understand your response, it sounds like you have SQL running under
> > >> the "Local System" context. If so, you cannot access network resources
> > >> this way - SQL Server MUST run under a domain account, and that domain
> > >> account MUST have permission to access the desired network resources.
> > >> This has nothing to do with the login that you use to connect to SQL server.
> > >>
> > >>
> > >> --
> > >> Tracy McKibben
> > >> MCDBA
> > >> http://www.realsqlguy.com