Check 'instdist.out' in the install directory.

What does this mean? It appears to be keeping the wizard from running the
distributor setup on the server.
Code used is:
use master go
sp_adddistributor @.distributor = @.@.servername GO
sp_adddistributiondb @.database='distribution' GO
Error is:
Server: Msg 14113, Level 16, State 1, Procedure sp_adddistributiondb, Line 304
Could not execute 'copy "d:\Program Files\Microsoft SQL
Server\MSSQL\DATA\DISTMDL.MDF" "d:\Program Files\Microsoft SQL
Server\MSSQL\DATA\distribution.MDF"'. Check 'instdist.out' in the install
directory.
Regards,
Jamie
What does 'instdist.out say?
You can also issue this command
sp_adddistributiondb 'distributionNew',@.data_folder='c:\temp,
@.data_file='distribution.mdf',
@.log_folder='ctemp', @.log_file='distribution.ldf',
@.log_file_size=5,@.create_mode=1
where c:\temp already exists
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:B7FBF6B2-F8D0-40C6-9844-A21E386C991E@.microsoft.com...
> What does this mean? It appears to be keeping the wizard from running the
> distributor setup on the server.
> Code used is:
> use master go
> sp_adddistributor @.distributor = @.@.servername GO
> sp_adddistributiondb @.database='distribution' GO
> Error is:
> Server: Msg 14113, Level 16, State 1, Procedure sp_adddistributiondb, Line
> 304
> Could not execute 'copy "d:\Program Files\Microsoft SQL
> Server\MSSQL\DATA\DISTMDL.MDF" "d:\Program Files\Microsoft SQL
> Server\MSSQL\DATA\distribution.MDF"'. Check 'instdist.out' in the install
> directory.
>
> --
> Regards,
> Jamie
|||Unfortunately, it does not exist on the server.
Regards,
Jamie
"Hilary Cotter" wrote:

> What does 'instdist.out say?
> You can also issue this command
> sp_adddistributiondb 'distributionNew',@.data_folder='c:\temp,
> @.data_file='distribution.mdf',
> @.log_folder='ctemp', @.log_file='distribution.ldf',
> @.log_file_size=5,@.create_mode=1
> where c:\temp already exists
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:B7FBF6B2-F8D0-40C6-9844-A21E386C991E@.microsoft.com...
>
>
|||Let's try again... the last post appears blank on my screen.
Hilary,
We have searched the server hoping to find this file. The file does not
exist on the server so I don't know what it says. Thus my question - any
idea what it may mean?
Regards,
Jamie
"Hilary Cotter" wrote:

> What does 'instdist.out say?
> You can also issue this command
> sp_adddistributiondb 'distributionNew',@.data_folder='c:\temp,
> @.data_file='distribution.mdf',
> @.log_folder='ctemp', @.log_file='distribution.ldf',
> @.log_file_size=5,@.create_mode=1
> where c:\temp already exists
>
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:B7FBF6B2-F8D0-40C6-9844-A21E386C991E@.microsoft.com...
>
>
|||There is a script called instdist.sql which is run. Any errors are logged to
a file called instdist.out. Somehow you are having a problem running this
script.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"thejamie" <thejamie@.discussions.microsoft.com> wrote in message
news:225ACF20-532F-41E5-A1AB-C0FFB3EF6B35@.microsoft.com...[vbcol=seagreen]
> Let's try again... the last post appears blank on my screen.
> Hilary,
> We have searched the server hoping to find this file. The file does not
> exist on the server so I don't know what it says. Thus my question - any
> idea what it may mean?
> --
> Regards,
> Jamie
>
> "Hilary Cotter" wrote:
|||Someone deleted one of the folders that SQL Server installs. We reinstalled
SQL and voila, the file is there now.
Regards,
Jamie
"Hilary Cotter" wrote:

> There is a script called instdist.sql which is run. Any errors are logged to
> a file called instdist.out. Somehow you are having a problem running this
> script.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
>
> "thejamie" <thejamie@.discussions.microsoft.com> wrote in message
> news:225ACF20-532F-41E5-A1AB-C0FFB3EF6B35@.microsoft.com...
>
>

Check if files exist Stored Proc

