How to create Parameter Field with embedded SQL

I want to create an expression field on my report that performs an sql statement that I specify to bring back a total instead of using the built-n functions for SSRS, how can I do this? Basically just like in Crystal where you can create a formula field that runs a sql statement then brings back a value from your sql statement. This value is not related to the other data in the report...so yes in theory you may be thinking subreport...is tha the only way? or can I create some sort of textbox that shows the total I want below?

DECLARE @.TotalDaysInMonth int,

@.today datetime,

@.TotalWeekendDays int,

@.TotalHolidaysThisMonth int,

@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH

SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN

31

ELSE

DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))

END

SELECT @.TotalDaysInMonth

-- TOTAL HOLIDAYS THIS MONTH

SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM Apex_ReportingServer.dbo.Holidays

WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))

AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

SELECT @.TotalHolidaysThisMonth

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME

SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END

SELECT @.TotalWeekendDays

SELECT @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)

SELECT @.TotalPostingDays

You could simply add the SQL Statement above to a new/different Dataset and reference that Dataset in the given field on your report...does that make sense, or am I missing something?

|||exactly thanks! I actually tried that and it worked before you posted...thanks you were right on.

How to create Parameter Field with embedded SQL

I want to create an expression field on my report that performs an sql statement that I specify to bring back a total instead of using the built-n functions for SSRS, how can I do this? Basically just like in Crystal where you can create a formula field that runs a sql statement then brings back a value from your sql statement. This value is not related to the other data in the report...so yes in theory you may be thinking subreport...is tha the only way? or can I create some sort of textbox that shows the total I want below?

DECLARE @.TotalDaysInMonth int,

@.today datetime,

@.TotalWeekendDays int,

@.TotalHolidaysThisMonth int,

@.TotalPostingDays int

SET @.today = GETDATE()

-- TOTAL DAYS THIS MONTH

SET @.TotalDaysInMonth = CASE WHEN DatePart(mm,GetDate()) IN (1,3,5,7,8,10,12) THEN

31

ELSE

DateDiff(day,GetDate(),DateAdd(mm, 1, GetDate()))

END

SELECT @.TotalDaysInMonth

-- TOTAL HOLIDAYS THIS MONTH

SELECT @.TotalHolidaysThisMonth = (SELECT COUNT(*) FROM Apex_ReportingServer.dbo.Holidays

WHERE HolidayDate BETWEEN (DATEADD(DAY, -DATEPART(DAY, @.today) + 1, @.today))

AND (DATEADD(DAY, -DATEPART(DAY, @.today), DATEADD(MONTH, 1, @.today))))

SELECT @.TotalHolidaysThisMonth

-- TOTAL # WEEKEND DAYS THIS MONTH

DECLARE @.date DATETIME

SET @.date = '20060101'

SELECT @.TotalWeekendDays = 8 +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '29') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '01') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '30') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '02') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END +

CASE WHEN ISDATE(CONVERT(CHAR(6), @.date, 112) + '31') = 1 THEN

CASE WHEN DATENAME(WEEKDAY, CONVERT(CHAR(6), @.date, 112) + '03') IN ('Saturday', 'Sunday')

THEN 1 ELSE 0 END ELSE 0 END

SELECT @.TotalWeekendDays

SELECT @.TotalPostingDays = @.TotalDaysInMonth - (@.TotalHolidaysThisMonth + @.TotalWeekendDays)

SELECT @.TotalPostingDays

You could simply add the SQL Statement above to a new/different Dataset and reference that Dataset in the given field on your report...does that make sense, or am I missing something?

|||exactly thanks! I actually tried that and it worked before you posted...thanks you were right on.sql

How to create image field?

