Hi,
I have created a datatable which is filled by the result of stored procedure. So I need to know like how to bind this datatable with the report using report viewer.
Apoorva
First you have to make sure you have a dataset. Also that the data set is connecting to this table.
If this is your first time creating a report I would suggest finding a walk through on a simple report creation
Here is a pretty good wak through
http://www.codeproject.com/dotnet/HowToReport.asp
good luck
I have a stored procedure which is not working the way I think it
should be.
I have a CURSOR which has a variable in the WHERE clause:
DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @.dbname
ORDER BY tablename
It won't return anything, even when I verify that @.dbname has a value
and if I run the query in Query Analyzer with the value, it returns
rows:
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = 'Archive'
ORDER BY tablename
DB_Rpt_Fragmentation11575791622006-03-29 09:52:11.7772006-03-29
09:52:11.823
DtsAdtStdArchive_DataSourceType5175768822006-03-29
09:52:11.8702006-03-29 09:52:11.887
DtsADTstdArchiveNotUsed3575763122006-03-29 09:52:11.8872006-03-29
09:52:12.103
I've taken out most of the guts for simplicity, but here's what I've
got:
--CREATE TABLE dbo.db_ind
--(
--db_ind_tkintIDENTITY,
-- id int NULL,
-- tablename sysname NOT NULL,
-- indid int NULL,
-- indexname sysname NOT NULL,
-- shcontig1dt datetime NULL,
-- defragdt datetime NULL,
-- shcontig2dt datetime NULL,
-- reindexdt datetime NULL
--)
ALTER PROCEDURE IDR
(@.hours int
)
AS
--SET NOCOUNT ON
--SET ANSI_WARNINGS OFF
DECLARE @.tabname varchar(100),
@.indname varchar(100),
@.dbname varchar(50),
@.vsql varchar(1000),
@.v_hours varchar(4),
@.shcontig1dtdatetime,
@.shcontig2dtdatetime,
@.defragdtdatetime,
@.reindexdtdatetime,
@.idint,
@.indidint,
@.rundbcursorint,
@.runtabcursorint,
@.runindcursorint
DECLARE get_dbs CURSOR local fast_forward FOR
SELECT dbname
FROM db_jobs
WHERE idrdate < getdate() - 4
or idrdate is null
ORDER BY dbname
DECLARE get_tabs CURSOR local fast_forward FOR
SELECT distinct tablename, id, shcontig1dt, shcontig2dt
FROM db_ind
WHERE dbname = @.dbname
ORDER BY tablename
DECLARE get_inds CURSOR local fast_forward FOR
SELECT indid, indexname, defragdt, reindexdt
FROM db_ind
WHERE dbname = @.dbname
AND tablename = @.tabname
ORDER BY indexname
OPEN get_dbs
FETCH NEXT FROM get_dbs
INTO @.dbname
IF @.@.FETCH_STATUS = 0
SELECT @.rundbcursor = 1
ELSE
SELECT @.rundbcursor = 0
SELECT @.v_hours = CONVERT(varchar,@.hours)
--================================================== ================================================== =====
--================================================== ================================================== =====
--================================================== ================================================== =====
WHILE @.rundbcursor = 1
BEGIN -- db while
PRINT '============================='
PRINT @.dbname
PRINT '============================='
--================================================== ================================================== =====
--================================================== ================================================== =====
OPEN get_tabs
FETCH NEXT FROM get_tabs
INTO @.tabname, @.id, @.shcontig1dt, @.shcontig2dt
IF @.@.FETCH_STATUS = 0
BEGIN
PRINT 'table: ' + @.tabname
SELECT @.runtabcursor = 1
end
ELSE
BEGIN
PRINT 'not getting any tables! '-- <<<<< THIS IS WHERE IT HITS
SELECT @.runtabcursor = 0
end
WHILE @.runtabcursor = 1
BEGIN
PRINT @.dbname
PRINT @.tabname
--================================================== ================================================== =====
OPEN get_inds
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt
IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0
WHILE @.runindcursor = 1
BEGIN
PRINT 'Index:' + @.dbname + '.' + @.tabname + '.' + @.indname
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt
IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0
END-- 1st loop through indexes
CLOSE get_inds
--================================================== ================================================== =====
--==========
PRINT 'db.tab: ' + @.dbname + '.' + @.tabname
--==========
--================================================== ================================================== =====
OPEN get_inds
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt
IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0
WHILE @.runindcursor = 1
BEGIN
PRINT 'dbname: ' + @.dbname
PRINT 'tabname: ' + @.tabname
PRINT 'indname: ' + @.indname
FETCH NEXT FROM get_inds
INTO @.indid, @.indname, @.defragdt, @.reindexdt
IF @.@.FETCH_STATUS = 0
SELECT @.runindcursor = 1
ELSE
SELECT @.runindcursor = 0
END -- 2nd loop through indexes
CLOSE get_inds
--================================================== ================================================== =====
FETCH NEXT FROM get_tabs
INTO @.tabname, @.id, @.shcontig1dt, @.shcontig2dt
IF @.@.FETCH_STATUS = 0
SELECT @.runtabcursor = 1
ELSE
SELECT @.runtabcursor = 0
END-- loop through tables
CLOSE get_tabs
--================================================== ================================================== =====
--================================================== ================================================== =====
PRINT 'Index Maintenence complete. Job report in
[DB_Rpt_Fragmentation]'
PRINT ''
FETCH NEXT FROM get_dbs
INTO @.dbname
IF @.@.FETCH_STATUS = 0
SELECT @.rundbcursor = 1
ELSE
SELECT @.rundbcursor = 0
END -- loop through databases
CLOSE get_dbs
deallocate get_dbs
deallocate get_tabs
deallocate get_inds
--================================================== ================================================== =====
--================================================== ================================================== =====
--================================================== ================================================== =====
GO
And this is what I'm getting:
=============================
Archive
=============================
(0 row(s) affected)
not getting any tables!
Index Maintenence complete. Job report in [DB_Rpt_Fragmentation]
..
..
..
etc.
Am I missing something obvious?
Thank you for any help you can provide!!One of my fellow emps got it - apparently the CURSOR needed to be
declare w/in the loop right before I opened it.
I moved the get_tabs and get_inds cursor declarations and all is well .
.. .sql
I know a sql stored procedure can return >1 tables. How can I use .Net 2.0 to read these tables one at a time, for example the first one could iterate Forum entries and the second one all internal links used in these forums... The idea is to use fewer backtrips to the sql server?
Isthis article of any use?|||no fortunately not :(RepID-- PhoneUPS
---- ----
3---- 3
RepID-- PhoneUPS
---- ----
4---- 0
RepID-- PhoneUPS
---- ----
5---- 2
No more results.
(9 row(s) returned)
@.RETURN_VALUE = 0
All of the tables have the same header row, but are a seperate table. From my experience, I am not able to bind more than one table to a datagrid. Does anyone have any suggestions on how I can go about displaying this full dataset on my aspx page? I've been going in circles on this for two days, so I'm open to any suggestions :)
Cheers,
AndrewCan you create a UNION query in the stored procedure, rather then seperate resultsets?
Failing that, you can manually add each row of each resultset to the Items collection of the Datagrid.|||Thanks for the tip. I've been working on my SQL procedure trying to incorporate the UNION in the SELECT statement. I am having problems with the logic of working it in, though. Here is the original SQL procedure I've created:
/*BEGIN Procedure */
ALTER PROCEDURE Rep_Status_Array
AS
/* Build array or ID numbers to cycle through */
DECLARE RepIDs_cursor CURSOR
FOR SELECT UserID
FROM TBL_Users
WHERE (TBL_Users.DepartmentID > 0)
OPEN RepIDs_cursor
DECLARE @.userID int
FETCH NEXT FROM RepIDs_cursor INTO @.userID
/* Begin WHILE loop to collect data for each ID */
WHILE (@.@.FETCH_STATUS <> -1)
BEGIN
SELECT
RepID=@.userID,
PhoneUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 11) AND (SalesmanID = @.userID)),
LOTUPS=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),
CVR=(SELECT Count(*) FROM TBL_UpEntry WHERE (TypeID = 1) AND (SalesmanID = @.userID)),
FETCH NEXT FROM RepIDs_cursor INTO @.userID
END
/* END WHILE loop */
CLOSE RepIDs_cursor
DEALLOCATE RepIDs_cursor
/* END Procedure */
The problem I'm having with this is that each time through the WHILE loop creates a new table in the dataset that is returned by the procedure. Any suggestions?|||Is it even possible to use UNION or UNION ALL within a WHILE loop?|||To the best of my knowledge, it is not possible to use the UNION statement in conjunction with a WHILE loop.
I'm going to go with Douglas' second recommendation and manually create a table within my page, adding each row one at a time.|||Another alternative:
Create a temporary table, and then INSERT all selected rows into the temp table, and then at the end of the SP,
SELECT * FROM #temp
(with any required ORDER BY).|||Thanks for the help, Douglas, much appreciated!
Andrew
i need to write a function to replace one field from another field in strode procedure .
as a example
SELECT
profc.SURNAME + ', ' + profc.FORENAME AS ProfCarer,
SPECS.DESCRIPTION AS Speciality,
steams.STEAM_REFNO_NAME AS StaffTeam,
REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,
COUNT(REFS.REFRL_REFNO) AS NoOfOpenReferrals
FROM REFS
LEFT OUTER JOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO
LEFT OUTER JOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO
LEFT OUTER JOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO
INNER JOIN PrimaryCareTrust pct ON REFS.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL
i need to replace REFS.PCT_CODE FROM GEOGAREA.PCT_CODE THIS FUNCTION NEED ONE INPUT PARAMETER CALLED REFS.REFTOPROCA_REFNO
Any Idea
Thank's
I'm a little confused.
You want to translate the REFS.PCT_CODE in order to use it in the joing on PrimaryCareTrust?
|||ya i need to replace refs.pct_code from another cord in all the strode procs|||OK, what is the the code you want as the replacement?
Where does it come from?
Where does it go within the query?
Please provide more info.
|||the code is GEOGAREA.PCT_CODE its from GeographicArea tableThis doesn't make sense, but anyway:
Code Snippet
SELECT
profc.SURNAME +', '+ profc.FORENAME AS ProfCarer,
SPECS.DESCRIPTION AS Speciality,
steams.STEAM_REFNO_NAME AS StaffTeam,
REFS.SORRF_REFNO_DESCRIPTION AS SourceOfReferral,
COUNT(REFS.REFRL_REFNO)AS NoOfOpenReferrals
FROM REFS
INNERJOIN GEOGAREA
ON REFS.PCT_CODE = GEOGAREA.PCT_CODE
LEFTOUTERJOIN PROFCARERS profc ON REFS.REFTO_PROCA_REFNO = profc.PROCA_REFNO
LEFTOUTERJOIN SPECS ON REFS.REFTO_SPECT_REFNO = SPECS.SPECT_REFNO
LEFTOUTERJOIN STAFFTEAMS steams ON REFS.REFTO_STEAM_REFNO = steams.STEAM_REFNO
INNERJOIN PrimaryCareTrust pct ON GEOGAREA.PCT_CODE = pct.PCTCode --AND REFS.CLOSR_DATE IS NULL
|||ya i know this mate. i have lots of procedures so what i want to do is i need create a function that i execute that it'll go and search that REFS.PCT_CODE in each and every procedures and replace it by GEOGAREA.PCT_CODEcan we do this?|||
You bet.
REFS.PCT_CODE is the input
GEOGAREA.PCT_CODE is the output
Now, how are REFS and GEOGAREA related?
How does REFS.PCT_CODE find the correct entry in GEOGAREA?
Spend a little bit time to change your quires(even it is on multiple stored procedures).
If you use function it may decrease the performance.
|||PCT_CODE STANDS geographic area code this are same but refs.pct_code is going to be change thats why.|||Niranga,
Please provide some details and specifics.
What is the current DDL/schema?
How is it changing?
Provide some sample table data and your expected results.
heres my problem, since I migrated to SQL-Server 2005, I was able to use theRow_Number() Over Method to make my Custom Paging Stored Procedure better. But theres onte thing that is still bothering me, and its the fact the Im still using and old and classic Count instruction to find my total of Rows, which slow down a little my Stored Procedure. What I want to know is: Is there a way to use something more efficiant to count theBig Total of Rows without using the Count instruction? heres my stored procedure:
SELECT RowNum, morerecords, Ad_Id FROM (Select ROW_NUMBER() OVER (ORDER BY Ad_Id) AS RowNum,morerecords = (Select Count(Ad_Id) From Ads) FROM Ads) as test
WHERE RowNum Between 11 AND 20
The green part is the problem, the fields morerecords is the one Im using to count all my records, but its a waste of performance to use that in a custom paging method (since it will check every records, normally, theres a ton of condition with a lot of inner join, but I simplified things in my exemple)...I hope I was clear enough in my explication, and that someone will be able to help me. Thank for your time.
Well, since you want to join a single value (the row count) of a table with other columns from the table, the single value must be returned as a result set from a subquery or a join table. If you don't like using count(Ad_Id) to get the row count, you can join the sysindexes table to get the row count for a specific table. For example:
SELECT RowNum, morerecords, Ad_Id,RowCnt
FROM (Select ROW_NUMBER() OVER (ORDER BY Ad_Id) AS RowNum
FROM Ads) as test,sysindexes s
WHERE RowNum Between 11 AND 20
and s.id=object_id('Ads')
and s.indid=(select min(indid)
from sysindexes where id=object_id('Ads'))
If you have a cluster index on the table, you can replace the green part with 1.
Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it.
@.UserNamenvarchar(128),
@.Emailnvarchar(50),
@.FirstNamenvarchar(25),
@.LastNamenvarchar(50),
@.Teachernvarchar(25),
@.GradYrint
DECLARE@.UserIDuniqueidentifier
SELECT@.UserID =NULL
SELECT @.UserID = UserIdFROMdbo.aspnet_UsersWHERE LOWER(@.UserName) = LoweredUserName
INSERT INTO[table name]
(UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
VALUES(@.UserID,@.UserName,@.Email,@.FirstName,@.LastName,@.Teacher,@.GradYr)
Also, add some error handling in the stored procedure after the insert. Something like as follows.
SET @.returnstatus = @.@.error
IF @.returnstatus <> 0
BEGIN
RETURN @.returnstatus
END
From what you show, it seems like you are making a "custom" way to just write Profile data (email, First, last, Teacher, GradYr). If you enable the Profile provider and supply these fields, you can use the built-in provider to do this kind of stuff - - no need to write your own.
|||
pbromberg:
From what you show, it seems like you are making a "custom" way to just write Profile data (email, First, last, Teacher, GradYr). If you enable the Profile provider and supply these fields, you can use the built-in provider to do this kind of stuff - - no need to write your own.
OK, I give. I just read a bunch of articles on the subject of profile provider and none of them really helped me--seemed like they were talking about creating instead of enabling profiles. Where's some good, simple information on activating the built-in provider. The closest I've come is implementing _CreatingUser on the CreateNewUser wizard, and listing the field names from my new table in web.config. If you can educate we where to go from here in order to enable the profile provider, I'd appreciate it.
|||I just read up on profiles in Walther'sUnleashed book, and came up with the stuff below for web.config. I get Intellisense in the code-behind, which is a good sign, but when I run the page, I can't get it right for "type = " and the book doesn't elaborate on it. Not having the correct entry for the type criteria or omitting it produces an error. If someone can help me on this, I think I'll be in good shape.
One other thing: Should I use a separate table (shown as tblAlumni below) or use one of the standard aspnet tables? I'd prefer the latter to keep things the most simple, but Walther shows an "outside" table. Thanks in advance for any help.
<profile defaultProvider="DNProfileProvider"> <properties> <add name="FirstName" /> <add name="LastName" /> <add name="GradYr" type="integer"/> <add name="Address1" /> <add name="Address2" /> <add name="City" /> <add name="State" /> <add name="Zip" /> <add name="SpouseName" /> <add name="Gender" /> <add name="MaidenName" /> <add name="Phone" /> </properties> <providers> <add name="DNProfileProvider" type="??" connectionStringName="sqlConnection" profileTableName="tblAlumni"/> </providers> </profile>|||
It depends on how the Profile class is written. The default profile provider has its own table and it can hold any type and number of fields that you define for each profile. However, this data is "opaque" in the database - you cannot easily search on it. If you want to use a custom profile provide as it seems from the snippet you posted, then it may use it's own table. There are some samples for "Table Profile Provider" and "Stored Procedure Profile Provider" that you can use as a model if you want a custom table.
Here is an article with some examples:
http://www.eggheadcafe.com/articles/20060731.asp
|||
Following is a stored procedure I'm thinking of using in my ASP 2.0 project and I need opinions so I can evaluate if this is the optimum way to access and write to my database. I will be writing to an extra table (in addition to the standard aspnet_ tables). If you can please let me know your opinion, I'd appreciate it.
@.UserNamenvarchar(128),
@.Emailnvarchar(50),
@.FirstNamenvarchar(25),
@.LastNamenvarchar(50),
@.Teachernvarchar(25),
@.GradYrint
DECLARE@.UserIDuniqueidentifier
SELECT@.UserID =NULL
SELECT @.UserID = UserIdFROMdbo.aspnet_UsersWHERE LOWER(@.UserName) = LoweredUserName
INSERT INTO[table name]
(UserID,UserName,Email,FirstName,LastName,Teacher,GradYr)
VALUES(@.UserID,@.UserName,@.Email,@.FirstName,@.LastName,@.Teacher,@.GradYr)
A new problem has arisen, having to do with database, that I need to resolve first. I make an entry to my form, save it to the database; then the next entry I make throws the error that the same UID can't be written to the database. I believe that, Mr. DB, but since when am I writing the same UID to you? I don't know where to start tracing this, except to show the stored proc again (above), and to describe my process and some of my code that might generate it.
Protected Sub cuwCreateUserWizard1_CreatingUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cuwCreateUserWizard1.CreatedUser strEmail =CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Email"), TextBox).Text strUserName =CType(cuwCreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox).Text.ToLower strFirstName =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtFirstName"), TextBox).Text strLastName =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtLastName"), TextBox).Text lngGradYr =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtGradYr"), TextBox).Text strTeacher =CType(cuwCreateUserWizard1.CreateUserStep.CustomNavigationTemplateContainer.FindControl("txtTeacher"), TextBox).TextEnd Sub Protected Sub cuwCreateUserWizard1_CreatedUser(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles cuwCreateUserWizard1.CreatedUserDim cmdAs New SqlCommand("sp_CreateUser", con) cmd.CommandType = Data.CommandType.StoredProcedure cmd.Parameters.AddWithValue("@.UserName", strUserName) cmd.Parameters.AddWithValue("@.Email", strEmail) cmd.Parameters.AddWithValue("@.FirstName", strFirstName) cmd.Parameters.AddWithValue("@.LastName", strLastName) cmd.Parameters.AddWithValue("@.Teacher", strTeacher) cmd.Parameters.AddWithValue("@.GradYr", lngGradYr) Using con con.Open() cmd.ExecuteScalar() con.Close()End Using cmd.Equals(Nothing)End Sub Please let me know any other info you'd need to help me determine what's wrong.|||
Can you please start a new POST / Thread. By this way we can concentrate your new issue. Since a post is marked as answered, every one will think that your issue is resolved. Hope you understand.
I am working on a stored procedure that ultimately puts data into an excel report. What I'm looking for is the most efficient way to "split" the data in the table if the table has more than 65535 rows. I have already coded for more than one report if this happens, but I still need to split the data. I could do "SELECT TOP 65535..." for the first report, but that leaves me with the problem of getting the next 65535 into the next report, etc. Also, I thought about using a cursor, but as far as I know a SQL Server cursor can only return 1 row at a time. This is the code I have so far for this part of the stored procedure:
DECLARE @.RecordCount FLOAT, @.RowCap FLOAT, @.Counter INT, @.NumberOfReports INT
DECLARE @.AttachmentList NVARCHAR(MAX)
SET @.RowCap = 65535
SELECT @.RecordCount = COUNT(*) FROM ##MyTempTable
SET @.NumberOfReports = CAST(ROUND(CEILING(@.RecordCount/@.RowCap), 0) AS INT)
SET @.Counter = 1
WHILE @.Counter <= @.NumberOfReports
BEGIN
SET @.rptLongDesc = '<h5><center>ReportFrom ' + Convert(varchar,@.@.ServerName) + '</center></h5>'
SET @.rptName = Report_' + CONVERT (char (3),DATENAME(Month, GetDate())) + CONVERT (char (3),DATENAME(day, GetDate()))+
CONVERT (varchar (4),YEAR (GetDate())) + CAST(@.Counter AS CHAR(1))
SET @.rptPath = '\\Reports\'
SET @.rptOutputFile = @.rptPath + @.rptName + '.xls'
IF @.Counter = 1
SET @.AttachmentList = @.rptOutputFile
ELSE
SET @.AttachmentList = @.AttachmentList + ';' + @.rptOutputFile
EXEC sp_makewebtask
@.htmlheader = 3,
@.outputfile = @.rptOutputFile,
@.query = 'SELECT * FROM ##MyTempTable', --This needs to be split into groups of 65535 for each report.
@.resultstitle = @.rptLongDesc,
@.webpagetitle = @.rptName
SET @.Counter = @.Counter + 1
END
As you can see, the @.query parameter for sp_makewebtask needs to be fixed. I would appreciate any suggestions. Thanks.
Dave
DECLAREDECLARE @.AttachmentList NVARCHAR(MAX)
SET @.RowCap = 65535
SELECT @.RecordCount = COUNT(*) FROM ##MyTempTable
SET @.NumberOfReports = CAST(ROUND(CEILING(@.RecordCount/@.RowCap), 0) AS INT)
SET @.Counter = 1
WHILE @.Counter <= @.NumberOfReports
BEGIN
SET @.rptLongDesc = '<h5><center>ReportFrom ' + Convert(varchar,@.@.ServerName) + '</center></h5>'
SET @.rptName = 'Report_' + CONVERT (char (3),DATENAME(Month, GetDate())) + CONVERT (char (3),DATENAME(day, GetDate()))+
CONVERT (varchar (4),YEAR (GetDate())) + CAST(@.Counter AS CHAR(1))
SET @.rptPath = '\\Reports\'
SET @.rptOutputFile = @.rptPath + @.rptName + '.xls'
IF @.Counter = 1
SET @.AttachmentList = @.rptOutputFile
ELSE
SET @.AttachmentList = @.AttachmentList + ';' + @.rptOutputFile
EXEC sp_makewebtask
@.htmlheader = 3,
@.outputfile = @.rptOutputFile,
@.query = ' SELECT TOP (@.cnt*65535) * FROM ##MyTempTable
EXCEPT
SELECT TOP ((@.Counter-1) * 65535) * FROM ##MyTempTable', --This needs to be split into groups of 65535 for each report.
@.resultstitle = @.rptLongDesc,
@.webpagetitle = @.rptName
SET @.Counter = @.Counter + 1
END
Note: If you think of performance issues, I would suggest to create a identity column on your temptable and split the data based on that.|||
Hi,
Another possibility is using partioned tables. You set an identity column in the table and create a partition function on that column with intervals of 65535.
Reference: http://msdn2.microsoft.com/en-us/library/ms188730.aspx
Greetz,
Geert
Geert Verhoeven
Consultant @. Ausy Belgium
My Personal Blog
I thought I could just copy over some asp.net code like:
System.Web.Mail.MailMessage mailMessage =new System.Web.Mail.MailMessage();
But VS2005 doesn't seem to want me touching System.Web.Mail.
Any ideas?
Thanks,
Allen
OK, I figured out that I need to useSystem.Net.Mail, but here is the next problem- when I create an instance of the SmtpClient object like this:
SmtpClient
client =newSmtpClient("localhost",25);I get this exception:System.Security.SecurityException: Requestfor the permissionof type'System.Net.Mail.SmtpPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.CodeAccessPermission.Demand() at System.Net.Mail.SmtpClient.Initialize() at System.Net.Mail.SmtpClient..ctor(String host, Int32 port)|||
has anyone managed to solve this one?
am getting the same error...
|||I was able to get around that error (and a few others) by doing the following:
ALTER DATABASE [PUBS]SET TRUSTWORTHYONGOALTER ASSEMBLY [TaskScheduler]WITH PERMISSION_SET = UNSAFE
I hope this helps.
-Allen Cryer
|||I was able to get around that error (and a few others) by doing the following:
ALTER DATABASE [PUBS]SET TRUSTWORTHYONGOALTER ASSEMBLY [TaskScheduler]WITH PERMISSION_SET = UNSAFE
I hope this helps.
-Allen Cryer
|||mate,you're a champ.I've looked for the last 24 hours all over.Thank you very much. Works like a charm now.UPDATE YourTable
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3)
... etc
WHERE ...
--
David Portas
SQL Server MVP
--|||>> In general, What is the best approach in creating a dynamic update
stored procedure, <<
In general, building dynamic is a bad idea. It says that you don't
know what you are doing, so you are turning over control of the system
at runtime to any random user, present or future. SQL is a compiled
language, not like BASIC.|||what would be wrong with using:
UPDATE YourTable
SET col1 = COALESCE(@.col1, col1),
col2 = COALESCE(@.col2, col2),
col3 = COALESCE(@.col3, col3)
... etc
WHERE ...
if i want to have one stored procedure to update a table.|||jw56...@.gmail.com wrote:
> if i want to have one stored procedure to update a(ny) table.
what would be wrong
--Strider|||jw56...@.gmail.com wrote:
> if i want to have one stored procedure to update a(ny) table.
what would be wrong
--Strider|||I think this is just some confusion over terminology. The term "dynamic
update" or "dynamic code" refers to code that references metadata
(usually table and column names) dynamically - elements of the code
being constructed at runtime. This is not generally good practice for
various reasons to do with performance, security, maintainability and
modular design. In your case however, no dynamic code is necessary.
--
David Portas
SQL Server MVP
--