Hi,
This stored proc code uses DOS Copy command and xp_cmdshell stored
procedure to copy files form one location to another. See below the
code. It is working fine!. What I need is to add a check if the files in
the Source location exist or not. If not, then send an email (using
xp_sendmail) to us saying "files do not exist". If yes, then start
copying the files. How do I do that?
Thanks for your help.
declare @.source varchar(150)
declare @.destination varchar(150)
declare @.DOScmd varchar(300)
select @.source =
case @.@.servername
when 'A' then '\\server1\folder1\a*.*'
when 'B' then '\\server2\folder2\b*.*'
when 'C' then '\\server3\folder3\c*.*'
end,
@.destination =
case @.@.servername
when 'A' then '\\serverx\folderx'
when 'B' then '\\serverx\folderx'
when 'C' then '\\serverx\folderx'
end
-- copy only if the files in the source folder exist, otherwise send an
email for "files do not exixts".
set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
rtrim(@.destination)+'"'
exec master.dbo.xp_cmdshell @.DOScmd
GO
*** Sent via Developersdex http://www.examnotes.net ***Look up xp_fileexist in Books Online.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Whoops, never mind! I forgot this proc is not documented / supported.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:uwg8xEguFHA.2568@.TK2MSFTNGP15.phx.gbl...
> Look up xp_fileexist in Books Online.|||A particular file can be checked with:
EXEC master..xp_fileexist 'c:\boot.ini'
HTH, Jens Suessmeyer.|||If this were implemented as a DTS package, you would perhaps find the file
system object more suitable for copying files, etc.
http://msdn.microsoft.com/library/d...ystemObject.asp
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:%23CYCQDguFHA.3236@.TK2MSFTNGP14.phx.gbl...
> Hi,
> This stored proc code uses DOS Copy command and xp_cmdshell stored
> procedure to copy files form one location to another. See below the
> code. It is working fine!. What I need is to add a check if the files in
> the Source location exist or not. If not, then send an email (using
> xp_sendmail) to us saying "files do not exist". If yes, then start
> copying the files. How do I do that?
> Thanks for your help.
> declare @.source varchar(150)
> declare @.destination varchar(150)
> declare @.DOScmd varchar(300)
> select @.source =
> case @.@.servername
> when 'A' then '\\server1\folder1\a*.*'
> when 'B' then '\\server2\folder2\b*.*'
> when 'C' then '\\server3\folder3\c*.*'
> end,
> @.destination =
> case @.@.servername
> when 'A' then '\\serverx\folderx'
> when 'B' then '\\serverx\folderx'
> when 'C' then '\\serverx\folderx'
> end
> -- copy only if the files in the source folder exist, otherwise send an
> email for "files do not exixts".
> set @.DOScmd = 'Copy ' + '"'+ rtrim(@.source) +'"'+ ' ' +'"'+
> rtrim(@.destination)+'"'
> exec master.dbo.xp_cmdshell @.DOScmd
> GO
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Thanks for the respone. The "xp_fileexist" works great if you know the
exact file name. In my case, I dont know the file name. All I need is to
capture the files start with "a" i.e. a*.*
So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
Any other ideas?
*** Sent via Developersdex http://www.examnotes.net ***|||CREATE TABLE #files
(
filename SYSNAME NULL
)
SET NOCOUNT ON
INSERT #files EXEC master..xp_cmdshell 'dir \\server1\folder1\a*.* /b'
SELECT COUNT(*) FROM #files WHERE filename IS NOT NULL
SELECT * FROM #files WHERE filename IS NOT NULL
DROP TABLE #files
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:uvoSyaguFHA.1032@.TK2MSFTNGP12.phx.gbl...
> Thanks for the respone. The "xp_fileexist" works great if you know the
> exact file name. In my case, I dont know the file name. All I need is to
> capture the files start with "a" i.e. a*.*
> So, EXEC master..xp_fileexist '\\server1\folder1\a*.*' does not work.
> Any other ideas?
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||If you're already planning on using xp_cmdshell, then try this:
declare @.cmd varchar(1024)
declare @.path varchar(1024)
create table #fs
(
fId int identity (1, 1) primary key
,fName varchar(1024)
)
set @.path = 'c:\Inetpub*.*'
set @.cmd = 'dir /b ' + @.path
insert #fs
(
fName
)
exec master.dbo.xp_cmdshell @.cmd
if (exists (
select fs.fId
from #fs fs
where (fs.fName is not null)
))
begin
print 'exists'
end
else
begin
print 'does not exist'
end
ML|||Aaron! Thanx a lot!!!. It works like a champ!!!
*** Sent via Developersdex http://www.examnotes.net ***

Check if file is already open

Hi,

I wrote a VB code to generate a xls file. Users are able to run it fine but if they have another file with same name already open, then it just crashes excel.

So I want to include a code that checks if file "file.xls" is open on user's machine.

If file is open, then message "file "File.xls" is already open. Generating File_1.xls"

Run the code but create the file with file name "file_1.xls"

If file doesn't exist, then run code and create file with file name "File.xls"

So basically I want the code to run and generate the file. Only difference is that if file with same name is already open, then just rename the newly created file.

Here's the code I've created for generating the file:

Public Function getrmpricing()
Dim queryoption As String
Dim ans, Msg As String
Dim fs As Object
Dim sTemplateFile As String
Dim e_TemplateFile As String

On Error Resume Next

sTemplateFile = g_dashboard & "crm proposal input.XLT"
e_TemplateFile = "C:\"

If Forms!rmpricingdataform!BU = "CS" Then
MsgBox "No template available for CS!", vbOKOnly, "RM Pricing Report"
Else

Set fs = CreateObject("Scripting.FileSystemObject")
fs.CopyFile sTemplateFile, e_TemplateFile, True

Dim xl As New Excel.Application
xl.Workbooks.Open e_TemplateFile & "crm proposal input.XLT"

DoCmd.OutputTo acOutputQuery, "CustPricingbyRMCrosstabquery", acFormatXLS, "c:\customerpricing.xls", True

Dim xs As New Excel.Application
xs.Workbooks("customerpricing").Activate
xs.ActiveWorkbook.Activate
Select Case Forms!rmpricingdataform!BU

Case "CRM"
xl.Run "'crm proposal input.XLT'!CRM_CAPSPriceTemplate.CRM_CAPSPriceTemplate"

End Select
'xs.Workbooks.CLOSE - NEWLY COMMENTED OUT
xl.Workbooks("crm proposal input.XLT").CLOSE
'xl.Workbooks("crmpricing.xls").Save - NEVER USED

'fs.DeleteFile e_TemplateFile & "crm proposal input.XLT", True - NEWLY COMMENTED OUT
Set fs = Nothing

DoCmd.CLOSE acForm, "rmpricingdataform"
Call AuditTrail("RM Pricing report", "Execute")
End If

End Function

Please advise.

Not the right forum, try this one

http://www.microsoft.com/communities/newsgroups/en-us/default.aspx?query=Excel&dg=&cat=en_US_d02fc761-3f6b-402c-82f6-ba1a8875c1a7&lang=en&cr=&pt=&catlist=&dglist=&ptlist=&exp=&sloc=en-us

sql

Check if a file exists using sql

Is there is a piece for code ot sample code that can let me check if a file exists? Has any one done this before?
ThanksTry this idea.