Hello,
I would like to store some images in an msde table. When I go into the
enterprise manager design table mode and pick image as a field type, it
defaults to 16 bytes in size, and doesn't allow me to change the size. I
can't create a varbinary field greater than 8000 bytes either. The msde
instance is a recent install downloaded fresh from Microsoft, so it is the
current version (but for the life of me I can't figure out how to determine
the version #).
I've also tried using a shareware package called MSDE manager, but with the
same results.
How do I create an image field of 50K, for example?
thanks
Marc Pelletier
Hi,
It is not an issue, By default it will take bytes. Image data type will
allow you store a maximum of 2 GB. No need to change any thing
VARBINARY will allow a maximum of bytes.
Thanks
Hari
SQL Server MVP
"Marc Pelletier" <no.email@.please.com> wrote in message
news:Xns959A92CA0DCBAmpdd445@.216.168.3.44...
> Hello,
> I would like to store some images in an msde table. When I go into the
> enterprise manager design table mode and pick image as a field type, it
> defaults to 16 bytes in size, and doesn't allow me to change the size. I
> can't create a varbinary field greater than 8000 bytes either. The msde
> instance is a recent install downloaded fresh from Microsoft, so it is the
> current version (but for the life of me I can't figure out how to
> determine
> the version #).
> I've also tried using a shareware package called MSDE manager, but with
> the
> same results.
> How do I create an image field of 50K, for example?
> thanks
> Marc Pelletier
|||"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in news:eFR6PmUxEHA.1264
@.TK2MSFTNGP12.phx.gbl:

> It is not an issue, By default it will take bytes. Image data type will
> allow you store a maximum of 2 GB. No need to change any thing
> VARBINARY will allow a maximum of bytes.
>
I don't mean to look a gift horse in the mouth, but you haven't told me
anything. Do you mean to say that even though it appears to be 16 bytes, I
can actually stuff whatever I want in there? That doesn't seem likely.
cheers
Marc Pelletier
|||> I don't mean to look a gift horse in the mouth, but you haven't told me
> anything. Do you mean to say that even though it appears to be 16 bytes, I
> can actually stuff whatever I want in there? That doesn't seem likely.
Stop looking at enterprise manager to "tell you anything." The 16 bytes
represents a pointer to the data, not the actual data. Hari was not lying
when he said that you can store ~2 GB of data in there...
|||Typically, all that is stored in the data row for image, text, or ntext
columns is a 16 byte pointer to where the actual data is stored. You can
store up to 2GB of data for each data value using these data types.
For more information about how the data for these data types are stored,
see:
http://msdn.microsoft.com/library/?u...asp?frame=true
http://msdn.microsoft.com/library/?u...asp?frame=true
http://msdn.microsoft.com/library/?u...asp?frame=true
Alan Brewer [MSFT]
Content Architect
SQL Server Documentation Team
This posting is provided "AS IS" with no warranties, and confers no rights
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:#l6OeKbxEHA.2876@.TK2MSFTNGP12.phx.gbl:

> Stop looking at enterprise manager to "tell you anything." The 16
> bytes represents a pointer to the data, not the actual data. Hari was
> not lying when he said that you can store ~2 GB of data in there...
>
Ok, thanks, I didn't think he was lying ( and if I gave that impression,
sorry). That being the case, how come varbinary allows me to set the size
up to 8000, when it also has a limit of ~2G? How would I define my field
to store a large amount of data?
I'm guessing that under the hood they are essentially the same type,
since I could stream any binary data into an image field anyway, couldn't
I?
thanks
Marc Pelletier
|||> sorry). That being the case, how come varbinary allows me to set the size
> up to 8000, when it also has a limit of ~2G?
Who said varbinary has a limit of ~2 GB?

> How would I define my field
> to store a large amount of data?
Using the IMAGE datatype, not VARBINARY.

> I'm guessing that under the hood they are essentially the same type,
> since I could stream any binary data into an image field anyway, couldn't
> I?
I'm not going to presume to know exactly how the engine works with these
types. But I know that they are stored at least slightly differently,
similar to VARCHAR(8000) and TEXT (even though, deep down, they are both
just storing text).
|||"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
news:O0zalzcxEHA.2568@.TK2MSFTNGP10.phx.gbl:

> Who said varbinary has a limit of ~2 GB?
>
Well... I did. But I shouldn't have. If it isn't clear already, I'm new to
sql server. I generally vigorously avoid database programming, but can't
avoid it for my current project.
Thanks for the help, everyone. I think I'm away for now.
Marc
|||Hello,
I am new to databases. But i dont understand how one could store a picture
in a cell in a database? Or do you mean that there is a link to the picture
in the cell?
Thanks
WStoreyII
"Marc Pelletier" wrote:

