I can design a report fine in the Report Designer. I can build it fine. I can preview it fine. But, when I try to deploy it I get the error: No mapping between account names and security IDs was done
Any help will be appreciated.
Glenn,
Did you get past this error? I'm having the same issue now with my SQL Server Reporting Services / Report
I can design a report fine in the Report Designer. I can build it fine. I can preview it fine. But, when I try to deploy it I get the error: No mapping between account names and security IDs was done
Any help will be appreciated.
Glenn,
Did you get past this error? I'm having the same issue now with my SQL Server Reporting Services / Report
I am writing a tracking system. There is a table in the Sql Server 2000 database that contains a column for the user's ntid, the page they visited, the date of the last visit, a column each to track hits for the current year and a previous year column (basically for archiveing and reporting purposes), and 12 columns for hits per month (obviously, one per column). To record a hit, my unit determined we would only track one hit per day, so basically, there are 3 possible outcomes I needed to account for :
1) A user had never hit the page before, so I need to record the user's ID, the page they hit for the first time (since it won't exist yet), increment the year counter for that user on that page, and then determine what month column counter should be incremented as well.
2) A user had hit the page before, but not on this same day, so I need to update the row for that user on that page, changing the last visit field to reflect the current date, and icnrementing the appropriate counters.
3) A user had hit the page already on the same day, so basically, nothing should be changed whatsoever. No action should be taken.
I wrote a stored procedure to attempt to accomplish that logic, and though it's probably not very pretty, I was surprised at how few errors I got on my first Syntax check. Here's the stored procedure :
CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit datetime
AS
SET NOCOUNT ON
DECLARE @.tempDate datetime
DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END
DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking (ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (' + @.ntid + ', ' + @.page + ', ' + @.thisHit + ', 1, 1)'
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
end
else
begin
DECLARE @.theColumn varchar (100)
SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Exec @.theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
Exec @.insString
end
GO
And to my surprise, the only 3 errors I got were :
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 39
Incorrect syntax near the keyword 'end'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 45
Incorrect syntax near the keyword 'Exec'.
Server: Msg 156, Level 15, State 1, Procedure sp_hitMe, Line 50
Incorrect syntax near the keyword 'end'.
However, these are of course so vague as to be useless to me. What's wrong with the procedure? What have I missed?
At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else
An alternative is finding out how to do something like an if not exists()
As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.
|||
LD50:
At around line 33 I changed the --Do Nothing so that it actually declares something that is never used...
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
elseAn alternative is finding out how to do something like an if not exists()
As far as the Exec, why don't you make @.theColumn a temporary table instead, and work with it that way? I don't know about using variables as tables, but you can do this easily with a temporary table.
That do nothing must actually have been a slightly larger error, because when I changed it to declare a small variable (basically a bit, just to avoid too much memory usage), 2 of the errors went away. I'm still having ti gripe at me about the Exec (@.theColumn) line. I don't think in this case a temporary table will do the trick only because I'm basically only trying to get one value, which is the current value of the month counter. Any other ideas?
|||I'm not quite sure what you're trying to do, or what the column names in tbltracking are, so you'll have to modify this a bit. But it will compile.
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
DECLARE @.theColumn1 varchar (100)
end
else
begin
Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
An alternative is finding out how to do something like an if not exists()
That's exactly how you do it. IF NOT EXISTS(SELECT ...)
|||Oh, THANK you! I did have to slightly modify what you put in there, but you had the gist of it, so that's awesome. I would go ahead and mark an answer here, and I probably will since you answered that concern, but I was testing the logic, and trying to input the same record twice, which should have simply done nothing, but instead it inserted a duplicate record. Any idea why the logic failed me there?
|||well why don't you paste up your new code and we'll let you know...
A good troubleshooting tip is to add some select statements in each part of the if. That way you can run it and see which if statement is firing and/or see if the columns in the table contain the data you expect.
Example:
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
-- DO NOTHING!
select * from tblTracking where lastVisit = @.thisHit
DECLARE @.theColumn1 varchar (100)
end
else
begin
select * from tblTracking
Create Table #theColumn (theColumn varchar (100))
Insert into #theColumn
Select monthname from tblTracking Where ntid = @.ntid and page = @.page
--SET @.theColumn = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND @.page = page'
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND @.page = page) + 1
SET @.monthCount = (Select Count(*) from #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + @.thisHit + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND @.page = page'
Exec @.updString
end
end
else
begin
select * from tblTracking
Exec @.insString
end
I also noticed that your if is illogical
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
should be:
if exists(select * from tblTracking where ntid = @.ntid and page = @.page)
|||
I also noticed that your if is illogical
if exists(select * from tblTracking where ntid = @.ntid and @.page = page)
should be:
if exists(select * from tblTracking where ntid = @.ntid and page = @.page)
Actually, I noticed that myself and had already corrected it... *sheepish shrug*
OK, well I've added in a SELECT statement for the 'do-nothing' branch of logic and changed the two Exec statements to Print statements so I don't get a ton of duplicate records. But the logic is still somehow faulty, as I'm using the Query Analyzer to test it, and trying to input the same record that already exists in the table still prints out the INSERT string, instead of the do-nothing SELECT statement. Here's what I have now, see if you can help me figure out what the problem is...
CREATE PROCEDURE sp_hitMe
@.ntid varchar(10),
@.page varchar(50),
@.thisHit smalldatetime
AS
SET NOCOUNT ON
DECLARE @.yearCount int
DECLARE @.monthCount int
DECLARE @.inMonth varchar(20)
DECLARE @.monthColumn varchar(10)
SET @.inMonth = DATENAME(mm, @.thisHit)
SET @.monthColumn =
CASE
WHEN @.inMonth = 'January' THEN 'hitsInJan'
WHEN @.inMonth = 'February' THEN 'hitsInFeb'
WHEN @.inMonth = 'March' THEN 'hitsInMar'
WHEN @.inMonth = 'April' THEN 'hitsInApr'
WHEN @.inMonth = 'May' THEN 'hitsInMay'
WHEN @.inMonth = 'June' THEN 'hitsInJun'
WHEN @.inMonth = 'July' THEN 'hitsInJul'
WHEN @.inMonth = 'August' THEN 'hitsInAug'
WHEN @.inMonth = 'September' THEN 'hitsInSep'
WHEN @.inMonth = 'October' THEN 'hitsInOct'
WHEN @.inMonth = 'November' THEN 'hitsInNov'
WHEN @.inMonth = 'December' THEN 'hitsInDec'
END
DECLARE @.insString varchar(500)
DECLARE @.updString varchar(500)
SET @.insString = 'INSERT INTO tblTracking(ntid, page, lastVisit, hitsThisYear, ' + @.monthColumn + ') VALUES (''' + @.ntid + ''','' ' + @.page + ''', ''' + Convert(varchar(15), @.thisHit, 101) + ''', ''1'', ''1'')'
if exists(select * from tblTracking where ntid = @.ntid and page = @.page)
begin
if exists(select * from tblTracking where lastVisit = @.thisHit)
begin
DECLARE @.bs bit
SELECT * FROM tblTracking where lastVisit = @.thisHit
end
else
begin
DECLARE @.theString varchar (100)
SET @.theString = 'SELECT ' + @.monthColumn + ' FROM tblTracking WHERE ntid = @.ntid AND page = @.page'
CREATE TABLE #theColumn (theColumn varchar (100))
INSERT INTO #theColumn
Exec @.theString
SET @.yearCount = (SELECT hitsThisYear FROM tblTracking WHERE ntid = @.ntid AND page = @.page) + 1
SET @.monthCount = (SELECT theColumn FROM #theColumn)
SET @.monthCount = @.monthCount + 1
SET @.updString = 'UPDATE tblTracking SET lastVisit = ' + Convert(varchar(15), @.thisHit, 101) + ', hitsThisYear = ' + @.yearCount + ', ' + @.monthColumn + ' = ' + @.monthCount + ' WHERE ntid = @.ntid AND page = @.page'
Print @.updString
end
end
else
begin
Print (@.insString)
end
GO
Woo! Nevermind, I have finally figured it out. Turns out I needed to use LIKEs instead of =s when I was comparing the varchar values, and I needed to throw in some CASTs to turn some of the ints into varchars for the UPDATE string. If anyone cares to see the corrected, working code, I will post it, but it is working now.
Thanks for the help!
Hello All,
I have a package in which I have enabled "Package Configuration".
When I run the package i am sure that it reads the configuration file and executes the package correctly.
However if I remove the configuration file, the package still executes correctly with the settings which were used at the time of development.
I have event handlers for OnError and OnWarning and both these are NOT invoked.
IMO, this is incorrect behavior because if a package has been configured for "package configuration, then we should atleast have a warning generated that SSIS did not find the configuration and it would execute the package with hard coded values (from the time of development).
Is there any work around for this? how can I make SSIS warn me if the config file is missing for a package which was configured for package configuration?
A missing configuration should trigger a warning and then the package uses the values provides at design time. I always get a the warnings.|||What event handlers have you written?
I have written event handlers at package level for OnError and OnWarning
In both the event handlers I log the message into the windows event log.
The only messages I see in the windows event log is the "package started" and "package completed".
regards,
Abhishek.
|||I was talking about SSIS logining. I choose OnError and OnWarning.|||The first thing I would look at is what circumstances are causing the configuration files to be missing at run time, as it seems like this is the root cause of your problem. The behavior you're describing appears to be "by design" to me - this is what I would expect, although I would also expect a warning to be logged if the configuration file were missing.
One possible workaround would be to have a Script task or custom component explicitly check for the existence of the file(s) before the main package logic executes, and only continue if the config files exist.
Another possible workaround would be to ensure that the design time values for the settings that should be set by the package configuration are obviously invalid. (For example, string that is not a valid path, or the like, depending on what you're setting in your configurations.) This would cause the package to fail outright at run time if the configuration could not be set, as opposed to executing with incorrect values and failing silently.
With that said, neither one of these workarounds seems particularly clean or ideal to me. I'd look at the root cause (why are the files missing) before doing anything else.
|||> The first thing I would look at is what circumstances are causing the configuration files to be missing at run time
The circumstance is that we are testing our operations management strategy. We are deliberately causing things to go wrong in our test environment and we see if MOM (Microsoft Operations Manager) informs the admin correctly of what went wrong.
regards,
Abhishek.
No Error but Export to Excel does not finish
When the report has 2 pages with total 500 rows exporting to Excel is not a problem.
If it has 100 pages 5000 rows exporting to excel does not end and it does not return any error but the process does not end either. What might the problem be?
How you are exporting?
Are you using SSIS or DTS package?
I don't see any relation between reporting services & export to excel or are you trying to save as EXCEL spreadsheet.
|||In there report manager, I run the report and then choose excel and export.
|||Try increasing the value of MemoryLimit in the RSReportServer.config file available under Program Files\Microsoft SQL Server\MSSQL\Reporting Services\ReportServer folder. The default value is 60, try increasing to a higher value but keep it under 120.
Shyam
sqlHello,
I am not sure if this is the right forum.
This is the first time that I have seen this error,
I have a web app that when some one inserts text in a text box and clicks a button it creates a new web page and a new table in the database. Up until this point it has always worked. A datalist returns the data. In the datalist there is a hyperlink that when clicked takes the client to the new webpage. But for some reason it is not working now and that is the error above.
I am lost. Any help appreciated.
Thanks Matt
I figured it out.
Thanks
Matt
Thanks a lot, it worked for me !!
sql
hoopslife:
If you have Personal MDF opened in the Sever Explorer try disconnecting and then running your page again.
LazyWriter: warning, no free buffers found.
After that I have to restart the SQLServer service to get things up and
running. How can I prevent this from happening?
Thanks,
RayHi
Without knowing the configuration values it is hard to say exactly what is
happening. a likely cause may be lack of memory and it may also mention this
in your SQL Server log.
You may want to search for the error message on Google. There are lots of
posts about pinning tables in 6.5 so your version number is also important
information.
John
"Ray" <someone@.nowhere.com> wrote in message
news:vT3Ad.4051$Y8.1567@.newssvr17.news.prodigy.com ...
> Occasionally on my SQL 2K SP3 Standard servers. The servers lock up with
> the error.
> LazyWriter: warning, no free buffers found.
> After that I have to restart the SQLServer service to get things up and
> running. How can I prevent this from happening?
> Thanks,
> Ray
Sql client for 2003 server web edition
2. i tried to install sql client in window 2003 web edition but the msdn cd could not start after i double click on the setupsql.exe. I thought the sql client can be install but not the server on web edition ?I have solve Q1 above by using run the type in
Setup.exe /qb+ INSTANCENAME=NetSDK DISABLENETWORKPROTOCOLS=1 SAPWD=<YOUR SA PASSWORD
It seems you cannot double click on the setup.exe in the extracted folder and you cannot use the ms-dos command prompt becuase both return errors like,
"A strong SA password is required for security reason. Please us SAPWD switch to supplyt he same. Refer to readme for more details. Setup will now exits."
or
"Go to the control panel to install and configure system components"
Hope some one can help on Q2|||I think you can not install sql clinet tools on 2003 WE
We have a new big project coming, which will use Notification Services.
So, I bought a book: "Microsoft SQL Server 2000 Notification Services" by Shyam Pather and did all steps including installing NS, setting up the environment, and creating the first sample NS application (Chapter03). Every single step was successful, no errors. I am pretty sure I didn't miss anything.
All went well until I copied a sampla data file into the EventsWatchDirectory.
At this point, the file name almost immediately changes to EventData.xml.20060908-160948.370.err (digits are different at every attempt, but please note the extension).
I open this file using Notepad, and it is the exact copy of the original .xml file:
<eventData>
<StockPriceChange>
<StockSymbol>XYZ</StockSymbol>
<StockPrice>55.55</StockPrice>
</StockPriceChange>
<StockPriceChange>
<StockSymbol>PQS</StockSymbol>
<StockPrice>95.3</StockPrice>
</StockPriceChange>
<StockPriceChange>
<StockSymbol>JKL</StockSymbol>
<StockPrice>15.0</StockPrice>
</StockPriceChange>
</eventData>
The EventViewer has the following message:
<NotificationServicesEvent>
<Description>
The SQL XML Bulk Loader could not be initialized. Ensure that it is installed or repair its installation.
</Description>
<Context>
</Context>
<Description>
Retrieving the COM class factory for component with CLSID {1DB51355-B2CA-43CB-B045-1FAA42A724B2} failed due to the following error: 80040154.
</Description>
<Instance>Chapter03</Instance>
<ApplicationName>Stock</ApplicationName>
<Component>Event collection</Component>
<Thread>16</Thread>
</NotificationServicesEvent>
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
The link did not have info on this error.
My environment is as follows:
XP Pro SP2; SQL Server 2000 Dev SP4; SQL Server NS 2.0 SP1; SQLXML3.0 SP3.
Could someone kindly point me to the right direction as to what's wrong, please?
To my great regret, I found (by trial and error all over the past weekend) what was conflicting with the NS:
it's the .NET Framework 2.0.
As soon as I uninstalled it, the sample application started working as expected (that is, the file's extension in the Notifications directory changes to ".done").
...which means I won't be able to use VS 2005 and SQL Server 2005 on the same dev machine with NS 2000.
|||Although I don't doubt what you observed, you should be able to run a SQLNS instance on a machine that has v2.0 of the .NET framework installed.
Try this post and see if it helps...
http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_frm/thread/aa48d73a8ed227a1/19cdd27c687ca70d?lnk=gst&q=framework+&rnum=2#19cdd27c687ca70d
HTH...
Joe
|||Joe, thank you very much!
I followed your advice, and got it to work.
We have a new big project coming, which will use Notification Services.
So, I bought a book: "Microsoft SQL Server 2000 Notification Services" by Shyam Pather and did all steps including installing NS, setting up the environment, and creating the first sample NS application (Chapter03). Every single step was successful, no errors. I am pretty sure I didn't miss anything.
All went well until I copied a sampla data file into the EventsWatchDirectory.
At this point, the file name almost immediately changes to EventData.xml.20060908-160948.370.err (digits are different at every attempt, but please note the extension).
I open this file using Notepad, and it is the exact copy of the original .xml file:
<eventData>
<StockPriceChange>
<StockSymbol>XYZ</StockSymbol>
<StockPrice>55.55</StockPrice>
</StockPriceChange>
<StockPriceChange>
<StockSymbol>PQS</StockSymbol>
<StockPrice>95.3</StockPrice>
</StockPriceChange>
<StockPriceChange>
<StockSymbol>JKL</StockSymbol>
<StockPrice>15.0</StockPrice>
</StockPriceChange>
</eventData>
The EventViewer has the following message:
<NotificationServicesEvent>
<Description>
The SQL XML Bulk Loader could not be initialized. Ensure that it is installed or repair its installation.
</Description>
<Context>
</Context>
<Description>
Retrieving the COM class factory for component with CLSID {1DB51355-B2CA-43CB-B045-1FAA42A724B2} failed due to the following error: 80040154.
</Description>
<Instance>Chapter03</Instance>
<ApplicationName>Stock</ApplicationName>
<Component>Event collection</Component>
<Thread>16</Thread>
</NotificationServicesEvent>
For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
The link did not have info on this error.
My environment is as follows:
XP Pro SP2; SQL Server 2000 Dev SP4; SQL Server NS 2.0 SP1; SQLXML3.0 SP3.
Could someone kindly point me to the right direction as to what's wrong, please?
To my great regret, I found (by trial and error all over the past weekend) what was conflicting with the NS:
it's the .NET Framework 2.0.
As soon as I uninstalled it, the sample application started working as expected (that is, the file's extension in the Notifications directory changes to ".done").
...which means I won't be able to use VS 2005 and SQL Server 2005 on the same dev machine with NS 2000.
|||Although I don't doubt what you observed, you should be able to run a SQLNS instance on a machine that has v2.0 of the .NET framework installed.
Try this post and see if it helps...
http://groups.google.com/group/microsoft.public.sqlserver.notificationsvcs/browse_frm/thread/aa48d73a8ed227a1/19cdd27c687ca70d?lnk=gst&q=framework+&rnum=2#19cdd27c687ca70d
HTH...
Joe
|||Joe, thank you very much!
I followed your advice, and got it to work.