create table #tmp(result varchar(100))
go
insert #tmp
exec master..xp_cmdshell 'dir filename.txt'
select * from #tmp where result like '%filename.txt%'|||Originally posted by snail
Try this idea.

create table #tmp(result varchar(100))
go
insert #tmp
exec master..xp_cmdshell 'dir filename.txt'
select * from #tmp where result like '%filename.txt%'

I found another method to do this. I used the xp_fileexist command and it works well. The idea above works as well

Thanks for the input.sql

Check for duplicates

I am trying to do a check if the record already exists in my database. I wrote this code and all is well except it takes about 2-5 seconds to execute on my access database. Wen you know this one is called about 30 000 to 50 000 times that is unacceptable.

Can someone please take a look and see if this can be accelerated?

Thanks

Napivo

Public Function CheckDouble(psWeb As String, psIP As String, pdDate As Date, _
pdTime As Date, psEnvironment As String, psControler As String, _
plType As Long, plSize As Long, pbSpecial As Boolean, plSpend As Long) As Boolean

Dim rs As ADODB.Recordset
Dim sql As String

sql = "select count(ip) as cnt from Logs where [IP] = '" & psIP & "'" _
& " And [Web] = '" & psWeb & "'" _
& " And [Date] = #" & Format(pdDate, "MM/DD/YY") & "#" _
& " and [Time] = #" & Format(pdTime, "HH:MM") & "#" _
& " and [Environment] = '" & psEnvironment & "'" _
& " and [Controler] = '" & psControler & "'" _
& " and [Spend] =" & plSpend & "" _
& " and [Type] =" & plType & "" _
& " and [Special] = " & CBool(pbSpecial) & "" _
& " and [Size] =" & plSize

On Error GoTo CheckDouble_Error
Set rs = oCon.Execute(sql)
On Error GoTo 0
If rs.Fields("cnt").Value > 0 Then
CheckDouble = True
End If
Exit Function
CheckDouble_Error:

Debug.Print "Error " & Err.Number & " (" & Err.Description & ") in procedure CheckDouble of Class Module cLogDatabase"
End Function

This is an example of the SQL statement I get

select count(ip) as cnt from Logs where [IP] = '194.235.127.40 ' And [Web] = 'WEB2' And [Date] = #07/01/04# and [Time] = #14:14# and [Environment] = 'AON' and [Controler] = 'EAFormController' and [Spend] =2 and [Type] =200 and [Special] = False and [Size] =23489My first suggestion would be to look at adding indices to make it easier for Jet to process the query. You may well need to experiment a bit to find a good combination of columns, since Jet often makes "interesting" choices where indicies are concerned.

If that doesn't help enough, I'd switch to MSDE in order to get more help understanding the query itself. It is a lot easier to find and fix query problems in MSDE than it is in Jet, and once you've solved the problem you can almost always move back to Jet if you want.

-PatP

Check for Date

I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.Hi, Watson
You can use:
a) the ISDATE() function (which accepts any date format),
b) a LIKE expression (which accepts a given pattern, but cannot easily
check if the date is valid), or
c) a combination of the above
For example:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0)
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9]
[0-9][0-9]')
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0
AND USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9]
1;0-9][0-9]')
Razvan
Watson SQL wrote:
> I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.|||You could have a CHECK constraint as shown below:
ALTER TABLE YourTableName ADD CONSTRAINT CheckDate CHECK (ISDATE(ColumNName)
= 1 or ColumnName IS NULL)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.|||>I want to enforce users to enter a date or leave the field null into a
> varchar field.

> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Try:
ALTER TABLE dbo.CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 IS NULL OR ISDATE(USER_7) = 1)
Hope this helps.
Dan Guzman
SQL Server MVP
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>|||ALTER TABLE CUST_ORDER_LINE ADD CONSTRAINT constraintname CHECK(ISDATE(USER_
7))
No need to explicitly allow NULL. If a CHECK constraint evaluate to TRUE or
UNK, the modification is
allowed.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>

Check for Date

I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.Hi, Watson
You can use:
a) the ISDATE() function (which accepts any date format),
b) a LIKE expression (which accepts a given pattern, but cannot easily
check if the date is valid), or
c) a combination of the above
For example:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0)
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
or:
ALTER TABLE CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (ISDATE(USER_7)<>0
AND USER_7 LIKE '[0-9][0-9]/[0-9][0-9]/[0-9][0-9][0-9][0-9]')
Razvan
Watson SQL wrote:
> I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.|||You could have a CHECK constraint as shown below:
ALTER TABLE YourTableName ADD CONSTRAINT CheckDate CHECK (ISDATE(ColumNName)
= 1 or ColumnName IS NULL)
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
I want to enforce users to enter a date or leave the field null into a
varchar field.
We do not own the code to our database so I can not change the field to
a date/Time (It would violate our contract). I was thinking we could
enter a check constraint (This is OK per our contact) into the database
but I am not sure how.
THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Thanks for any help.|||>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
Try:
ALTER TABLE dbo.CUST_ORDER_LINE
ADD CONSTRAINT CK_CUST_ORDER_LINE_USER_7
CHECK (USER_7 IS NULL OR ISDATE(USER_7) = 1)
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>|||ALTER TABLE CUST_ORDER_LINE ADD CONSTRAINT constraintname CHECK(ISDATE(USER_7))
No need to explicitly allow NULL. If a CHECK constraint evaluate to TRUE or UNK, the modification is
allowed.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Watson SQL" <APHILLEY@.WATSONFURNITURE.COM> wrote in message
news:1152544408.938003.176800@.h48g2000cwc.googlegroups.com...
>I want to enforce users to enter a date or leave the field null into a
> varchar field.
> We do not own the code to our database so I can not change the field to
> a date/Time (It would violate our contract). I was thinking we could
> enter a check constraint (This is OK per our contact) into the database
> but I am not sure how.
> THE TABLE IS CUST_ORDER_LINE AND THE FIELD IS USER_7.
> Thanks for any help.
>