> Hello,
> I would like to store some images in an msde table. When I go into the
> enterprise manager design table mode and pick image as a field type, it
> defaults to 16 bytes in size, and doesn't allow me to change the size. I
> can't create a varbinary field greater than 8000 bytes either. The msde
> instance is a recent install downloaded fresh from Microsoft, so it is the
> current version (but for the life of me I can't figure out how to determine
> the version #).
> I've also tried using a shareware package called MSDE manager, but with the
> same results.
> How do I create an image field of 50K, for example?
> thanks
> Marc Pelletier
>
|||=?Utf-8?B?V1N0b3JleUlJ?= <WStoreyII@.discussions.microsoft.com> wrote in
news:54836FEC-DDCB-4F80-85F0-13F580D4C292@.microsoft.com:

> I am new to databases. But i dont understand how one could store a
> picture in a cell in a database? Or do you mean that there is a link
> to the picture in the cell?
>
WStorey,
The image is stored as a binary stream directly in a blob (binary large
object, I think ) field of the database. It adds a substantial overhead to
the process, and I think that in many cases it is better to store the image
on disk and store a reference to it in the db, as you have suggested.
However it is what I am going to do. In my case the number ( and size) of
images will be fairly small and dynamic, maybe a couple of thousand, and
will be accessed a lot, so I expext the sql server cacheing to help with
the performance hit.
The how is explained quite clearly in
http://support.microsoft.com/default...en-us;309158#1
for the csharp case.
Hope this helps.
Marc Pelletier

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table.
My table contains three fields, file name, file data, and an index. I need to
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value from the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.
You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--TEXTCOPY OUT
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from
the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.
|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:

> I am using SQL 8.0 and have the challenge of writing text files from a table.
> My table contains three fields, file name, file data, and an index. I need to
> create a file for every row that uses the 1st field as the file name, which
> is already in the correct format [filename.txt] and I need the value from the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table
.
My table contains three fields, file name, file data, and an index. I need t
o
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value fro
m the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from[/vbc
ol]
the[vbcol=seagreen]
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:

> I am using SQL 8.0 and have the challenge of writing text files from a tab
le.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name, whic
h
> is already in the correct format [filename.txt] and I need the value f
rom the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create file using field from a table.

I am using SQL 8.0 and have the challenge of writing text files from a table.
My table contains three fields, file name, file data, and an index. I need to
create a file for every row that uses the 1st field as the file name, which
is already in the correct format [filename.txt] and I need the value from the
second column, filed data, to be the text within the text file.
Can anyone assit me in creating these files? I cannot find anything to
assist me with this in the online books nor MSDN.You can use this demo code as a start:
-- OJ: TEXTCOPY example
-- Loading files into db &
-- exporting files out to folder
--
--
--TEXTCOPY IN
--
--create tb to hold data
create table tmp(fname varchar(100),img image default '0x0')
go
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass'
--specify desired folder
set @.path='c:\winnt\'
set @.sql='dir ' + @.path + '*.bmp /c /b'
--insert filenames into tb
insert tmp(fname)
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /i' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
go
select * from tmp
go
--
--TEXTCOPY OUT
--
declare @.sql varchar(255),
@.fname varchar(100),
@.path varchar(50),
@.user sysname,
@.pass sysname
set @.user='myuser'
set @.pass='mypass,'
--specify desired output folder
set @.path='c:\tmp\'
set @.sql='md ' + @.path
--create output folder
exec master..xp_cmdshell @.sql
--loop through and insert file contents into tb
declare cc cursor
for select fname from tmp
open cc
fetch next from cc into @.fname
while @.@.fetch_status=0
begin
set @.sql='textcopy /s"'+@.@.servername+'" /u"'+@.user+'" /p"'+@.pass+'"
/d"'+db_name()+'" /t"tmp" /c"img" /w"where fname=''' + @.fname + '''"'
set @.sql=@.sql + ' /f"' + @.path + @.fname + '" /o' + ' /z'
print @.sql
exec master..xp_cmdshell @.sql ,no_output
fetch next from cc into @.fname
end
close cc
deallocate cc
set @.sql='dir ' + @.path + '*.bmp /c /b'
exec master..xp_cmdshell @.sql
go
drop table tmp
go
"Dorian Foster" <DorianFoster@.discussions.microsoft.com> wrote in message
news:94D63419-1F96-4437-A07E-C293141D84C8@.microsoft.com...
> I am using SQL 8.0 and have the challenge of writing text files from a
table.
> My table contains three fields, file name, file data, and an index. I need
to
> create a file for every row that uses the 1st field as the file name,
which
> is already in the correct format [filename.txt] and I need the value from
the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.|||Refer to Books Online; search for "Write File Transformation"
"Dorian Foster" wrote:
> I am using SQL 8.0 and have the challenge of writing text files from a table.
> My table contains three fields, file name, file data, and an index. I need to
> create a file for every row that uses the 1st field as the file name, which
> is already in the correct format [filename.txt] and I need the value from the
> second column, filed data, to be the text within the text file.
> Can anyone assit me in creating these files? I cannot find anything to
> assist me with this in the online books nor MSDN.

How to create a trigger to update a field

Hi -

I know my way around VS but I am just exploring "advanced" SQL Server 2005 and have run into a challenge which I think a trigger could solve, but I am not sure and also don't know how to set that up. So any help or links to tutorials are highly appreciated.

Here is the challenge: I have a table with a number of fields, among them RequestID (bigint) and Booktime (datetime). What I would like to happen is whenever someone writes a value different from NULL into RequestID, Booktime gets set to the current timestamp. When RequestID gets set to NULL, Booktime gets set to NULL.

Is there a way to do this with a trigger (or an otherwise elegant way)?

Thanks in advance for ANY help or ideas.

Oliver

Why do you need a trigger for that? Cant your application/stored proc handle the logic? Triggers are generally a maintenance nightmare (IMHO). You have to use them cautiously if you have to.

|||

Thanks for the advice. Oliver

|||

I agree with ndinakar, stay away from triggers.

Your best bet is to handle the logic in your app or stored proc. For a stored procedure you could handle it as so

Create Procedure dbo.myUpdateProc( @.fieldIdentityint, @.RequestIDbigint =NULL)If @.RequestIDISNOT NULLBegin Update <table>set RequestID = @.RequestID, Booktime =GetDate()where = @.fieldIdentityEnd

sql

How to create a table with query

I want to create a empty new table (if it doesn't exist)
when the the page/program starts. Maybe with copying field names from other table.
It would be nice to have its name personal for each user. But I don't know if it possible.
Also, what event is best place for this.

Perhaps like this:

dim username as string = Page.User.Identity.Name

Dim tablename as string

tablename= shopping_basket+username

create from t_product new

@.tablename

What you are thinking of doing is not a very good idea. You will have to give the user higher permissions to create tables on the fly and if they are not cleaned up properly you will end up with tons of tables that you dont know if they are being used or not. On the otherhand, think of using temp tables or table variables in the proc.

|||

Aha good to know, and yes I should thought about that myself.

What about a table with a constant name. But then, have you good code using or making a temp table or table variable.

What is a table variable?

Regards

Leif

|||

LISM:

What about a table with a constant name.

You can do that too. If you can identify a batch from a different batch via some unique key. If you have multiple users inserting into that table you should have a way of identifying one set from the other.

LISM:

But then, have you good code using or making a temp table or table variable.

What is a table variable?

Please read up books online on these topics. They have a much better explanation than what I can type.

How to create a RTF field ?

How can I save the text of a rich text box in a sql express 2005 datatable ?

Of course I'd like to save the string and the format of the text (bold, color etc...).

Which column type I have to use for this RTF field ?

Thank you.

hi,

I'd guess nvarchar(MAX)

this data type can host up to 2gb of text data, in national format thus including chars other than the ones present in the traditional alphabet, like chinese, cyrillic and the like..
http://msdn2.microsoft.com/en-us/library/a1904w6t.aspx
http://msdn2.microsoft.com/en-us/library/ms186939.aspx

http://msdn2.microsoft.com/en-us/library/ms187752.aspx for the complete list of supprted data types..

regards

|||

hi,

Thank you for your response.

In fact, my problem was I directly bind the text property and not the Rtf property of my rich text box to the sql field.

It's strange that the Rtf property is not available for binding in the designer so I have to code the binding !

Me.RichTextBox1.DataBindings.Add(New System.Windows.Forms.Binding("Rtf", Me.TbTextBindingSource, "colText", True))

But now it's working !

regards

How to create a field that may accoss multiple pages?

I have a memo field that may not fit into one page. Is there any solution to let crystal report create multiple pages automatically at runtime? Splitting the string manually may not be the solution because how many characters are displayed for each page cannot be determined at the design time.

Thanks in advance!Doesn't the 'Can Grow' option work? (Right click field, Format Field, Common tab)?|||'Can Grow' does not work because the displaying area has to be fixed while the message may be too long to fit into one page.

How to create a dimesnion for a range of values

I am new to OLAP and I was wondering if it is possible to create a dimension
in Analysis Services 2005 based on a range of values for a given field in a
Fact table.
Example:
Field Name: Quantity
Dimension Name: Quantity Range
Dimension Values: 0-100
101-200
201-1000
> 1000
The only solution I see now is to denormalize Quantity into a new field
QuantityRange that is computed based on the value of the quantity
1: 0-100
2: 101-200
3: 201-1000
4: > 1000
The values 1 to 4 would be my dimension.
Thanks al lot
I gave you some ideas on the microsoft.public.sqlserver.olap newsgroup.
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
This posting is provided "AS IS" with no warranties, and confers no rights.
"carlos" <carlos@.discussions.microsoft.com> wrote in message
news:6043ABBE-7ADA-4E9D-8AA7-D252F075DAEB@.microsoft.com...
>I am new to OLAP and I was wondering if it is possible to create a
>dimension
> in Analysis Services 2005 based on a range of values for a given field in
> a
> Fact table.
> Example:
> Field Name: Quantity
> Dimension Name: Quantity Range
> Dimension Values: 0-100
> 101-200
> 201-1000
> The only solution I see now is to denormalize Quantity into a new field
> QuantityRange that is computed based on the value of the quantity
> 1: 0-100
> 2: 101-200
> 3: 201-1000
> 4: > 1000
> The values 1 to 4 would be my dimension.
> Thanks al lot
|||You could drive this denormalisation using the data in the dimension
table.
1) Create a dimension table with max and min columns
ID MinValue MaxValue
1 0 100
2 101 200
3 201 1000
4 1000 2147483648 <-- max integer value
2) Create a view on the fact table like the following
Select
...
<list fact columns here>
...
,dim.ID
from fact f
inner join dimQuantityRange dim
on f.Quantity between dim.MinValue and dim.MaxValue
3) Alter you cube to use the newly created view as the fact "table".
then you can join the dimension back onto the id in the view.
The advantage of doing things this way is that everything is data
driven. If you need to add more ranges or change the ranges you just
update the max/min columns in the dim table and re-process.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

