SQL Server 2005 Reporting Services
From the Report Manager home page, if you click on the Properties tab, you get to a security page where you can add new role assignments for users or groups. We need to add role assignments programmatically (not manually using Report Manager).
How can we programmatically add new role assignments?
We have been working on an application that will be using a forms-authenticated report server (RS2005) as a reporting back-end. Using the reporting services web service I have been able to assign permissions to objects in reporting services no problem. The issue is that each user needs to be added to the System User role to be able to use the report builder properly. I can't seem to find a way to do this programmatically. Any idea?
View 1 Replies View RelatedI really need help with the following. I have a stored procedure that assigns values to declared variables and I need to make sure that I'm doing it right. It uses a lot of IF..ELSE statements which adds to the complexity, but here is a sample of code...
Code:
DECLARE @Code Int, @Weights Float
SELECT somestuff , @Code, @Weights
IF(something )
BEGIN
IF(something )
BEGIN
SELECT @Code = 2
SELECT @Weights = A*B
END
END
FROM a bunch of tables
WHERE a bunch of stuff
Now the stored procedure is way more complicated than above, I just want to make sure I can assigne values to the declared variables the way I have done in the example above. Currently I get an error on the FROM line of the stored procedure.
Any help on this would be greately appreciated 'cause I have several people telling different things in regards to assigning variables in Transact-SQL. Thanks in advance
I have a table of employee assignments that I'm narrowing down to a specific group. Employees have multiple assignments (job positions essentially) and each has start and end dates. I need to identify the date range(s) that are covered by the assignments in my data set. Here's an example of data for one person ID. NULL in the end_date just means it is an active assignment. The pos_id column isn't necessary, but it define the data I'm looking at. The end result won't use it.
IDCOMPANYPOS_IDSTART_DATEEND_DATE
999119/2/20119/9/2012
999119/10/20129/10/2012
999119/11/20129/11/2012
999119/12/20126/2/2013
999116/3/20136/30/2013
[Code] ....
In this case I want results to say that ID 999 has a range from 9/2/2011 to NULL. There are no gaps in the date range. Or to say it differently, there's always an assignment starting the next day after an end_date. Or an assignment that overlaps the end and beginning of another assignment.
Here's another example where there is a gap in the ranges.
IDCOMPANYPOS_IDSTART_DATEEND_DATE
333112011-09-022012-08-31
333112012-09-012012-09-10
333112012-09-112012-09-11
333112012-09-122013-01-06
333112013-09-01NULL
There would be 2 result rows for this with a range from 2011-09-02 to 2013-01-06 and a second for 2013-09-01 to NULL.
The end result would be to have a row per date range for every ID. I've written a script that will find the ranges but it is a painful RBAR solution that takes forever to run. Every different path I've gone down trying to solve it ends in evaluating row by row grouped by the Person ID. The assignments are not always continuous, so I can't use a MAX and MIN and there may be 1 or more gaps in the dates to account for.
Ive been reading over the documentation and some stuff online, but I still dont really understand what the difference is and when you would use one vs the other. Can someone put it in simple terms for this dummy (me) ?
thanks
In sql server 2000, I created some custom database roles called ProjectLeader and Developer. I would make these roles a member in the fixed database roles so that I would only have to add the user to the ProjectLeader or Developer role once and they would presto-magico have the security I wanted them to have with no unecessary mouse clicking. I'm not sure how to repeat this process in 2005? Management Studio doesn't seem to allow you to add a role as a member in another role. Is there a work around or solution for this?
View 1 Replies View Related Hi all, I am trying to connect to the database using application role. But gives an error 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)
for the given connection string Dim connstring
As String = "Data Source=Northwind;Initial
Catalog=OrderProcessing;Persist Security Info=True;UserID=application_login;Password=wewewe;"
Dim cmd
As SqlCommand
Dim
param As SqlParameter
Dim
cookie As Byte()
Dim cn As New
SqlConnection(connstring)
If
(cn.State = ConnectionState.Closed) Then
cn.Open()
End If Please help..
Thanking you, Nirmala
Hi,
I have SQL server 2005 (Developer edition) installed and I want to add a database role to a database role. It is working on my SQL 2000 server,however, when I tried to do the same thing on SQL2005, It didn't allowed me.
When I go to the Add role and then adding members to the role, the browse screen does not allow me the choice of object type Roles. It only shows me "Users".
Can someone please help me with this and provide me some information of how / what should I set to get the Roles in object types list so I can add a role to a role.
Thanks
I need to programmatically backup a database in SQL Server Express. I actually also need to programmatically restore it from a backup file. How can I do this programmatically? I know how to do simple ADO commands for simple db operations, but backup and restore sound like "meta" commands to me, and I don't know where to begin from.
View 15 Replies View RelatedI'm building SSIS packages through code and I would like to set the properties of some custom tasks (not data flow tasks) to expressions. I've done some searches but turned up nothing. This is the only thing I'm hitting a brick wall on at the moment; Books Online has been excellent in detailing how to create packages via code up to this point.
For the sake of argument, let's say I want to set the SqlStatementSource property of an Execute SQL task to this value:
"INSERT INTO [SomeTable] VALUES (NEWID(), '" + @[User:omeStringVariable] + "')"
What would the code look like?
Ok. So I have this ASP.NET page and I've programmatically taken a report from the report server and rendered it in PDF. Now I would like to take this a step further and save the report as a pdf document on the local machine.
So at this point I have a byte array representing the document, now how would I save this as a pdf on the local machine? I'm unaware of an ASP Response method to allow this and I'm unaware of a SSRS ReportingService method, but as I said I'm unaware...
Any ideas, thoughts, resources are all welcome.
I've seen several post asking for that possibility, but all 've read, didn't help me.Some sing SQLDMO, other suggest to use SQLSMO, others only explaining to connect to a server and then do "CREATE DATABASE".I will do this within .Net. Connecting to a SQL Server 2005 and execute "CREATE DATABASE" I could realize.But how doing this with SQLExpress? Trying to do SqlConnection.Open with a non existing DB does not work., says "file not exists".Or do I only have the wrong connection string? Can someone post here an excample connection string, which works with a non existing database?Some hints I've read make me considering to use SQLSMO. But I don't have it on my computer. Where do I get it from? Any links would be nice.
View 6 Replies View Relatedhi all... how can i change the collation of a table that i dynamically created during runtime??i wanna change the default collation to Arabic
View 2 Replies View RelatedHi ,Can we make a backup from a SQL-server 2000 database programmatically, using asp.net and then restore this backup programmatically ?
View 4 Replies View RelatedHi Guys, I have this SqlDataSource, that counts some records and sets it in "NotStartedBugs". How do I retrive "NotStartedBugs" programmatically?
<asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString %>" SelectCommand="SELECT (SELECT COUNT(*) AS Expr1 FROM tickets WHERE (TicketType = 'Bug') AND (TicketStatus = 'Not Started')) AS NotStartedBugs"></asp:SqlDataSource>
Hi,
I am using Visual Web Developer 2005 Express Edition.
I am trying to SELECT three information fields from a table when the Page_Load take place (so I select the info on the fly). The refering page, sends the spesific record id as "Articleid", that looks typically like this: "http://localhost:1424/BelaBela/accom_Contents.aspx?Articleid=2". I need to extract the "Article=2" so that I can access record 2 (in this example).
How do I define the SelectParameters or QueryStingField on the fly so that I can define the WHERE part of my query (see code below). If I remove the WHERE portion, then it works, but it seem to return the very last record in the database, and if I include it, then I get an error "Must declare the scalar variable @resortid". How do I programatically set it up so that @resortid contains the value that is associated with "Articleid"?
My code is below.
Thank you for your advise!
RegardsJan/*******************************************************************************
* RETRIEVE INFORMATION FROM DATABASE
*******************************************************************************/
// specify the data source
string connContStr = ConfigurationManager.ConnectionStrings["tourism_connect1"].ConnectionString;
SqlConnection myConn = new SqlConnection(connContStr);
// define the command query
String query = "SELECT resortid, TourismGrading, resortHits FROM Resorts WHERE ([resortid] = @resortid)";
SqlCommand myCommand = new SqlCommand(query, myConn);
// open the connection and instantiate a datareader
myConn.Open();
SqlDataReader myReader = myCommand.ExecuteReader();
// loop thru the reader
while (myReader.Read())
{
Label5.Text = myReader.GetInt32(0).ToString();
Label6.Text = myReader.GetInt32(1).ToString();
Label7.Text = myReader.GetInt32(2).ToString();
}
// close the reader and the connection
myReader.Close();
myConn.Close();
Hello. Im trying to create an SQLDataSource control programmatically. I need to do this because I want to do some stuff on my MasterPage's 'Page_Init' event.
heres my code (Master.master.vb): Protected Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Init
lblUser.Text = Page.User.Identity.Name
Dim PUser As New ControlParameter
PUser.ControlID = "lblUser"
PUser.Name = "LoginName"
PUser.PropertyName = "Text"
PUser.Type = TypeCode.String
PUser.DefaultValue = Page.User.Identity.Name
Dim SQLDS_Login As New SqlDataSource
SQLDS_Login.ID = "SQLDS_Login"
SQLDS_Login.ConnectionString = "I put conection string here. How do I use the one on my web.config?"
SQLDS_Login.SelectCommand = "SELECT [LoginID], [LoginName], [Role], [Status] FROM [myLogin] WHERE ([LoginName] = @LoginName)"
SQLDS_Login.SelectParameters.Add(PUser)
SQLDS_Login.SelectCommandType = SqlDataSourceCommandType.Text
GridView1.DataSource = SQLDS_Login
GridView1.DataBind()
End Sub
When i run, i get this error message:
The SqlDataSource control 'SQLDS_Login' does not have a naming container. Ensure that the control is added to the page before calling DataBind.
I never had any problem with Inserts, Updates and Deleting, but I have never made it work for Select when doing it programmatically.
Can you help me with this?
I am trying to add a number of dates into a Sql database. Basically I want the user to add the start and end date and then all the dates in between are are added to a database in unique records. I can create an ArrayList but I don't know how to bind it to an SqlDataSource Dim startdate As DateTime = tbstartdate.Text Dim enddate As DateTime = tbenddate.Text Dim datediff As TimeSpan = enddate.Subtract(startdate) Dim noofdays As Integer = datediff.Days Dim ar As New ArrayList Dim i For i = 0 To noofdays ar.Add(startdate.AddDays(i)) Next Sorry if this is a total noob question....
View 1 Replies View RelatedI am trying to customize my update statement and this MUST happen in codebehind, otherwise I will be overwriting data. The following is updating the data that is should be. The problem is that eventhough my UpdateCommand is clear in my .aspx and there are no parameters set... It is STILL running it's own update and overwriting the information it isn't supposed to. From what I can tell, it is using a default. What can I do to prevent this?
SqlDataSource1.UpdateCommand = "UPDATE MyTable SET MyField1=@MyField1 WHERE MyField2=@MyField2 AND MyField3=@MyField3"SqlDataSource1.UpdateParameters.Add("MyField1", "CustomText")
SqlDataSource1.UpdateParameters.Add("Task_ID", "Parameter")SqlDataSource1.UpdateParameters.Add("Comments", "Parameter")
SqlDataSource1.Update()
I have a GridView bound to a SqlDataSource. On page load I would like to programmatically specify a SelectParameter value based on the role of the user. This SelectParameter will be used in my WHERE clause. The closest post I can find is http://forums.asp.net/thread/1233258.aspx but no answer was posted.
What code would I use to modify a SelectParameters value? Is it possible to reference a parameter by name (SqlDataSource1.SelectParameters["usertype"]) or does it have to be by index? (SqlDataSource1.SelectParameters[0])
Alternatively, perhaps I'm going about this in the wrong way, is there a better way to have dynamic GridView content based on the role of the user?
Thank you very much for your help.
Great Monday Morning to one and all,
Setup: SQL7 w/SP4 running on W2K Pro
Table in Question:
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
Great Monday Morning to one and all,
Setup: SQL7 w/SP4 running on W2K Pro
Table in Question:
I have a system that processes inserts that originate from automatic data collection subsystems on manufacuturing cells. The system processes about 2500 records a day. The system is isolated with no ready support or attention. My goal is to automate any and every reasonable admin task. My present activity centers on re-indexing the main table (receives the data from the inserts, supplies the data for web based reporting).
The table - tb_production_log - receives inserts that are time stamped and bear a Machine_id. The table has a clustered index built on the Machine_id (int) and Date_time (time of data's acquisition). The table only receives Inserts, the records are never Updated. No inserts are out of time sequence (no older records ever have to be 'wedged' in amongst existing records). Ulitmately, the table is tested daily for records with age > 365 days. Such records are Deleted.
For the past week, I have been running a monitoring stored procedure on my test box to track the fragmentation of the tb_production_log table. It's based on DBCC SHOWCONTIG with some extra tests. After capturing the SHOWCONTIG data, the sp runs a test query against the table to emulate a typical User report. I track the time this query takes. The query covers records over the last 7 days. (approx. 17,500 records involved). In addition, I track the time it takes Inserts to run. Inserts are done in batches from an external app. I get a RecordsPerSecond data point for each batch.
Is there a way to schedule tasks to run from an ASP script? If not ASP, how about VB? What function is called if any? Thanks!
View 1 Replies View RelatedHowdy,
Is the any easy way to clone a database programatically?
I've searched the web but did not come up with anything.
At the moment I create a new blank database (based on the model database)
and create tables, indexes, etc. via asp. (The databases are always exactly
the same)
It seems to me that it would be a better option to
create one database with tables, indexes, etc and copy
it to a new database (on the same server).
One of the options I'm considering would be to create a database,
fill it with tables and indexes, etc. and then detach it.
Whenever I need a new database all I would have to do is use the
file system object to copy and rename the mdf file and then reattach the
new mdf file.
I realise that I could also make the changes to the model
database so that all new databases have the same structure
but that would be my last resort.
Is there any easier way to do this? A stored procedure perhaps?
You're opinions and advice would be welcomed.
I recently had to find ways to programmatically backup and restore anSQL Server Express database.For backup, I found a one-liner stored procedure that does the job(BACKUP DATABASE Toy to disk = 'c:ProjectsToy.bak'.My question is, is there something as simple for a restore operation?Thanks in Advance-Kostas
View 4 Replies View RelatedHi,The following script does not return any resultset against a test dbwhile I know for a fact tables with letter "aaa" has columns thatcontains "ccc".What's wrong? the the inner cursor?Thanks.-- get all tbls with letter aaadeclare @tbl varchar(8000)declare tblCursor cursor forSELECT nameFROM sysobjectsWHERE xtype = 'U'AND name LIKE '%aaa%'open tblCursorfetch next from tblCursorinto @tblwhile (@@fetch_status = 0)begin-- get all columns with letter ccc and replace it with nothing /remove itdeclare @tbuffer varchar(4000)declare @cbuffer varchar(8000)declare abnormal_cols cursor forSELECT o.name, c.nameFROM sysobjects oJOIN syscolumns c ON o.id = c.idWHERE o.xtype = 'U'AND c.name LIKE '%ccc%'and o.id = object_id('+@tbl')-- ORDER BY c.nameopen abnormal_colsfetch next from abnormal_colsinto @tbuffer,@cbufferwhile (@@fetch_status = 0)begin-- EXEC sp_rename '+@tbuffer+'.['+@cbuffer+']','+Replace(+@cbuffer+','%ccc%','')','COLUMN';-- testprint @tbuffer + ', ' + @cbuffer;fetch next from abnormal_colsinto @tbuffer,@cbufferendclose abnormal_colsdeallocate abnormal_cols;fetch next from tblCursorinto @tblendclose tblCursordeallocate tblCursor;
View 1 Replies View RelatedHi!
I have a rdl file and would like to publish it programmatically? Is there any API or Web Service available for this.
Does anybody know can you programmatically instantiate a control at run-time?
I need to make a dynamic number of images appear on a report and programmatically grab an image name based on data in the report.
vbwrangler@yahoo.com
In the old DTS, we can use the ActiveX Script to change any task's property programmatically.
Can we still do it in SSIS? Using the Script task? It seems changing the value of variables then use a expression can do some of the work, but what if a task has no expression defined?
Say, I want to change the Fuzzy look up reference table name.
Can we do it?
All,
I'm wondering if there are any methods to set the permissions for a specific user on a specific report within SQL Server Reporting Services.
I got how to get permission from them programmatically, but not how to set them but the user interface..
Please apologize me if there was a similar thread in the recent past; I tried to find it out, I didn't succeed.
Thanks a lot in advance for your timely response (as usual deadlines are by "yesterday").
-G
I wrote a custom IIS log reader windows service that reads and parses log data and periodically uploads it to a SQL server table which I run my reports from later. Currently I read the file by giving the absolute physical path name for the log directory followed by the log file name I anticipate depending on date/time stamps.
My question is: Is there a programmatic way to get the directory path which is configured in IIS to store the IIS log files to? We use non default location e.g. D:DATALOGSIISW3SVC1 or something similar, which might change from server to server. Also, I am not worried about the individual log file names but the directory. I would prefer a C# code example.
Any help is appreciated!
KC
Hi there,
I got a user who is requesting a weekly report to be exported in csv (comma delimited) format. But this process will run weekly using schedule job and he wants the file to save to a certain directory on the network. Two part questions...
1. Is there a way to create a .csv file programmatically after runing the query?
2. How would I save the .csv file to a specified directory on the network?
TIA
Can a package executed from code be aborted in code? The Package class has an Execute() method but no Abort() method. Clearly the debugger can stop a package at any point, so it must be possible somehow.