Check for constraint on delete

How do you code a procedure to delete a record where if it has an
error (because of foeign key constraint , no cascade and related
records) it will continue and do an update instead.
I have tried this but it doesnt continue if the delete hits an error.
Thanks
Create procedure dbo.delete_record
@.id smallint
as
delete from table
where id=@.id
if @.@.error <>0
update table set deleted=1
where id=@.idwhy don't you update before deleting?

Check constraint?

I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
MikeThis is a multi-part message in MIME format.
--=_NextPart_000_00F6_01C3529D.605F7E80
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
Mike
--=_NextPart_000_00F6_01C3529D.605F7E80
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

You can use an indexed view to enforce =the uniqueness for codes > 5650000:
create view =dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > =5650000
go
create unique clustered index =idx on MyView (ProjectCode)
-- Tom
---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql
"Mike" wrote in message news:eIkPv3rUDHA.1688=@.TK2MSFTNGP11.phx.gbl...I have an integer column that is used for our project codes. We use adefault project code of 5650000. Every other project code =must be greaterthan that number and then must be distinct, but because we =have manyprojects pending that have not been assigned project codes yet =there aremultiple default values of 5650000. Is there any way to =apply a checkconstraint to this? I recently had a problem where a user =added a projectcode that had already been used. What can I do to =prevent this in thefuture?Thanks for any =helpMike

---

--=_NextPart_000_00F6_01C3529D.605F7E80--|||This is a multi-part message in MIME format.
--=_NextPart_000_001D_01C3529E.BB1064B0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
I am kind of new to this. I tried this and I got the message
Cannot create index on view 'myview' because the view is not schema =bound.
What does this mean?
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eArxy7rUDHA.1916@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there =are
multiple default values of 5650000. Is there any way to apply a check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in the
future?
Thanks for any help
Mike
--=_NextPart_000_001D_01C3529E.BB1064B0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

I am kind of new to this. I tried =this and I got the message
Cannot create index on view 'myview' =because the view is not schema bound.
What does this mean?
Mike
"Tom Moreau" = wrote in message news:eArxy7rUDHA.1916=@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to =enforce the uniqueness for codes > 5650000:

create view =dbo.MyView
as
select =ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go

create unique clustered index =idx on MyView (ProjectCode)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Mike" wrote in message news:eIkPv3rUDHA.1688=@.TK2MSFTNGP11.phx.gbl...I have an integer column that is used for our project codes. We =use adefault project code of 5650000. Every other project code =must be greaterthan that number and then must be distinct, but because we =have manyprojects pending that have not been assigned project codes yet =there aremultiple default values of 5650000. Is there any way to =apply a checkconstraint to this? I recently had a problem where a =user added a projectcode that had already been used. What can I do to =prevent this in thefuture?Thanks for any helpMike

---

--=_NextPart_000_001D_01C3529E.BB1064B0--|||This is a multi-part message in MIME format.
--=_NextPart_000_002F_01C352AD.D241BFD0
Content-Type: text/plain;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
Makes sense. Thanks for the help.
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eYrWjDsUDHA.3152@.tk2msftngp13.phx.gbl...
Oops. Here's the revised code:
create view dbo.MyView
with schemabinding
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
Schema binding ensures that any attempt to change an object referenced =by the view will fail.
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:u0GmDBsUDHA.1928@.TK2MSFTNGP12.phx.gbl...
I am kind of new to this. I tried this and I got the message
Cannot create index on view 'myview' because the view is not schema =bound.
What does this mean?
Mike
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message =news:eArxy7rUDHA.1916@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to enforce the uniqueness for codes > =5650000:
create view dbo.MyView
as
select ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go
create unique clustered index idx on MyView (ProjectCode)
-- Tom
---
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com/sql
"Mike" <Mike@.nospam.com> wrote in message =news:eIkPv3rUDHA.1688@.TK2MSFTNGP11.phx.gbl...
I have an integer column that is used for our project codes. We use =a
default project code of 5650000. Every other project code must be =greater
than that number and then must be distinct, but because we have many
projects pending that have not been assigned project codes yet there =are
multiple default values of 5650000. Is there any way to apply a =check
constraint to this? I recently had a problem where a user added a =project
code that had already been used. What can I do to prevent this in =the
future?
Thanks for any help
Mike
--=_NextPart_000_002F_01C352AD.D241BFD0
Content-Type: text/html;
charset="Windows-1252"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Makes sense. Thanks for the help.
Mike
"Tom Moreau" = wrote in message news:eYrWjDsUDHA.3152=@.tk2msftngp13.phx.gbl...
Oops. Here's the revised code:

create view =dbo.MyView
with schemabinding
as
select =ProjectCode
from dbo.MyTable
where ProjectCode > 5650000
go

Schema binding ensures that any =attempt to change an object referenced by the view will fail.
-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Mike" wrote in message news:u0GmDBsUDHA.1928=@.TK2MSFTNGP12.phx.gbl...
I am kind of new to this. I =tried this and I got the message

Cannot create index on view 'myview' =because the view is not schema bound.

What does this mean?

Mike
"Tom Moreau" = wrote in message news:eArxy7rUDHA.1916=@.TK2MSFTNGP12.phx.gbl...
You can use an indexed view to =enforce the uniqueness for codes > 5650000:

create view =dbo.MyView
as
select =ProjectCode
from =dbo.MyTable
where ProjectCode > 5650000
go

create unique clustered =index idx on MyView (ProjectCode)

-- Tom

=---T=homas A. Moreau, BSc, PhD, MCSE, MCDBASQL Server MVPColumnist, SQL =Server ProfessionalToronto, ON Canadahttp://www.pinnaclepublishing.com/sql">www.pinnaclepublishing.com=/sql