How to create a dimesnion for a range of values

I am new to OLAP and I was wondering if it is possible to create a dimension
in Analysis Services 2005 based on a range of values for a given field in a
Fact table.
Example:
Field Name: Quantity
Dimension Name: Quantity Range
Dimension Values: 0-100
101-200
201-1000
> 1000
The only solution I see now is to denormalize Quantity into a new field
QuantityRange that is computed based on the value of the quantity
1: 0-100
2: 101-200
3: 201-1000
4: > 1000
The values 1 to 4 would be my dimension.
Thanks al lotI gave you some ideas on the microsoft.public.sqlserver.olap newsgroup.
--
Dave Wickert [MSFT]
dwickert@.online.microsoft.com
Program Manager
BI Systems Team
SQL BI Product Unit (Analysis Services)
--
This posting is provided "AS IS" with no warranties, and confers no rights.
"carlos" <carlos@.discussions.microsoft.com> wrote in message
news:6043ABBE-7ADA-4E9D-8AA7-D252F075DAEB@.microsoft.com...
>I am new to OLAP and I was wondering if it is possible to create a
>dimension
> in Analysis Services 2005 based on a range of values for a given field in
> a
> Fact table.
> Example:
> Field Name: Quantity
> Dimension Name: Quantity Range
> Dimension Values: 0-100
> 101-200
> 201-1000
> The only solution I see now is to denormalize Quantity into a new field
> QuantityRange that is computed based on the value of the quantity
> 1: 0-100
> 2: 101-200
> 3: 201-1000
> 4: > 1000
> The values 1 to 4 would be my dimension.
> Thanks al lot|||You could drive this denormalisation using the data in the dimension
table.
1) Create a dimension table with max and min columns
ID MinValue MaxValue
1 0 100
2 101 200
3 201 1000
4 1000 2147483648 <-- max integer value
2) Create a view on the fact table like the following
Select
..
<list fact columns here>
..
,dim.ID
from fact f
inner join dimQuantityRange dim
on f.Quantity between dim.MinValue and dim.MaxValue
3) Alter you cube to use the newly created view as the fact "table".
then you can join the dimension back onto the id in the view.
The advantage of doing things this way is that everything is data
driven. If you need to add more ranges or change the ranges you just
update the max/min columns in the dim table and re-process.
Regards
Darren Gosbell [MCSD]
<dgosbell_at_yahoo_dot_com>
Blog: http://www.geekswithblogs.net/darrengosbell

