Hi All,
I have a large number of SPs that I would like to be able to document and provide this documentation to prospective clients. That is, provide them enough information without giving them the source code for the procedures.
I have found that all the parameters are in the sys.parameters table.
But I was wondering. Are the fields that are sent back out of an SP captured and recorded somewhere in the SQL Server catalog?
Is there an easy way to find out what fields are coming out of an SP?
I am getting a headache trying to research what to do when you have a large number of parameters to include in a query. For example, if I have a large number of checkboxes for the user to pick criteria for a report and they select several, I'm assuming it would be bad practise to say:
WHERE Field = "a" OR Field = "b" OR Field = "c" OR Field = "d" OR Field = "e" OR.....etc etc etc
Is there a good solution for this, given that the number of parameters may vary dramatically depending on what the user selects to include in a report?!
I'm running SQL Server 2000 with an ASP front end.
We are experiencing a situation where the SRS (2000 SP2) report server will no longer render reports. In the log file, there are many instances of
w3wp!runningjobs!434!3/23/2007-10:12:57:: i INFO: Adding: 8 running jobs to the databasew3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:13:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsClientConnected; found orphaned requestw3wp!runningjobs!434!3/23/2007-10:14:57:: i INFO: RunningJobContext.IsExpired; found expired request
What could be causing this? The reports are making queries through an OLE DB provider. There are no scheduled jobs, and the load doesn't seem that heavy.
I have a VB.NET Windows Application with a SQL 2K5 back end.
The application creates a view which produces approximately 5 million records. I am attempting to transfer these records to a table programmatically, but every attempt causes a timeout. The name of the source server and view will change depending on the client's installation.
Using the standard SQL connection/command objects with the "INSERT INTO TABLE (SELECT * FROM VIEW)" doesn't work at all.
What is the best way to transfer these records programmatically?
I work with a large and complex reporting system with several hundred reports: the Programmability; Stored Procedures node of the object explorer has become very difficult to navigate.
Is there any metadata that can be embedded in the stored procs that would create subfolders like the existing System Stored Procedures node in this node of the object explorer?
I suspect that the correct answers are:
Rename all your queries with a rational naming convention; Cull the deadwood; Assign them to categories then export them to separate databases.
Unfortunately, one of these has already been done, and the other two will break several hundred dependent processes - the recoding and retesting is neither economical nor desirable.
Still, all advice is welcome. Pitch your answers at a banking geek who does intermediate to advanced stored procedures and triggers, but isn't allowed to play with sharp things (like sys objects) - but I can probably get help from a grown-up on the sysadmin team: I have discovered that the rumours about human sacrifice are baseless, and they will perform favours in return for beer.
This is also a good time to ask: just how many stored procs and functions are you allowed in SQL Server 2005?
I have a report with a column which contains either a string such as "N/A" or a number such as 12. A user exports the report to Excel. In Excel the numbers are formatted as text.
I already tried to set the value as CDbl which returns error for the cells containing a string.
The requirement is to export the column to Excel with the numbers formatted as numbers and the strings such as "N/A' in the same column as string.
I am new to this list server and have a dumb question. Does anybody know of any software that will document table/attributes very similiar to sp_help?? Basically, what I am looking for is some fancy documentor for tables very much like the one with access.
Is there any way to print a report with column name, datatype, size, etc in a report under SQL Server 6.5. I am looking for something like the Documenter which is available with Access 97. Karen Suenram
I'm so sure this is a clueless newbie question, I'm embarrassed to be asking it. The short version of my question is, "What's the best path to generating printed documentation of my SQL 2005 database?" The long version follows...
I've just completed the first draft of my new SQL database design (using Management Studio). We were previously storing our data in seven (yes, seven) separate Access databases, now I have everything set up quite nicely in one SQL db, all nice and relational and with proper field sizes and naming conventions, etc. It's all beautiful, but now I need to document it.
Why wasn't it documented first and then built? Let's just say I inherited this task in mid-stream with a looming deadline. Yes, I would have liked to follow a different workflow, but it is what it is and I am where I am.
So I can go through every table and type up everything in Word, but I'm hoping there's some feature in Management Studio that will give me a head start. I read the Help file under "documenting databases," and it says you can output the metadata as and XMLA script. Sadly, it doesn't actually tell you how to do this, which is rather unhelpful.
I'd like to end up with a document that list all my tables, with field definitions, indexes, keys, and triggers. Is there an easier way to get there than going table-to-table and typing it all out?
Is there a way to quickly document all tables and views a DTS package uses? Is there any system table/view which will hold this information?
For one of our projects we need to document these objects so that the client could remove unused objects from the db. There are 20 + DTS packages to go through.
Why does M$ Query Analyzer display all numbers as positive, no matterwhether they are truly positive or negative ?I am having to cast each column to varchar to find out if there areany negative numbers being hidden from me :(I tried checking Tools/Options/Connections/Use Regional Settings bothon and off, stopping and restarting M$ Query Analyer in betwixt, butno improvement.Am I missing some other option somewhere ?
I'm fairly new to a DBA role, I've been tasked with documenting all the SQL servers/databases. Is there anywhere on the Web where I could find a standard list of what needs to be documented. Any help/advice would be appreciated.
I would like to select transforms in a package and paste them as jpeg or similar into word or powerpoint in order to allow me to create a separate set of documentation.
However, whenever I copy some transformations and jump to word, the copy buffer is empty.
I have a table with a column ID of ContentID. The ID in that column is all NULLs. I need a way to change those nulls to a number. It does not matter what type of number it is as long as they are different. Can someone point me somewhere with a piece of T-SQL that I could use to do that. There are over 24000 rows so cursor change will not be very efficient.
I am looking for an easy way to document the specifications of my tables, views, etc. In MS Access, one could use the "Documenter" utility to print out field names, data types, field sizes, and comments. Does SQL Server 2000 have a similar utility?
If not... 1. Why not? This would seem to be very useful. 2. What is the recommended way to document database specifications?
My ultimate goal is to develop an administration manual for our database.
Please note: I looked at the database designer diagram utility which is great for visually representing the relationships between tables. However, I am looking for a utility that will print out the field names, data types, field sizes and comments related to the fields.
What is the best way of documenting SSIS packages?I am not interested in purchasing any software, any general template that covers the key information of any SSIS package that should be defined and documented.
I have an 'ID' column. I'm up to about ID number 40000, but not all are in use, so ID 4354 might not be in any row. I want a list of all numbers which aren't in use. I want to write something like this:
select [numbers from 0 to 40000] where <number> not in (select distinct id from mytable)
In a pretty standard select statement (as shown), i want to return 0 when "dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission" is less than 0.
SELECT dbo.t_Agents.agent_code, dbo.v_CurrentParamPaymentTotal.ammount AS weekley_payment_total, dbo.v_AgentNumberOfCustomers.count AS number_of_cust, dbo.v_AgentAmmountPaid.total AS total_paid, dbo.v_AgentOrderTotals.Total AS ytd_order_total, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total AS amount_outstanding, ISNULL(dbo.v_AgentAmmountPaid.total / dbo.v_AgentOrderTotals.Total, 0) * 100 AS ytd_percentage, dbo.v_AgentOrderTotals.Total - dbo.v_AgentAmmountPaid.total - dbo.v_AgentCommClean.total AS amount_outstanding_commission, ISNULL(dbo.v_AgentOrderChange.amount, 0) AS net_weekly_order FROM dbo.t_Agents LEFT OUTER JOIN dbo.v_AgentOrderChange ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderChange.AGENT_ID LEFT OUTER JOIN dbo.v_AgentCommClean ON dbo.t_Agents.AGENT_ID = dbo.v_AgentCommClean.AGENT_ID LEFT OUTER JOIN dbo.v_AgentNumberOfCustomers ON dbo.t_Agents.AGENT_ID = dbo.v_AgentNumberOfCustomers.AGENT_ID LEFT OUTER JOIN dbo.v_AgentOrderTotals ON dbo.t_Agents.AGENT_ID = dbo.v_AgentOrderTotals.AGENT_ID LEFT OUTER JOIN dbo.v_AgentAmmountPaid ON dbo.t_Agents.AGENT_ID = dbo.v_AgentAmmountPaid.AGENT_ID LEFT OUTER JOIN dbo.v_CurrentParamPaymentTotal ON dbo.t_Agents.AGENT_ID = dbo.v_CurrentParamPaymentTotal.AGENT_ID
I have a sales report that includes dollar amount, tonnage, and profit margin among other things. They are currently sorted by tonnage sold from highest to lowest. I'd like to be able to place a number in a column counting 1 up for tonnage ranking. I'd also like to get a number ranking for sales amount ranking along with profit margin ranking. The most tonnage sold might not have been the biggest sale nor had the highest profit margin.
Does this sound like something that can be done within SSRS?
I should ad I'm runing MDX queries against a cube so I can't use T-SQL for ranking.
Hi, Is it possible to search in columns with a number datatype (I'm using an MS SQL database with bigint columns) with the ICriterion Expression.Like? Normally the Expression.Like is used for varchar columns. However, if there's a bigint column with the value 167829 I want to search for example on %678%. Cheers, koekie
I needed to come up with an algorithm to create unique user- friendly account numbers such as AC0000000001, AC0000000002, etc...Where they increment by 1. I created a SQL function that retrieves the previous number generated, adds 1 to it, inserts the new value into the table, then returns the new value. I started thinking, what if the function is ran at the same time? What if function # 1 creates the new number, and function #2 creates a new number as well before function #1 inserts it? Is this a possible scenario? If so, how do I lock the process until the function completes to prevent this? Thanks for any help you provide.