"Mike" wrote in message news:eIkPv3rUDHA.1688=@.TK2MSFTNGP11.phx.gbl...I have an integer column that is used for our project codes. We =use adefault project code of 5650000. Every other project code =must be greaterthan that number and then must be distinct, but because =we have manyprojects pending that have not been assigned project codes =yet there aremultiple default values of 5650000. Is there any way to =apply a checkconstraint to this? I recently had a problem where a =user added a projectcode that had already been used. What can I =do to prevent this in thefuture?Thanks for any helpMike

---

--=_NextPart_000_002F_01C352AD.D241BFD0--

Chart, how do I use format code for a label?

How do I refer to the actual value for the label inside my expression?
Say you have datetime data across the x-axis for the data. I get one row per
month in the dataset (every row is same date and time for each row/month). I
understand how I can format this, for instance "MMM" to get month name in
short format.
But if I want to do further or a bit more complex manipulation? In this
case, I want to show only first letter of the month. I fail to connect how
to put the actual data value inside my VB.NET expression (substring, left or
similar function in this case).
(I can retrieve the first letter of the month along with the data, as an
extra column, doing this in my stored procedure. If above is difficult, I'd
appreciate tips on how to refer to this column for the chart label. I.e., I
want to show some other column as the label, not the one used to derive the
actual value.)
TIA
Tibor Karaszi
SQL Server MVPExpressions for formatting labels are not directly supported in the current
release. You could do a bar chart and use an expression
(=Left(Format(Fields!OrderDate.Value, "MMM"), 1)) for the corresponding
category group. The sample report attached at the end of this post (which
runs against local Northwind database) demonstrates this.
--
Ravi Mumulla (Microsoft)
SQL Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OEwX0qaiEHA.2848@.TK2MSFTNGP10.phx.gbl...
> How do I refer to the actual value for the label inside my expression?
> Say you have datetime data across the x-axis for the data. I get one row
per
> month in the dataset (every row is same date and time for each row/month).
I
> understand how I can format this, for instance "MMM" to get month name in
> short format.
> But if I want to do further or a bit more complex manipulation? In this
> case, I want to show only first letter of the month. I fail to connect how
> to put the actual data value inside my VB.NET expression (substring, left
or
> similar function in this case).
> (I can retrieve the first letter of the month along with the data, as an
> extra column, doing this in my stored procedure. If above is difficult,
I'd
> appreciate tips on how to refer to this column for the chart label. I.e.,
I
> want to show some other column as the label, not the one used to derive
the
> actual value.)
> TIA
> Tibor Karaszi
> SQL Server MVP
>
+++++++++++++ Sample report +++++++++++++
<?xml version="1.0" encoding="utf-8"?>
<Report
xmlns="http://schemas.microsoft.com/sqlserver/reporting/2003/10/reportdefini
tion"
xmlns:rd="">http://schemas.microsoft.com/SQLServer/reporting/reportdesigner">
<RightMargin>1in</RightMargin>
<Body>
<ReportItems>
<Chart Name="chart1">
<ThreeDProperties>
<Rotation>30</Rotation>
<Inclination>30</Inclination>
<Shading>Simple</Shading>
<WallThickness>50</WallThickness>
</ThreeDProperties>
<Style>
<BackgroundColor>White</BackgroundColor>
</Style>
<Legend>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
<Position>RightCenter</Position>
</Legend>
<Palette>Default</Palette>
<ChartData>
<ChartSeries>
<DataPoints>
<DataPoint>
<DataValues>
<DataValue>
<Value>=Sum(Fields!Freight.Value)</Value>
</DataValue>
</DataValues>
<DataLabel />
<Marker />
</DataPoint>
</DataPoints>
</ChartSeries>
</ChartData>
<CategoryAxis>
<Axis>
<Title />
<Style>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Visible>true</Visible>
</Axis>
</CategoryAxis>
<DataSetName>Northwind</DataSetName>
<PointWidth>0</PointWidth>
<Type>Bar</Type>
<Title />
<Width>5.25in</Width>
<CategoryGroupings>
<CategoryGrouping>
<DynamicCategories>
<Grouping Name="chart1_CategoryGroup1">
<GroupExpressions>
<GroupExpression>=Fields!OrderDate.Value</GroupExpression>
</GroupExpressions>
</Grouping>
<Label>=Left(Format(Fields!OrderDate.Value, "MMM"), 1) & "
[" & Format(Fields!OrderDate.Value, "MMM yyyy") & "]"</Label>
</DynamicCategories>
</CategoryGrouping>
</CategoryGroupings>
<Subtype>Plain</Subtype>
<PlotArea>
<Style>
<BackgroundColor>LightGrey</BackgroundColor>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</PlotArea>
<ValueAxis>
<Axis>
<Title />
<Style>
<Format>c</Format>
<FontSize>8pt</FontSize>
</Style>
<MajorGridLines>
<ShowGridLines>true</ShowGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MajorGridLines>
<MinorGridLines>
<Style>
<BorderStyle>
<Default>Solid</Default>
</BorderStyle>
</Style>
</MinorGridLines>
<MajorTickMarks>Outside</MajorTickMarks>
<Min>0</Min>
<Margin>true</Margin>
<Visible>true</Visible>
<Scalar>true</Scalar>
</Axis>
</ValueAxis>
</Chart>
</ReportItems>
<Style />
<Height>3in</Height>
</Body>
<TopMargin>1in</TopMargin>
<DataSources>
<DataSource Name="Northwind">
<rd:DataSourceID>14b06457-afff-49a5-9624-2ecc74ef5643</rd:DataSourceID>
<ConnectionProperties>
<DataProvider>SQL</DataProvider>
<ConnectString>initial catalog=Northwind</ConnectString>
<IntegratedSecurity>true</IntegratedSecurity>
</ConnectionProperties>
</DataSource>
</DataSources>
<Width>6.25in</Width>
<DataSets>
<DataSet Name="Northwind">
<Fields>
<Field Name="OrderDate">
<DataField>OrderDate</DataField>
<rd:TypeName>System.DateTime</rd:TypeName>
</Field>
<Field Name="Freight">
<DataField>Freight</DataField>
<rd:TypeName>System.Decimal</rd:TypeName>
</Field>
</Fields>
<Query>
<DataSourceName>Northwind</DataSourceName>
<CommandText>SELECT TOP 10 OrderDate, Freight
FROM Orders
ORDER BY ShipCity</CommandText>
</Query>
</DataSet>
</DataSets>
<LeftMargin>1in</LeftMargin>
<rd:SnapToGrid>true</rd:SnapToGrid>
<rd:DrawGrid>true</rd:DrawGrid>
<rd:ReportID>b8405333-c29c-4c57-8ba3-7915ae7bf5eb</rd:ReportID>
<BottomMargin>1in</BottomMargin>
<Language>en-US</Language>
</Report>

