converting Crystal Reports to SSRS

I came across a website that claimed "There are approximately 116 functions in Crystal Reports that do not have a direct equivalent in VB.NET or Reporting Services (SSRS)." Does anyone know what these functions are?I know this is not a direct answer to your question, but we looked into a way to convert Crystal Reports to SQL Reports, but were unable to find a viable solution. There is an application called RPT2RDL that claims to convert some files, but it does not handle a good many functions even though some are supported by SQL Reporting Services. This has left us manually recreating the reports which takes a great deal of time.

Convert SQL in ASP 3.0 site to UPDATE ntext

Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql ="SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.com
Sorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com
|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.droptable.co m...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com

Convert SQL in ASP 3.0 site to UPDATE ntext

Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql = "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.comSorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.dr
optable.com...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com

Convert SQL in ASP 3.0 site to UPDATE ntext

Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql = "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
--
Message posted via http://www.sqlmonster.comSorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
--
Message posted via http://www.sqlmonster.com|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.SQLMonster.com...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.sqlmonster.com

convert or transfer access mdb file to sql server

what's the easiest way to do this. i have 5 tables within the mdb file
i tried the "easiest way" shown in a website but it does not work.
the way i did it was on access(2007) > database tools tab > sql server button
it gave me an error when it ask for an login ID
i do not have any password/id..

please help
thanksIf you do not have a logon to the SQL server then you do not have the permissions to do anything to it - simple eh?|||He could be set up with windows authentication or mixed-mode security, which would not require a password.|||it's microsoft sql server management
when i open it.
it will show a box to connect.

server type is Database Engine
Server name is Mackenzo
auth. is windows auth

yes i have a username without a password which is mackenzo/mackenzie

what can i do??

convert dates

dear experts,
i'm working in a website in asp.net where all the data is coming from stored procedures, i have also a callendar, now what i want is that when a client click a date i want to send this value to the stored procedure and the query the database according to that.
i have a day, week, month selection, i did the following in C#
date1(if the client click on a day)
= Calendar1.SelectedDate.ToShortDateString(); this one is simple
but i want to know how to convert the date for the week and the date for the month so the query knows which date and the query the database according to that date.
for the week selection i have just the value stored in DateW = (just the week number) and for the month DateM = (month number 3) how can i convert these dates in sqlserver so the query knows for example if a month selected is march, then the C# value will be 3 and i want the query knows that this is march(from 01-03-2007 till 31-03-2007)?!!!
any suggestions will be very appreciated.
thanks experts.Don't send the bits and pieces of the date string to SQL Server. Convert them to a valic datetime value in C# and then send that to the sproc.|||Hi,

For the first date and the last date of a month check the article at http://www.kodyaz.com/content/FirstAndLastDaysOfMonth.aspx

But also the following select may help you for both month and week

SELECT
firstdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0),
lastdate_month = DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()) + 1, -1) ,
firstdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0),
lastdate_week = DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)

Eralper
http://www.kodyaz.com|||thanks for the quick reply,
actually it can not be converted from int to datetime in C# because in C# it is Gregorian calendar which gives you just the week number and the month number, what i want is if there is a way to take this value which is dateM = 3 which is march and convert it in the stored procedure so the query will know that this is march?!!!!
thanks|||Hi,

Do you mean you have only the month and the year parameters?

If so, please check the following sql code,

declare @.y int, @.m int
set @.m = 12
set @.y = 2007

SELECT
firstdate_month =
CAST(
CAST(@.y as varchar(4)) + '/' + CASE WHEN @.m > 9 THEN CAST(@.m as varchar(2)) ELSE '0' + CAST(@.m as char(1)) END + '/01'
as datetime
),
lastdate_month =
DATEADD(dd,-1,
CAST(
CAST(
CASE WHEN @.m < 12 THEN @.y ELSE @.y + 1 END as varchar(4)
) +
'/' +
CASE
WHEN (@.m+1 < 10) THEN '0' + CAST(@.m+1 as char(1))
WHEN (@.m+1 > 9 and @.m+1 < 12) THEN CAST(@.m+1 as varchar(2))
ELSE '01'
END +
'/01'
as datetime
)
)

Eralper
http://www.kodyaz.com|||yes it is something like that, but i already have the query for the database and two parameters, the daystamp is one of them. how can i combine the SP i have and the one you give me? plus how do i need to do the week date, in C# i have just the week number, how can i tell to the SP which date to query the database for example dateW = 13 which is the week 13 of the year?!!
thanks my friend for the help,
good looking|||Hi,

I prepared the following script for the week problem. This is a little bit different when compared with the month version.

But this has to be modified for dates if the week is not in the current year.

declare @.w int
set @.w = 1

SELECT
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0)
),
DATEADD(
dd,
(@.w - DATEPART(ww,DATEADD(wk, DATEDIFF(wk, 0, GETDATE()), 0))) * 7,
DATEADD(wk, DATEDIFF(wk, 0, GETDATE()) + 1, -1)
)

Eralper
http://www.kodyaz.com|||thanks man for the help,
last question :) i have already a stored procedure that have 2 parameters one of these parameter is the date, how can i combine the sp i already have with the 2 SP you gives me, the @.w is the one i need to give to my stored procedure to query the date, so how can i send the value from the C# code convert it with your sp and then feed it to the my sp to query the database according to the converted date?!!!

thanks