Passing result sets: net

Showing posts with label net. Show all posts

Wednesday, March 28, 2012

PDF Render Problem

Hello,
Below is the code I have written using SOAP API, VB.Net and ASP.net, so that
user can download the report onto their local drive. everything works fine
except when they open the file it gives errors ' There was an error opening
this document. The file is damaged and could not be repaired'. Please let me
know what is wrong with this code.
Thanks
_sReportLocation = "/BAOS Reporting/DashBoardReport/RepoDashBoard"
_S_DEVICE_INFO = "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>true</HTMLFragment></DeviceInfo>"
_bResults = rs.Render(_sReportLocation, "PDF", Nothing,
_S_DEVICE_INFO, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,
Nothing, Nothing)
Response.Clear()
Response.ContentType = "application/pdf"
If _sMimeType <> "text/html" Then
Response.AddHeader("Content-Disposition", "attachment;
filename=" & "BarChart.pdf")
End If
Response.BinaryWrite(_bResults)
--
Raj ChandraDoes it work on some computers but not all? This may sound kind of silly,
but we have seen similar problems with Adobe before.
Try deleting all of temp internet files on the client computer and running
the report. Its worked for us in the past when the report runs fine on one
computer but not the next.
"Raj Chandra" wrote:
> Hello,
>
> Below is the code I have written using SOAP API, VB.Net and ASP.net, so that
> user can download the report onto their local drive. everything works fine
> except when they open the file it gives errors ' There was an error opening
> this document. The file is damaged and could not be repaired'. Please let me
> know what is wrong with this code.
> Thanks
> _sReportLocation = "/BAOS Reporting/DashBoardReport/RepoDashBoard"
> _S_DEVICE_INFO => "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>true</HTMLFragment></DeviceInfo>"
> _bResults = rs.Render(_sReportLocation, "PDF", Nothing,
> _S_DEVICE_INFO, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,
> Nothing, Nothing)
> Response.Clear()
> Response.ContentType = "application/pdf"
> If _sMimeType <> "text/html" Then
> Response.AddHeader("Content-Disposition", "attachment;
> filename=" & "BarChart.pdf")
> End If
> Response.BinaryWrite(_bResults)
> --
> Raj Chandra|||Hi Pinolian, thanks for the response.
I tried on a different machine it did not work and I also tried after
deleting temp files same result.
"Pinolian" wrote:
> Does it work on some computers but not all? This may sound kind of silly,
> but we have seen similar problems with Adobe before.
> Try deleting all of temp internet files on the client computer and running
> the report. Its worked for us in the past when the report runs fine on one
> computer but not the next.
>
> "Raj Chandra" wrote:
> > Hello,
> >
> >
> > Below is the code I have written using SOAP API, VB.Net and ASP.net, so that
> > user can download the report onto their local drive. everything works fine
> > except when they open the file it gives errors ' There was an error opening
> > this document. The file is damaged and could not be repaired'. Please let me
> > know what is wrong with this code.
> >
> > Thanks
> >
> > _sReportLocation = "/BAOS Reporting/DashBoardReport/RepoDashBoard"
> > _S_DEVICE_INFO => > "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>true</HTMLFragment></DeviceInfo>"
> >
> > _bResults = rs.Render(_sReportLocation, "PDF", Nothing,
> > _S_DEVICE_INFO, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,
> > Nothing, Nothing)
> >
> > Response.Clear()
> > Response.ContentType = "application/pdf"
> >
> > If _sMimeType <> "text/html" Then
> > Response.AddHeader("Content-Disposition", "attachment;
> > filename=" & "BarChart.pdf")
> > End If
> > Response.BinaryWrite(_bResults)
> > --
> > Raj Chandra|||Have you installed Reporting Services Service Pack 1?
"Raj Chandra" wrote:
> Hi Pinolian, thanks for the response.
> I tried on a different machine it did not work and I also tried after
> deleting temp files same result.
>
> "Pinolian" wrote:
> >
> > Does it work on some computers but not all? This may sound kind of silly,
> > but we have seen similar problems with Adobe before.
> >
> > Try deleting all of temp internet files on the client computer and running
> > the report. Its worked for us in the past when the report runs fine on one
> > computer but not the next.
> >
> >
> > "Raj Chandra" wrote:
> >
> > > Hello,
> > >
> > >
> > > Below is the code I have written using SOAP API, VB.Net and ASP.net, so that
> > > user can download the report onto their local drive. everything works fine
> > > except when they open the file it gives errors ' There was an error opening
> > > this document. The file is damaged and could not be repaired'. Please let me
> > > know what is wrong with this code.
> > >
> > > Thanks
> > >
> > > _sReportLocation = "/BAOS Reporting/DashBoardReport/RepoDashBoard"
> > > _S_DEVICE_INFO => > > "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>true</HTMLFragment></DeviceInfo>"
> > >
> > > _bResults = rs.Render(_sReportLocation, "PDF", Nothing,
> > > _S_DEVICE_INFO, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,
> > > Nothing, Nothing)
> > >
> > > Response.Clear()
> > > Response.ContentType = "application/pdf"
> > >
> > > If _sMimeType <> "text/html" Then
> > > Response.AddHeader("Content-Disposition", "attachment;
> > > filename=" & "BarChart.pdf")
> > > End If
> > > Response.BinaryWrite(_bResults)
> > > --
> > > Raj Chandra|||Yes, I have RS SP1 installed. XL file works fine I am trouble only with PDF
files.
"Kevin Weir" wrote:
> Have you installed Reporting Services Service Pack 1?
>
> "Raj Chandra" wrote:
> > Hi Pinolian, thanks for the response.
> >
> > I tried on a different machine it did not work and I also tried after
> > deleting temp files same result.
> >
> >
> > "Pinolian" wrote:
> >
> > >
> > > Does it work on some computers but not all? This may sound kind of silly,
> > > but we have seen similar problems with Adobe before.
> > >
> > > Try deleting all of temp internet files on the client computer and running
> > > the report. Its worked for us in the past when the report runs fine on one
> > > computer but not the next.
> > >
> > >
> > > "Raj Chandra" wrote:
> > >
> > > > Hello,
> > > >
> > > >
> > > > Below is the code I have written using SOAP API, VB.Net and ASP.net, so that
> > > > user can download the report onto their local drive. everything works fine
> > > > except when they open the file it gives errors ' There was an error opening
> > > > this document. The file is damaged and could not be repaired'. Please let me
> > > > know what is wrong with this code.
> > > >
> > > > Thanks
> > > >
> > > > _sReportLocation = "/BAOS Reporting/DashBoardReport/RepoDashBoard"
> > > > _S_DEVICE_INFO => > > > "<DeviceInfo><Toolbar>False</Toolbar><HTMLFragment>true</HTMLFragment></DeviceInfo>"
> > > >
> > > > _bResults = rs.Render(_sReportLocation, "PDF", Nothing,
> > > > _S_DEVICE_INFO, Nothing, Nothing, Nothing, Nothing, Nothing, Nothing,
> > > > Nothing, Nothing)
> > > >
> > > > Response.Clear()
> > > > Response.ContentType = "application/pdf"
> > > >
> > > > If _sMimeType <> "text/html" Then
> > > > Response.AddHeader("Content-Disposition", "attachment;
> > > > filename=" & "BarChart.pdf")
> > > > End If
> > > > Response.BinaryWrite(_bResults)
> > > > --
> > > > Raj Chandra

Wednesday, March 21, 2012

Pausing-Restarting SQL Server Service

Hi,
When I use NET PAUSE in cmd mode, the service pauses successfully but when I
issue NET START again, the service stays in pause state unless I use service
manager to start it.
Any help would be greatly appreciated,
LeilaYou need to use NET CONTINUE
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23eb9TK6AGHA.984@.TK2MSFTNGP12.phx.gbl...
> Hi,
> When I use NET PAUSE in cmd mode, the service pauses successfully but when
> I
> issue NET START again, the service stays in pause state unless I use
> service
> manager to start it.
> Any help would be greatly appreciated,
> Leila
>|||Thanks indeed!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:#Jdojl8AGHA.360@.TK2MSFTNGP09.phx.gbl...
> You need to use NET CONTINUE
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23eb9TK6AGHA.984@.TK2MSFTNGP12.phx.gbl...
when[vbcol=seagreen]
>|||
>Hi,
>When I use NET PAUSE in cmd mode, the service pauses successfully but when
I
>issue NET START again, the service stays in pause state unless I use servic
e
>manager to start it.
>Any help would be greatly appreciated,
>Leila
>
>
You need to use NET CONTINUE. NET START won't do anything - it's already
started.
Cheers -
Simon

Pausing-Restarting SQL Server Service

Hi,
When I use NET PAUSE in cmd mode, the service pauses successfully but when I
issue NET START again, the service stays in pause state unless I use service
manager to start it.
Any help would be greatly appreciated,
LeilaYou need to use NET CONTINUE
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23eb9TK6AGHA.984@.TK2MSFTNGP12.phx.gbl...
> Hi,
> When I use NET PAUSE in cmd mode, the service pauses successfully but when
> I
> issue NET START again, the service stays in pause state unless I use
> service
> manager to start it.
> Any help would be greatly appreciated,
> Leila
>|||Thanks indeed!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:#Jdojl8AGHA.360@.TK2MSFTNGP09.phx.gbl...
> You need to use NET CONTINUE
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23eb9TK6AGHA.984@.TK2MSFTNGP12.phx.gbl...
> > Hi,
> > When I use NET PAUSE in cmd mode, the service pauses successfully but
when
> > I
> > issue NET START again, the service stays in pause state unless I use
> > service
> > manager to start it.
> > Any help would be greatly appreciated,
> > Leila
> >
> >
>|||>Hi,
>When I use NET PAUSE in cmd mode, the service pauses successfully but when I
>issue NET START again, the service stays in pause state unless I use service
>manager to start it.
>Any help would be greatly appreciated,
>Leila
>
>
You need to use NET CONTINUE. NET START won't do anything - it's already
started.
Cheers -
Simon

Pausing-Restarting SQL Server Service

Hi,
When I use NET PAUSE in cmd mode, the service pauses successfully but when I
issue NET START again, the service stays in pause state unless I use service
manager to start it.
Any help would be greatly appreciated,
Leila
You need to use NET CONTINUE
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23eb9TK6AGHA.984@.TK2MSFTNGP12.phx.gbl...
> Hi,
> When I use NET PAUSE in cmd mode, the service pauses successfully but when
> I
> issue NET START again, the service stays in pause state unless I use
> service
> manager to start it.
> Any help would be greatly appreciated,
> Leila
>
|||Thanks indeed!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:#Jdojl8AGHA.360@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> You need to use NET CONTINUE
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23eb9TK6AGHA.984@.TK2MSFTNGP12.phx.gbl...
when
>
|||
>Hi,
>When I use NET PAUSE in cmd mode, the service pauses successfully but when I
>issue NET START again, the service stays in pause state unless I use service
>manager to start it.
>Any help would be greatly appreciated,
>Leila
>
>
You need to use NET CONTINUE. NET START won't do anything - it's already
started.
Cheers -
Simon
sql

Pausing-Restarting SQL Server Service

Hi,
When I use NET PAUSE in cmd mode, the service pauses successfully but when I
issue NET START again, the service stays in pause state unless I use service
manager to start it.
Any help would be greatly appreciated,
LeilaYou need to use NET CONTINUE
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Leila" <Leilas@.hotpop.com> wrote in message
news:%23eb9TK6AGHA.984@.TK2MSFTNGP12.phx.gbl...
> Hi,
> When I use NET PAUSE in cmd mode, the service pauses successfully but when
> I
> issue NET START again, the service stays in pause state unless I use
> service
> manager to start it.
> Any help would be greatly appreciated,
> Leila
>|||Thanks indeed!
"Jasper Smith" <jasper_smith9@.hotmail.com> wrote in message
news:#Jdojl8AGHA.360@.TK2MSFTNGP09.phx.gbl...
> You need to use NET CONTINUE
> --
> HTH
> Jasper Smith (SQL Server MVP)
> http://www.sqldbatips.com
> I support PASS - the definitive, global
> community for SQL Server professionals -
> http://www.sqlpass.org
> "Leila" <Leilas@.hotpop.com> wrote in message
> news:%23eb9TK6AGHA.984@.TK2MSFTNGP12.phx.gbl...
when
>|||
>Hi,
>When I use NET PAUSE in cmd mode, the service pauses successfully but when
I
>issue NET START again, the service stays in pause state unless I use servic
e
>manager to start it.
>Any help would be greatly appreciated,
>Leila
>
>
You need to use NET CONTINUE. NET START won't do anything - it's already
started.
Cheers -
Simon

patindex with nvarchar

Hi Microsoft,

My Name is Harshal Choksi. i am working in microsoft technolgy in .NET and SQL Server 2000. we are storing some unicode data in database in table which has a nvarchar data types. i have written one function in SQL which contains some T-Transact function like SubString, Len & PatIndex. in which i m not getting any value with PatIndex, it shows me always column with 0 value.

Means my patindex is not working well if i have 'hindi text'.

GO
SELECT PATINDEX ('%??????%', keyword)

FROM tblKeywords where keywordID = 68
GO

where i tblKeyword is a tableName and keyword is a column name. "??????" is text what my parameter in function .keyword is column name which has a datatype NVARCHAR. i m getting result with in result wizard 0 each time, instead of "??????", if i write something "London",then it will show me right value.

so please help in this manner. i would be feel great if you would me help so. i have to implement some search functionality with hindi word..... i could use Contains keyword in SQL in wheere condion but i want to use patIndex only. so help me as soon as possible....

I will be waiting for your reply.

Thanks in advance.

You have to put N prefix on Nvarchar String values

See the sample here..

Code Snippet

SELECT PATINDEX ('%??????%', N'??????????????')

--Output : 0

SELECT PATINDEX (N'%??????%', N'??????????????')

--Output : 9

--InYour Query

SELECT

PATINDEX (N'%??????%', keyword)

FROM

tblKeywords where keywordID = 68

|||

Hi Manivannan, Thanks for your help.. yet still not getting output. i have written function is SQL as below,u might have some sort of idea about it. & this function i m using in my stored procedure... which also i am mentioning below this SQL function.

--SQL Function

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

--
-- GetKeywordsFromQuery(query)
-- SUMMARY Parses a full-text query for keywords that can be used in a keyword (rather
-- than full-text) search
-- PARAMS query Query containing one or more keywords
-- RETURNS A temporary TABLE containing the found keywords
--
ALTER FUNCTION GetKeywordsFromQuery
(
@.query nvarchar(255)
)
RETURNS @.words TABLE (Word nvarchar(255)COLLATE DATABASE_Default)
AS
BEGIN
-- Define processing flags
-- NOTE A processing flag is used to ensure that words are excluded from
-- NOTE the results if they are preceeded with a "NOT" or that a complete
-- NOTE phrase is added as a whole (rather than seperate words)
DECLARE @.isNotWord bit
SET @.isNotWord = 0
DECLARE @.isPhrase bit
SET @.isPhrase = 0
-- Define word variables
DECLARE @.word AS nvarchar(255)
DECLARE @.phrase AS nvarchar(255)
DECLARE @.substring AS nvarchar(255)
SET @.substring = @.query
-- Find the first space
DECLARE @.spacePosition AS bigint
SET @.spacePosition = PATINDEX(N'% %', @.query)
-- Iterate over the query until all words have been found
WHILE 0 <= @.spacePosition
BEGIN
-- Get the next word in the query
IF(0 != @.spacePosition)
SET @.word = SUBSTRING(@.substring, 1, (@.spacePosition -1))
ELSE
BEGIN
SET @.word = @.substring
SET @.spacePosition = -1
END
-- Check for a phrase
IF(N'"' = SUBSTRING(@.word, 1, 1))
BEGIN
-- Start the phrase
SET @.isPhrase = 1
SET @.phrase = SUBSTRING(@.word, 2, LEN(@.word) - 1)
END
ELSE IF(N'"' = SUBSTRING(@.word, LEN(@.word), 1))
BEGIN
-- Complete the phrase
SET @.isPhrase = 0
SET @.phrase = @.phrase + SPACE(1) + SUBSTRING(@.word, 1, LEN(@.word) - 1)
SET @.word = @.phrase
END

ELSE IF(1 = @.isPhrase)
-- Append the current word to the phrase
SET @.phrase = @.phrase + SPACE(1) + @.word
-- Add the word to temporary table
IF (UPPER('NEAR') != UPPER(@.word))
AND (UPPER('AND') != UPPER(@.word))
AND (UPPER('NOT') != UPPER(@.word))
AND (0 = @.isNotWord)
AND (0 = @.isPhrase)
BEGIN
-- This word can be used to search for keywords
INSERT @.words VALUES(@.word)
SET @.isNotWord = 0
END
-- Reset the "Not Word" exclusion flag
-- NOTE This is for cases where an AND or NEAR follows an AND NOT
ELSE IF (UPPER('NEAR') = UPPER(@.word))
OR (UPPER('AND') = UPPER(@.word))
SET @.isNotWord = 0
-- Indicate that the next word needs to be excluded
ELSE IF (UPPER('NOT') = UPPER(@.word))
SET @.isNotWord = 1
-- Move on to the next word or exit the WHILE
SET @.substring = SUBSTRING(@.substring, (@.spacePosition + 1), LEN(@.substring) - (@.spacePosition))
IF(-1 != @.spacePosition)
SET @.spacePosition = PATINDEX(N'% %', @.substring)
END
RETURN
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Here this is my sp:

SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN tblKeywords AS k ON k.KeywordId = c.KeywordId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
WHERE c.SiteId = @.siteId
AND c.StatusID = 2
AND c.DeletedBy IS NULL

so i have to sort out this this thing as well,can you please look at thru my function as well stored procedure.

Thanks in Advance.

|||

so please give me solution as possible, here i have written my stored procedure as below which use above function:

I am using for all column nvarchar only in which i stored hindi text and English as well.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--
-- spSearch
-- SUMMARY Executes a search using the specified query
-- PARAMS @.siteId Identifier of the site from which the search request was generated
-- @.query Full-text query to be executed against the full-text engine
--
ALTER PROCEDURE spSearch
(
@.siteId INT,
@.textOnly BIT,
@.query NVARCHAR(4000)
)
AS
-- Select Alternatives
Declare @.partialQuery NVARCHAR(50)
Declare @.keyphrase NVARCHAR(50)
--Query can be provided like 'searchterm' or as "search term" depending in existance of space character
--must format the partial query if it contains a space like '"search term*"'
--the keyphrase is @.query without quotes
IF CHARINDEX('"',@.query) > 0
BEGIN
SET @.partialQuery = LEFT(@.query, LEN(@.query) - 1) + '*"'
SET @.keyphrase = LEFT(RIGHT(@.query, LEN(@.query) - 1), LEN(@.query) - 2)
END
ELSE
BEGIN
--must format the partial query like '"searchterm*"'
set @.partialQuery = ' "' + @.query + '*" '
set @.keyphrase = @.query
END

--Now we can search for alternatives as exact match of @.query on alternatives
--or partial match on keyword, but ignore exact match on keyword (i.e. only alternatives to @.query)
SELECT *
FROM tblKeywords
WHERE NOT Keyword = @.keyphrase
AND SiteId = @.siteId
AND (
CONTAINS(Keyword, @.partialQuery )
)

-- Select the pages that match the query
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c.MasterId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
WHERE c.SiteId = @.siteId
AND c.StatusID = 2
AND c.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
UNION -- the following selects list pages that match, with primarylist page as their navigationid
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN tblContents AS c1 ON c.PrimaryListMasterId = c1.MasterId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c1.MasterId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE c.SiteId = @.siteId
AND c.DeletedBy IS NULL
AND c.StatusID = 2
AND c1.statusid = 2
AND c1.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
AND lst.ContentMasterId = c.PrimaryListMasterId

-- Select the downloads that match the query
SELECT DISTINCT r.ResourceId, r.ResourceName, r.ResourceLongSummary, r.FileSize
FROM tblResources AS r
INNER JOIN trelKeywordResources AS kr ON kr.ResourceId = r.ResourceId
INNER JOIN tblKeywords AS k ON k.KeywordId = kr.KeywordId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN trelContentItemResources AS cir ON r.ResourceId = cir.ResourceId
WHERE r.ResourceTypeId = 2
AND r.SiteId = @.siteId
AND cir.StatusId = 2

SELECT CASE WHEN nc.MasterId IS NULL THEN cir.MasterId ELSE nc.MasterId END AS MasterId, CASE WHEN nc.MasterId IS NULL
THEN ln.NavigationId ELSE nc.NavigationId END AS NavigationId, c.ContentName, res.ResourceId, res.ResourceTextType, res.ResourceText,
c.ContentLongSummary
FROM tblResources res INNER JOIN
trelContentItemResources cir ON cir.ResourceId = res.ResourceId
LEFT OUTER JOIN trelNavigationContents nc ON nc.MasterId = cir.MasterId
LEFT OUTER JOIN tblContents c ON c.MasterId = cir.MasterId AND c.StatusId = cir.StatusId
LEFT OUTER JOIN trelNavigationContents ln ON c.PrimaryListMasterId = ln.MasterId
LEFT OUTER JOIN tblNavigation nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tlkpResourceTypes rt ON rt.ResourceTypeId = res.ResourceTypeId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE (res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (nc.MasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
OR
(res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (c.PrimaryListMasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
ORDER BY rt.SearchResultOrder, c.ContentName

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||

Your query is working fine on my mac..

I tried with following statement ...

Code Snippet

Select * From dbo.GetKeywordsFromQuery(N'"SQL Server" is "very good" AND "very Powerfull" ?????? ?????? AND ?????? ????????????????????????')

OUTPUT:

Word

SQL Server

very good

very Powerfull

??????

????????????????????????

What I am doubting here is @.QUERY parameter.

Pls check the datatype. And when you call the SP you should prefix the N.

Example:

Code Snippet

Exec dbo.YourSP @.query = N'"SQL Server" is "very good" AND "very Powerfull" ?????? ?????? AND ?????? ????????????????????????')

|||

yeah thanks for your help,but can you check my storedProcedure as well?

Please look at it & if you have any idea of it.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--
-- spSearch
-- SUMMARY Executes a search using the specified query
-- PARAMS @.siteId Identifier of the site from which the search request was generated
-- @.query Full-text query to be executed against the full-text engine
--
ALTER PROCEDURE spSearch
(
@.siteId INT,
@.textOnly BIT,
@.query NVARCHAR(4000)
)
AS
-- Select Alternatives
Declare @.partialQuery NVARCHAR(50)
Declare @.keyphrase NVARCHAR(50)
--Query can be provided like 'searchterm' or as "search term" depending in existance of space character
--must format the partial query if it contains a space like '"search term*"'
--the keyphrase is @.query without quotes
IF CHARINDEX(N'"',@.query) > 0
BEGIN
SET @.partialQuery = LEFT(@.query, LEN(@.query) - 1) + N'*"'
SET @.keyphrase = LEFT(RIGHT(@.query, LEN(@.query) - 1), LEN(@.query) - 2)
END
ELSE
BEGIN
--must format the partial query like '"searchterm*"'
set @.partialQuery = N' "' + @.query + N'*" '
set @.keyphrase = @.query
END

-- Select the pages that match the query
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c.MasterId
INNER JOIN GetKeywordsFromQuery (@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
WHERE c.SiteId = @.siteId
AND c.StatusID = 2
AND c.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
UNION -- the following selects list pages that match, with primarylist page as their navigationid
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN tblContents AS c1 ON c.PrimaryListMasterId = c1.MasterId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c1.MasterId
INNER JOIN GetKeywordsFromQuery (@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE c.SiteId = @.siteId
AND c.DeletedBy IS NULL
AND c.StatusID = 2
AND c1.statusid = 2
AND c1.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
AND lst.ContentMasterId = c.PrimaryListMasterId

-- Select the downloads that match the query
SELECT DISTINCT r.ResourceId, r.ResourceName, r.ResourceLongSummary, r.FileSize
FROM tblResources AS r
INNER JOIN trelKeywordResources AS kr ON kr.ResourceId = r.ResourceId
INNER JOIN tblKeywords AS k ON k.KeywordId = kr.KeywordId
INNER JOIN GetKeywordsFromQuery (@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN trelContentItemResources AS cir ON r.ResourceId = cir.ResourceId
WHERE r.ResourceTypeId = 2
AND r.SiteId = @.siteId
AND cir.StatusId = 2

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

you call the SP you should prefix the N

in this sp, i m using three times that function. even i m not getting how to call the sp with it should have prefix the N....please let me know. i am just waiting for your reply.even i have using NVARCHAR whereever i stored a hindi text...i hope you have already seen my function.please do the needful. i have tried lot since few days on this, still couldn't get the right solution.

|||

can u tell me how you are executing your sp ..

I need the Exec spSearch .... statement

|||

well i don't have written something like Exec spSearch statement, actually i m calling this function from my ASP.NET application that's all. passing a 3 parameters and wanted to get results accordingly. Waiting for your reply.

Thanks Again for your response.

|||

hey check your ASP.NET code, the param declaration. It should be NVARCHAR.

Test your SP from Query Analyser using EXEC statement.. So you can validate where the problem resides.

|||

well,even not my sp giving me results at all if i pass a parameters,i checked using Exec statement also,not getting result..in ASP.NET i have written someting:--

public virtual SafeSqlDataReader GetSearchResults(int siteId, bool textOnly, string input)

{

// Define the command

SqlCommand command = new SqlCommand();

command.CommandType = CommandType.StoredProcedure;

command.CommandText = StoredProcedures.spSearch.ToString();

// Set the parameters

command.Parameters.Add("@.siteId", siteId);

command.Parameters.Add("@.textOnly", textOnly);

command.Parameters.Add("@.query", input);

command.Connection = SqlHelperWrapper.OpenConnection(this.ConnectionString);

return new SafeSqlDataReader(command);

}

so not getting a problem where it might be? if i have declared varchar here also then problem still remain same for the Stored Procedure because not giving me result at all if i pass a parameter in stored procedure,so problem is with sp and then we solved a problem with our code.

waiting for your reply.

|||

Hi still i am waiting for your response. Let me know if any solution you have for this, i am not getting this thing, i have written in my .NET code varchar , but if i pass parameters for hindi, not getting a results at all. so stored procedure may have some problem.

Please give me any solution for this.

Waiting for your response soon.

|||

how is it work with Patindex if i have one column which have data type 'Image', i want to search some hindi text within that, i have written a function in SQL which contains some Patindex and some string function as well. i have written something like:- PATINDEX(N'% %', @.query), but i want to find a some value with the help of stored procesedure:

here is my query which is a part of my stored procedure:

SELECT DISTINCT r.ResourceId, r.ResourceName, r.ResourceLongSummary, r.FileSize
FROM tblResources AS r
INNER JOIN trelKeywordResources AS kr ON kr.ResourceId = r.ResourceId
INNER JOIN tblKeywords AS k ON k.KeywordId = kr.KeywordId
INNER JOIN GetKeywordsFromQuery(@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN trelContentItemResources AS cir ON r.ResourceId = cir.ResourceId
WHERE r.ResourceTypeId = 2
AND r.SiteId = @.siteId
AND cir.StatusId = 2
AND k.SiteId = @.siteId

SELECT CASE WHEN nc.MasterId IS NULL THEN cir.MasterId ELSE nc.MasterId END AS MasterId, CASE WHEN nc.MasterId IS NULL
THEN ln.NavigationId ELSE nc.NavigationId END AS NavigationId, c.ContentName, res.ResourceId, res.ResourceTextType, res.ResourceText,
c.ContentLongSummary
FROM tblResources res INNER JOIN
trelContentItemResources cir ON cir.ResourceId = res.ResourceId
LEFT OUTER JOIN trelNavigationContents nc ON nc.MasterId = cir.MasterId
LEFT OUTER JOIN tblContents c ON c.MasterId = cir.MasterId AND c.StatusId = cir.StatusId
LEFT OUTER JOIN trelNavigationContents ln ON c.PrimaryListMasterId = ln.MasterId
LEFT OUTER JOIN tblNavigation nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tlkpResourceTypes rt ON rt.ResourceTypeId = res.ResourceTypeId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE (res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (nc.MasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
OR
(res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (c.PrimaryListMasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
ORDER BY rt.SearchResultOrder, c.ContentName

where ResourceText is having a datatype 'Image', so could you tell me how to use that function as well.

i will be waiting for your response soon.

Thanking You.

|||

Are you using Image datatype for storing information .. .

You can't do any Text/String manipulation on Image datatype.

Can I know why you are using image datatype here.

|||

yeah we have taken its an image data type because we have an editor somethning like this in which we are writing but it may have some image as well. so we taken as image data type.

0x4C6F6E646F6E206973206120636974792E3C7370616E207374796C653D22464F4E542D53495A453A2031307074223E203C703EE0A4B5E0A4BEE0A4B2E0A58DE0A4AEE0A580E0A495E0A4BF3C2F703E3C2F7370616E3E

i am having some value like that in Resource Text. so we have an editor complety & we are storing some text as well as possible to stored images as well in editor. but if we find english keyword then it works fine, else it is not suported with hindi keyword. so tell me solution.

|||

HarshalChoksi wrote:

but if we find english keyword then it works fine, else it is not suported with hindi keyword. so tell me solution.

This is not bcs of the SQL server. There is a problem on your Resource Text.

The Unicode english codes & ASCII codes are same. So it will work fine. But your Building the Resource Text somehow failed to create a Unicode text.

You have to look the solution on that area.. Not in SQL Server

patindex with nvarchar

Hi Microsoft,

Means my patindex is not working well if i have 'hindi text'.

GO
SELECT PATINDEX ('%??????%', keyword)

FROM tblKeywords where keywordID = 68
GO

I will be waiting for your reply.

Thanks in advance.

You have to put N prefix on Nvarchar String values

See the sample here..

Code Snippet

SELECT PATINDEX ('%??????%', N'??????????????')

--Output : 0

SELECT PATINDEX (N'%??????%', N'??????????????')

--Output : 9

--InYour Query

SELECT

PATINDEX (N'%??????%', keyword)

FROM

tblKeywords where keywordID = 68

|||

--SQL Function

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

Here this is my sp:

so i have to sort out this this thing as well,can you please look at thru my function as well stored procedure.

Thanks in Advance.

|||

so please give me solution as possible, here i have written my stored procedure as below which use above function:

I am using for all column nvarchar only in which i stored hindi text and English as well.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

|||

Your query is working fine on my mac..

I tried with following statement ...

Code Snippet

Select * From dbo.GetKeywordsFromQuery(N'"SQL Server" is "very good" AND "very Powerfull" ?????? ?????? AND ?????? ????????????????????????')

OUTPUT:

Word

SQL Server

very good

very Powerfull

??????

????????????????????????

What I am doubting here is @.QUERY parameter.

Pls check the datatype. And when you call the SP you should prefix the N.

Example:

Code Snippet

Exec dbo.YourSP @.query = N'"SQL Server" is "very good" AND "very Powerfull" ?????? ?????? AND ?????? ????????????????????????')

|||

yeah thanks for your help,but can you check my storedProcedure as well?

Please look at it & if you have any idea of it.

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

--
-- spSearch
-- SUMMARY Executes a search using the specified query
-- PARAMS @.siteId Identifier of the site from which the search request was generated
-- @.query Full-text query to be executed against the full-text engine
--
ALTER PROCEDURE spSearch
(
@.siteId INT,
@.textOnly BIT,
@.query NVARCHAR(4000)
)
AS
-- Select Alternatives
Declare @.partialQuery NVARCHAR(50)
Declare @.keyphrase NVARCHAR(50)
--Query can be provided like 'searchterm' or as "search term" depending in existance of space character
--must format the partial query if it contains a space like '"search term*"'
--the keyphrase is @.query without quotes
IF CHARINDEX(N'"',@.query) > 0
BEGIN
SET @.partialQuery = LEFT(@.query, LEN(@.query) - 1) + N'*"'
SET @.keyphrase = LEFT(RIGHT(@.query, LEN(@.query) - 1), LEN(@.query) - 2)
END
ELSE
BEGIN
--must format the partial query like '"searchterm*"'
set @.partialQuery = N' "' + @.query + N'*" '
set @.keyphrase = @.query
END

-- Select the pages that match the query
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c.MasterId
INNER JOIN GetKeywordsFromQuery (@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
WHERE c.SiteId = @.siteId
AND c.StatusID = 2
AND c.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
UNION -- the following selects list pages that match, with primarylist page as their navigationid
SELECT DISTINCT c.ContentName, c.ContentLongSummary, c.MasterId, nc.NavigationId
FROM tblContents AS c
INNER JOIN trelKeywords AS ck ON ck.MasterId = c.MasterId
INNER JOIN tblKeywords AS k ON k.KeywordId = ck.KeywordId
INNER JOIN tblContents AS c1 ON c.PrimaryListMasterId = c1.MasterId
INNER JOIN trelNavigationContents AS nc ON nc.MasterId = c1.MasterId
INNER JOIN GetKeywordsFromQuery (@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN tblNavigation AS nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE c.SiteId = @.siteId
AND c.DeletedBy IS NULL
AND c.StatusID = 2
AND c1.statusid = 2
AND c1.DeletedBy IS NULL
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
AND lst.ContentMasterId = c.PrimaryListMasterId

-- Select the downloads that match the query
SELECT DISTINCT r.ResourceId, r.ResourceName, r.ResourceLongSummary, r.FileSize
FROM tblResources AS r
INNER JOIN trelKeywordResources AS kr ON kr.ResourceId = r.ResourceId
INNER JOIN tblKeywords AS k ON k.KeywordId = kr.KeywordId
INNER JOIN GetKeywordsFromQuery (@.query) AS kq ON kq.Word = k.Keyword
INNER JOIN trelContentItemResources AS cir ON r.ResourceId = cir.ResourceId
WHERE r.ResourceTypeId = 2
AND r.SiteId = @.siteId
AND cir.StatusId = 2

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

you call the SP you should prefix the N

|||

can u tell me how you are executing your sp ..

I need the Exec spSearch .... statement

|||

Thanks Again for your response.

|||

hey check your ASP.NET code, the param declaration. It should be NVARCHAR.

Test your SP from Query Analyser using EXEC statement.. So you can validate where the problem resides.

|||

well,even not my sp giving me results at all if i pass a parameters,i checked using Exec statement also,not getting result..in ASP.NET i have written someting:--

public virtual SafeSqlDataReader GetSearchResults(int siteId, bool textOnly, string input)

{

// Define the command

SqlCommand command = new SqlCommand();

command.CommandType = CommandType.StoredProcedure;

command.CommandText = StoredProcedures.spSearch.ToString();

// Set the parameters

command.Parameters.Add("@.siteId", siteId);

command.Parameters.Add("@.textOnly", textOnly);

command.Parameters.Add("@.query", input);

command.Connection = SqlHelperWrapper.OpenConnection(this.ConnectionString);

return new SafeSqlDataReader(command);

}

waiting for your reply.

|||

Please give me any solution for this.

Waiting for your response soon.

|||

here is my query which is a part of my stored procedure:

SELECT CASE WHEN nc.MasterId IS NULL THEN cir.MasterId ELSE nc.MasterId END AS MasterId, CASE WHEN nc.MasterId IS NULL
THEN ln.NavigationId ELSE nc.NavigationId END AS NavigationId, c.ContentName, res.ResourceId, res.ResourceTextType, res.ResourceText,
c.ContentLongSummary
FROM tblResources res INNER JOIN
trelContentItemResources cir ON cir.ResourceId = res.ResourceId
LEFT OUTER JOIN trelNavigationContents nc ON nc.MasterId = cir.MasterId
LEFT OUTER JOIN tblContents c ON c.MasterId = cir.MasterId AND c.StatusId = cir.StatusId
LEFT OUTER JOIN trelNavigationContents ln ON c.PrimaryListMasterId = ln.MasterId
LEFT OUTER JOIN tblNavigation nav ON nc.NavigationId = nav.NavigationId
INNER JOIN tlkpResourceTypes rt ON rt.ResourceTypeId = res.ResourceTypeId
INNER JOIN tblListContents AS lst ON lst.ListItemMasterId = c.MasterId
WHERE (res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (nc.MasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
OR
(res.SiteId = @.siteId)
AND (res.ResourceTypeId = 1 OR res.ResourceTypeId = 6)
AND CONTAINS(res.ResourceText, @.query)
AND (c.ContentPublicationDate IS NOT NULL)
AND (c.DeletedBy IS NULL)
AND (c.StatusId = 2)
AND (c.PrimaryListMasterId IS NOT NULL)
AND ( (@.textOnly=0) or (nav.ExcludeFromTextSite=0 and @.textOnly=1) )
ORDER BY rt.SearchResultOrder, c.ContentName

where ResourceText is having a datatype 'Image', so could you tell me how to use that function as well.

i will be waiting for your response soon.

Thanking You.

|||

Are you using Image datatype for storing information .. .

You can't do any Text/String manipulation on Image datatype.

Can I know why you are using image datatype here.

|||

yeah we have taken its an image data type because we have an editor somethning like this in which we are writing but it may have some image as well. so we taken as image data type.

0x4C6F6E646F6E206973206120636974792E3C7370616E207374796C653D22464F4E542D53495A453A2031307074223E203C703EE0A4B5E0A4BEE0A4B2E0A58DE0A4AEE0A580E0A495E0A4BF3C2F703E3C2F7370616E3E

|||

HarshalChoksi wrote:

but if we find english keyword then it works fine, else it is not suported with hindi keyword. so tell me solution.

This is not bcs of the SQL server. There is a problem on your Resource Text.

The Unicode english codes & ASCII codes are same. So it will work fine. But your Building the Resource Text somehow failed to create a Unicode text.

You have to look the solution on that area.. Not in SQL Server

Monday, March 12, 2012

password storage

hai all!
i am going to store my user details to login from vb.net in a user table in sql.i would like to disguise the password column with dots or stars.how do i do that?kindly help.its urgent.i also need to compare the password right.so it should be readable by the program

Quote:

Originally Posted by pangsans

i doubt if you can revert back to the original string from "dots or stars", what you could is to use an encrypting function...you may create your own or find one on the net|||

Quote:

Originally Posted by ck9663

i doubt if you can revert back to the original string from "dots or stars", what you could is to use an encrypting function...you may create your own or find one on the net

thanks yaar
give me some more info yaar|||That would depend on how you want to encrypt it. one way or 2 way?
and syntax will differ from language to language :)
cheers

Friday, March 9, 2012

Password protected Access 2000 DB

Hi everyone,

I'm having a problem connecting to my Access 2000 DB from VB.net 2003. The DB has an password protection, but no username/password protection. So, no .mdw files are used, only the DB own Jet password. At desing-mode, when connecting to DB first time, I'm asked the username (blanc) password (blanc) and the Jet database password ('myPass'). Everything works just fine until when I try to debug the program. Database login dialogbox appears and asks for username and password, but no Jet database password... So, I'm unable to connect. PLEASE help me on this one, because I starting to lose my mind while trying to find the answer. Thx in advance.

Jari@.FinlandPlease help. I'm desperate.|||I'm STILL strugging with this problem. Somebody, please help me. I've tried every solution presented on this forum (and every other that I was able to find) but the problem still exists. I managed to change the db path, userID and password at runtime, but can't find a way to enter the DB password... If I remove the password from DB everything works just fine. Even with normal logon information (userID & password). The TableLogOnInfo class doesn't give the option for DB password, only the UserID and password. The ConnectionInfo class has a password-option but I'm not sure is this for the DB password or what? And how should I use this class? I'll paste some code for you to see.

Dim oCRTab As New CrystalDecisions.Shared.TableLogOnInfo
Dim oCRConn As New CrystalDecisions.Shared.ConnectionInfo

Dim tab As Table
Dim report As New MyReport
tab = report .Database.Tables("MyTable")
oCRConn.Password = "TheDBPass"

oCRTab.ConnectionInfo = oCRConn
oCRTab.ConnectionInfo.ServerName = "c:\dbTest\db1.mdb"
oCRTab.TableName = "MyTable"

tab.ApplyLogOnInfo(oCRTab)

And later I bind the report to the CRViewer and it still asks for the login information...|||Yep, talking to myself again. Found out the solution to the problem: there isn't any... I received a post from businessobjects and found out this:

====================
NOTE:

You cannot set a database level password when
using Crystal Reports for Visual Studio .NET 2002 or
2003.
====================

So back to square one. I quess I'll have to find some other way to do my reports.

antijape .......boycottin CR since 2005.......

Monday, February 20, 2012

Passing status back from Command line application to SQL Job

Hi,

I have created a job in SQL Server 2005 in which one of the step executes a .NET console application which is created in .NET to update some status to database before the next step. i need some help in sending some status back to sql job when i come accross any problem in the console application for ex when there is a exception i need to send some status to the job, so the job gets failed permanently. i tried few other methods of updating some temporary status database with this error information and have another intermediate step in the job to check for the status... it worked but i dont like doing it. please let me know if there is any other method to do this.

Thanks in advance

Hariharan

Are you doing anything with SSIS, or is this a SQL Server Agent question only?|||I'ts in SSIS as one of the step.|||

Hari Haran wrote:
I'ts in SSIS as one of the step.

Please provide more details then... As it reads, it sounds like it has nothing to do with SSIS.

Thanks,
Phil|||

To make the to step fail, just ensure the applicaton returns a suitable exit code. Take a closer look at the Job Step dialog and note of the "Process exit code of a successful cvommand:" option. Match this to you exist code as required. If this is the SSIS task after all, then look at the properties SuccessValue and FailTaskIfRetrnCodeIsNotSuccessValue.

You have set an exit code? If not see -

Environment.Exit Method (System)
(http://msdn2.microsoft.com/en-us/library/system.environment.exit.aspx)

Passing status back from Command line application to SQL Job

Hi,

Thanks in advance

Hariharan

Are you doing anything with SSIS, or is this a SQL Server Agent question only?|||I'ts in SSIS as one of the step.|||

Hari Haran wrote:
I'ts in SSIS as one of the step.

Please provide more details then... As it reads, it sounds like it has nothing to do with SSIS.

Thanks,
Phil|||

You have set an exit code? If not see -

Environment.Exit Method (System)
(http://msdn2.microsoft.com/en-us/library/system.environment.exit.aspx)

Passing some sort of Data Structure to a Stored Procedure

Hi all,
Is it possible to pass some sort of array to a stored procedure using
ADO.net.
In particular, I have a list of usernames that I need to pass to the
procedure and then have the procedure loop through that array and perform an
update action on the database.
The only alternative I can think of is to call a stored procedure over and
over again. I'd rather pass th usernames in bulk. Can anyone suggest how to
do this?
Thanks all
Kindest Regards
SimonSimon
This is one approach
CREATE PROCEDURE sparray_method
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC sparray_method
@.array = '''sysobjects'',''sysindexes'',''syscolu
mns'''
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:#YWnhn4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is it possible to pass some sort of array to a stored procedure using
> ADO.net.
> In particular, I have a list of usernames that I need to pass to the
> procedure and then have the procedure loop through that array and perform
an
> update action on the database.
> The only alternative I can think of is to call a stored procedure over and
> over again. I'd rather pass th usernames in bulk. Can anyone suggest how
to
> do this?
> Thanks all
> Kindest Regards
> Simon
>|||If the list is short, perhaps Uri's method would be faster...
You could also parse the list using SQL string commands in a loop and do the
updates
You could also store the names in a #temp table and have the SP join to the
#temp table to choose which rows would be updated...
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:%23YWnhn4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is it possible to pass some sort of array to a stored procedure using
> ADO.net.
> In particular, I have a list of usernames that I need to pass to the
> procedure and then have the procedure loop through that array and perform
an
> update action on the database.
> The only alternative I can think of is to call a stored procedure over and
> over again. I'd rather pass th usernames in bulk. Can anyone suggest how
to
> do this?
> Thanks all
> Kindest Regards
> Simon
>|||Thank you all
Simon|||YOU MIGHT JUST TRY USING THE "adArray" type declaration for the type of data
being passed. I would guess that you would create a dimensioned array
Dim A As Variant
A = Array(30)
A(1) = "JOHN"
A(1) = "JOHN2"
With cmd_Users_Update
.ActiveConnection = Users_DB_Connection
.CommandType = adCmdStoredProc
.CommandText = "dp_process_users_array"
.Parameters.Append .CreateParameter("@.users_array", _
adArray, adParamInput)
.parameters("@.users_array").Value = A
End With
and pass that array to the parameter of the command object.
Dan Kirk

Passing some sort of Data Structure to a Stored Procedure

Hi all,
Is it possible to pass some sort of array to a stored procedure using
ADO.net.
In particular, I have a list of usernames that I need to pass to the
procedure and then have the procedure loop through that array and perform an
update action on the database.
The only alternative I can think of is to call a stored procedure over and
over again. I'd rather pass th usernames in bulk. Can anyone suggest how to
do this?
Thanks all
Kindest Regards
SimonSimon
This is one approach
CREATE PROCEDURE sparray_method
@.array nvarchar(4000)
AS
BEGIN
SET NOCOUNT ON
DECLARE @.nsql nvarchar(4000)
SET @.nsql = '
SELECT *
FROM sysobjects
WHERE name IN ( ' + @.array + ')'
PRINT @.nsql
EXEC sp_executesql @.nsql
END
GO
EXEC sparray_method
@.array = '''sysobjects'',''sysindexes'',''syscolumns'''
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:#YWnhn4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is it possible to pass some sort of array to a stored procedure using
> ADO.net.
> In particular, I have a list of usernames that I need to pass to the
> procedure and then have the procedure loop through that array and perform
an
> update action on the database.
> The only alternative I can think of is to call a stored procedure over and
> over again. I'd rather pass th usernames in bulk. Can anyone suggest how
to
> do this?
> Thanks all
> Kindest Regards
> Simon
>|||Depending on what processing you are actually doing, you
could create an ADO.NET dataset that has a datatable
holding the usernames, write the table to the SQL Server,
and then have your procedure access this table. One
advantage to this is that you may be able to perform a
joined update statement that would speed things up
drastically over looping through a list of users.
Just a thought which I hope will help.
Matthew Bando
matthew.bando@.csctgi(remove).com
>--Original Message--
>Hi all,
>Is it possible to pass some sort of array to a stored
procedure using
>ADO.net.
>In particular, I have a list of usernames that I need to
pass to the
>procedure and then have the procedure loop through that
array and perform an
>update action on the database.
>The only alternative I can think of is to call a stored
procedure over and
>over again. I'd rather pass th usernames in bulk. Can
anyone suggest how to
>do this?
>Thanks all
>Kindest Regards
>Simon
>
>.
>|||If the list is short, perhaps Uri's method would be faster...
You could also parse the list using SQL string commands in a loop and do the
updates
You could also store the names in a #temp table and have the SP join to the
#temp table to choose which rows would be updated...
--
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Simon Harvey" <simon.harvey@.the-web-works.co.uk> wrote in message
news:%23YWnhn4$DHA.692@.TK2MSFTNGP11.phx.gbl...
> Hi all,
> Is it possible to pass some sort of array to a stored procedure using
> ADO.net.
> In particular, I have a list of usernames that I need to pass to the
> procedure and then have the procedure loop through that array and perform
an
> update action on the database.
> The only alternative I can think of is to call a stored procedure over and
> over again. I'd rather pass th usernames in bulk. Can anyone suggest how
to
> do this?
> Thanks all
> Kindest Regards
> Simon
>|||Thank you all
Simon|||YOU MIGHT JUST TRY USING THE "adArray" type declaration for the type of data being passed. I would guess that you would create a dimensioned array
Dim A As Varian
A = Array(30
A(1) = "JOHN
A(1) = "JOHN2
With cmd_Users_Updat
.ActiveConnection = Users_DB_Connectio
.CommandType = adCmdStoredPro
.CommandText = "dp_process_users_array
.Parameters.Append .CreateParameter("@.users_array",
adArray, adParamInput
.parameters("@.users_array").Value = End Wit
and pass that array to the parameter of the command object
Dan Kirk

Passing settings

In my .NET application, I have my report control Zoom set to Page Width
(rc:Zoom=Page Width). This works fine for the first report that appears,
but when I click a link in this report to navigate to another, the zoom
resets to 100%.
Is there a way to get my Zoom to stay what I've set it to? Do I have to do
this in a config file rather than in .NET?
Thanks,
MelissaYes, we too would like to have all of our reports come up in page width
instead of 100%, is there a way to do this in report manager? maybe a setting
in the config file?
"Melissa" wrote:
> In my .NET application, I have my report control Zoom set to Page Width
> (rc:Zoom=Page Width). This works fine for the first report that appears,
> but when I click a link in this report to navigate to another, the zoom
> resets to 100%.
> Is there a way to get my Zoom to stay what I've set it to? Do I have to do
> this in a config file rather than in .NET?
>
> Thanks,
> Melissa
>
>

Wednesday, March 28, 2012

PDF Render Problem

Wednesday, March 21, 2012

Pausing-Restarting SQL Server Service

Pausing-Restarting SQL Server Service

Pausing-Restarting SQL Server Service

Pausing-Restarting SQL Server Service

patindex with nvarchar

patindex with nvarchar

Monday, March 12, 2012

password storage

Friday, March 9, 2012

Password protected Access 2000 DB

Monday, February 20, 2012

Passing status back from Command line application to SQL Job

Passing status back from Command line application to SQL Job

Passing some sort of Data Structure to a Stored Procedure

Passing some sort of Data Structure to a Stored Procedure

Passing settings

Passing result sets

Blog Archive

About Me