chart question

Could someone please tell me how I can show 0 - 100% on Y-axis in line
charts. I have been trying Format code of #% but that adds 2 additional zeros
like this 20% will be 2000%. Please advise.
Also, I am unable to change the series color.
I need to add a window outset all around the chart with a small window on
top for label. Is it even possible? It seems so easy to do with .Net charting
but I can't figure it out here.
Thanks!!
SonalIf you format values as percentages, then 1 mean 100%. 0.5 means 50%
This behavior is similar to e.g. Excel.
-- Robert
This posting is provided "AS IS" with no warranties, and confers no rights.
"sonal" <sonal@.discussions.microsoft.com> wrote in message
news:9F5930D7-14B7-43DE-AE94-09774E41BF7F@.microsoft.com...
> Could someone please tell me how I can show 0 - 100% on Y-axis in line
> charts. I have been trying Format code of #% but that adds 2 additional
> zeros
> like this 20% will be 2000%. Please advise.
> Also, I am unable to change the series color.
> I need to add a window outset all around the chart with a small window on
> top for label. Is it even possible? It seems so easy to do with .Net
> charting
> but I can't figure it out here.
> Thanks!!
> Sonal

CHARINDEX is not working

i am using the below code but its not working .. what could be the reason?
CHARINDEX(@.SPName,@.DateAndIntCols)
to CHARINDEX function i am passing to variables, cant i pass variables to
CHARINDEX?Please elaborate on "not working" . Are there errors? Don't you get expected
results? Don't you get any results? What?
ML
http://milambda.blogspot.com/|||On Thu, 1 Dec 2005 15:33:02 -0800, KL wrote:

>i am using the below code but its not working .. what could be the reason?
>CHARINDEX(@.SPName,@.DateAndIntCols)
>to CHARINDEX function i am passing to variables, cant i pass variables to
>CHARINDEX?
Hi KL,
The following code returns 4 on my computer, which is as expected. What
are the values of @.SPName and @.DateAndIntCols in your case, and what was
the result you got?
DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
SET @.SPName = 'xyz'
SET @.DateAndIntCols = 'uvwxyzabcdef'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||I am passing the below values to the variables but its still returning 0...
SET @.SPName ='eUpdated'
SET @.DateAndIntCols = 'eUpdated,eDeleted'
but its working (returning 1) when use the following one...
SET @.DateAndIntCols = 'eUpdated,eDeleted'
CHARINDEX('eUpdated',@.DateAndIntCols)
"ML" wrote:

> Please elaborate on "not working" . Are there errors? Don't you get expect
ed
> results? Don't you get any results? What?
>
> ML
> --
> http://milambda.blogspot.com/|||On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:

>I am passing the below values to the variables but its still returning 0...
>SET @.SPName ='eUpdated'
>SET @.DateAndIntCols = 'eUpdated,eDeleted'
>but its working (returning 1) when use the following one...
>SET @.DateAndIntCols = 'eUpdated,eDeleted'
>CHARINDEX('eUpdated',@.DateAndIntCols)
Hi KL,
Works for me:
DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
SET @.SPName ='eUpdated'
SET @.DateAndIntCols = 'eUpdated,eDeleted'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
Can you please post your complete code?
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Here is the code i am using ....
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
And when i printed the variable @.DateAndIntCols its showing as
'eUpdated,eDeleted'....
"Hugo Kornelis" wrote:

> On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:
>
> Hi KL,
> Works for me:
> DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
> SET @.SPName ='eUpdated'
> SET @.DateAndIntCols = 'eUpdated,eDeleted'
> SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>
> Can you please post your complete code?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||Here is the complete code i am using ....
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
And when i printed the variable @.DateAndIntCols its showing as
'eUpdated,eDeleted'....
"Hugo Kornelis" wrote:

> On Thu, 1 Dec 2005 16:03:03 -0800, KL wrote:
>
> Hi KL,
> Works for me:
> DECLARE @.SPName varchar(10), @.DateAndIntCols varchar(100)
> SET @.SPName ='eUpdated'
> SET @.DateAndIntCols = 'eUpdated,eDeleted'
> SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>
> Can you please post your complete code?
> Best, Hugo
> --
> (Remove _NO_ and _SPAM_ to get my e-mail address)
>|||On Thu, 1 Dec 2005 16:26:02 -0800, KL wrote:

>Here is the complete code i am using ....
>DECLARE
>@.SPName varchar(128) ,
>@.DateAndIntCols varchar(1000)
>SELECT @.SPName='eUpdated'
>SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
>SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
>And when i printed the variable @.DateAndIntCols its showing as
>'eUpdated,eDeleted'....
Hi KL,
Since I don;t have your table, I had to add some lines to the script.
Here's what I executed:
CREATE TABLE MetaData
(SPID char(4) NOT NULL PRIMARY KEY,
DateAndIntCols varchar(1000) NOT NULL)
INSERT INTO MetaData (SPID, DateAndIntCols)
VALUES ('CC-3', 'eUpdated,eDeleted')
go
DECLARE
@.SPName varchar(128) ,
@.DateAndIntCols varchar(1000)
SELECT @.SPName='eUpdated'
SELECT @.DateAndIntCols=DateAndIntCols FROM MetaData WHERE SPID='CC-3'
SELECT CHARINDEX(@.SPName,@.DateAndIntCols)
go
DROP TABLE MetaData
go
And here's the output:
1
This is the expected output. What output are you getting? Also, what is
the output if you execute
SELECT @.@.VERSION
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

CHARINDEX

SQL Server 2000

Ya know, it is always the simplest stuff that gets ya !!

I am having the hardest time getting a simple piece of code working.
Must be brain dead today.

Goal: Get the users full name from a string

Here is sample data:

"LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

Code:
IF LEN(@.strReturnValue) > 0 BEGIN
SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
(CHARINDEX('CN=',@.strReturnValue)+3),
(CHARINDEX(',',@.strReturnValue)-1))
END

It will extract:
"Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"

I want it to extract:
Kevin Jones

Thanks.On 23 Jun 2005 08:51:27 -0700, csomberg@.dwr.com wrote:

> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))

declare @.test varchar(255)
declare @.pos int

select @.test = 'LDAP://blahblahblah/CN=Kevin
Jones,OU=DevEng,DC=nobody,DC=priv,DC=com'

select @.pos = CHARINDEX('CN=',@.test)+3

SELECT SUBSTRING(@.test, @.pos ,(CHARINDEX(',',@.test)) - @.pos)

Tony
--
http://www.dotnet-hosting.com
Free web hosting with ASP.NET & SQL Server
No ads - No trials - Innovative features|||...oops i should have written:

You're thinking the substring syntax is
substring( string, start, end )

when the correct syntax is
substring( string, start, length )

where length is end_position - start position

Sorry for the confusion.

hth,

victor dileo

csomberg@.dwr.com wrote:
> SQL Server 2000
> Ya know, it is always the simplest stuff that gets ya !!
> I am having the hardest time getting a simple piece of code working.
> Must be brain dead today.
> Goal: Get the users full name from a string
> Here is sample data:
> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))
> END
> It will extract:
> "Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> I want it to extract:
> Kevin Jones
> Thanks.|||Note the charindex syntax:
charindex ( string, start, length )

You're mistakenly thinking the syntax is:
charindex ( string, start, end )

The "end" parameter should actually be length from the "start"
position, and be calculated as something like:
length = end - start

Once you fix that, I think your code will work just fine.

hth,

victor dileo

csomberg@.dwr.com wrote:
> SQL Server 2000
> Ya know, it is always the simplest stuff that gets ya !!
> I am having the hardest time getting a simple piece of code working.
> Must be brain dead today.
> Goal: Get the users full name from a string
> Here is sample data:
> "LDAP://blahblahblah/CN=Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> Code:
> IF LEN(@.strReturnValue) > 0 BEGIN
> SELECT @.strReturnValue = SUBSTRING(@.strReturnValue,
> (CHARINDEX('CN=',@.strReturnValue)+3),
> (CHARINDEX(',',@.strReturnValue)-1))
> END
> It will extract:
> "Kevin Jones,OU=DevEng,DC=nobody,DC=priv,DC=com"
> I want it to extract:
> Kevin Jones
> Thanks.

Character Set (code page)

I have to test an application under a Greek version of Windows.
I heave installed Windows XP - Greek. The Greek characters appears OK in the application contros (edits), but Crystal Reports viewer continues showing U.S.English character set.
Please, does anybody know how to set Greek character set for the Crystal Reports?
Any clue should be very appreciated.
Thanks!I've got the same problem. :confused:|||Hey, crystalreportsgurus around the world, wake up!

Character is not valid error

Here's my code:
Dim Cmd as New SQLCommand(sqlString, conn)
cmd.CommandType=CommandType.StoredProcedure
Breaks Here -->>cmd.parameters.add(New SQLParameter(@.OrdAlias, OrdNum))
cmd.parameters.add(New SQLParameter(@.AliasSourceCode, 4))
The error says:
compilation error - -
then, on the line that is in red:
Compiler Error Message:BC30037: Character is not valid.
I have 'OrdNum' declared globally, and OrdNum is assigned right away in the Page_Load event. I've checked the spelling of the SQL parameters (OrdAlias is a varchar, and AliasSourceCode is a tinyInt)
After all this - then, this code runs
Any ideas why I'm getting this error??

Try surrounding your'@.OrdAlias' and the other one, with double quotes

Character is not valid

Hi,

I do not know what I did wrong. I tested a simple function in my custom code and get the error saying "Character is not valid". Any help is appreciated!

Here is the function inside my custom code.

Shared Function test() As string

Dim items As Fields
Return items("DataField1").Value;

End Function

I call the function in the body of my report using: Code.test()

and the error says "There is an error on line 18 of custom code: [BC30037] Character is not valid."

Thanks,

Tabbey

Try to remove " ; " after Return items("DataField1").Value|||Also, you cannot refer to dataset items inside of your Report code block. You must pass values into your function.

Something like this:

Shared Function test(fieldvalue as string) As string
test = fieldvalue

End Function

And then call the function in the body of the report:

=Code.test(ReportItems!DatasetField.Value)

|||

I need to build "fields string" in my custom code depending on the parameters users selected.

If I cannot refer to the dataset items inside the custom code, can you think of any other alternative solution?

