Does anyone know
if the following sql view is possible to write and execute as a view script?
**********
find employee matching the given UserID*************
SELECT
* FROM Employees WHERE EmployeeID=@UserID
***********
find client matching the given ClientID**********
SELECT *
FROM Clients WHERE ClientID=@ClientID
**********find
all contacts and events associated with ClientID*********
SELECT *
FROM Contacts WHERE Contact.ClientID=@ClientID
SELECT *
FROM Events WHERE Event.ClientID=@ClientID
*********select
all audits with Key values matching the primary keys of each client, contact or
event*********
SELECT *
FROM Audit Where Key In (Client.ClientID, Contact.ContactID, Event.EventID)
I basically
need to find a employee based on its ID.
Then I need to find any records from the table Audit
with Key values matching the given fields in the results of any clients, contacts events that were returned from the previous select statements. Is this possible?
I am new to .NET programming and the C# language. I'm coming from a ColdFusion/Java background, but am trying to learn .NET to further my abilities as a programmer, and take advantage of the CLR functionality in SQL Server.
What I am wondering is: is it possible to write an assembly which has stored procedures and functions that each utilize a custom class written within that assembly?
I am having some trouble with this concept. I am writing a UDF that will compile various data together and build a string from it. To help simplify the code, I want to build a custom class which will represent the primary object I am running the compilations against.
I would like to have the class be able to load its data directly from the database using a load() method. However, I am getting the following error when trying to run the UDF:
Msg 6522, Level 16, State 2, Line 1 A .NET Framework error occurred during execution of user defined routine or aggregate 'CompileList': System.Security.SecurityException: Request for the permission of type 'System.Data.SqlClient.SqlClientPermission, System.Data, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed. System.Security.SecurityException: at System.Security.CodeAccessSecurityEngine.Check(Object demand, StackCrawlMark& stackMark, Boolean isPermSet) at System.Security.PermissionSet.Demand() at System.Data.Common.DbConnectionOptions.DemandPermission() at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory) at System.Data.SqlClient.SqlConnection.Open() at vtrenz_listmanager_package.lib.iMAList.load() at UserDefinedFunctions.CompileList(Int32 ownerid, Int32 userid, Int32 listid)
Any help and/or insight would be greatly appreciated.
I have been stuck in a problem for a while now. I am trying to count the number of groups. I tried soo many things and nothing worked . Now I want to try to write a function that does the counting for me.
Can somebody write me an example of a cutom code that can be called from expression. So my custome code should be something like that
Public int CountFun(int counter) { Return counter= counter +1;
}
Also how do I call this method from my expression? i am going to call it from a field inside the groups, so each time the group is implemented i add one to the counter.
I’m currently writing a web application on student exam timetables, I’m using SQL Server 2005 as the back-end for the database.
At present, the case states that if a student is in one examination, he/she can’t attend, or be allocated another examination while the first examination is in place, which would result in a clash
The way I’m going to target this is by writing a stored procedure in SQL Server 2005 to return an error code, which I’ll translate using ASP,NET, however at present I’m having difficulty writing the SQL code. This is because…
I’m using SQL Server 2005 Management Studio; I created the tables using MS Access and upsized them using the wizard. I can now access my database, but having difficulty editing my tables and with code…
I've the following view in my DB and I need to modify it in such a way that it should be grouped by 'categoryId' and display the count of rows that come under each categoryId. In brief, I need to use 'Group By' clause and 'Count/SUM' aggregate function.
Here is my view. if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MessageCategoryView]') and OBJECTPROPERTY(id, N'IsView') = 1) drop view [dbo].[MessageCategoryView] GO
FROM kc_message m JOIN kc_messagecategory mc on m.messageid = mc.messageid JOIN CategoryView cv on mc.categoryid = cv.categoryid WHERE mc.type not in (3,5)
I want to add custom view so that it show the records that their dateis less than a specific field like "2007/12/25". Dates are saved in DB like "2006/08/29 12:00:00 A.M" nad smalldatetime format in MSSQL.what should I do?
I am creating a custom transformation component, and a custom user interface for that component.
In my custom UI, I want to show the custom properties, and allow users to edit these properties similar to how the advanced editor shows the properties.
I know in my UI I need to create a "Property Grid". In the properties of this grid, I can select the object I want to display data for, however, the only objects that appear are the objects that I have already created within this UI, and not the actual component object with the custom properties.
How do I go about getting the properties for my transformation component listed in this property grid?
Hi all I'm into a project which uses a lot of views for joining 2 or more tables. Using the MERGE component in SSIS will be a huge effort coz it only has 2 inputs and I gotta SORT the input too. Isnt it possible to have a VIEW like component that joins more than 2 tables and DOESNT need sorting?? (I've thought about creating views in database engine but it breaks my data floe in SSIS and is'nt a practical solution)
Could someone please help me out? I need to write a sql stored proc to query the following table.My SQL experience is very week. If someone can help me with this, I will be happy to pay you $40 foryour help. I need the proc to do the following:1.) For every Superintendent in a region, country state and county; return the state name, superintendent name, the county name and and a string which is a comma delimited list of schools they supervise. See the sample output italicised and bold. So the big challenge here is to also return a string that is a concatenation of school names for a particularSuperintendent in a given state and county. For example: East,Kennedy,Apolo,Morrison. So basically the stored proc should accept input parameters of the Region, Country, State, and County
Here is the data table:
REGION COUNTRY STATE SUPER_INTENDENT PHONE_NO SCHOOL County NA USA Texas Mike Andrews 789-3614 East LakeNA USA Texas Mike Andrews 789-3614 Kennedy LakeNA USA Texas Mike Andrews 789-3614 Apolo LakeNA USA Texas Mike Andrews 789-3614 Morrison LakeNA USA Texas Amy Markson 789-2134 Anderson MaylorNA USA Texas Amy Markson 789-2134 Molina MaylorNA USA Texas Amy Markson 789-2134 Polima MaylorNA USA Ohio Terry Ellis 966-8314 Kingston KeelNA USA Ohio Terry Ellis 966-8314 Martin KeelNA USA Ohio Terry Ellis 966-8314 Eastmore KeelNA USA Ohio Terry Ellis 966-8314 Canondale Keel Here is the sample output the way it will appear on a web form: State:Texas County:Lake Mike Andrews East,Kennedy,Apolo,Morrison 789-3614 County:Maylor Amy Markson789-2134 Anderson,Molina,Polima State:Ohio County:Keel Terry Ellis Kingston,Martin,Eastomore,Keel
I would like to know how to write a function that will go through datain a column and change it. For example, I have a column of ISBN's forbooks, and the ISBN's have a period in them randomly distributed. I'dlike to pull the period out.Any help I can get will be appriciated.Thanks,Bill
I am working on a project where data is stored remotely in a Postgres database. I need to download some of the postgres data and store it in the SQL Server. The data in PG is in UTF-8. I use another application to write the data to the PG database. To talk to the PG database I am using the npgsql data provider (http://gborg.postgresql.org/project/npgsql/projdisplay.php). The data I am trying to download is arabic.
Everything seems to work fine except when I get the data from the PG DB and write it to SQL Server. I've done lots of debugging and can see that the data is correctly in arabic write until I do the update on the local dataset which saves it in SQL server. For some strange reason it makes the data into jibberish (just question marks).
I am using SQL Server express 2005. If anyone can help me with this I'd be extremely grateful as this has become a big problem and I've tried to find a solution without any success.
DataBase i am using is Sql Server6.5. In a trigger i had written code to transfer updated records from one table to other table.These updated records needs to be written into a text file. I had used xp_cmdshell but it is taking time.Is there a way to write data to flat file.
Hi everyone, is there any way to turn off SQL server re-writing the syntax of certain queries? An example would be if in the where you set primary keys equal to foreign keys and then it converts it to inner-joins. Thanks for your help.
I am having a problem with an ASP program that inserts data into a table on SQL Server 2000.
No error msg is returned upon submission and the confirmation msg that displays after the commit command is sent to the server displays, but when we go to the DB, the data sent isn't there. This is an occassional occurance and usually the data is there, just some times, it isn't. Other forms function just fine, using the *exact* same file to perform the submit function (all the forms "include" the same submit page). The only difference we can find is a trigger on the table having problems which executes upon update, capturing the information about who updated the record when. From what we can see, this is the only programmatic difference. The other thought tickling our minds was the possiblity of a simultaneous submission, since all the users submit with the same db user name via the form, if user 1's data gets written but not yet commited, user 2's data is submitted, then the commit transaction is submitted by user 1 as the program steps run in sequence, would the commit by user one cause eiither of the records inserted but not commited to be lost? If so, why wouldn't that be causing problems on other forms ...
Hi, I am working on an application in ASP.NET 1.1 and SQL Server 2005 as database.I wanted to use SQLCLR feature of SQL Server 2005. Is it possible that i write Stored Procedures in C# 1.1 and deploy on SQL Server 2005? as it is in case of C# 2.0. Please refer some good tutorial for it. Regards,Imran Ghani
I have a project were I will have it so that users can sign in and change information on an SQL server. The catch is that this site will be from a different domain name and from a different hosting company then where the SQL database is located. Sorry if this is a dumb question but how can I utilize asp.net to change and view an SQL database that is located else-where. For example: a user logs into www.something.com and he/she can view and edit SQL tables from www.somethingelse.com's database. Thanks in advance.
Dear All,Please suggest some of the best practices for writing SQL serverstored procedures?I'm writing a business function (stored procedure), which callsmany-stored procedure one after another.I want this to be best optimized, so that speed can be very good.Suggestion in this regard will be appreciated.Thanks in advance,T.S.Negi
Any way to have a process run that will not write its changes to the transaction log? I have a process that runs every three hours and has a huge impact on the transaction log (it becomes larger than the database itself). We do hourly backups of the transaction log and normally it is reasonably sized but when this process runs, it gets HUGE.
The process takes source data, massages it and writes it to summary tables. It is not something we need to track as we can recreate the summary tables if needed and it has no impact on the source tables.
Everything is driven through a stored procedure. Is there a way to run a stored procedure and tell it that nothing it does should be written to the transaction log?
I have an asp drive web page that writes a row to a table on sqlserver 2000. The web site is set to use windows authenication and thesql server is set to use windows authentication.This process works fine on windows xp sp 1 machines but on win2k sp4machines logged in as the same user i get the errorAn error occurred making the change -2147217843 Error connection toSQL Server: [Microsoft][ODBC SQL Server Driver][SQL Server]Loginfailed for user '(null)'. Reason: Not associated with a trusted SQLServer connection.can anyone explain why win2k client would have this issue and notwinxp clients?Glenn
What I want to accomplish is that at design time the designer can enter a value for some custom property on my custom task and that this value is accessed at executing time.
I am writing a custom task that has some custom properties. I would like to parameterize these properties i.e. read from a varaible, so I can change these variables from a config file during runtime.
I read the documentation and it says if we set the ExpressionType to CPET_NOTIFY, it should work, but it does not seem to work. Not sure if I am missing anything. Can someone please help me?
In the Editor of my custom task, under custom properties section, I expected a button with 3 dots, to click & pop-up so we can specify the expression or at least so it evaluates the variables if we give @[User::VaraibleName]
I have a web application with a sql server 2000 backend. I use stored procedures to enter data, and use the sql RAISEERROR function to raise custom errors with the input. My question is, is there anyway I can assign a number to these error and then catch them in my code?
e.g. I have an asset. Someone has authorised an inspection to occur on it. Now, they cannot authorise two inpsections - they must wait for one of the inspections to occur. I catch when someone is trying to authorise two inspections in the database, and raise an error. But I then want my code to catch this error message and display a user friendly message to the user. Is there a way of attaching a number to the message so that I can catch it in my code and display an appropriate message?
I have tried to search on the topic, but there's a variety of answers that has left me a bit confused, so let me try asking a-new...
I have RS 2005 installed (Report Server on one server with IIS and everything else, Integrated Security set; the Report Catalog on a separate server with SQL Server 2005, etc.). So far so good.
I can easily access a few of my test reports using URL Access, and I can also access Report Mgr just fine; all that to indicate I'm pretty sure I have RS installed and setup and working correctly.
Now I have a custom ASP.NET 2.0 based web app that, of course, uses Forms based authentication, not Windows. I can create a hyperlink on one my web pages that uses URL Access to get to some of my test reports. But, of course, it's prompts me for (and seems to remember) domain credentials.
That's the piece I want to avoid. I have created a specific Windows domain account that would be used for this purpose. Now, I just need to figure out code to put in my ASP.NET page to pass that along. I am not using the ReportViewer control.
So, I am trying to find a decent reference/example code on how to set the credentials. I have seen much example code that is like:
//assumes a web reference has been setup... ReportingService rs = new ReportingService();
But I don't think I want "DefaultCredentials"... I want to use my custom domain account. Any help?
My other wild idea was creating this custom page in a directory where I could drop in a custom web.config that set the <authentication> element to "Windows" then set the <identity> element to my custom account... any thoughts on that would be appreciated.
Hello to everyone, I've a question about UDTs and the way I can use them to access tables and columns where they are applied in a SQL Server 2005 DB. I've already spent 2 days googling and MSDN reading but nothing helped me to solve my problem, thats why I'm posting it here (this is the second post, maybe the last one was in the wrong Forum).
The scenario follows:
I've created a UDT called MyUDT that exposes 2 properties MyTable, MyColumn, here its the code:
[Serializable] [SqlUserDefinedType(Format.UserDefined, IsByteOrdered = true, MaxByteSize = 8000, Name = "MyUDT")] public class MyUDT : INullable, IBinarySerialize {
/// <summary> /// Set or Get the Table Name where the UDT is applied. /// </summary> public string MyTable { get { return this._myTable; } set { this._myTable = value; } }
/// <summary> /// Set or Get the Table's Column Name where the UDT is applied. /// </summary> public string MyColumn { get { return this._myColumn; } set { this._myColumn = value; } }
....
}
And here it's my question/s:
How can I expose the defined Properties (MyTable, MyColumn) in order to be directly used from SQL Server Management Studio within the Column Properties Panel?
If it is not possible, is there a way for any UDT to get back from the sql server execution context the table and the column where it is applied/used?
I need to solve that in order to later retrieve via SQL the Extended Table Properties where the UDT is used and make some work on presented MetaData. Thanks in advance, every answer/help will be very much appreciated.
For the above function I added reference to the System.Data . The report works as desired when I execute in the VS 2005 IDE but when I deploy the report on the reporting server and execute it from there the function doesn€™t execute and returns me #Error.
Can any one provide some insight into it to as to how i can resolve this issue.
I have a SQL server database that I am trying to connect to from VS2005 in the Server Explorer. I am not using the standard port for my SQL server listening port and I don't see where I can tell the Server Explorer to use a different port? Can someone point me in the right direction for connecting to my database? Thanks.
It might be an old question but wanted to see, if we have any latest techniques (other than bcp).
SELECT Field1, Field2 FROM MyTable
If I want to export the output of the above query to a csv on a network folder? I would like to avoid usage of SSIS package or BCP (as user needs to get additional rights to execute bcp).