how to count rows in field

Hello.
I built a report with one field as a group.
I want to count the number of rows in each field so I can add it to
the group field or somw where in the report.
How can I count how many rows do I have in each group?
Thanks.On May 1, 3:44 am, nicknack <roezo...@.gmail.com> wrote:
> Hello.
> I built a report with one field as a group.
> I want to count the number of rows in each field so I can add it to
> the group field or somw where in the report.
> How can I count how many rows do I have in each group?
> Thanks.
I'm not sure if it is accessible by group, but you could try using the
'rownumber' keyword. Hope this helps.
Regards,
Enrique Martinez
Sr. Software Consultant|||Hi,
If you want to show the total group by:
In the group header write a expression like
=count(Field!Yourcolumn.Value)
If you want distinct count use this expression
=countdistinct(Field!yourcolumn.value)
Regards,
Sri
"nicknack" wrote:
> Hello.
> I built a report with one field as a group.
> I want to count the number of rows in each field so I can add it to
> the group field or somw where in the report.
> How can I count how many rows do I have in each group?
> Thanks.
>|||Hi Martinez
Thanks for the replay.
I used :
=3DCountRows("GroupName")
in the group row and it does the work.
Thanks :)
EMartinez =D7=9B=D7=AA=D7=91:
> On May 1, 3:44 am, nicknack <roezo...@.gmail.com> wrote:
> > Hello.
> >
> > I built a report with one field as a group.
> > I want to count the number of rows in each field so I can add it to
> > the group field or somw where in the report.
> >
> > How can I count how many rows do I have in each group?
> >
> > Thanks.
>
> I'm not sure if it is accessible by group, but you could try using the
> 'rownumber' keyword. Hope this helps.
> Regards,
> Enrique Martinez
> Sr. Software Consultant