I construct the fields string in textbox expression, but I get an error if the field did not exist since IIF evaluate the entire expression.

Thanks,

Tabbey

|||Can you create a function with a signature that accepts all the fields you wish to concatenate?

Function addString (field1 as string, field2 as string, field3 as string) as String
if Parameter!UserInput.Value = true then
addString = field1 & field2 & field3
End Function

You could always create a custom data processing extension and handle it there. you could also probably handle this in the logic of your stored procedure.|||

Andy,

That sounds like a good idea. Let me try that out. Thanks!

Char Trim

Hi,

I imported records from text file using BCP, in one column I am seeing blank space in starting as well in end. I printed the ASCII code it turn out as 32. But when I tried to trim with LTRIM and RTRIM it didn't work. I tried to replace the character using REPLACE witn no avail.

Please help.

Thanks.

You could try the following:

RIGHT(LEFT(ColumnName, LEN(ColumnName) - 1), LEN(ColumnName) - 2)

This will trim the leading and trailing characters.

Is there a chance you could you check the results of the following just to confirm that the ASCII value of the character is 32 ? :

SELECT TOP 1 ASCII(LEFT(ColumnName, 1))
FROM MyTable

Thanks

Chris

|||

I confirmed that the char is 32. I am still seeing the blanks before and after the values.

|||Is the column CHAR or VARCHAR or NVARCHAR?|||

As Dan states, the datatype is interesting here, but if you are using rtrim and ltrim, it shouldn't matter... Please post some sample code like this so we can get an idea of what you are really wanting...

drop table test
go
create table test
(
charValue char(10),
varcharValue varchar(10)
)
go
insert into test
select 'Test' + char(32) ,
'Test' + char(32)
go
select '*' + charValue + '*' as char1,
'*' + ltrim(rtrim(charValue)) + '*' as char2,
'*' + varcharValue + '*' as varchar1,
'*' + ltrim(rtrim(varcharValue)) + '*' as varchar2
from test
go
char1 char2 varchar1 varchar2
*Test * *Test* *Test * *Test*

|||

Louis,

I was surprised to see that your varcharValue included the trailing space. I thought VARCHAR columns removed trailing spaces. So then I went to study the references and found that it depends on ANSI_PADDING in the database.

http://msdn2.microsoft.com/en-us/library/ms175055.aspx

The varchar data type is a variable-length data type. Values shorter than the size of the column are not right-padded to the size of the column. If the ANSI_PADDING option was set to OFF when the column was created, any trailing blanks are truncated from character values stored in the column. If ANSI_PADDING was set ON when the column was created, trailing blanks are not truncated.

Thanks for the education! ;-)

|||Thanks for the added info...|||

Hi,

Thaks for your help.

Column is Varchar(50) also I am seeing following property of column (please note I am using SQLServer 2005 SP1).

TrimTrailingBlank =0

FixedLenNullinSource=0

This column is contains database sizes like 2000 MB

I need to remove the MB thing which I am doing

Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize)))

then I have to convert to Numeric type but when I am trying

Cast(ltrim(rtrim(Substring(DatabaseSize,1,(LEN(DatabaseSize)-CharIndex('M',DatabaseSize))))) as Numeric(20,2))

getting error

Msg 8114, Level 16, State 5, Line 3

Error converting data type varchar to numeric.

Thanks

|||

I think the computation for the third parameter of SUBSTRING is not correct. I think you just want "(CharIndex('M',DatabaseSize)-1)".

declare @.DatabaseSize varchar(50)
set @.DatabaseSize = ' 2000 MB '

print ' ' + cast(LEN(@.DatabaseSize) as varchar)
print ' ' + cast(CharIndex('M',@.DatabaseSize) as varchar)

print ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as Numeric(20,2))
print cast(len(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1))))) as varchar)
print cast(ltrim(rtrim(Substring(@.DatabaseSize,1,(CharIndex('M',@.DatabaseSize) - 1)))) as binary)

Dan

|||

Thanks a lot,

Reason I put Len() function cause we do have some rows where MB was missing so what I did I concanated 'MB' and use ChartIdex -1 and it is working now !!!! All along I was looking at wrong place to resolve the problem.

Cheers.

Char and VarChar Datatype

Hi ,
What is the diff within Char and Varchar ? If define Char(10) in TableA
and this field is store the State code and the length is a non standard. User
can key in the length from 1 to 10.
Understand that I should define this field as VarChar(10) because is it a
no fixed length. But I don't really undertand what is the diff between this
two datatype in the backend structure ? It save the space ?
Travis Tan
In the backend char(10) is always stored as 10 characters (bytes), which
can give you problems when doing string comparisons because the string
will be padded with trailing spaces.
Varchar(10) will store a 32 bit integer (4 bytes) at the front of the
field to indicate the field length, followed by the actual number of
characters in the string.
So if you store a 2 character string in a varchar(10) it will use 6
bytes storage, if you store a 10 character string it will store 14
bytes.
HTH
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

Char and VarChar Datatype

Hi ,
What is the diff within Char and Varchar ? If define Char(10) in TableA
and this field is store the State code and the length is a non standard. Use
r
can key in the length from 1 to 10.
Understand that I should define this field as VarChar(10) because is it a
no fixed length. But I don't really undertand what is the diff between this
two datatype in the backend structure ? It save the space ?
Travis TanIn the backend char(10) is always stored as 10 characters (bytes), which
can give you problems when doing string comparisons because the string
will be padded with trailing spaces.
Varchar(10) will store a 32 bit integer (4 bytes) at the front of the
field to indicate the field length, followed by the actual number of
characters in the string.
So if you store a 2 character string in a varchar(10) it will use 6
bytes storage, if you store a 10 character string it will store 14
bytes.
HTH
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell