Does Anyone Know Of A Great Access Help Site Similar To This.
May 17, 2000Does any one have information on any good Access Sites that are similar to this one, which could be helpful in completing some task. Thanks.
View 1 Replies
ADVERTISEMENT
Populating SQL Database With Something Similar To Access's Dropdown Lookup Field.
Sep 5, 2007I am using two tables, courses and video with a one to many relationship with the videos table being the many. How do I populate the courseID in the video table with the corresponding courseID from the courses table using a combination of Formview & Gridview or two Gridviews, or a dropdown field populated with the course name and courseID in a Form or details view.This is a simple task in Access accomplished using a dropdown list to select the correct video.
View 1 Replies View RelatedHelp! Site Crashing On Data Access When Busy!
Dec 4, 2007Clearly, my code isn't written as well as it should be. I don't understand enough about data access and could use some help.I have several database tables, but one primary table that is the most accessed. Generally, I need to build a list from the data based on some filter. I'm using a repeater control, since all I need to display per record is a name, maybe a city or birthday, and possibly a little graphic, and my customer doesn't want a grid type of display. The filter is determined by the page requested. The exception is a search page where the user builds the filter and a grid is used to display the results.The results always contain a link to a page that has more detail on the selected record. What is the best way to handle this? I'm still trying to get a handle on different ways to get data and I'm not doing much with caching. Would it make sense to keep the data in memory from the page that displays the list (or search page) to the detail page? What if the detail page is accessed directly, say from a bookmark? How do I cache this?I'm currently using strongly typed datasets.Below is an example of what I'm doing - this is from the code behind of one of the list pages - members with birthdays this month. Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load Dim theMonth As String = DateTime.Now.ToString("MMMM") Me.LabelMonth.Text = theMonth Dim MemberAdapter As New WAPTableAdapters.membersTableAdapter Repeater1.DataSource = MemberAdapter.GetBirthday("Female", DatePart("m", Today)) Repeater1.DataBind() Repeater2.DataSource = MemberAdapter.GetBirthday("Male", DatePart("m", Today)) Repeater2.DataBind() End Sub Protected Sub Repeater1_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater1.ItemDataBound If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label) Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow) If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then LabelIcon.Visible = True End If End If End Sub Protected Sub Repeater2_ItemDataBound(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.RepeaterItemEventArgs) Handles Repeater2.ItemDataBound If e.Item.ItemType = ListItemType.Item OrElse e.Item.ItemType = ListItemType.AlternatingItem Then Dim LabelIcon As Label = CType(e.Item.FindControl("LabelIcon"), Label) Dim person As WAP.membersRow = CType(CType(e.Item.DataItem, System.Data.DataRowView).Row, WAP.membersRow) If System.IO.File.Exists(Server.MapPath("~/images/picts/" & person.FILE2 & ".jpg")) Then LabelIcon.Visible = True End If End If End Sub It seems pretty simple and straightforward to me, but these pages shouldn't be crashing when the site gets busy, so I have to be doing something wrong. Diane
View 5 Replies View RelatedSite-Wide Security: Restrict Access
Feb 27, 2007Hi,
I have added several Active Directory groups and set the system roles for each to "System User" and set one of the groups (DBAdmin) to "System Adminstrator"
My issue is that even after doing this, the users in the other groups are able to access the "Configure site-wide security" link under Security and change the permissions. The only system permission these users have is "View shared schedules" so it doesn't seem that this should be possible.
I would appreciate any feedback on this issue. Thanks!
SQLExpress - Remote Access Trouble When Publishing A Site
Sep 12, 2007Hi all,
I followed this excellent tutorial (from Microsoft) about creating and using a SQLExpress db table internal to your website:
http://www.asp.net/learn/videos/video-08.aspx
and the site I created works just fine - locally. When I publish to my web host provider I get this error:
[SqlException (0x80131904): An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)]
My web host provider supports .NET 2.0 but doesn't support MS SQL Server. But that should matter - right? Because I'm putting the db instance right in the website?
I did a search and followed some of the advice:
I went into the SQL Server Surface Area Config Mgr and set SQLExpress for local and remote connections and rebooted/republished. I also have SQL Server 2005 installed as a separate entity (if that makes a difference).
I still get the above error. This is the connection string from the web.config:
<connectionStrings><add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|Customers.mdf;Integrated Security=True;User Instance=True" providerName="System.Data.SqlClient"/>
</connectionStrings>And this is the call from the markup:
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>"
SelectCommand="SELECT * FROM [Customer]"></asp:SqlDataSource>
Does any know the fix for this?
Thanking you in advance
Pat
Access Denied Virtual Directory ReportServer In Default Web Site
May 15, 2008Hi all,
I have some problem about Reporting Service integrated for SharePoint 2007.
At first, I created 1 user named "User1" under "Domain1". User1 has role "SharePointAdmin" Group and Local Admin Group.
Then I created application pool named "RSAppPool". Its identy was "Domain1User1".
After that, I install Reporting Service and SP2.
Then I config Reporting Service to Integrated Sharepoint Mode below.
Report Server Directory Setting: "ReportServer" in "Default Web Site".
Window Service Identity:
Service Account : "Domain1User1" (Windows Accout)
Web Service Identity:
Report Server: "RSAppPool" (that created above)
Report Manager: Empty
Database Setup:
Database name: Database1 (Server Mode: SharePoint Integrated)
Credential Type : Windows Credentials
Account Name: Domain1User1
After configuration, I reset IIS and test browse to http://servername/ReportServer/ , then there is a Windows Login dialog.
I enter "Domain1User1" and User1's password. But User1 cannot right to access to "ReportServer" virtual directory.
This is error message
You are not authorized to view this page
You do not have permission to view this directory or page using the credentials that you supplied.
HTTP Error 401.1 - Unauthorized: Access is denied due to invalid credentials.
Internet Information Services (IIS)
I'm not sure. Where is I config wrong? ....
Please help me.
Thank you very much.
How To Basically Copy Tables With New Names Rather Than Create Similar Tables From Similar Manual Input.
May 26, 2007I have a table that I am basically reduplicating a couple of times for each part of this database that I want to create.Each table basically has the same data: The tables will be called motherTable, fatherTable, sonTable, daughterTable and so on.I am pretty much using the following in each column: UserID, MotherID(or FatherID or SonID, etc., etc. and so on for each unique table), FirstName, LastName, MiddleName, BirthPlace, Photo, Age.I don't see an option to copy a table and just modify the second ID part and rename that table accordingly.How can I make this an easier way of creating these similar tables without retyping all these columns over and over again?Thanks in advance.
View 4 Replies View RelatedSQL Hell....little Help Would Be Great!
Jan 4, 2005hi, im currently on sql hell right now. im having a hard time learning this sql thingie....
...the thing is this: im currently using the book ASP.NET Unleashed and most of the examples there are on SQL. what i was trying to do before was convert everything to OleDb to fit the ms access which i have right now.
unfortunately, some of the codes seem not to work properly. maybe its because of im using OleDb...
so what i did was i downloaded the MSDE sp3 package and installed it on my PC. now that i have an sql server for my WebMatrix, i just dont know what to do next? i mean, where do i put the sql sample databases like northwind and pubs???
im really confused about this sql thing. i really hate it.
help!!!
Great MS SQL Sites?
Mar 17, 2004Does anyone know of any links to some great MS SQL sites I can check out to learn from?
Thanks for your thoughts.
Sincerely,
Tim
Between Or Great Than Operator
Feb 10, 2014 Im not getting data when I execute the below 0 Rows:
select mydate from [dbo].mytable
where convert(varchar, mydate,101) between '11/18/2013' and '02/08/2014'
However the below gives results...
select mydate from [dbo].mytable
where convert(varchar, mydate,101) between '01/01/2013' and '02/08/2014'
Morning - Any Help Would Be Great
Jan 30, 2007I have two servers both with different collation. Server A being SQL_Latin1_General_CP1_CI_AS and the live server and Server B being Latin1_General_CI_AS and a dev server. Now i have a load of data on the dev which i'm query to see if its on the live server.
select * from ServerA.Table1 where Col1 in
(select Col1 from ServerB.Table1)
I get this Error message.
Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in the equal to operation.
TARA Is Great
Nov 28, 2007I am SQL server DBA for 4 years. ALways i am getting help from TARA and other SQL gurus. Salute to all
View 1 Replies View RelatedGreat Plains V8
Aug 20, 2007Hope someone can solve my problem
I have a working version of the above installed on a win 2000 machine with SQL ver (MSDE2000A.exe)
I purchase a laptop with vista home premium installed on it
MSDE2000A.exe would not install hence I installed SQL Server Express I managed to install the server and connect but when trying to login through Great Plains it says that I need SQL version 7. I have done a little research and found that SQL 7 is not supported by Vista.
Which way do I go now ?????
Do I install a copy of xp, update Great Plains or wait for an updated version of SQL Server Express SP ???
Please can anyone help !!!
and please dont get too technical Im not an expert
Many Thanks
Mr Magoo
Help With Great Plains Query
May 31, 2006Greetings,
We currently use GP 8.00 with the SQL Server 8.0.
We are trying to develop a view based on manfacturing orders - when a finished good is placed on hold and then calculating the componet parts that are on hold.
The calculation runs fine, we multiply end quanity x componet quanity found on the BOM. But we are not getting the correct componet item numbers to display.
Here is the syntax we are using:
SELECT DISTINCT
TOP 100 dbo.BM010115.QUANTITY_I, dbo.WO010032.ENDQTY_I, dbo.WO010032.ENDQTY_I * dbo.BM010115.QUANTITY_I AS QTY_REQHOLDMO,
dbo.BM010115.CPN_I, dbo.WO010032.MANUFACTUREORDER_I, dbo.WO010032.ITEMNMBR, dbo.WO010032.MANUFACTUREORDERST_I
FROM dbo.TEC_MOSumm RIGHT OUTER JOIN
dbo.WO010032 ON dbo.TEC_MOSumm.ITEMNMBR = dbo.WO010032.ITEMNMBR RIGHT OUTER JOIN
dbo.BM010115 ON dbo.WO010032.BOMCAT_I = dbo.BM010115.BOMCAT_I
WHERE (dbo.WO010032.MANUFACTUREORDERST_I = 4)
We have tried several different types of joins but still no luck. The upshot is we need to know that when a finished good is placed on hold, - how many of its componet parts are placed on hold.
Any assistance you can provide will be appreciated.
Sam
Installing Great Plains V8.0
Feb 27, 2008Here is my problem.
I am setting up a new server with Great Plains v8.0 (I have migrated all the databases from the other server, which has a working version of Great Plains v8.0 at the present moment). We are planning on getting rid of that server as it causing a lot of problems at the moment. I start the install of Great Plains v8.0 on the new server, install the components. After Great Plains v8.0 installs I go into Great Plains Utilities, and get the following message (I have installed SQL Server 2005 on the new server, and SQL Server 2000 is being used on the old server).
After I run Great Plains Utilities, I get the following error message -
"The stored procedure verifyServerVersion() of form duSQLinstall: 111
Pass Through SQL returned the following results: DBMS: 0, Great Plains: 0."
and when I click OK, it gives me another notification message -
"Your current SQL SERVER is not a support version
Req: Microsoft SQL Server 7.0
Act: Microsoft Server 2005
You need to upgrade to SQL Server 7.0 before continuing".
Why am I get that error message when I have SQL Server 2005 installed?
Can someone point me in the right direction.
Thanks
Got Some Great Replication Links????
Nov 24, 2006can anybody give me some nice links with a detailed explaination of the various replication errors and solution
thanks in advance
Jacx
Great New Learning Resource
Mar 30, 2007 We've been working hard with our teams here to get better/more/good information out to our users. We€™ve created a new a customized Windows Live search, that limits results to Books Online. Check this out, and make sure you let your contacts know to visit it and provide feedback:
http://search.live.com/macros/sql_server_user_education/booksonline
We want to generate as much traffic as possible here so that we can see if this is useful to our users. We€™d love to hear back from everyone we can!
How To Group Similar Column Name And Sum The Similar Column Name Together
Apr 10, 2008Hey Gurus,
I have a problem on getting the sql statement which will group similar column name and sum their number together(another column).
A million thanks in advance.
Great Circle Distance Calculation
Oct 15, 2007Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation
Anyone Know Of A Great Book For Learning Transact SQL?
Feb 26, 2001Hello, I have just started working with Microsoft SQL Server 7.0 and was wondering if anyone could recommend a great book for learning T-SQL. I was looking for something that would provide several examples on triggers and stored procedures.
View 5 Replies View RelatedReplication Of Great Plains Data
Jan 13, 2006Hi All,
We have the requirement to replicate financial data to Aus from the UK, however I dont know if Replication is the best solution around?
Reason why I ask this, is that to create the publication, there are in excess of 10000 articles, which takes forever and a day to create, then when setting up the push subscription, this takes equally as long.
DB's physically range between 100MB and 2GB. Link to Aus is 2MB E1.
The accounts server isnt the most powerful of beasts (HP DL380, 1x1.4Ghx CPU) and with 4 DB's to setup and replicate, it's going to take some time.
With this in mind, I would also be looking to script out the publication should there be any failures and put it into SourceSafe, however this would also take a vast amount of time.
I've thought about using Log Shipping, however I dont know if there are any better ways
?
Thoughts appreciated.
Steve
Great Plains Database Replication
Feb 1, 2007Our company has a database server (Windows Server 2003 x64 EE w/ SQL Server 2005 x64 EE) that has Great Plains installed. We have a database for one of our companies that I would like to begin replicating to another server. The reason behind this is that we want to run the GP/CRM Connector which requires 32-bit ODBC connections.
Anyways, what I need to do is enable replication to our 32-bit server. We have been successful in making this happen from 64-bit to 32-bit with a much smaller database. When I go to enable peer-to-peer in the properties of the publication (which I set up using all of the default settings), SQL Management Studio will hang and run for days while appearing to be working. However, when we end up killing Management Studio days later (we let it run for a week once) the peer-to-peer option does not seem to have been set properly.
Any ideas?
Great Circle Distance Calculation
Oct 15, 2007
Great Circle distance calculation
Is there any stored procedure or application that implements Great Circle distance calculation
Great Tool To Compare Data From Two Different Queries
Feb 6, 2007http://www.download.com/Firefly-Dat...j=uo&tag=button
View 1 Replies View RelatedRestarted SQL Server 2005, And It's Working Great Now, But WHY??
Aug 3, 2007My small business has about 21GIGs of data in our database. This entire week we've been running absolutely slow. Posting transactions have been taking up to 30+ seconds when they're normally instant.
We ran the profiler and found that certain stored procedures were taking an absolutely long time to run. We checked for fragmentation, indexing, etc. Keep in mind our hardware was constantly pegged at 80%+ all the time. We have about 60 users connected to our server at any given time.
After much effort to no avail, we finally resorted to just restarting the software. Some have even suggested powering down and rebooting our hardware, but I halted that. We stopped and restarted the server via the management studio and now everything is just peachy. We're funning super fast and smooth.
My concern is that we're just bandaid-ing the problem and not resolving it; so eventually we're going to be faced with this problem again. Even though restarting the server is quite easy and not too effective on our downtime, personally, I'd like to know what the real issue is. Isn't sql server 2005 a self-tuning software to begin with?
Has anyone experienced this and share some insights on what they did and didn't do to resolve this quirk?
SSIS Job Runs Great Manually, But Not Using SQL Agent
Oct 16, 2006I am using SQL 2005. I have created a SSIS package that basically executes another SSIS package (as part of a larger package) . It runs fine in SSBIDS but will not run if I save it and schedule it using SQL Agent. I should mention I am using a domain/admin account with SQL Agent, so I don't think that is the problem.
When I execute the job in SSBIDS, the Execute Package Utility window pops up, at which point I click on the Execute button, the job runs successfully and then I click on the close button.
I suspect it is not running via SQL Agent because of the user intervention required to complete the task (i.e. clicking on execute as described above). Is this correct? If so, is there a way to override the requirement for any user intervention. Or, could it be from something else?
Thanks for any insight.
Great Circle Distance Function - Haversine Formula
Mar 28, 2007This function computes the great circle distance in Kilometers using the Haversine formula distance calculation.
If you want it in miles, change the average radius of Earth to miles in the function.
create function dbo.F_GREAT_CIRCLE_DISTANCE
(
@Latitude1 float,
@Longitude1 float,
@Latitude2 float,
@Longitude2 float
)
returns float
as
/*
fUNCTION: F_GREAT_CIRCLE_DISTANCE
Computes the Great Circle distance in kilometers
between two points on the Earth using the
Haversine formula distance calculation.
Input Parameters:
@Longitude1 - Longitude in degrees of point 1
@Latitude1 - Latitude in degrees of point 1
@Longitude2 - Longitude in degrees of point 2
@Latitude2 - Latitude in degrees of point 2
*/
begin
declare @radius float
declare @lon1 float
declare @lon2 float
declare @lat1 float
declare @lat2 float
declare @a float
declare @distance float
-- Sets average radius of Earth in Kilometers
set @radius = 6371.0E
-- Convert degrees to radians
set @lon1 = radians( @Longitude1 )
set @lon2 = radians( @Longitude2 )
set @lat1 = radians( @Latitude1 )
set @lat2 = radians( @Latitude2 )
set @a = sqrt(square(sin((@lat2-@lat1)/2.0E)) +
(cos(@lat1) * cos(@lat2) * square(sin((@lon2-@lon1)/2.0E))) )
set @distance =
@radius * ( 2.0E *asin(case when 1.0E < @a then 1.0E else @a end ))
return @distance
end
Edit: corrected spelling
CODO ERGO SUM
Microsoft Great Plain Integration Application And New Tables On Sql
Jul 28, 2006In dexterity iam using table_compare() function to check if tables in datasource and dictionary are same but its not picking up the primary key change but it does pick up the column change
any suggestion
thanks
Database Constaint Violation Error In SQL For Great Plains
Feb 1, 2006Hi there. We're doing a save on a MS Great Plains with SQL Server screen and get the following error:
"A save operation on table 'WS_Time_Sheet_TRX_WORK' failed because a database constraint was violated."
If I hit More Info it says:
[Microsoft][ODBC SQL Server Driver][SQL Server]INSERT statement conflicted with COLUMN FOREIGN KEY constraint 'FK_WS10702_UPR00100'. The conflict occurred in database 'NSP', table 'U00100PR', column 'EMPLOYID'.
I know these messages probably make perfect sense to you guys, but I'm a newbie. Can you point me in the right direction? My thinking is that maybe I should force the foreign key constraint using the "WITH NOCHECK" option or maybe the wrong data type is mapped between the two tables sharing the Foreign Key and the key should be deleted and recreated.
Any help you can provide would be most appreciated! Assume that I'm a newbie and that I know very little. You won't hurt my feelings if you "dumb it down" so I can understand where to begin! lol I'm not even entirely sure I know where to look to edit the connection in the first place.
Trigger Throws Exception Error In Great Plains 8.0
Sep 21, 2007Not sure if there's a GP 8.0 forum, so giving this one a go.
I've added an AFTER UPDATE trigger to the RM00101 table (customer master) in a Great Plains 8.0 SQL Server 2000 SP4 DB. The trigger assigns field values to variables, constructs an update query, and executes the query on a table in a linked SQL Server 2005 DB.
The trigger works fine when fired as a result of a direct field update made through Enterprise Manager. However, when the same update is made through the Great Plains GIU (customer card window), an exception error is thrown:
"Unhandled database exception:
A Save operation on table €˜RM_Customer_MSTR€™ failed accessing SQL data
EXCEPTION_CLASS_DB
DB_ERR_SQL_DATA_ACCESS_ERR€?
The odd thing is that if I drop the trigger from the RM00101 table, the exception error still occurs. Not just on the record originally updated, but on all records and all fields within the record.
Code for the trigger follows:
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE TRIGGER CTrig_Logic_Update_Customer
ON RM00101
AFTER UPDATE
AS
IF UPDATE(CUSTNMBR) or
UPDATE(CUSTCLAS) or
UPDATE(CNTCPRSN) or
UPDATE(STMTNAME) or
UPDATE(SHRTNAME) or
UPDATE(TAXSCHID) or
UPDATE(ADDRESS1) or
UPDATE(ADDRESS2) or
UPDATE(ADDRESS3) or
UPDATE(CITY) or
UPDATE(STATE) or
UPDATE(ZIP) or
UPDATE(PHONE1) or
UPDATE(FAX) or
UPDATE(SLPRSNID) or
UPDATE(PYMTRMID) or
UPDATE(PRCLEVEL) or
UPDATE(SALSTERR) or
UPDATE(INACTIVE) or
UPDATE(HOLD) or
UPDATE(CRLMTAMT)
BEGIN
DECLARE @Server_Name Varchar(25),
@Logic_DB_Name Varchar(25),
@SQLStr nvarchar(1000),
@CustomerN int,
@SICCode int,
@ARContact Varchar(35),
@LongName Varchar(50),
@CustomerName Varchar(24),
@SalesTaxCode Int,
@AddrLine1 Varchar(40),
@AddrLine2 Varchar(40),
@AddrLine3 Varchar(40),
@City Varchar(30),
@StateProv Varchar(4),
@PostalCode Varchar(15),
@TelephoneN Varchar(25),
@FaxTelephoneN Varchar(25),
@SalespersonN Int,
@TermsCode Varchar(60), -- Put the customer terms into the CommentN1 field
@CustRateSched Int,
@SalesAreaCode Int,
@InactivePurge Tinyint,
@CreditStatus Tinyint,
@CreditLimit Int
------- Get the new Customer data from Inserted table
SELECT @CustomerN = CAST(RTRIM(i.CUSTNMBR) as Integer),
@SICCode = ISNULL((SELECT Dex_Row_ID FROM RM00201 WHERE RM00201.CLASSID = i.CUSTCLAS),0),
@ARContact = RTRIM(i.CNTCPRSN),
@LongName = RTRIM(i.STMTNAME),
@CustomerName = RTRIM(i.SHRTNAME),
@SalesTaxCode = ISNULL((SELECT Dex_Row_ID FROM TX00101 WHERE TX00101.TAXSCHID = i.TAXSCHID),0),
@AddrLine1 = RTRIM(i.ADDRESS1),
@AddrLine2 = RTRIM(i.ADDRESS2),
@AddrLine3 = RTRIM(i.ADDRESS3),
@City = RTRIM(i.CITY),
@StateProv = RTRIM(LEFT(i.STATE,2)),
@PostalCode = RTRIM(i.ZIP),
@TelephoneN = RTRIM(LEFT(i.PHONE1,10)),
@FaxTelephoneN = RTRIM(LEFT(i.FAX,10)),
@SalespersonN = RTRIM(i.SLPRSNID),
@TermsCode = RTRIM(i.PYMTRMID),
@CustRateSched = RTRIM(i.DEX_ROW_ID),
@SalesAreaCode = ISNULL((SELECT Dex_Row_ID FROM RM00303 WHERE RM00303.SALSTERR = i.SALSTERR),0),
@InactivePurge = i.INACTIVE,
@CreditStatus = i.HOLD,
@CreditLimit = i.CRLMTAMT
FROM inserted i
------- Get Logic server name and database name
SELECT @Server_Name = RTRIM(l.Server_Name),
@Logic_DB_Name = RTRIM(l.Logic_DB_Name)
FROM tbl_Logic_DB l
------- Insert new Customer record into Logic database
SET @SQLStr = 'UPDATE [' + @Server_Name + '].[' + @Logic_DB_Name + '].dbo.[Customer] ' + '
SET SICCode = ' + CAST(@SICCode as varchar(10)) + ', ' + '
ARContact = ''' + @ARContact + ''', ' + '
LongName = ''' + @LongName + ''', ' + '
CustomerName = ''' + @CustomerName + ''', ' + '
SalesTaxCode = ' + CAST(@SalesTaxCode as varchar(10)) + ', ' + '
AddrLine1 = ''' + @AddrLine1 + ''', ' + '
AddrLine2 = ''' + @AddrLine2 + ''', ' + '
AddrLine3 = ''' + @AddrLine3 + ''', ' + '
City = ''' + @City + ''', ' + '
StateProv = ''' + @StateProv + ''', ' + '
PostalCode = ''' + @PostalCode + ''', ' + '
FaxTelephoneN = ''' + @TelephoneN + ''',' + '
SalespersonN = ' + CAST(@SalespersonN as varchar(10)) + ', ' + '
CommentN1 = ''' + @TermsCode + ''', ' + '
CustRateSched = ' + CAST(@CustRateSched as varchar(10)) + ', ' + '
SalesAreaCode = ' + CAST(@SalesAreaCode as varchar(10)) + ', ' + '
InactivePurge = ' + CAST(@InactivePurge as varchar(10)) + ', ' + '
CreditStatus = ' + CAST(@CreditStatus as varchar(10)) + ', ' + '
CreditLimit = ' + CAST(@CreditLimit as varchar(10)) + ' ' + '
WHERE CustomerN = ' + CAST(@CustomerN as varchar(10))
SET ANSI_NULLS ON
SET ANSI_WARNINGS ON
SET XACT_ABORT ON
EXEC sp_executesql @SQLStr
END
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Export To Excel Looks Great In Base Report But Wonky In Sub Reports
Dec 28, 2007I have an SSRS file with 2 total reports - the first is a sort of template - a matrix with about 6 varying parameters, and the second report uses the first as a subreport - ultimately, there are about 5 subreports on the second page, all using the main "template" report, with the only variance being certain parameters. The client wanted an excel file as the end result, and I decided the quickest way to populate the data they needed would be SSRS.
My problem is when I export this stuff to excel. The template report, by itself, comes out beautifully. The expand/collapse boxes on the groups are all aligned and the data just fits.
However, when I export the "combined" report, the whole thing turns to caca....
For one, subtotals are auto-magically added in for certain groups (these aren't in the main report, and they don't show render in IE, and subtotals are showing for groups where subtotals aren't even on. ) Annoying, but not major.
More importantly, the expand/collapse buttons for the matrix groupings seem to align to the top, and not the actual row of the grouping. So, if I wanted to expand the data on say, row 11, the '+' button is actually on the previous row (row 110). This is the real showstopper and just not acceptable on a usability level.
Again - the only difference between the two spreadsheets is that one is its own report, and the second one renders the same report from a subreport. On IE it looks identical. Any reason why simply using a sub would cause such drastic differences in excel appearance??
Similar To PhpMyAdmin For MS Sql?
Apr 11, 2005Is there any program similar to phpMyAdmin for MS SQL servers?
Thanks in advance
Do I Need DatePart Or Similar
May 11, 2006I have a function that uses the following statement in it
SELECT src_terrier.Areacode, src_terrier.siteref, src_terrier.estatename, src_terrier.Securitised, src_terrier.unitref, src_terrier.unittype,
src_terrier.unittype_count, src_terrier.tenantname, src_terrier.tenantstatus, src_terrier.tenantstatus_count, src_terrier.unitstatus,
src_terrier.unitstatus_count, src_terrier.floortotal, src_terrier.floortotocc, src_terrier.initialvacarea, src_terrier.initialvacnet, src_terrier.TotalRent,
src_terrier.NetRent, src_terrier.FinalRtLsincSC, src_terrier.ErvTot, src_terrier.tenancyterm, src_terrier.landact, src_terrier.datadate,
src_div_mgr.div_mgr, src_portfolio_mgr.portfolio_mgr, src_centre_list.propcat, src_tbl_rental.budgeted_net_rent,
src_tbl_rental.budgeted_occupancy
FROM src_terrier INNER JOIN
src_centre_list ON src_terrier.siteref = src_centre_list.Site_Ref AND src_terrier.Areacode = src_centre_list.Division INNER JOIN
src_div_mgr ON src_centre_list.Division = src_div_mgr.division INNER JOIN
src_portfolio_mgr ON src_centre_list.Portfolio_no = src_portfolio_mgr.portfolio_no LEFT OUTER JOIN
src_tbl_rental ON src_terrier.siteref = src_tbl_rental.site_ref
WHERE (src_terrier.datadate = @dt_src_date) AND
(src_terrier.Areacode = @chr_div) AND
(src_centre_list.Portfolio_no = @vch_portfolio_no) AND
(src_centre_list.propcat = @vch_prop_cat) AND
(src_tbl_rental.site_ref = src_terrier.siteref)
The problem I have is that the 'src_terrier.datadate' is passed through as mm/dd/yyyy (which I do actually want to change to dd/mm/yyyy as that is how the data is stored) however, the src_date within the table src_tbl_rental is only set to 01/mm/yyyy. When I put an inner join on the date element it obviously does not find it as the sample data I am using is as follows
src_terrier = 28/04/2006 and src_tbl_rental is 01/04/2006. Therefore if I pass the same parameter value through the dates are not the same and I get no data at all.
How can I specify that for the purposes of the src_tbl_rental element of the select query, that I only want it to match the mm/yyyy part of the src_date.
Therefore if some passes in
28/04.2006 it will get the records from the terrier table that match that date, and only the records from rental that match the 04/2006 part of the date.
Anybody confused by that , cause I am!
Regards