How to cotrol format of datetime attributes?

There is an attribute that have type DateTime, based on the field type in data source. It hasn't separate name column. The member caption is formatted as yyyy-MM-dd hh:mm:ss. Can I control the format of Member name without giving separate name column.

I assigned different format in the format field of key property window, but it had no effect. What I did wrong?

Hello. I do not think that you will have to pay any penalty from adding a new column in the data source view with a new format of your date column. I recommend to use the TSQL function CONVERT that have arguments for different date formats. Have a look at CONVERT in Books On Line, and you will see the complete list of different codes/arguments for different date formats.

HTH

Thomas Ivarsson

|||

Thank you,

I thought about more sofisticated solution, that can be used in multi culture environment without adding x additional fields with "formatting" of a datetime attribute.

I hoped, that the AS2005 is smarter as AS2005 and offers more possibilties.

Do you know what is the format field in key properties for?

How to copy the value of one field into another using SQL Server 2000

I am a little embarrased to be asking this question because I know that the answer must be simple but I am still a nube at SQL. I am stuck with a simple query that copies one field of data into another. Below is the script that I am trying to execute.

UPDATE table
SET field2 = field1

I get an error from SQL Server that says that the String or binary data would be truncated. I am assuming that the SQL server is trying to concatenate all the value of field1 into field2. All I am trying to do is copy the values of one field into another for each and every row in the table. Thanks in advance.I am assuming that the SQL server is trying to concatenate all the value of field1 into field2.

No, the UPDATE command goes through each record that matches your WHERE criteria (which is blank here, so it goes through every record) and performs the SET portion on individual records. In your example, after the UPDATE is performed, field2 should be equal to field1 for the entire table.

I get an error from SQL Server that says that the String or binary data would be truncated.

That probably means that the two fields are different types. BTW, VARCHAR(25) and VARCHAR(15) are "different types" to SQL. Obviously a 25 character string isn't going to fit in a field that is only 15 characters long.|||...so you will need to either expand both columns to the same size, or use the CAST(), CONVERT(), or LEFT() functions to trim the long string to the shorter length. SQL Server will not do it for you because it entails a loss of data.|||Thanks guys!. The field size was the problem.

How to copy the image field

Hi,
I have to write a procedure which copies some data within and between
tables, including image fields of unknown size. Those image fields contain
documents in various formats and can vary from couple of kilobytes to couple
of megabytes. How can I do it? My first instinct was to look at READTEXT,
WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
recommend them any more and is going to drop them altogether. Can anyone
direct me to some materials that would show how to do it?
Thanks,
MiroslawMPA (miroslaw_pa@.pf.pl) writes:
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields
> contain documents in various formats and can vary from couple of
> kilobytes to couple of megabytes. How can I do it? My first instinct was
> to look at READTEXT, WRITETEXT and UPDATETEXT functions, but it seems
> Microsoft does not recommend them any more and is going to drop them
> altogether. Can anyone direct me to some materials that would show how
> to do it?
You are correct that these operations are depreacated, but so is the
image data type.
The preferred data type for image data in SQL 2005 is varbinary(MAX). If
your data type is image, and you can change that, then you probably
have to use WRITETEXT and UPDATETEXT.
varbinary(MAX) works very much like the regular varbinary(n) data type,
but you can work with chunks with the .write mutator, and this way
update only a part of the column.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||MPA wrote:
> Hi,
> I have to write a procedure which copies some data within and between
> tables, including image fields of unknown size. Those image fields contai
n
> documents in various formats and can vary from couple of kilobytes to coup
le
> of megabytes. How can I do it? My first instinct was to look at READTEXT,
> WRITETEXT and UPDATETEXT functions, but it seems Microsoft does not
> recommend them any more and is going to drop them altogether. Can anyone
> direct me to some materials that would show how to do it?
> Thanks,
> Miroslaw
You can use the regular UPDATE statement to copy data between IMAGE
columns. However, in SQL Server 2005 the IMAGE type exists for
backwards compatibility only. Microsoft recommends that you use
VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
also deprecated.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||The customers have different servers. The first one has SQL Server 2000, so
it looks like I have to use READTEXT and UPDATETEXT. I just wanted first to
make a test to copy the small piece of data from one row of the table to
another, but it does not seem to work. It runs like this:
DECLARE @.ptrval varbinary(16)
DECLARE @.ptrval2 varbinary(16)
SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
and doc_zkz_id=35)
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
doc_zkz_id=35
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
I was trying here to read 16 first bytes of DOC_BLOB column from the first
row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
After the operation the written 16 bytes are completely different than the
data in the first row. On the other hand I was able to update a second row
with the statement
like:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 'abcdef"
What I am missing?
Thanks,
Miroslaw
Uzytkownik "David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> napisal w
wiadomosci news:1147621152.612045.54660@.j55g2000cwa.googlegroups.com...
> MPA wrote:
contain
couple
READTEXT,
> You can use the regular UPDATE statement to copy data between IMAGE
> columns. However, in SQL Server 2005 the IMAGE type exists for
> backwards compatibility only. Microsoft recommends that you use
> VARBINARY(MAX) instead, which is why the old TEXT/IMAGE functions are
> also deprecated.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||MPA (miroslaw_pa@.pf.pl) writes:
> The customers have different servers. The first one has SQL Server 2000,
> so it looks like I have to use READTEXT and UPDATETEXT. I just wanted
> first to make a test to copy the small piece of data from one row of the
> table to another, but it does not seem to work. It runs like this:
> DECLARE @.ptrval varbinary(16)
> DECLARE @.ptrval2 varbinary(16)
> SET @.ptrval = (SELECT TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=1
> and doc_zkz_id=35)
> READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
> SELECT @.ptrval2=TEXTPTR(doc_blob) FROM SMS_DOKUMENTE where doc_num=2 and
> doc_zkz_id=35
> WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
> I was trying here to read 16 first bytes of DOC_BLOB column from the first
> row of SMS_DOKUMENTE table and write them to DOC_BLOB in the second row.
> After the operation the written 16 bytes are completely different than the
> data in the first row. On the other hand I was able to update a second row
> with the statement
The statement
READTEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval 0 16
does not assign @.ptrval. Rather @.ptrval is a pointer to the blob data
in the column. The 16 bytes gets returned to the client. So when you
say:
WRITETEXT SMS_DOKUMENTE.DOC_BLOB @.ptrval2 @.ptrval
you are just writing that pointer to the blob.
You can copy blob data with UPDATETEXT. But please don't ask me for an
example. I very rarely work with any of the text operations, and when I
do, I need to study Books Online carefully.
Now, you mentioned that the source and target tables were on different
servers. This is going to make it even more painful.
I would try a straight UPDATE across the linked server. If this breaks
down, I would try to use BCP to bring the source table over the target
server.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx