The Accounting Department needed some reports, and this was back when I was first learning SQL, so I wrote a batch file that used osql to run a procedure. This procedure created a second batch file with several hundred lines to run another stored procedure and output to .rpt files complete with page breaks.
Now I'm revisiting this process having learned alot since then, and would like some input on how to handle this better. The batch file works, but I've never been one of those "if it's not broke" type of people. I'm one of those "If it's not broke, give me 5 minutes and it will be" kind of people
I'm specifically interested in something to replace this CURSOR that builds the batch file:
Code:
DECLARE @Dealer Varchar(20)
DECLARE Dealer_Cursor CURSOR
FOR SELECT Dealer_id FROM Dealer WHERE (Dealer_id != '-1') AND (isnull(Category,'-1') != 'NOBILLING')
OPEN Dealer_Cursor
FETCH NEXT FROM Dealer_Cursor INTO @Dealer
WHILE @@Fetch_Status = 0
BEGIN
/* @Folder is passed as a fully qualified path to the billing folder */
/* Logging and Command Window Notification, to be placed into DealerBilling.bat */
I'm very new to report builder and I don't even know if this is possible. Some of my users have asked if they can build an ad-hoc report to show say top 50 annual salaries. This has to be done soley with whatever report builder has for building ad-hoc reports as that's all the users have access to.
When you build a report and run the report if you click on a field it will drilldown into detail. How do you choose what detail you want it to bring back?
I removed a role in the datasourceview of a datamodel. Later I removed the role in the reportmodel. There are always two roles for one relation. Now I only have one role left and I can't delete it anymore.
This is the error I get when building the project.
Error 2 The RelatedRoleID property of the Role 'invoicelineid' refers to the Role 'Gd1b03590-f98b-46aa-945d-c90ccd5d6028', which is not in the same SemanticModel as the Role 'invoicelineid'. Max generiek.smdl 0 0
When I try to remove the role I'll get an error : object reference not set to an instance of an object.
If I look at the properties of the role I see that when I open 'related role' there is nothing in it, except an ID: Gd1b03590-f98b-46aa-945d-c90ccd5d6028 and as name: Object reference not set to an instance of an object.
I also tried to remove the whole entity but that resulted in the same error. It's not an option for mee to remove the whole model and autogenerate it again, but there is too much work in it already.
If I try to remove this thing in the xml itself it gives other errors and the model won't load anymore.
we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this..
C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild
Through build process we get a error:
External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1):
We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line?
On the front sheet I've created system wide figures with a two columns per month and a matrix which is also broken down by category (e.g. child fiction, child non fiction, adult fiction, adult non fiction) which has row and column totals.
I want to have a page like this for each branch in the system.
I can see report builder in IE (I am report admin), but my users are not able to, despite having ticked 'report builder' against their name in folder security.
They have Browser, Report Builder
Why can't they see it?
"Site Settings - Security - Config sys wide role defs - sys user " has "execute report defs" ticked.
I know how to use sql to write a block of sql statements by cutting and pasting the results in to the execution window. Now my question is this... is there a way to create the sql and execute the results. i.e. Automatically cut and paste the results into the execution window and run it?? Something to munch on if your bored. Ideas appreciated.
The common way that we use to delpoy reports in production server is using rsbuild.config file and running the command to execute it. Is this a Microsoft product ? does it have any licensing issue if i need to use this approach in my production environment to deploy the reports?
Hi, I have two tables called Actcodes and a another table called FundBalances...
The Act codes have the following format..
PlanId int, ClientId int, StartDate datetime, EndDate datetime, ClientActCode char(2) CodeDescription char(15) so based on what the user has selected i am getting the names of each codes
and my fund balance i have the following rows.. PlanId int Participantid int StartDAte datetime, end date datetime, FundId int Loans Act1 char(2) TotAct1 money . Act20 char(2) TotAct20 money and the data is as follows PlanId ParticipantId StartDate EndDate fundId Loans Act1 TotAct1 Act2 totact2 ----- Act20 TotAct20 111 1212 1/1/2008 3/31/2008 15 NULL 01 15.15 02 15.48 20 12.4561 111 1212 1/1/2008 3/31/2008 45 0 01 45.12 02 453.123 20 54.00 so on and so so forth
tht Act1 matches the ClientActCode in the Act.. So is there a way i can get those column in my select which have actCodes present in the Act code table. for in... in my act code table for this plan i have 01,02,03,04,05,06, 09, 15 clientAct codes
so can i get the its respective Acts and Totacts column as Act1, Act, Act3, Act Act5, Act6 based on the ClientAct codes.
I'm trying to add a CASE to my statement, I keep getting a syntax error. Can anyone see the issue? Should this CASE work?
Thanks for your thoughts,
Select tmp.Alternate_Name,dbo.tblClient.Salutation CASE WHEN (tmp.Alternate_Name > '') Then tmp.Alternate_Name As Card_Name Else dbo.tblClient.Salutation AS Card_Name END From dbo.tblClient INNER JOIN dbo.tblCards tmp ON dbo.tblClient.ClientID = tmp.ClientID
Hi there, I have 3 columns that contain the same kind of information and I would like to do a select that would return all the distinct records of all 3 colums. Any help woulb be appreciated. Thanks
I have two tables linked with the colunm name systemId and I need to get all the systems responding to the condition (t1.software & 0000000001) = 1 and if they have any contacts I would like to see them. Here is what I have for the moment however my querry do not return the system info if no contact exist and that is wrong.
select customers.customerId,customers.systemId,customers.country from customers,contacts where (customers.software & 0000000001) = 1 and customers.SystemId = contacts.SystemId order by customerid
I swear I saw a stored procedure that would tell me what version service pack and what build my sql servers are... Does anybody know how to find out? I think its a transact query... Also, I think I saw a stored procedure that would associate that cryptic string of numbers into text; ex: "SQL 6.5, Service Pack 5"
l would like to intergrate the the two procedures listed below into one procedure or function. l'm doing it in two steps.This report will have each loan listed and a final line summed on each of the amounts? enddate and startdate being my parameters and print the results to screen.And l'm failing to do that thats why its in two steps.Hope this makes it clear enough.
Alter procedure new (@startdate datetime,@enddate datetime) as begin
SELECT Distinct Loan_no AS Loan_no, Date_Issued AS Date_Issued, Store AS Store, Product AS Product, Capital_Amount AS Capital_Amount, Interest_Amount AS Interest_Amount, Total_Amount AS Total_Amount, Insurance_amount As Insurance_Amount, Admin_Fee AS Admin_Fee, User_Issued AS User_Issued, LoanBook AS Company, Status FROM Loan Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000' End
Alter procedure new1(@startdate datetime,@enddate datetime) As Begin
SELECT Sum(Capital_Amount) AS Capital_Amount, Sum(Interest_Amount) AS Interest_Amount, Sum(Total_Amount) AS Total_Amount, Sum(Insurance_amount) As Insurance_Amount, Sum(Admin_Fee) AS Admin_Fee FROM Loan Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000'
I have a table with 2 columns: Col1 Col2 stat1,stat2 AV,AD stat3 TD I need to build a query like this: SELECT * FROM table3 WHERE stat1=AV and stat2=AD and stat3=TD How do I do this?
I need to build a new SQL query and im almost new with all the sql language. Im searching a lot and I have 2 book but I cant found what I'm looking for.
I need to update the Site and Department in the table 1 with the site/department in the table 3 using the table 2 as a join. The table one is an item owned by the person in the table 3 and I need to set the Site and Department as the same.
I would like to build a workflow system where 100 processes are requesting an item from a ~1.000.000 items large DB, process that item and move it to the next state. The problem with the current implementation I tried is that I get deadlocks....
The DB table looks like:
CREATE TABLE Transactions( itemid CHAR(32), status TINYINT not null default 0, result INT not null default 0, lockby TINYINT not null default 0, .... (etc.) PRIMARY KEY(refno) ); CREATE INDEX IxStatus on transactions (status)
Each process (with his own ID) is doing 4 step: 1) update transactions set status=1, lockby=<ID> from (select top 1 itemid from transactions where status=0) as t1 where t1.itemid=transactions.itemid
2) select itemid from transactions where status=1 and lockby=<ID>
3) process item
4) update transactions set status=2,result=<RESULT> where itemid=<ITEMID>
I have developed one application in sql server 2005. Once i generate the scripts for all the objects, i want to give them to the client as a build. so that he can execute that build by giving parametres like server name,error log path,build source path and db name. Please let me know how to create that. Please do the needful.
Hello all. I have an .xsd file and a .xml file that goes with it. Are there any tools available that will build a database structure in SQL Server from either of these two files? I can't see the .xml file being of much use for building the structure, but how about the .xsd file?
I am confused with the build versions in SQL server.
When I install in my XP local machine...when I say select @@version i get the following...
Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
In my Enterprise 2003 server I get this...
Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
I am concerned with the compatibility when I am using service broker between two sql servers...why do i get 2 different build versions...?
I am at a site where they are having problems seeing the backup data in the reports feature and they cannot delete old backups using theie scheduled maintenance job. They were wondering if the above mentioned build would resolve this issue. They do not see the "Include 1st level folders" check box in the backup wizard. They have a copy of the above build, but know instructions were sent. MS will not resend. If anyone knows whether this build will help or has the hotfix.txt file that includes the instructions, I and they would certainly appreciate it. Thanks. Tom.
I'm very new in SSIS. I've created 3 packages in the project. sometimes when i modified the project and save/save-all it, when i tried to build (isn't this used to deploy?), I am being asked by this:
Package 1 has been modified outside the source editor. Do you want to reload it?
When I press on yes, all my modifications were not save. If i answered no, the build process stops. i dunno if this is because the build process is already finished or it was terminated because i chose 'NO'. When i tried to rebuild it again, it will ask me the same question.
What is the proper way to save and build the project? When it says ' do you want to reload it?', does it mean reloading the old copy before modification?
I have the following T-SQL CASE statement. I've spent the last 10 minutes trying to convert it to an expression in my derived column component but to no avail.
case when f.etypeid < 10 then '000' + cast(f.etypeid as varchar) when f.etypeid > 10 and f.etypeid < 100 then '00' + cast(f.etypeid as varchar) else '0' + cast(f.etypeid as varchar) end
