I am trying to make it that a user can just click on my form and generate a custom sql query.
for example:
If I had three list boxes,I would like my user to be able to click on any of the items in the three boxes and come up with a datagrid displaying the query results.(in effect creating an individual query
i.e
ListBox1
robert redford
dustin hoffman
steve mc queen
ListBox2
butch Cassidy and the Sundance Kid
the Sting
straw dogs
pappilon
List 3
1965
1966(through to)
2008)
So then I want my user to come along click on Dustin Hoffman then click on a film(the sting perhaps) then the year,to find out if Dustin was actually in that film in that particular year...and if he was produce the details.
This will create a "custom" sql statement like
select * from lst 1 ...ect.
What I would like to know is can this actually be done.
This is only a small example of the datbase.I have thousands of films and name ect so it can get quite big.
We have a production server "prod" which has a user say 'test' that lets the users connect to the application and a logon trigger which stops them from connecting to the server through SSMS. I log ship 'prod' to the 'rep' database and the user 'test' is obviously created in the logshipped database 'rep' during the logshipping.
Now the login 'test' does not have any login from server 'rep', But people can still login to the 'rep' server and query the 'rep' db . I checked with SUSER_SNAME and found the SID of the user in rep server which I never created and which is not even present in the login names, Even If I create a new username in the 'prod' db, after logshipping the new user is replicated in the 'rep' server.
I can easily find user created stat in a databaseSELECT * FROM DB.sys.stats WHERE user_created=1But how do I determine what tables those stats are in? with over 6000 tables I don't feel like looking through all the tables.
I have added one webpage designed in ASP.Net with C# and sql server 2005 as database. There is table for user registration in which there is a column for ProfileCreationDate the data type of that column is date time .
I would like to fetch data of those user who have created profile within 7 days. For getting desired result I am trying this query.
select Name ,Profession,ProfileCreationDate from tblRegistration where DATEDIFF ( Day , '" + System.DateTime.Now + "',ProfileCreationDate)<7 order by ProfileCreationDate DESC
System.DateTime.Now is a function for getting current date time in C#
The query is neither giving error nor giving desired result.
I am working on Sql server Reporting Services(Sql Server 2005),
i have designed a Report and deployed that report on Report Server, on this report i need to show the user selected values in page header by using Parameters which i have already created in the Report.
There are 35 to 40 fields in the Front End(Asp.net2.0)
on passing all these values from front end Report is prompting for parameters which is not desired for my case even though i have made ShowParameterPrompts to false.
I have DWH where users query on regular basis. I wanted to know what queries they run and which user fired the queries for the last one month or 6 months.
I have an sql query that has specific criteria (like state='PA' orstate = 'NJ'...) and would like to be able to have the user specifythe criteria dynamically either through the web or from MSAccess oranother tool.The query also does a GROUP BY the state and other variables that arepart of the criteria.I know how to get MSAccess and asp pages to do the sorting andselecting against an SQL tbl or view, but when access queries the sameinfo as the original sql view, the process takes much longer than whenthe sql view does all of the sorting, selecting and grouping..The table we are currently using is 5 million records and will begrowing to 250 million records shortly, so speed is of the essence.The sql views and MSAccess are both running from the same server sothere is no issue at this point of a network impacting the MSAccessquery.Any suggestions...
Hi everybody,I've five instances of SQL Server 2000 with the SAME database with aDIFFERENT owner in each server. I, as the administrator, have a lot ofqueries that I have to execute in some or all servers. The problem isthat I have to connect to all servers with MY user, not each of the dbowners...So I have queries this way:select * from mike.table1 t1 join mike.table2 t2 on...And when I connect to another server I have to change mike for jeremyin all the SQLs...And when I connect to another server I have to change jeremy for ninain all the SQLs...I know that there was an old, v7, deprecated way to change the"schema", something likechange current user to kimberlygoselect * from table1 t1 join table2 t2 on...This way, I'll change ONLY once the connected user. I could even do atthe beginning of the script an IF, to change the connected userdepending on @@SERVERNAME !!!Can someone remember this instruction???Thanks in advance for your help !!!
trying to get a new database created then running a script to created the tables, relationships, indexes and insert default data. All this I'm making happen during the installation of my Windows application. I'm installing SQL 2012 Express as a prerequisite of my application and then opening a connection to that installed SQL Server using Windows Authentication.
E.g.: Data Source=ComputerNameSQLEXPRESS;Initial Catalog=master;Integrated Security=SSPI; Then I run a query from my code to create the database eg: "CREATE DATABASE [MyDatabaseName]".
From this point I run a script using a Batch file containing "SQLCMD....... Myscriptname.sql". In my script I have my tables being created using "Use [MyDatabaseName] Go CREATE TABLE [dbo].[MyTableName] .....". So question is, should I have [dbo]. as part of my Create Table T-SQL commands? Can I remove "[dbo]."? Who would be the owner of the database? If I can remove the [dbo]., should I also remove dbo. from any query string from within my code?
We have created several Table Valued User Defined Functions in a Production SQL Server 2005 DB that are returning large (tens of thousands of) rows obtained through a web service. Our code is based on the MSDN article Extending SQL Server Reporting Services with SQL CLR Table-Valued Functions .
What we have found in our implementations of variations of this code on three seperate servers is that as the rowset grows, the length of time required to return the rows grows exponentially. With 10 columns, we have maxed out at approximately 2 500 rows. Once our rowset hit that size, no rows were being returned and the queries were timing out.
Here is a chart comparing the time elapsed to the rows returned at that time for a sample trial i ran:
It took 570 seconds (just over 9 1/2 minutes to return 2566 rows).
The minute breakdown during my trial is as follows: 1 = 655 (+ 655) 2 = 1081 (+ 426) 3 = 1325 (+244) 4 = 1610 (+285) 5 = 1822 (+212) 6 = 1988 (+166) 7 = 2160 (+172) 8 = 2308 (+148) 9 = 2451 (+143)
As you can tell, except for a few discrepancies to the resulting row count at minutes 4 and 7 (I will attribute these to timing as the results grid in SQL Management Studio was being updated once every 5 seconds or so), as time went on, fewer and fewer rows were being returned in a given time period. This was a "successful" run as the entire rowset was returned but on more than several occasions, we have reached the limit and have had 0 new rows per minute towards the end of execution.
Allow me to explain the code in further detail:
[SqlFunction(FillRowMethodName = "FillListItem")] public static IEnumerable DiscoverListItems(...) {
ArrayList listItems = new ArrayList();
SPToSQLService service = new SPToSQLService();
[...]
DataSet itemQueryResult = service.DoItemQuery(...); // This is a synchronous call returning a DataSet from the Web Service
//Load the DS to the ArrayList
return listItems; }
public static void FillListItem(object obj, out string col1, out string col2, out string col3, ...) {
As you will notice, the web service is called, and the DataSet is loaded to an ArrayList object (containing ArrayList objects), before the main ArrayList is returned by the UDF method. There are 237 rows returned within 10 seconds, which leads me to believe that all of this has occured within 10 seconds. The method GetListItems has executed completely and the ArrayList is now being iterated through by the code calling the FillListItem method. I believe that this code is causing the result set to be returned at a decreasing rate. I know that the GetListItems code is only being executed once and that the WebService is only being called once.
Now alot of my larger queries ( > 20 000 rows) have timed out because of this behaviour, and my workaround was to customize my web service to page the data in reasonable chunks and call my UDF's in a loop using T-SQL. This means calling the Web Service up to 50 times per query in order to return the result set.
Surely someone else who has used Table Valued UDFs has come accross this problem. I would appreciate some feedback from someone in the know, as to whether I'm doing something wrong in my code, or how to optimize an SQL Server properly to allow for better performance with CLR functions.
This is my first time to deploy an asp.net2 web site. Everything is working fine on my local computer but when i published the web site on a remote computer i get the error "Failed to generate a user instance of SQL Server due to failure in retrieving the user's local application data path. Please make sure the user has a local user profile on the computer. The connection will be closed" (only in pages that try to access the database) Help pleaseee
Here i have small problem in transactions.I don't know how it is happaning. Up to my knowldge if you start a transaction in side the transaction if you have DML statements Those statements only will be effected by rollback or commit but in MS SQL SERVER 7.0 and 6.5 It is rolling back all the commands including DDL witch it shouldn't please let me know on that If any one can help this please tell me ...........Please............ For Example begin transaction t1 create table t1 drop table t2
then execute bellow statements select * from t1 this query gives you table with out data
select * from t2 you will recieve an error that there is no object
but if you rollback T1 willn't be there in the database
droped table t2 will come back please explain how it can happand.....................
HelloWhen I use a PreparedStatement (in jdbc) with the following query:SELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = ?ORDER BY group_nameIt takes a significantly longer time to run (the time it takes forexecuteQuery() to return ) than if I useSELECT store_groups_idFROM store_groupsWHERE store_groups_id IS NOT NULLAND type = 'M'ORDER BY group_nameAfter tracing the problem down, it appears that this is not preciselya java issue, but rather has to do with the underlying cost of runningparameterized queries.When I open up MS Enterprise Manager and type the same query in - italso takes far longer for the parameterized query to run when I usethe version of the query with bind (?) parameters.This only happens when the table in question is large - I am seeingthis behaviour for a table with > 1,000,000 records. It doesn't makesense to me why a parameterized query would run SLOWER than acompletely ad-hoc query when it is supposed to be more efficient.Furthermore, if one were to say that the reason for this behaviour isthat the query is first getting compliled and then the parameters aregetting sent over - thus resulting in a longer percieved executiontime - I would respond that if this were the case then A) it shouldn'tbe any different if it were run against a large or small table B) thisperformance hit should only be experienced the first time that thequery is run C) the performance hit should only be 2x the time for thenon-parameterized query takes to run - the difference in response timeis more like 4-10 times the time it takes for the non parameterizedversion to run!!!Is this a sql-server specific problem or something that would pertainto other databases as well? I there something about the coorect use ofbind parameters that I overall don't understand?If I can provide some hints in Java then this would be great..otherwise, do I need to turn/off certain settings on the databaseitself?If nothing else works, I will have to either find or write a wrapperaround the Statement object that acts like a prepared statement but inreality sends regular Statement objects to the JDBC driver. I wouldthen put some inteligence in the database layer for deciding whetherto use this special -hack- object or a regular prepared statementdepending on the expected overhead. (Obviously this logic would onlybe written in once place.. etc.. IoC.. ) HOWEVER, I would desperatelywant to avoid doing this.Please help :)
When I am in Visual Studio 2005, and I try to add an SQL database, I get the following error "generating user instances in sql server is disabled. use sp_configure user instances enabled to generate user instances." I am currently using SQL server 2005 Express. What do I need to do, to create an SQL database? Thanks in advance.
I've created a job using SQL DMO (in VB6) but it fails to run. When try to manual start the job from the Enterprise Manager, it gives error "cannot start job because it does not have any job server(s) defined."
Here is the code :
Dim oSQLServer As New SQLDMO.SQLServer Dim oJobServer As New SQLDMO.JobServer Dim oJob As New SQLDMO.Job Dim oJobSch As New SQLDMO.JobSchedule Dim oJobStep As SQLDMO.JobStep
i'm using the Enterpirse library logger to write logs into a database. When choosing connection string i choose the database i want in the "connection properties" dialog box and push 'Test connection' button. everything goes well.
then i open the SQL Server Management studio express and connect to the databse to check some things, from that point on , when i push the 'Test Connection' button in the Enterprise library i get the error:
"cannot open user default database. Login failed. login failed for user My'server/MyuserName'"
even when i close the sql server manager , it is still stuck - the connection test doesn't work anymore.... it only work when i restart the computer.
I'm buildng an application using the ADO.NET OleDB providor in order to maintain back end compatabiity with both SQL Server and Access Jet4.0. I need to get the Identity key of the new row created as a result of the following insert statement.
OLEDB COMMANT TEXT: "insert into tblPerson (LastName,FirstName,SSN,BirthDate,Family) values( ?, ?,?,?,?)";
I assume I need to add a select statement immediately following the insert. Can anyone help?
PS - not using stored procedures due to Access jet4.0 limitation
I want to remove 2 big ldf files (400 mgb and 300 mgb) and start db with only one ldf. I tried sp_attach_db specifing only mdf file but geting errors ------- microsoft help PRB: sp_attach_single_file_db Does Not Work for Databases with Multiple Log Files (Q271223) ..... You cannot attach a database that has been created with multiple log files without also attaching all the log files. ----
how can I remove those big ldf files and create new ldf (defult(1mgb)
This procedure runs and I see my GM names come up one at a time but I get no tables created.
Sorry about the sloppy code - I'm not a real pro.
declare @@GM Char(100) declare @SQL VarChar (2000) Declare spot cursor scroll for select GM from BIM_Historical_Performance.dbo.Performance_Master open spot fetch first from spot into @@GM While @@Fetch_Status =0 BEGIN
set @SQL = 'select Comp, Billto, Cust_name as Customer, Branch, BAC, [MgMt_Type], BondValue as Bondbucks , BondValueAlloc as Allocbucks, c1 as Curcode, u1 as CurUnderP$, s1 as Cur3mthBIMsales, p1 as performance, I1 as Inside, [IS_since] as Insidesince, O1 as Outside, [OS_since] as Outsidesince, c2 as CodeM-1, c3 as CodeM-2, c4 as CodeM-3, c5 as CodeM-4 ,c6 as CodeM-5, GM into ' +@@GM + ' from BIM_Historical_Performance.dbo.Performance_Master where BIM_Historical_Performance.dbo.Performance_Master.gm = ' +@@GM
I have created a installer for my application and database. When I run the installer it only creates the database but no tables created. But in some other workstation it works fine. I dont know what causes this problem....
Please guide me.
RON ________________________________________________________________________________________________ "I won't last a day without SQL"
ElementTypeDep_Cursor seems is not being created in this stored procedure:
Code Snippet ALTER procedure spCopyTemplateElementTypesToIssues @TemplateRecno integer, @ProjRecNo integer, @IssueRecNo integer as declare @ElementTypeRecno integer declare @ElementTypeDepRecno integer declare @ProjTypeRecno integer declare @PreElementRecNo integer declare @PostElementRecno integer declare @Count integer DECLARE element_Cursor CURSOR FOR SELECT ElementTypeRecNo FROM dbo.tblTemplateElementType where TemplateRecno = @TemplateRecNo OPEN element_cursor FETCH NEXT FROM Element_Cursor into @ElementTypeRecno --delete from tblElementCPO WHILE @@FETCH_STATUS = 0 BEGIN select @count = count (*) from tblElementCPO where ProjRecno = @ProjRecNo and IssueRecno = @IssueRecno and TemplateRecno = @TemplateRecno and ElementTypeRecno = @ElementTypeRecNo if @Count = 0 begin insert into tblElementCPO(Ignore, ElementTypeRecno, IssueRecno, ProjRecno, MaxAttemptNum, IsMileStone, ComponentOnly, Phase, TaskHoursEst, TemplateRecNo, ChangeDate, ChangePerson) values (0, @ElementTypeRecno, @IssueRecno, @ProjRecno, 5,0,6,99, 99,@TemplateRecno, getdate(), current_user) end FETCH NEXT FROM element_Cursor into @ElementTypeRecno END CLOSE element_Cursor DEALLOCATE element_Cursor select @Count = count (*) FROM dbo.tblElementTypeDep where TemplateRecno = @TemplateRecNo if @Count > 0 then begin DECLARE ElementTypeDep_Cursor CURSOR FOR SELECT ElementTypeDepRecNo, PreElementTypeRecNo, PostElementTypeRecno FROM dbo.tblElementTypeDep where TemplateRecno = @TemplateRecNo OPEN ElementTypeDep_cursor FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno WHILE @@FETCH_STATUS = 0 BEGIN select @Count = count (*) from tblElementDepCPO where ElementTypeDepRecno = @ElementTypeDepRecno and PreElementRecNo = @PreElementRecNo and PostElementRecno = @PostElementRecno if @Count = 0 begin insert tblElementDepCPO (ElementTypeDepRecno, PreElementRecNo, PostElementRecno, ChangeDate, ChangePerson) values (@ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno, getdate(), current_user) end FETCH NEXT FROM ElementTypeDep_Cursor into @ElementTypeDepRecno, @PreElementRecNo, @PostElementRecno END CLOSE elementTypeDep_Cursor DEALLOCATE elementTypeDep_Cursor end
called by Code Snippet Dim cnQI02414 As New SqlConnection(My.Settings.csQI02414Dev) Dim cmd As New SqlCommand Dim reader As SqlDataReader cmd.CommandText = "spCopyTemplateElementTypesToIssues" cmd.CommandType = CommandType.StoredProcedure Dim spmTemplateRecNo As SqlParameter = _ cmd.Parameters.Add("@TemplateRecNo", SqlDbType.Int) spmTemplateRecNo.Value = _ Me.cbTemplate.SelectedValue Dim spmProjRecNo As SqlParameter = _ cmd.Parameters.Add("@ProjRecNo", SqlDbType.Int)
spmProjRecNo.Value = _ Me.cbProject.SelectedValue
Dim spmIssueRecNo As SqlParameter = _ cmd.Parameters.Add("@IssueRecNo", SqlDbType.Int)
spmIssueRecNo.Value = _ Me.cbIssue.SelectedValue
cmd.Connection = cnQI02414 cnQI02414.Open() reader = cmd.ExecuteReader() ' Data is accessible through the DataReader object here. cnQI02414.Close()
