Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
Bill
You will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
Showing posts with label function. Show all posts
Showing posts with label function. Show all posts
Friday, March 30, 2012
Problem with Charindex function
Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
Problem with Charindex function
Hi,
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
I met a problem with charindex function. CharIndex can't find the char
after 8000 in a text field. You can try the following script:
create table #test (notes text)
insert #test values (replicate('1',8000)+'2'+'111')
select * from #test where charindex('2',notes)>0
drop table #test
I tested it on SQl 2000 EE SP3 and SP4.
If you change the above number 8000 to 7999 it will return the data. I am
not sure it's a bug or limitation but there is no any information about it
on BOL.
Thanks for any help!
BillYou will have to use TEXTPTR
>From BOL
If an ntext, text, and image data value is no longer than a Unicode,
character, or binary string (4,000 characters, 8,000 characters, 8,000
bytes respectively), the value can be referenced in SELECT, UPDATE, and
INSERT statements much the same way as the smaller data types. For
example, an ntext column with a short value can be referenced in a
SELECT statement select list the same way an nvarchar column is
referenced. Some restrictions that must be observed, such as not being
able to directly reference an ntext, text, or image column in a WHERE
clause. These columns can be included in a WHERE clause as parameters
of a function that returns another data type (such as ISNULL, SUBSTRING
or PATINDEX) or in an IS NULL, IS NOT NULL, or LIKE expression.
Handling Larger Data Values
When the ntext, text, and image data values get larger, however, they
must be handled on a block-by-block basis. Both Transact-SQL and the
database APIs contain functions that allow applications to work with
ntext, text, and image data block by block.
----
--
"I sense many useless updates in you... Useless updates lead to
fragmentation... Fragmentation leads to downtime...Downtime leads to
suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG
and DBCC DBREINDEX are the force...May the force be with you" --
http://sqlservercode.blogspot.com/
Problem With Case Function
Hi! Guys
i have problem with order by clause.
i have table some T1 with two columns say Itemname and Status
and data like this
ItemName Status
T1 G
T2 D
T3 US
T4 S
T5 G
T6 NULL
T7 NULL
Now i want to show with status like
G
D
S
US
SELECT
CASE Status
WHEN 'G' THEN 0
WHEN 'NULL' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END AS STATUSORDER
FROM T1
But it is not ececuting properly.it is not exact answer what i want.
plz , give solutionHi, shiva
You can use one of these queries:
SELECT
CASE
WHEN Status='G' THEN 0
WHEN Status IS NULL THEN 0
WHEN Status='D' THEN 1
WHEN Status='S' THEN 2
WHEN Status='US' THEN 3
END AS STATUSORDER
FROM T1
or:
SELECT
CASE Status
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3
ELSE 0
END AS STATUSORDER
FROM T1
Razvan|||What does it mean "not properly" ?
CREATE TABLE #Test
(
col VARCHAR(2) NULL
)
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES ('D')
INSERT INTO #Test VALUES ('US')
INSERT INTO #Test VALUES ('S')
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES (null)
INSERT INTO #Test VALUES (null)
SELECT col FROM #TesT WHERE col IS NOT NULL
GROUP BY col
ORDER BY
CASE col
WHEN 'G' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133867516.797293.72880@.f14g2000cwb.googlegroups.com...
> Hi! Guys
>
> i have problem with order by clause.
>
> i have table some T1 with two columns say Itemname and Status
>
> and data like this
>
> ItemName Status
> T1 G
> T2 D
> T3 US
> T4 S
> T5 G
> T6 NULL
> T7 NULL
> Now i want to show with status like
> G
> D
> S
> US
> SELECT
> CASE Status
> WHEN 'G' THEN 0
> WHEN 'NULL' THEN 0
> WHEN 'D' THEN 1
> WHEN 'S' THEN 2
> WHEN 'US' THEN 3 END AS STATUSORDER
> FROM T1
> But it is not ececuting properly.it is not exact answer what i want.
> plz , give solution
>|||thanks Razvan,
it's working fine.
thanks a lot.
i have problem with order by clause.
i have table some T1 with two columns say Itemname and Status
and data like this
ItemName Status
T1 G
T2 D
T3 US
T4 S
T5 G
T6 NULL
T7 NULL
Now i want to show with status like
G
D
S
US
SELECT
CASE Status
WHEN 'G' THEN 0
WHEN 'NULL' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END AS STATUSORDER
FROM T1
But it is not ececuting properly.it is not exact answer what i want.
plz , give solutionHi, shiva
You can use one of these queries:
SELECT
CASE
WHEN Status='G' THEN 0
WHEN Status IS NULL THEN 0
WHEN Status='D' THEN 1
WHEN Status='S' THEN 2
WHEN Status='US' THEN 3
END AS STATUSORDER
FROM T1
or:
SELECT
CASE Status
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3
ELSE 0
END AS STATUSORDER
FROM T1
Razvan|||What does it mean "not properly" ?
CREATE TABLE #Test
(
col VARCHAR(2) NULL
)
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES ('D')
INSERT INTO #Test VALUES ('US')
INSERT INTO #Test VALUES ('S')
INSERT INTO #Test VALUES ('G')
INSERT INTO #Test VALUES (null)
INSERT INTO #Test VALUES (null)
SELECT col FROM #TesT WHERE col IS NOT NULL
GROUP BY col
ORDER BY
CASE col
WHEN 'G' THEN 0
WHEN 'D' THEN 1
WHEN 'S' THEN 2
WHEN 'US' THEN 3 END
"shiva" <bany.shanker@.gmail.com> wrote in message
news:1133867516.797293.72880@.f14g2000cwb.googlegroups.com...
> Hi! Guys
>
> i have problem with order by clause.
>
> i have table some T1 with two columns say Itemname and Status
>
> and data like this
>
> ItemName Status
> T1 G
> T2 D
> T3 US
> T4 S
> T5 G
> T6 NULL
> T7 NULL
> Now i want to show with status like
> G
> D
> S
> US
> SELECT
> CASE Status
> WHEN 'G' THEN 0
> WHEN 'NULL' THEN 0
> WHEN 'D' THEN 1
> WHEN 'S' THEN 2
> WHEN 'US' THEN 3 END AS STATUSORDER
> FROM T1
> But it is not ececuting properly.it is not exact answer what i want.
> plz , give solution
>|||thanks Razvan,
it's working fine.
thanks a lot.
problem with calculating sum of large nos
hello all
i am calculating sum of very large nos using sum function of SQL. but the final result i get is incorrect. i am using SQL server 2000.
like value of a field(cost) is calculated by multiplying .0018 to other fields (no of units)
the sum of (no of units) is 140374
but when i calculate the sum of (cost) it comes to 273.1600 which is incorrect \.
it shud have been 140374 * .0018
It is really difficult to pinpoint any problem with the information you provide. How does the actual query look like? What datatypes are used in the table?|||just for fun. . . .
Given the table is called UnitTable try this query:
i am calculating sum of very large nos using sum function of SQL. but the final result i get is incorrect. i am using SQL server 2000.
like value of a field(cost) is calculated by multiplying .0018 to other fields (no of units)
the sum of (no of units) is 140374
but when i calculate the sum of (cost) it comes to 273.1600 which is incorrect \.
it shud have been 140374 * .0018
It is really difficult to pinpoint any problem with the information you provide. How does the actual query look like? What datatypes are used in the table?|||just for fun. . . .
Given the table is called UnitTable try this query:
select cast(.0018 as decimal(4,4)) * cast(sum([no of units]) as decimal(14,4)) from UnitsTable
does that give the right answer?
Wednesday, March 28, 2012
Problem with blob field in SQL Server
In SQLServer database i have stored a blob using updateblob function,
type of the table column is image.
When i use Selectblob statement to get the blob into a blob variable, i am getting only 32 KB of data. But with ASA i am getting the complete data..
How to get Complete data in SQL Server?
type of the table column is image.
When i use Selectblob statement to get the blob into a blob variable, i am getting only 32 KB of data. But with ASA i am getting the complete data..
How to get Complete data in SQL Server?
regds.,
rangaWhich data provider are you using?
Terrisql
Monday, March 26, 2012
Problem with analytic sql function (The OVER SQL construct or statement is not supported)
Hi All!
Could You comment the next situation:
I'm configuring my TableAdapter just like Scott Mitchell does in his tutorial
http://www.asp.net/learn/data-access/tutorial-70-vb.aspx
The only principal difference is that I need Insert/update and delete
methods to be generated (His aim is only SELECT).
I'm also using analytic function (ROW_NUMBER) and I'm also gettin
warning "The OVER SQL construct or statement is not supported." You
say then that it could be ignored. But, in this case statements to
modify data (insert/update and delete) aren't being generated, though
after warning SQL command is executed without errors.
So, the question is obvious - why does this warning occur and how must
I perform configuration of TableAdapter based on SQL query with
analytic function?
Manually create your own insert/update/delete statements. The wizard isn't there to do it all.
Friday, March 23, 2012
problem with a table
Hi All,
When I try it to push a suscriber I got an error:
The process could not bulk copy out of table '[dbo].[OrderDetails]'.
Function sequence error
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
In the snopshot said: Running -- generating conflict schema script for
article ['name']
What is the problem here?
Tks in advance
Johnny
Nobody have an idea of this?
This problem is when I create the publication of a merge replication.
At the snapshot initialization.
Tks
Johnny
"JFB" <jfb@.newSQL.com> wrote in message
news:usaLmKy1EHA.412@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> When I try it to push a suscriber I got an error:
> The process could not bulk copy out of table '[dbo].[OrderDetails]'.
> Function sequence error
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
> In the snopshot said: Running -- generating conflict schema script for
> article ['name']
> What is the problem here?
> Tks in advance
> Johnny
>
|||This could mean that the snapshot agent timed out or was
blocked. Try stopping and restarting the snapshot agent.
If you are dealing with a large table, you may need to
manually transfer it to the subscriber. Also, can you
enable logging (http://support.microsoft.com/?id=312292)
to get a detailed output, as it may be a disk issue.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Tks Paul for you reply and help.
1. Restart the snapshot agent give me the same error.
2. The table is no large (database size is 58mb)
3. I enable logging with number 2 and I got this error... what it is mean?
Generating schema script for article '[spTestCSV]'
*** [Article:'spTestCSV'] Time generating all schema scripts: 141 (ms) ***
SourceTypeId = 5
SourceName = SERVER-SQL
ErrorCode = 3724
ErrorText = Cannot drop the procedure
'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal' because it is being used
for replication.
Cannot drop the procedure 'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal'
because it is being used for replication.
Disconnecting from Publisher 'SERVER-SQL'
4. I have plenty space in my Hard drive.
Regards
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:207a01c4d859$0c9ed000$a501280a@.phx.gbl...
> This could mean that the snapshot agent timed out or was
> blocked. Try stopping and restarting the snapshot agent.
> If you are dealing with a large table, you may need to
> manually transfer it to the subscriber. Also, can you
> enable logging (http://support.microsoft.com/?id=312292)
> to get a detailed output, as it may be a disk issue.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Strange
- Is this error definitely produced when
running the snapshot agent (and not the merge agent)? If
it is the latter, do you have a publication of the same
table on the subscriber?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I only create the publication and only the snapshot is there. I will pull a
suscriber later.
Tks
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:07e701c4d883$d0b03220$a301280a@.phx.gbl...
> Strange - Is this error definitely produced when
> running the snapshot agent (and not the merge agent)? If
> it is the latter, do you have a publication of the same
> table on the subscriber?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql
When I try it to push a suscriber I got an error:
The process could not bulk copy out of table '[dbo].[OrderDetails]'.
Function sequence error
(Source: ODBC SQL Server Driver (ODBC); Error number: 0)
In the snopshot said: Running -- generating conflict schema script for
article ['name']
What is the problem here?
Tks in advance
Johnny
Nobody have an idea of this?
This problem is when I create the publication of a merge replication.
At the snapshot initialization.
Tks
Johnny
"JFB" <jfb@.newSQL.com> wrote in message
news:usaLmKy1EHA.412@.TK2MSFTNGP14.phx.gbl...
> Hi All,
> When I try it to push a suscriber I got an error:
> The process could not bulk copy out of table '[dbo].[OrderDetails]'.
> Function sequence error
> (Source: ODBC SQL Server Driver (ODBC); Error number: 0)
> In the snopshot said: Running -- generating conflict schema script for
> article ['name']
> What is the problem here?
> Tks in advance
> Johnny
>
|||This could mean that the snapshot agent timed out or was
blocked. Try stopping and restarting the snapshot agent.
If you are dealing with a large table, you may need to
manually transfer it to the subscriber. Also, can you
enable logging (http://support.microsoft.com/?id=312292)
to get a detailed output, as it may be a disk issue.
HTH,
Paul Ibison
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Tks Paul for you reply and help.
1. Restart the snapshot agent give me the same error.
2. The table is no large (database size is 58mb)
3. I enable logging with number 2 and I got this error... what it is mean?
Generating schema script for article '[spTestCSV]'
*** [Article:'spTestCSV'] Time generating all schema scripts: 141 (ms) ***
SourceTypeId = 5
SourceName = SERVER-SQL
ErrorCode = 3724
ErrorText = Cannot drop the procedure
'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal' because it is being used
for replication.
Cannot drop the procedure 'dbo.sp_sel_6475C47122084DB4A360C05743334338_pal'
because it is being used for replication.
Disconnecting from Publisher 'SERVER-SQL'
4. I have plenty space in my Hard drive.
Regards
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:207a01c4d859$0c9ed000$a501280a@.phx.gbl...
> This could mean that the snapshot agent timed out or was
> blocked. Try stopping and restarting the snapshot agent.
> If you are dealing with a large table, you may need to
> manually transfer it to the subscriber. Also, can you
> enable logging (http://support.microsoft.com/?id=312292)
> to get a detailed output, as it may be a disk issue.
> HTH,
> Paul Ibison
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||Strange
- Is this error definitely produced whenrunning the snapshot agent (and not the merge agent)? If
it is the latter, do you have a publication of the same
table on the subscriber?
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||I only create the publication and only the snapshot is there. I will pull a
suscriber later.
Tks
Johnny
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:07e701c4d883$d0b03220$a301280a@.phx.gbl...
> Strange
- Is this error definitely produced when> running the snapshot agent (and not the merge agent)? If
> it is the latter, do you have a publication of the same
> table on the subscriber?
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
sql
Wednesday, March 21, 2012
Problem with a date function
Hello All!
I have a table with a date column. I would like to be able to DELETE the rows based on the date column. The condition is 30 days from todays date. So anything older than 30 days from todays date, it will delete those rows.
Any suggesttion the best way to do this. I was thinking of a simple select statement, but can't figure it out.
TIA!!
Rudy
Hi there,Is your date column of data type DateTime? If so, try something like:
DELETE FROM [Table Name]
WHERE DATEADD(d, -30, GETDATE()) > [Date Column]
What happens is DATEADD(d, -30, GETDATE()) is used to obtain a date that is 30 days from todays date. Then, anything in the table where the entry in the Date Column (which I assume is of data type DateTime) is older than DATEADD(d, -30, GETDATE()), i.e. older than 30 days from today's date, gets deleted.
Hope that helps a bit, but sorry if it doesn't.
|||Thank you! Just what I needed!
Tuesday, March 20, 2012
PROBLEM WIHT OPENROWSET FUNCTION
HI FRIENDS THIS IS AMIT. THE PROBLEM IS I M TRYING TO EXPORT DATA FROM SQL SERVER TABLE TO EXCEL FILE USING FOLLOWING CODE SNIPPET,
insert into OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 7.0;Database=c:\testing.xls;',
'SELECT * FROM [Sheet1$]') select * from TABLE1
BUT IT IS SHOWING SOME ERROR WHICH IS AS FOLLOWS,
Invalid object name 'OPENROWSET'.
I FOUND OPENROWSET FUNCTION IN T-SQL REFERENCE BUT STILL THE ABOVE MESSAGE IS COMING PLS HELP IN THIS MATTER ASAP.
REGARDS,
AMIT.
What version of SQL Server are you using (show the output of SELECT @.@.VERSION)?Steve Kass
Drew University
http://www.stevekass.com|||
Make sure you have "Ad Hoc Remote Queries" enabled for your instance.
|||Kindly provide the version of the SQL Server you are using, it should ideally work without problem in SQL Server 2000 and above. You can however try the same using DTS (Data Transformation Services)|||Excel 7.0??Try Excel 5.0 or Excel 8.0
Monday, March 12, 2012
Problem while giving Quarter function
Sir,
When I am giving the command like Quarter(DateOfOrder) in new named dimension expression column , one error is showing ' Quarter is not recognised built in function ' . Please help me to find out the quarter of Date of Order column..
Thanks in advance..
Regards
Polachan
You need to use an expression such as
DATEPART(quarter,DateOfOrder)
To get the result you require
|||Dear Sir,
Thanks a lot
Sir Please can u give me a help for the following problem while deployment of the project
Error 4
When I am deploying the project I got the following error. Please help me sir
Errors in the OLAP storage engine: An error occurred while processing the 'Product Tran Header' partition of the 'Product Tran Header' measure group for the 'Test' cube from the productreport database. 0 0
I done the following steps
1. added new measure selecting new source table
2. selected one column dateoforder
3. Add new dimension as without using data source
4. selected server time dimension
5. Selected Year/Month/Quarter/date
6 Selected fiscal year
7.Selected dimension usage in cube desgn
8. selected time dimension and selected regular relation, Granulary attribute as Date
9. Measure group table 'Product tran header' new measure group
10 selected measure group column as dateoforder.
after that while deploying the above mentioned error will come
Please help
|||As far as I know, I answered your question. Why have you answered that with a completely different question ?
|||Sir,
I wan to know how to give relationship Servertime Dimension with the column in the table DateOfOreder. When I am giving this with the mentioned step I got the error. Please help me sir...
Wednesday, March 7, 2012
Problem using the textAlign expression
I am trying to use an iif function in the textAlign property.
=iif(A=B, left, center)
I am getting an error on the left...it seems to be expecting arguments for the left function.
How do I get around this?
Forscuis
Try
=IIF(A=B,"Left","Center")
This is should resolve your issue.
Ham
|||thanks that worked.|||not a problem,
Can you mark it as answers so that others will searching for answers will find our solution?
Ham
Saturday, February 25, 2012
Problem using Monthname function
I am trying to have the below formula populate a portion of a textbox
The right(Parameters!ToTimeDimensionCloseYYYYMM.label,2) is because I am dealing with "YYYYMM" and I only want the "MM" portion.
=iif(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)=01,Monthname(12),monthname(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)))
When I use this formula it will work for December, however for the rest of the months if a user chose 200603, it will read "March" when I really want it to read "February".
=iif(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)=01,Monthname(12),monthname(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)-1)))
However, when I try and write the formula I really want (above), it will work for February through December (giving me the prior month), but if a user chose 200601 (January) as their last Month, it reads "#ERROR", instead of "December"
The error it give me is = [rsRuntimeErrorInExpression] The Value expression for the textbox ‘textbox1’ contains an error: Argument 'Month' is not a valid value.
I have tried several different "versions" of this, including just telling it to put "December", putting "last" in front of the monthname instead of "-1", trying to use "Previous" (which I don't understand but doesn't seem to work either), to try to get it to recognize that I want it to give me the previous month when the user selects the current month, with no success. It seems as if the "-1" at the end causes it to break, because if I take the "-1" out, it works fine except I don't get the previous month for February through December.
Monthname needs an Integer parameter, so try
=iif(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2)="01",Monthname(12),monthname(Cint(right(Parameters!ToTimeDimensionCloseYYYYMM.label,2))-1))
Regards
Ayzan
|||Thanks Ayzan, but it still gives me the same thing:
If the parameter selected = 200602, then it gives me January;
If the parameter selected = 200607, then it gives me June,
But as soon as the parameter selected = 200601, it gives me "#ERROR" instead of December, with the same message that Argument 'Month' is not a valid expression.
It's almost as if it doesn't even recognize the first part of the expression with the -1 in it...b/c if I take out the -1 then it give me December for 200601, but then it gives me "February" for 200602 and "July" for 200607 when I really want "January" and "June".
Thanks again for your help...any additional help would be greatly appreciated.
|||
Create an instance of the DateTime struct and use the .NET functions it has to perform date arithmetic
=MonthName(
new DateTime(
CInt( Left( Parameters!ToTimeDimensionCloseYYYYMM.Label, 4 ) ) //Year
, CInt( Right( Parameters!ToTimeDimensionCloseYYYYMM.Label, 2 ) ) //Month
, 1 //Day
).AddMonths( -1 ).Month //Subtract a month and then access the Month Property
)
If you find it runs slowly then you could use an Iif statement as follows:
=MonthName(
Iif(
CInt( Right( Parameters!ToTimeDimensionCloseYYYYMM.Label, 2 ) ) = 1
, 12
, CInt( Right( Parameters!ToTimeDimensionCloseYYYYMM.Label, 2 ) ) - 1
)
)
Problem Using INITCAP function
Hey all,
I'm trying to use the INITCAP function to make the first letter of an
address appear in capital and the rest in lower case, but it seems to
appear all in Caps.
Can anyone please help. I've attached a snipplet of the code.
TIA
Dhwiren
DECLARE @.counter smallint
DECLARE @.counterLimit smallint
DECLARE @.RandomNumber float
DECLARE @.RandomNumberInt tinyint
DECLARE @.CurrentCharacter varchar(1)
DECLARE @.ValidCharacters varchar(255)
DECLARE @.ValidCharactersLength int
DECLARE @.DEBUG int
SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
SET @.ValidCharactersLength = len(@.ValidCharacters)
SET @.CurrentCharacter = ''
SET @.RandomNumber = 0
SET @.RandomNumberInt = 0
SET @.DEBUG=1
-- @.AddressLine1
SET @.counter=0
SET @.counterLimit = len(@.AddressLine1)
IF @.counterLimit>0
BEGIN
SET @.AddressLine1=''
WHILE @.counter < @.counterLimit
BEGIN
-- create a random sting
SET @.RandomNumber = Rand()
SET @.RandomNumberInt = Convert(tinyint,
((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
@.RandomNumberInt, 1)
IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
END
IF @.DEBUG=1 PRINT @.AddressLine1
ENDi havent ran the code, but your logic is slightly flawed. change the end par
t
to something like this. You can also use UPPER in place of INITCAP in your
code because you are pulling 1 character at a time from SUBSTRING.
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Hey all,
> I'm trying to use the INITCAP function to make the first letter of an
> address appear in capital and the rest in lower case, but it seems to
> appear all in Caps.
> Can anyone please help. I've attached a snipplet of the code.
> TIA
> Dhwiren
>
> DECLARE @.counter smallint
> DECLARE @.counterLimit smallint
> DECLARE @.RandomNumber float
> DECLARE @.RandomNumberInt tinyint
> DECLARE @.CurrentCharacter varchar(1)
> DECLARE @.ValidCharacters varchar(255)
> DECLARE @.ValidCharactersLength int
> DECLARE @.DEBUG int
> SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> SET @.ValidCharactersLength = len(@.ValidCharacters)
> SET @.CurrentCharacter = ''
> SET @.RandomNumber = 0
> SET @.RandomNumberInt = 0
> SET @.DEBUG=1
>
> -- @.AddressLine1
> SET @.counter=0
> SET @.counterLimit = len(@.AddressLine1)
> IF @.counterLimit>0
> BEGIN
> SET @.AddressLine1=''
> WHILE @.counter < @.counterLimit
> BEGIN
> -- create a random sting
> SET @.RandomNumber = Rand()
> SET @.RandomNumberInt = Convert(tinyint,
> ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> @.RandomNumberInt, 1)
> IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> END
> IF @.DEBUG=1 PRINT @.AddressLine1
> END
>|||Thank you for the quick reply Andy,
I am pulling 1 character out at a time but in the database they are all
uppercase,
which is why I was wondering using INITCAP
Dhwiren
Andy Price wrote:[vbcol=seagreen]
> i havent ran the code, but your logic is slightly flawed. change the end p
art
> to something like this. You can also use UPPER in place of INITCAP in your
> code because you are pulling 1 character at a time from SUBSTRING.
>
> IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
> --
> Andy Price,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Dwizz" wrote:
>|||I see, I didnt realize they were all in uppercase. You could change it to us
e
that, or you can just change your code to this (ie, added 1 line of code
prior to the block of code in the last post). INITCAP doesnt exist in SQL
Server. I am assuming you are an ORACLE or mysql guy also?
set @.CurrentCharacter =
LOWER(@.CurrentCharacter)
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Thank you for the quick reply Andy,
> I am pulling 1 character out at a time but in the database they are all
> uppercase,
> which is why I was wondering using INITCAP
> Dhwiren
> Andy Price wrote:
>|||Thanks Andy,
We got it to work, the extra line did it, I am indeed an Oracle man
Thanks once again Andy
Dhwiren|||Ahhh.. Andy, I just noticed that its the second character that becomes
Capitalised and not the first, but as a bonus all the other characters
are in lower case. So how would I make the first character Capitalised
and not the second character capitalised
I'm trying to use the INITCAP function to make the first letter of an
address appear in capital and the rest in lower case, but it seems to
appear all in Caps.
Can anyone please help. I've attached a snipplet of the code.
TIA
Dhwiren
DECLARE @.counter smallint
DECLARE @.counterLimit smallint
DECLARE @.RandomNumber float
DECLARE @.RandomNumberInt tinyint
DECLARE @.CurrentCharacter varchar(1)
DECLARE @.ValidCharacters varchar(255)
DECLARE @.ValidCharactersLength int
DECLARE @.DEBUG int
SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
SET @.ValidCharactersLength = len(@.ValidCharacters)
SET @.CurrentCharacter = ''
SET @.RandomNumber = 0
SET @.RandomNumberInt = 0
SET @.DEBUG=1
-- @.AddressLine1
SET @.counter=0
SET @.counterLimit = len(@.AddressLine1)
IF @.counterLimit>0
BEGIN
SET @.AddressLine1=''
WHILE @.counter < @.counterLimit
BEGIN
-- create a random sting
SET @.RandomNumber = Rand()
SET @.RandomNumberInt = Convert(tinyint,
((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
@.RandomNumberInt, 1)
IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
END
IF @.DEBUG=1 PRINT @.AddressLine1
ENDi havent ran the code, but your logic is slightly flawed. change the end par
t
to something like this. You can also use UPPER in place of INITCAP in your
code because you are pulling 1 character at a time from SUBSTRING.
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Hey all,
> I'm trying to use the INITCAP function to make the first letter of an
> address appear in capital and the rest in lower case, but it seems to
> appear all in Caps.
> Can anyone please help. I've attached a snipplet of the code.
> TIA
> Dhwiren
>
> DECLARE @.counter smallint
> DECLARE @.counterLimit smallint
> DECLARE @.RandomNumber float
> DECLARE @.RandomNumberInt tinyint
> DECLARE @.CurrentCharacter varchar(1)
> DECLARE @.ValidCharacters varchar(255)
> DECLARE @.ValidCharactersLength int
> DECLARE @.DEBUG int
> SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> SET @.ValidCharactersLength = len(@.ValidCharacters)
> SET @.CurrentCharacter = ''
> SET @.RandomNumber = 0
> SET @.RandomNumberInt = 0
> SET @.DEBUG=1
>
> -- @.AddressLine1
> SET @.counter=0
> SET @.counterLimit = len(@.AddressLine1)
> IF @.counterLimit>0
> BEGIN
> SET @.AddressLine1=''
> WHILE @.counter < @.counterLimit
> BEGIN
> -- create a random sting
> SET @.RandomNumber = Rand()
> SET @.RandomNumberInt = Convert(tinyint,
> ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> @.RandomNumberInt, 1)
> IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> END
> IF @.DEBUG=1 PRINT @.AddressLine1
> END
>|||Thank you for the quick reply Andy,
I am pulling 1 character out at a time but in the database they are all
uppercase,
which is why I was wondering using INITCAP
Dhwiren
Andy Price wrote:[vbcol=seagreen]
> i havent ran the code, but your logic is slightly flawed. change the end p
art
> to something like this. You can also use UPPER in place of INITCAP in your
> code because you are pulling 1 character at a time from SUBSTRING.
>
> IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
> --
> Andy Price,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Dwizz" wrote:
>|||I see, I didnt realize they were all in uppercase. You could change it to us
e
that, or you can just change your code to this (ie, added 1 line of code
prior to the block of code in the last post). INITCAP doesnt exist in SQL
Server. I am assuming you are an ORACLE or mysql guy also?
set @.CurrentCharacter =
LOWER(@.CurrentCharacter)
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Thank you for the quick reply Andy,
> I am pulling 1 character out at a time but in the database they are all
> uppercase,
> which is why I was wondering using INITCAP
> Dhwiren
> Andy Price wrote:
>|||Thanks Andy,
We got it to work, the extra line did it, I am indeed an Oracle man
Thanks once again Andy
Dhwiren|||Ahhh.. Andy, I just noticed that its the second character that becomes
Capitalised and not the first, but as a bonus all the other characters
are in lower case. So how would I make the first character Capitalised
and not the second character capitalised
Problem Using INITCAP function
Hey all,
I'm trying to use the INITCAP function to make the first letter of an
address appear in capital and the rest in lower case, but it seems to
appear all in Caps.
Can anyone please help. I've attached a snipplet of the code.
TIA
Dhwiren
DECLARE @.counter smallint
DECLARE @.counterLimit smallint
DECLARE @.RandomNumber float
DECLARE @.RandomNumberInt tinyint
DECLARE @.CurrentCharacter varchar(1)
DECLARE @.ValidCharacters varchar(255)
DECLARE @.ValidCharactersLength int
DECLARE @.DEBUG int
SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
SET @.ValidCharactersLength = len(@.ValidCharacters)
SET @.CurrentCharacter = ''
SET @.RandomNumber = 0
SET @.RandomNumberInt = 0
SET @.DEBUG=1
-- @.AddressLine1
SET @.counter=0
SET @.counterLimit = len(@.AddressLine1)
IF @.counterLimit>0
BEGIN
SET @.AddressLine1=''
WHILE @.counter < @.counterLimit
BEGIN
-- create a random sting
SET @.RandomNumber = Rand()
SET @.RandomNumberInt = Convert(tinyint,
((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
@.RandomNumberInt, 1)
IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
END
IF @.DEBUG=1 PRINT @.AddressLine1
END
i havent ran the code, but your logic is slightly flawed. change the end part
to something like this. You can also use UPPER in place of INITCAP in your
code because you are pulling 1 character at a time from SUBSTRING.
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Hey all,
> I'm trying to use the INITCAP function to make the first letter of an
> address appear in capital and the rest in lower case, but it seems to
> appear all in Caps.
> Can anyone please help. I've attached a snipplet of the code.
> TIA
> Dhwiren
>
> DECLARE @.counter smallint
> DECLARE @.counterLimit smallint
> DECLARE @.RandomNumber float
> DECLARE @.RandomNumberInt tinyint
> DECLARE @.CurrentCharacter varchar(1)
> DECLARE @.ValidCharacters varchar(255)
> DECLARE @.ValidCharactersLength int
> DECLARE @.DEBUG int
> SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> SET @.ValidCharactersLength = len(@.ValidCharacters)
> SET @.CurrentCharacter = ''
> SET @.RandomNumber = 0
> SET @.RandomNumberInt = 0
> SET @.DEBUG=1
>
> -- @.AddressLine1
> SET @.counter=0
> SET @.counterLimit = len(@.AddressLine1)
> IF @.counterLimit>0
> BEGIN
> SET @.AddressLine1=''
> WHILE @.counter < @.counterLimit
> BEGIN
> -- create a random sting
> SET @.RandomNumber = Rand()
> SET @.RandomNumberInt = Convert(tinyint,
> ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> @.RandomNumberInt, 1)
> IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> END
> IF @.DEBUG=1 PRINT @.AddressLine1
> END
>
|||Thank you for the quick reply Andy,
I am pulling 1 character out at a time but in the database they are all
uppercase,
which is why I was wondering using INITCAP
Dhwiren
Andy Price wrote:[vbcol=seagreen]
> i havent ran the code, but your logic is slightly flawed. change the end part
> to something like this. You can also use UPPER in place of INITCAP in your
> code because you are pulling 1 character at a time from SUBSTRING.
>
> IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
> --
> Andy Price,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Dwizz" wrote:
|||I see, I didnt realize they were all in uppercase. You could change it to use
that, or you can just change your code to this (ie, added 1 line of code
prior to the block of code in the last post). INITCAP doesnt exist in SQL
Server. I am assuming you are an ORACLE or MySQL guy also?
set @.CurrentCharacter =
LOWER(@.CurrentCharacter)
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Thank you for the quick reply Andy,
> I am pulling 1 character out at a time but in the database they are all
> uppercase,
> which is why I was wondering using INITCAP
> Dhwiren
> Andy Price wrote:
>
|||Thanks Andy,
We got it to work, the extra line did it, I am indeed an Oracle man
Thanks once again Andy
Dhwiren
|||Ahhh.. Andy, I just noticed that its the second character that becomes
Capitalised and not the first, but as a bonus all the other characters
are in lower case. So how would I make the first character Capitalised
and not the second character capitalised
I'm trying to use the INITCAP function to make the first letter of an
address appear in capital and the rest in lower case, but it seems to
appear all in Caps.
Can anyone please help. I've attached a snipplet of the code.
TIA
Dhwiren
DECLARE @.counter smallint
DECLARE @.counterLimit smallint
DECLARE @.RandomNumber float
DECLARE @.RandomNumberInt tinyint
DECLARE @.CurrentCharacter varchar(1)
DECLARE @.ValidCharacters varchar(255)
DECLARE @.ValidCharactersLength int
DECLARE @.DEBUG int
SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
SET @.ValidCharactersLength = len(@.ValidCharacters)
SET @.CurrentCharacter = ''
SET @.RandomNumber = 0
SET @.RandomNumberInt = 0
SET @.DEBUG=1
-- @.AddressLine1
SET @.counter=0
SET @.counterLimit = len(@.AddressLine1)
IF @.counterLimit>0
BEGIN
SET @.AddressLine1=''
WHILE @.counter < @.counterLimit
BEGIN
-- create a random sting
SET @.RandomNumber = Rand()
SET @.RandomNumberInt = Convert(tinyint,
((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
@.RandomNumberInt, 1)
IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
END
IF @.DEBUG=1 PRINT @.AddressLine1
END
i havent ran the code, but your logic is slightly flawed. change the end part
to something like this. You can also use UPPER in place of INITCAP in your
code because you are pulling 1 character at a time from SUBSTRING.
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Hey all,
> I'm trying to use the INITCAP function to make the first letter of an
> address appear in capital and the rest in lower case, but it seems to
> appear all in Caps.
> Can anyone please help. I've attached a snipplet of the code.
> TIA
> Dhwiren
>
> DECLARE @.counter smallint
> DECLARE @.counterLimit smallint
> DECLARE @.RandomNumber float
> DECLARE @.RandomNumberInt tinyint
> DECLARE @.CurrentCharacter varchar(1)
> DECLARE @.ValidCharacters varchar(255)
> DECLARE @.ValidCharactersLength int
> DECLARE @.DEBUG int
> SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> SET @.ValidCharactersLength = len(@.ValidCharacters)
> SET @.CurrentCharacter = ''
> SET @.RandomNumber = 0
> SET @.RandomNumberInt = 0
> SET @.DEBUG=1
>
> -- @.AddressLine1
> SET @.counter=0
> SET @.counterLimit = len(@.AddressLine1)
> IF @.counterLimit>0
> BEGIN
> SET @.AddressLine1=''
> WHILE @.counter < @.counterLimit
> BEGIN
> -- create a random sting
> SET @.RandomNumber = Rand()
> SET @.RandomNumberInt = Convert(tinyint,
> ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> @.RandomNumberInt, 1)
> IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> END
> IF @.DEBUG=1 PRINT @.AddressLine1
> END
>
|||Thank you for the quick reply Andy,
I am pulling 1 character out at a time but in the database they are all
uppercase,
which is why I was wondering using INITCAP
Dhwiren
Andy Price wrote:[vbcol=seagreen]
> i havent ran the code, but your logic is slightly flawed. change the end part
> to something like this. You can also use UPPER in place of INITCAP in your
> code because you are pulling 1 character at a time from SUBSTRING.
>
> IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
> --
> Andy Price,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Dwizz" wrote:
|||I see, I didnt realize they were all in uppercase. You could change it to use
that, or you can just change your code to this (ie, added 1 line of code
prior to the block of code in the last post). INITCAP doesnt exist in SQL
Server. I am assuming you are an ORACLE or MySQL guy also?
set @.CurrentCharacter =
LOWER(@.CurrentCharacter)
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Thank you for the quick reply Andy,
> I am pulling 1 character out at a time but in the database they are all
> uppercase,
> which is why I was wondering using INITCAP
> Dhwiren
> Andy Price wrote:
>
|||Thanks Andy,
We got it to work, the extra line did it, I am indeed an Oracle man
Thanks once again Andy
Dhwiren
|||Ahhh.. Andy, I just noticed that its the second character that becomes
Capitalised and not the first, but as a bonus all the other characters
are in lower case. So how would I make the first character Capitalised
and not the second character capitalised
Problem Using INITCAP function
Hey all,
I'm trying to use the INITCAP function to make the first letter of an
address appear in capital and the rest in lower case, but it seems to
appear all in Caps.
Can anyone please help. I've attached a snipplet of the code.
TIA
Dhwiren
DECLARE @.counter smallint
DECLARE @.counterLimit smallint
DECLARE @.RandomNumber float
DECLARE @.RandomNumberInt tinyint
DECLARE @.CurrentCharacter varchar(1)
DECLARE @.ValidCharacters varchar(255)
DECLARE @.ValidCharactersLength int
DECLARE @.DEBUG int
SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
SET @.ValidCharactersLength = len(@.ValidCharacters)
SET @.CurrentCharacter = ''
SET @.RandomNumber = 0
SET @.RandomNumberInt = 0
SET @.DEBUG=1
-- @.AddressLine1
SET @.counter=0
SET @.counterLimit = len(@.AddressLine1)
IF @.counterLimit>0
BEGIN
SET @.AddressLine1=''
WHILE @.counter < @.counterLimit
BEGIN
-- create a random sting
SET @.RandomNumber = Rand()
SET @.RandomNumberInt = Convert(tinyint,
((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
@.RandomNumberInt, 1)
IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
END
IF @.DEBUG=1 PRINT @.AddressLine1
ENDi havent ran the code, but your logic is slightly flawed. change the end part
to something like this. You can also use UPPER in place of INITCAP in your
code because you are pulling 1 character at a time from SUBSTRING.
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
--
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Hey all,
> I'm trying to use the INITCAP function to make the first letter of an
> address appear in capital and the rest in lower case, but it seems to
> appear all in Caps.
> Can anyone please help. I've attached a snipplet of the code.
> TIA
> Dhwiren
>
> DECLARE @.counter smallint
> DECLARE @.counterLimit smallint
> DECLARE @.RandomNumber float
> DECLARE @.RandomNumberInt tinyint
> DECLARE @.CurrentCharacter varchar(1)
> DECLARE @.ValidCharacters varchar(255)
> DECLARE @.ValidCharactersLength int
> DECLARE @.DEBUG int
> SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> SET @.ValidCharactersLength = len(@.ValidCharacters)
> SET @.CurrentCharacter = ''
> SET @.RandomNumber = 0
> SET @.RandomNumberInt = 0
> SET @.DEBUG=1
>
> -- @.AddressLine1
> SET @.counter=0
> SET @.counterLimit = len(@.AddressLine1)
> IF @.counterLimit>0
> BEGIN
> SET @.AddressLine1=''
> WHILE @.counter < @.counterLimit
> BEGIN
> -- create a random sting
> SET @.RandomNumber = Rand()
> SET @.RandomNumberInt = Convert(tinyint,
> ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> @.RandomNumberInt, 1)
> IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> END
> IF @.DEBUG=1 PRINT @.AddressLine1
> END
>|||Thank you for the quick reply Andy,
I am pulling 1 character out at a time but in the database they are all
uppercase,
which is why I was wondering using INITCAP
Dhwiren
Andy Price wrote:
> i havent ran the code, but your logic is slightly flawed. change the end part
> to something like this. You can also use UPPER in place of INITCAP in your
> code because you are pulling 1 character at a time from SUBSTRING.
>
> IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
> --
> Andy Price,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Dwizz" wrote:
> > Hey all,
> >
> > I'm trying to use the INITCAP function to make the first letter of an
> > address appear in capital and the rest in lower case, but it seems to
> > appear all in Caps.
> >
> > Can anyone please help. I've attached a snipplet of the code.
> >
> > TIA
> >
> > Dhwiren
> >
> >
> > DECLARE @.counter smallint
> > DECLARE @.counterLimit smallint
> > DECLARE @.RandomNumber float
> > DECLARE @.RandomNumberInt tinyint
> > DECLARE @.CurrentCharacter varchar(1)
> > DECLARE @.ValidCharacters varchar(255)
> > DECLARE @.ValidCharactersLength int
> > DECLARE @.DEBUG int
> >
> > SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> > SET @.ValidCharactersLength = len(@.ValidCharacters)
> > SET @.CurrentCharacter = ''
> > SET @.RandomNumber = 0
> > SET @.RandomNumberInt = 0
> > SET @.DEBUG=1
> >
> >
> > -- @.AddressLine1
> > SET @.counter=0
> > SET @.counterLimit = len(@.AddressLine1)
> > IF @.counterLimit>0
> > BEGIN
> > SET @.AddressLine1=''
> > WHILE @.counter < @.counterLimit
> > BEGIN
> > -- create a random sting
> > SET @.RandomNumber = Rand()
> > SET @.RandomNumberInt = Convert(tinyint,
> > ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> > SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> > @.RandomNumberInt, 1)
> > IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> > SET @.counter = @.counter + 1
> > SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> > END
> > IF @.DEBUG=1 PRINT @.AddressLine1
> > END
> >
> >|||I see, I didnt realize they were all in uppercase. You could change it to use
that, or you can just change your code to this (ie, added 1 line of code
prior to the block of code in the last post). INITCAP doesnt exist in SQL
Server. I am assuming you are an ORACLE or MySQL guy also?
set @.CurrentCharacter =LOWER(@.CurrentCharacter)
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
--
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Thank you for the quick reply Andy,
> I am pulling 1 character out at a time but in the database they are all
> uppercase,
> which is why I was wondering using INITCAP
> Dhwiren
> Andy Price wrote:
> > i havent ran the code, but your logic is slightly flawed. change the end part
> > to something like this. You can also use UPPER in place of INITCAP in your
> > code because you are pulling 1 character at a time from SUBSTRING.
> >
> >
> > IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
> > SET @.counter = @.counter + 1
> > SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
> >
> > --
> > Andy Price,
> > Sr. Database Administrator,
> > MCDBA 2003
> >
> >
> > "Dwizz" wrote:
> >
> > > Hey all,
> > >
> > > I'm trying to use the INITCAP function to make the first letter of an
> > > address appear in capital and the rest in lower case, but it seems to
> > > appear all in Caps.
> > >
> > > Can anyone please help. I've attached a snipplet of the code.
> > >
> > > TIA
> > >
> > > Dhwiren
> > >
> > >
> > > DECLARE @.counter smallint
> > > DECLARE @.counterLimit smallint
> > > DECLARE @.RandomNumber float
> > > DECLARE @.RandomNumberInt tinyint
> > > DECLARE @.CurrentCharacter varchar(1)
> > > DECLARE @.ValidCharacters varchar(255)
> > > DECLARE @.ValidCharactersLength int
> > > DECLARE @.DEBUG int
> > >
> > > SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> > > SET @.ValidCharactersLength = len(@.ValidCharacters)
> > > SET @.CurrentCharacter = ''
> > > SET @.RandomNumber = 0
> > > SET @.RandomNumberInt = 0
> > > SET @.DEBUG=1
> > >
> > >
> > > -- @.AddressLine1
> > > SET @.counter=0
> > > SET @.counterLimit = len(@.AddressLine1)
> > > IF @.counterLimit>0
> > > BEGIN
> > > SET @.AddressLine1=''
> > > WHILE @.counter < @.counterLimit
> > > BEGIN
> > > -- create a random sting
> > > SET @.RandomNumber = Rand()
> > > SET @.RandomNumberInt = Convert(tinyint,
> > > ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> > > SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> > > @.RandomNumberInt, 1)
> > > IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> > > SET @.counter = @.counter + 1
> > > SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> > > END
> > > IF @.DEBUG=1 PRINT @.AddressLine1
> > > END
> > >
> > >
>|||Thanks Andy,
We got it to work, the extra line did it, I am indeed an Oracle man
Thanks once again Andy
Dhwiren|||Ahhh.. Andy, I just noticed that its the second character that becomes
Capitalised and not the first, but as a bonus all the other characters
are in lower case. So how would I make the first character Capitalised
and not the second character capitalised
I'm trying to use the INITCAP function to make the first letter of an
address appear in capital and the rest in lower case, but it seems to
appear all in Caps.
Can anyone please help. I've attached a snipplet of the code.
TIA
Dhwiren
DECLARE @.counter smallint
DECLARE @.counterLimit smallint
DECLARE @.RandomNumber float
DECLARE @.RandomNumberInt tinyint
DECLARE @.CurrentCharacter varchar(1)
DECLARE @.ValidCharacters varchar(255)
DECLARE @.ValidCharactersLength int
DECLARE @.DEBUG int
SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
SET @.ValidCharactersLength = len(@.ValidCharacters)
SET @.CurrentCharacter = ''
SET @.RandomNumber = 0
SET @.RandomNumberInt = 0
SET @.DEBUG=1
-- @.AddressLine1
SET @.counter=0
SET @.counterLimit = len(@.AddressLine1)
IF @.counterLimit>0
BEGIN
SET @.AddressLine1=''
WHILE @.counter < @.counterLimit
BEGIN
-- create a random sting
SET @.RandomNumber = Rand()
SET @.RandomNumberInt = Convert(tinyint,
((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
@.RandomNumberInt, 1)
IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
END
IF @.DEBUG=1 PRINT @.AddressLine1
ENDi havent ran the code, but your logic is slightly flawed. change the end part
to something like this. You can also use UPPER in place of INITCAP in your
code because you are pulling 1 character at a time from SUBSTRING.
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
--
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Hey all,
> I'm trying to use the INITCAP function to make the first letter of an
> address appear in capital and the rest in lower case, but it seems to
> appear all in Caps.
> Can anyone please help. I've attached a snipplet of the code.
> TIA
> Dhwiren
>
> DECLARE @.counter smallint
> DECLARE @.counterLimit smallint
> DECLARE @.RandomNumber float
> DECLARE @.RandomNumberInt tinyint
> DECLARE @.CurrentCharacter varchar(1)
> DECLARE @.ValidCharacters varchar(255)
> DECLARE @.ValidCharactersLength int
> DECLARE @.DEBUG int
> SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> SET @.ValidCharactersLength = len(@.ValidCharacters)
> SET @.CurrentCharacter = ''
> SET @.RandomNumber = 0
> SET @.RandomNumberInt = 0
> SET @.DEBUG=1
>
> -- @.AddressLine1
> SET @.counter=0
> SET @.counterLimit = len(@.AddressLine1)
> IF @.counterLimit>0
> BEGIN
> SET @.AddressLine1=''
> WHILE @.counter < @.counterLimit
> BEGIN
> -- create a random sting
> SET @.RandomNumber = Rand()
> SET @.RandomNumberInt = Convert(tinyint,
> ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> @.RandomNumberInt, 1)
> IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> END
> IF @.DEBUG=1 PRINT @.AddressLine1
> END
>|||Thank you for the quick reply Andy,
I am pulling 1 character out at a time but in the database they are all
uppercase,
which is why I was wondering using INITCAP
Dhwiren
Andy Price wrote:
> i havent ran the code, but your logic is slightly flawed. change the end part
> to something like this. You can also use UPPER in place of INITCAP in your
> code because you are pulling 1 character at a time from SUBSTRING.
>
> IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
> SET @.counter = @.counter + 1
> SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
> --
> Andy Price,
> Sr. Database Administrator,
> MCDBA 2003
>
> "Dwizz" wrote:
> > Hey all,
> >
> > I'm trying to use the INITCAP function to make the first letter of an
> > address appear in capital and the rest in lower case, but it seems to
> > appear all in Caps.
> >
> > Can anyone please help. I've attached a snipplet of the code.
> >
> > TIA
> >
> > Dhwiren
> >
> >
> > DECLARE @.counter smallint
> > DECLARE @.counterLimit smallint
> > DECLARE @.RandomNumber float
> > DECLARE @.RandomNumberInt tinyint
> > DECLARE @.CurrentCharacter varchar(1)
> > DECLARE @.ValidCharacters varchar(255)
> > DECLARE @.ValidCharactersLength int
> > DECLARE @.DEBUG int
> >
> > SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> > SET @.ValidCharactersLength = len(@.ValidCharacters)
> > SET @.CurrentCharacter = ''
> > SET @.RandomNumber = 0
> > SET @.RandomNumberInt = 0
> > SET @.DEBUG=1
> >
> >
> > -- @.AddressLine1
> > SET @.counter=0
> > SET @.counterLimit = len(@.AddressLine1)
> > IF @.counterLimit>0
> > BEGIN
> > SET @.AddressLine1=''
> > WHILE @.counter < @.counterLimit
> > BEGIN
> > -- create a random sting
> > SET @.RandomNumber = Rand()
> > SET @.RandomNumberInt = Convert(tinyint,
> > ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> > SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> > @.RandomNumberInt, 1)
> > IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> > SET @.counter = @.counter + 1
> > SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> > END
> > IF @.DEBUG=1 PRINT @.AddressLine1
> > END
> >
> >|||I see, I didnt realize they were all in uppercase. You could change it to use
that, or you can just change your code to this (ie, added 1 line of code
prior to the block of code in the last post). INITCAP doesnt exist in SQL
Server. I am assuming you are an ORACLE or MySQL guy also?
set @.CurrentCharacter =LOWER(@.CurrentCharacter)
IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
SET @.counter = @.counter + 1
SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
--
Andy Price,
Sr. Database Administrator,
MCDBA 2003
"Dwizz" wrote:
> Thank you for the quick reply Andy,
> I am pulling 1 character out at a time but in the database they are all
> uppercase,
> which is why I was wondering using INITCAP
> Dhwiren
> Andy Price wrote:
> > i havent ran the code, but your logic is slightly flawed. change the end part
> > to something like this. You can also use UPPER in place of INITCAP in your
> > code because you are pulling 1 character at a time from SUBSTRING.
> >
> >
> > IF @.counter = 1 SET @.CurrentCharacter = UPPER(@.CurrentCharacter)
> > SET @.counter = @.counter + 1
> > SET @.AddressLine1 = @.AddressLine1 + @.CurrentCharacter
> >
> > --
> > Andy Price,
> > Sr. Database Administrator,
> > MCDBA 2003
> >
> >
> > "Dwizz" wrote:
> >
> > > Hey all,
> > >
> > > I'm trying to use the INITCAP function to make the first letter of an
> > > address appear in capital and the rest in lower case, but it seems to
> > > appear all in Caps.
> > >
> > > Can anyone please help. I've attached a snipplet of the code.
> > >
> > > TIA
> > >
> > > Dhwiren
> > >
> > >
> > > DECLARE @.counter smallint
> > > DECLARE @.counterLimit smallint
> > > DECLARE @.RandomNumber float
> > > DECLARE @.RandomNumberInt tinyint
> > > DECLARE @.CurrentCharacter varchar(1)
> > > DECLARE @.ValidCharacters varchar(255)
> > > DECLARE @.ValidCharactersLength int
> > > DECLARE @.DEBUG int
> > >
> > > SET @.ValidCharacters = 'aaabcdeeefghiiijklmnooopqrstuuuvwxyz'
> > > SET @.ValidCharactersLength = len(@.ValidCharacters)
> > > SET @.CurrentCharacter = ''
> > > SET @.RandomNumber = 0
> > > SET @.RandomNumberInt = 0
> > > SET @.DEBUG=1
> > >
> > >
> > > -- @.AddressLine1
> > > SET @.counter=0
> > > SET @.counterLimit = len(@.AddressLine1)
> > > IF @.counterLimit>0
> > > BEGIN
> > > SET @.AddressLine1=''
> > > WHILE @.counter < @.counterLimit
> > > BEGIN
> > > -- create a random sting
> > > SET @.RandomNumber = Rand()
> > > SET @.RandomNumberInt = Convert(tinyint,
> > > ((@.ValidCharactersLength - 1) * @.RandomNumber + 1))
> > > SELECT @.CurrentCharacter = SUBSTRING(@.ValidCharacters,
> > > @.RandomNumberInt, 1)
> > > IF @.counter = 1 SET @.CurrentCharacter =(@.CurrentCharacter)
> > > SET @.counter = @.counter + 1
> > > SET @.AddressLine1 = @.AddressLine1 + INITCAP(@.CurrentCharacter)
> > > END
> > > IF @.DEBUG=1 PRINT @.AddressLine1
> > > END
> > >
> > >
>|||Thanks Andy,
We got it to work, the extra line did it, I am indeed an Oracle man
Thanks once again Andy
Dhwiren|||Ahhh.. Andy, I just noticed that its the second character that becomes
Capitalised and not the first, but as a bonus all the other characters
are in lower case. So how would I make the first character Capitalised
and not the second character capitalised
Monday, February 20, 2012
problem useing Stored Procedure form vb.net
i have created a stored prcedure but is always give error : and please check the ways is correct using stored procedure
"Procedure or Function 'Add_Cb_Entry' expects parameter '@.Date', which was not supplied."
Dim SqlPrm As SqlParameter
Dim SqlCmd As New SqlCommand
With SqlCmd
.Connection = SqlConnection 'this is my connection setting
.CommandText = "Dbo.Add_Cb_Entry"
.CommandType = CommandType.StoredProcedure
End With
SqlPrm = SqlCmd.Parameters.Add("@.Cashbook_Id", Nothing)
SqlPrm.Direction = ParameterDirection.Output
SqlPrm = SqlCmd.Parameters.Add("@.Date", SqlDbType.DateTime, 8, TxtDate.Text)
SqlPrm.Direction = ParameterDirection.Input
SqlPrm = SqlCmd.Parameters.Add("@.Entry_ID", SqlDbType.Int, 4, "4")
SqlPrm = SqlCmd.Parameters.Add("@.Entry_Name", SqlDbType.VarChar, 75, "Irfan Imdad Memon")
SqlPrm = SqlCmd.Parameters.Add("@.Description", SqlDbType.VarChar, 100, "Chk")
SqlPrm = SqlCmd.Parameters.Add("@.Amount", SqlDbType.Money, 13, "1000.20")
SqlPrm = SqlCmd.Parameters.Add("@.Type", SqlDbType.NChar, 2, "DB")
SqlPrm = SqlCmd.Parameters.Add("@.Entry_Status", SqlDbType.VarChar, 20, "Account")
SqlPrm = SqlCmd.Parameters.Add("@.Ref_No", SqlDbType.Int, 4, "1")
SqlPrm = SqlCmd.Parameters.Add("@.Ref_Status", SqlDbType.VarChar, 20, "MeterialPurchase")
OpenConnection() 'this is my connection setting
SqlCmd.ExecuteNonQuery()
CloseConnection() 'this is my connection setting
TxtInvoiceNo.Text = SqlCmd.Parameters("@.Cashbook_Id").Value
hi
when you use this statement:
SqlPrm = SqlCmd.Parameters.Add("@.Cashbook_Id", Nothing)
sql server not found Nothing !
you set this parameter with zero or any varchar.
good luck
Subscribe to:
Posts (Atom)