Hello everyone, I had a previous issue with if...then....etc statement, but I got it to work, so thats a good thing. The only problem is that it works only when i have a single line displaying, if its more than 1, it dont work any ideas why????
My report is table, broken into 2 groups, one by department, then by last name. My thought is the Sum(Fields!AMT_EARNED.Value) still giving me issues.....but I dont know....
How to dynamically and partially Update the webpage? The content ofthe part of the page is from the user's requerement and get data fromthe server side.The purpose is to speed up the page update. We have a page that mostof content are static, only small part is changed per user's selectand the replying data need to get from server side database. Thanksfor help. Gene
I have a report that references a custom assembly. The assembly loads an xml file from a web site in the code OnInit. The reports designer the code runs fine, because it is called with full trust. I deployed the report to our test sever, and copied the two dll files to c:Program FileMicrosoft SQL ServerMSSQL.3Reporting ServicesReportServicein. I have added the following to the rssrvpolicy file.
<PermissionSet class="NamedPermissionSet" version="1" Name="LexLibrary" Description="Used by reports to access the Lex Library"> <IPermission class="SecurityPermission" version="1" Flags="Assertion, Execution "/> <IPermission class="WebPermission" version="1"> <ConnectAccess> <URI uri="http://lcserver3/lex/boe.xml"/> </ConnectAccess> </IPermission> </PermissionSet>
When I try to run the report on the server I get €śAn error occurred while executing OnInit: That assembly does not allow partially trusted callers. (rsErrorInOnInit) €ś Can anyone see what I€™m missing?
Can we do database shrink in volume not full to manage spaces. Only few gb spaces are available, so i want to shrink the database. How to do that. Â DB size-775 Gb Free Dis size-156 Gb out of 931 GB
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. Please provide separate solutions for SqlServer2000/2005. I have four tables namely – Forums,Topics,Threads and Messages in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT Forums.forumid AS ForumID,Topics.topicid AS TopicID,Topics.name AS TopicName,LastPosts.date as LastPostDate,LastPosts.author AS Author, (select count(threadid) from Threads where Threads.topicid=Topics.topicid) as NoOfThreads FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN ( SELECT Topics.forumid,Threads.topicid,Messages.date,Messages.author FROM Topics INNER JOIN Threads ON Topics.topicid = Threads.topicid INNER JOIN Messages ON Threads.threadid=Messages.threadid WHERE Messages.date=(SELECT MAX(date) FROM Messages WHERE Messages.threadid = Threads.threadid) ) as LastPosts ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid Whose result set is as below:-
forumid topicid name LastPostDate author NoOfThreads
5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the message) from the Messages table as shown above. When I use the query by using only three tables namely Forums,Topics and Threads, I get the correct result. The query is as:- SELECT Topics.forumid AS ForumID,Forums.name AS ForumName,Topics.topicid as TopicID,Topics.name as TopicName, LastPosts.author AS Author,LastPosts.lastpostdate AS LastPost, (select count(threadid) from threads where Threads.topicid=Topics.topicid) as NoOfThreads FROM Forums JOIN Topics ON Forums.forumid=Topics.forumid LEFT OUTER JOIN ( SELECT Topics.forumid, Threads.topicid, Threads.lastpostdate, Threads.author FROM Threads INNER JOIN Topics ON Topics.topicid = Threads.topicid WHERE Threads.lastpostdate IN (SELECT MAX(lastpostdate) FROM threads WHERE Topics.topicid = Threads.topicid) ) AS LastPosts ON LastPosts.forumid = Topics.forumid AND LastPosts.topicid = Topics.topicid Whose result set is as:-
5 General 17 General NULL NULL 0 The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__35BCFE0A]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__35BCFE0A GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__34C8D9D1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__34C8D9D1 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Messages__thread__33D4B598]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Messages] DROP CONSTRAINT FK__Messages__thread__33D4B598 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Messages]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Messages] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Messages] ( [msgid] [int] IDENTITY (1, 1) NOT NULL , [threadid] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [date] [datetime] NULL , [message] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Messages] ADD PRIMARY KEY CLUSTERED ( [msgid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Messages] ADD FOREIGN KEY ( [threadid] ) REFERENCES [dbo].[Threads] ( [threadid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM'); insert into messages(threadid,author,date,message) values(1,'a@b.com','1/27/2008 2:44:39 PM','Where can I find Java tutorials.'); insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:45:28 PM','Please visit www.java.sun.com'); insert into messages(threadid,author,date,message) values(1,'c@d.com','1/27/2008 2:46:41 PM','Do a Google serach for more tutorials.'); insert into messages(threadid,author,date,message) values(2,'x@y.com','1/27/2008 2:48:53 PM','Please provide some Beginner tutorials.'); insert into messages(threadid,author,date,message) values(3,'p@q.com','1/27/2008 3:12:51 PM','How many finds of layout managers are there?'); insert into messages(threadid,author,date,message) values(2,'l@m.com','2/2/2008 1:06:06 PM','Search on Google.');
I have a SSIS which have a main connection to a SQL Server 2005 DB, and also another connection to a Oracle database, and fetch data from the Oracle db.
I have the 2 connection managers, and everything runs fine when I test it in dev studio. However, once I imported it to the DB, and run it on command line, I get the "Class not registered" error.
Log file as follows:
C:>dtexec /DTS "MSDB ST1INST101CRDA_D_GIM_CONN_TEST" /SERVER crpnycmsq34q /CONNECTION "CRD_CM";""Data Source=crpnycmsq34qST1INST101;Initial Catalog=CRD;Provider=SQLNCLI.1;Integrated Security=SSPI;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 1:23:53 PM Info: 2008-02-28 13:23:53.91 Code: 0x40016040 Source: CRDA_D_GIM_CONN_TEST Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_SOURCE";". End Info Info: 2008-02-28 13:23:54.03 Code: 0x40016040 Source: CRDA_D_GIM_CONN_TEST Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_USER";". End Info Info: 2008-02-28 13:23:54.05 Code: 0x40016040 Source: CRDA_D_GIM_CONN_TEST Description: The package is attempting to configure from SQL Server using the configuration string ""CRD_CM";"[dbo].[CMG_SSIS_CONFIG]";"GIM_PWD";". End Info Error: 2008-02-28 13:23:54.55 Code: 0xC0202009 Source: CRDA_D_GIM_CONN_TEST Connection manager "GIM" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-02-28 13:23:54.56 Code: 0xC00291EC Source: Execute SQL Task Execute SQL Task Description: Failed to acquire connection "GIM". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-02-28 13:23:54.58 Code: 0x80019002 Source: CRDA_D_GIM_CONN_TEST Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 1:23:53 PM Finished: 1:23:54 PM Elapsed: 1.547 seconds
I DID TRIED MY BEST to search before posting, and most of them are having problem with 32 bit and 64 bit executions. However, in my case, both are 32 bit (as shown in the log file).
In the above test, I'm setting DoNotSaveSensitiveData, and then fetch it with package configuration.
To make sure the package configuration is not creating pblm for me, I also tried to hardcode everything in the package, and then encrypt the whole ssis with password, and run the package with the /DECRYPT option, so that everything is already intact.
But I still get the same error. Log file as follows.
C:>dtexec /DTS "MSDB ST1INST101CRDA_D_GIM_CONN_TEST" /SERVER crpnycmsq34q /De "painful" /CONNECTION "CRD_CM";""Data Source=crpnycmsq34qST1INST101;Initial Catalog=CRD;Provider=SQLNCLI.1;Integrated Security=SSPI;"" /MAXCONCURRENT " -1 " /CHECKPOINTING OFF /REPORTING V Microsoft (R) SQL Server Execute Package Utility Version 9.00.3042.00 for 32-bit Copyright (C) Microsoft Corp 1984-2005. All rights reserved. Started: 2:24:25 PM Error: 2008-02-28 14:24:27.11 Code: 0xC0202009 Source: CRDA_D_GIM_CONN_TEST Connection manager "GIM" Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80040154. An OLE DB record is available. Source: "Microsoft OLE DB Service Components" Hresult: 0x80040154 Description: "Class not registered". End Error Error: 2008-02-28 14:24:27.14 Code: 0xC00291EC Source: Execute SQL Task Execute SQL Task Description: Failed to acquire connection "GIM". Connection may not be configured correctly or you may not have the right permissions on this connection. End Error Warning: 2008-02-28 14:24:27.14 Code: 0x80019002 Source: CRDA_D_GIM_CONN_TEST Description: SSIS Warning Code DTS_W_MAXIMUMERRORCOUNTREACHED. The Execution method succeeded, but the number of errors raised (1) reached the maximum allowed (1); resulting in failure. This occurs when the number of errors reaches the number specified in MaximumErrorCount. Change the MaximumErrorCount or fix the errors. End Warning DTExec: The package execution returned DTSER_FAILURE (1). Started: 2:24:25 PM Finished: 2:24:27 PM Elapsed: 1.422 seconds
Can anyone suggest what is missing ?? or what can be done ??
As far as I know temp tables/objects will be created inside the default filegroup of the partially contained database and not in tempdb. Is it possible to either define a set of files dedicated to temp objects or define a second partially contained database dedicated to temp objects like tempdb?
I have successfully compiled & deployed the custom security extension. Currently I am having some runtime error when using the added Membership.Data.dll for authentication & authorization.
Membership.Data.dll * signed with a strong name key * [assembly: AllowPartiallyTrustedCallers] is added * it uses Microsoft.Practices.EnterpriseLibrary.Data.dll for database connection
I have also added connectionstring to web.config(s) under ReportManager and ReportServer.
Here is the exception:
System.Web.Services.Protocols.SoapException: Server was unable to process request. ---> System.Security.SecurityException: That assembly does not allow partially trusted callers. at System.Security.CodeAccessSecurityEngine.ThrowSecurityException(Assembly asm, PermissionSet granted, PermissionSet refused, RuntimeMethodHandle rmh, SecurityAction action, Object demand, IPermission permThatFailed) at Membership.Data.DataProvider.Acquire(String DatabaseName) at Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationUtilities.VerifyPassword(String suppliedUserName, String suppliedPassword) in C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesExtension SamplesFormsAuthentication SamplecsFormsAuthenticationAuthenticationUtilities.cs:line 116 at Microsoft.Samples.ReportingServices.CustomSecurity.AuthenticationExtension.LogonUser(String userName, String password, String authority) in C:Program FilesMicrosoft SQL Server90SamplesReporting ServicesExtension SamplesFormsAuthentication SamplecsFormsAuthenticationAuthenticationExtension.cs:line 70 at Microsoft.ReportingServices.WebServer.RSCustomAuthentication.LogonUser(String userName, String password, String authority) at Microsoft.ReportingServices.WebServer.ReportingService2005Impl.LogonUser(String userName, String password, String authority) at Microsoft.ReportingServices.WebServer.ReportingService2005.LogonUser(String userName, String password, String authority) The action that failed was: LinkDemand The assembly or AppDomain that failed was: Membership.Data, Version=1.0.0.0, Culture=neutral, PublicKeyToken=<key>The method that caused the failure was: Microsoft.Practices.EnterpriseLibrary.Data.Database CreateDatabase(System.String) The Zone of the assembly that failed was: MyComputer The Url of the assembly that failed was: file:///c:/Program Files/Microsoft SQL Server/MSSQL.4/Reporting Services/ReportServer/bin/Membership.Data.DLL --- End of inner exception stack trace ---
Any ideas? I assume that Microsoft.Practices.EnterpriseLibrary.Data.dll is in GAC so i shouldn't have to worry about [AllowPartiallyTrustedCallers]
When I try to debug the break points will always say the source code is different from the current version, but the custom component in the GAC has the new version number. The other strange thing is the toolbox will not reset to the original version meaning it will not remove the custom components. The funny thing is after I compile the custom components and restart VS the custom component runs with the new code changes. I can see the new features I added, but the debugger and toolbox still seem to be broken.
I have tried the following 1) Reset the tool box. 2) uninstall all my custom dll from the GAC €śC:WINDOWSassembly€? 3) remove all my custom dll from €śC:Program FilesMicrosoft SQL Server90DTSPipelineComponents€? 4) restart VS 2005 5) reselect the custom components. 6) reboot my computer.
It seem like VS has another cache. For the tool box or something.
We have a table with 500+ columns. The data is non-normalized, i.e. there are four groups of fields for for "people", followed by data that applies to all people in the row (a "household").For ad-hoc queries, and because I wanted to index columns within each person (person 1's age, person 2's age, etc.), I used UNION:
SELECT P1Firstname AS FirstName, P1LastName as LastName, P1birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns> UNION SELECT P2Firstname AS FirstName, P2LastName as LastName, P2birthday AS birthday, HouseholdIncome, HouseholdNumber of Children, <other "household" columns>
I could get at least the P1... P2... P3... columns with PIVOT, but then I believe I'd have to JOIN back to the row anyway for the "household" columns. Performance of UNION good, but another person here chose to use PIVOT instead.I can' find any articles on PIVOT vs. UNION for "de-flattening".
Hi, Please help me with an SQL Query that fetches all the records from the three tables but a unique record for each forum and topicid with the maximum lastpostdate. I have to bind the result to a GridView.Please provide separate solutions for SqlServer2000/2005. I have three tables namely – Forums,Topics and Threads in SQL Server2000 (scripts for table creation and insertion of test data given at the end). Now, I have formulated a query as below :- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate ORDER BY t.topicid ASC,th.lastpostdate DESC Whose result set is as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 On modifying the query to:- SELECT ALL f.forumid,t.topicid,t.name,th.author,th.lastpostdate,(select count(threadid) from threads where topicid=t.topicid) as NoOfThreads FROM Forums f FULL JOIN Topics t ON f.forumid=t.forumid FULL JOIN Threads th ON t.topicid=th.topicid GROUP BY t.topicid,f.forumid,t.name,th.author,th.lastpostdate HAVING th.lastpostdate=(select max(lastpostdate)from threads where topicid=t.topicid) ORDER BY t.topicid ASC,th.lastpostdate DESC I get the result set as below:-
forumid topicid name author lastpostdate NoOfThreads
5 17 General NULL NULL 0 I want all the rows from the Forums,Topics and Threads table and the row with the maximum date (the last post date of the thread) as shown above. The scripts for creating the tables and inserting test data is as follows in an already created database:- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Topics__forumid__79A81403]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Topics] DROP CONSTRAINT FK__Topics__forumid__79A81403 GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK__Threads__topicid__7C8480AE]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Threads] DROP CONSTRAINT FK__Threads__topicid__7C8480AE GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Forums]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Forums] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Threads]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Threads] GO if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Topics]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[Topics] GO CREATE TABLE [dbo].[Forums] ( [forumid] [int] IDENTITY (1, 1) NOT NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Threads] ( [threadid] [int] IDENTITY (1, 1) NOT NULL , [topicid] [int] NOT NULL , [subject] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [replies] [int] NOT NULL , [author] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [lastpostdate] [datetime] NULL ) ON [PRIMARY] GO CREATE TABLE [dbo].[Topics] ( [topicid] [int] IDENTITY (1, 1) NOT NULL , [forumid] [int] NULL , [name] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [description] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ) ON [PRIMARY] GO ALTER TABLE [dbo].[Forums] ADD PRIMARY KEY CLUSTERED ( [forumid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD PRIMARY KEY CLUSTERED ( [threadid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Topics] ADD PRIMARY KEY CLUSTERED ( [topicid] ) ON [PRIMARY] GO ALTER TABLE [dbo].[Threads] ADD FOREIGN KEY ( [topicid] ) REFERENCES [dbo].[Topics] ( [topicid] ) GO ALTER TABLE [dbo].[Topics] ADD FOREIGN KEY ( [forumid] ) REFERENCES [dbo].[Forums] ( [forumid] ) GO ------------------------------------------------------ insert into forums(name,description) values('Developers','Developers Forum'); insert into forums(name,description) values('Database','Database Forum'); insert into forums(name,description) values('Desginers','Designers Forum'); insert into forums(name,description) values('Architects','Architects Forum'); insert into forums(name,description) values('General','General Forum'); insert into topics(forumid,name,description) values(1,'Java Overall','Topic Java Overall'); insert into topics(forumid,name,description) values(1,'JSP','Topic JSP'); insert into topics(forumid,name,description) values(1,'EJB','Topic Enterprise Java Beans'); insert into topics(forumid,name,description) values(1,'Swings','Topic Swings'); insert into topics(forumid,name,description) values(1,'AWT','Topic AWT'); insert into topics(forumid,name,description) values(1,'Web Services','Topic Web Services'); insert into topics(forumid,name,description) values(1,'JMS','Topic JMS'); insert into topics(forumid,name,description) values(1,'XML,HTML','XML/HTML'); insert into topics(forumid,name,description) values(1,'Javascript','Javascript'); insert into topics(forumid,name,description) values(2,'Oracle','Topic Oracle'); insert into topics(forumid,name,description) values(2,'Sql Server','Sql Server'); insert into topics(forumid,name,description) values(2,'MySQL','Topic MySQL'); insert into topics(forumid,name,description) values(3,'CSS','Topic CSS'); insert into topics(forumid,name,description) values(3,'FLASH/DHTLML','Topic FLASH/DHTLML'); insert into topics(forumid,name,description) values(4,'Best Practices','Best Practices'); insert into topics(forumid,name,description) values(4,'Longue','Longue'); insert into topics(forumid,name,description) values(5,'General','General Discussion'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'About Java Tutorial',2,'a@b.com','1/27/2008 02:44:29 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (1,'Java Basics',0,'x@y.com','1/27/2008 02:48:53 PM'); insert into threads(topicid,subject,replies,author,lastpostdate) values (4,'Swings',0,'p@q.com','1/27/2008 03:12:51 PM');
My error is that 'Name tr is not declared' tr.Rollback() I tried moving the 'Dim tr As SqlTransaction' outside the try but then I get 'Variable tr is used before it si assinged a value'. What is the correct way? Try conn.Open() Dim tr As SqlTransaction tr = conn.BeginTransaction() cmdInsert1.Transaction = tr cmdInsert1.ExecuteNonQuery() cmdInsert2.Transaction = tr cmdInsert2.ExecuteNonQuery() cmdInsert3.Transaction = tr cmdInsert3.ExecuteNonQuery() tr.Commit() Catch objException As SqlException tr.Rollback() Dim objError As SqlError For Each objError In objException.Errors Response.Write(objError.Message) Next Finally conn.Close() End Try
I have the below procedure that will not work- I must be losing my mind, this is not that difficult - mental roadblock for me. Using SQL Server 2000 to create SP being called by ASP.Net with C# code behind stored procedure only returns if input exactly matches L_Name PROCEDURE newdawn.LinkNameLIKESearch @L_Name nvarchar(100)AS SELECT [L_Name], [L_ID], [C_ID], [L_Enabled], [L_Rank], [L_URL] FROM tblContractorLinkInfo WHERE L_Name LIKE @L_Name RETURN I tried: WHERE L_Name LIKE ' % L_Name % ' no luck. What am I missing? Thank you
SELECT H.id, H.CategoryID ,H.Image ,H.StoryId ,H.Publish, H.PublishDate, H.Date ,H.Deleted ,SL.ListTitle,C.CategoryTitle FROM HomePageImage H JOIN shortlist SL on H.StoryId = SL.id (INNER JOIN category C on H.CategoryId = C.CategoryId) order by date DESC
I have a textbox and a checkbox on a form and I'd like to add both values to a db. The textbox value gets inserted fine but I'm having trouble with the checkbox. Any ideas would be greatly appreciated.
Beside working right from the server how else someone can perform the SQL admistration job, I guess my question is how do most SQL DBA perform their administration without going to the server directly. Anyone --- can help please??
Declare @StartDate datetime Declare @EndDate Datetime Set @StartDate = dateadd(day, datediff(day, 0, getdate()), 0) Set @EndDate = getdate()
The job runs at 11:30 pm so I want the start date to be the same but the time to be equal to 00:00:00.0 When I run the getdate does it also return a time stamp?
I had a problem where some users were experiencing timeouts when trying to add a single record to a table with 2.3 million records. It's not a very wide table; only 10 columns and the biggest column in varchar 500. The rest are guid, datetime, tinyint...
There is also an old VB app that inserts about 3000 records a day into this table during office hours while users occasionally try and insert a record into this table.
Something said to me that the problem could be indexes but I wasn't quite sure because I though indexes only have an impact on select, delete & update. And not particulary on insert. But I checked it out anyway and noticed that the 3 indexes (1 column PK, 1 column Clustered & 1 column non-clustered) weren't padded. So I changed that (Fill Factor 95) and the problem has gone away. But why? I thought the insert would just have appended it to the end of the index before I made this change? Why would that time out?
I have the following queries. The first returns the 'Unknown' row, the second works the way I would expect. Are my expectations wrong? Can someone describe for me what is going on?
Thanks
Code Snippet select * FROM SynonymComFinancialCategory b LEFT JOIN TT_FinancialCategory a ON a.FinanceGroup = b.FinanceGroup AND a.FinanceCode = b.FinanceCode AND a.Finance = b.Finance AND b.Finance <> 'Unknown' WHERE a.FinanceGroup IS NULL AND a.FinanceCode IS NULL AND a.Finance IS NULL
Code Snippet select * FROM SynonymComFinancialCategory b LEFT JOIN TT_FinancialCategory a ON a.FinanceGroup = b.FinanceGroup AND a.FinanceCode = b.FinanceCode AND a.Finance = b.Finance WHERE a.FinanceGroup IS NULL AND a.FinanceCode IS NULL AND a.Finance IS NULL AND b.Finance <> 'Unknown'
Hi,I have the following table with some sample values, I want to return the first non null value in that order. COALESCE does not seem to work for me, it does not return the 3rd record. I need to include this in my select statement. Any urgent help please.Mobile Business PrivateNULL 345 NULL4646 65464 65765NULL 564654654 564 6546I want the following as my results:Number3454646564654654Select COALESCE(Mobile,Business,Private) as Number from Table returns:3454646654654 (this is a test to see if private returns & it did with is not null but then how do i include in my select statement to show any one of the 3 fields)select mobile,business,private where private is not null returns:657655646546thanks
Hello,I created a formview in a web page. The data are in a sql server express database.With this form, I can to create a new data, I delete it but I can't to modify the data.The app_data folder is ready to write data; the datakeynames element in formview web control declared. I replace the automated query created by VS 2005 by a strored procedure to see if the problem solved.The problem is the same with an update query or a update stored procedure...Have you an idea, please.Than you for your help.Regards.
Im working with a detailsview and when I try to edit something and then update, the changes are not saved. I have 2 tables ("[etpi.admin].Ocorrencias" and "[etpi.admin].SMS") that store the data that Im trying to change. Since Im having problems with the name of tables, Im coding it manually, using SQL server management studio and VWD. I believe my code can be wrong (Im new to vwd and C# world), so here it is: UpdateCommand="UPDATE [etpi.admin].Ocorrencias SET [Status_Ocor] = @Status_Ocor, [Percentual] = @Percentual, [IDPriori] = @IDPriori, [Abertura] = @Abertura, [IDTecRes] = @IDTecRes, [Area] = @Area, [CodEquip] = @CodEquip, [Descricao] = @Descricao, [Destinatario] = @Destinatario, [Data_Implanta] = @Data_Implanta WHERE [etpi.admin].Ocorrencias.IDOcorre = @IDOcorre UPDATE [etpi.admin].SMS SET [idSMS] = @idSMSWHERE [etpi.admin].SMS.IDOcorre = @IDOcorre"> <UpdateParameters><asp:Parameter Name="idSMS" Type="Int32" /><asp:Parameter Name="Status_Ocor" Type="String" /><asp:Parameter Name="Percentual" Type="Int32" /><asp:Parameter Name="IDPriori" Type="Int32" /><asp:Parameter Name="Abertura" Type="DateTime" /><asp:Parameter Name="IDTecRes" Type="Int32" /><asp:Parameter Name="Area" Type="String" /><asp:Parameter Name="CodEquip" Type="Int32" /><asp:Parameter Name="Descricao" Type="String" /><asp:Parameter Name="Destinatario" Type="String" /><asp:Parameter Name="Data_Implanta" Type="DateTime" /><asp:Parameter Name="IDOcorre" Type="Int32" /></UpdateParameters> Thx.
My rollback does not work In my SP I want any of cmdS,cmdS2,cmdS3,cmdS4 produces error Rollback must be executed for all of cmdS,cmdS2,cmdS3,cmdS4 . I tested for error producing situation but no rollbak occured. How can I solve this problem. Thanks. Below is my SP . .......... .......... .......... begin transaction .......... ..........If Len(ltrim(rtrim(@cmdS)))>0 execute(@cmdS) If Len(ltrim(rtrim(@cmdS2)))>0 execute(@cmdS2) If Len(ltrim(rtrim(@cmdS3)))>0 execute(@cmdS3) If Len(ltrim(rtrim(@cmdS4)))>0 execute(@cmdS4) If Len(ltrim(rtrim(@cmdS)))>0 or Len(ltrim(rtrim(@cmdS2)))>0 or Len(ltrim(rtrim(@cmdS3)))>0 or Len(ltrim(rtrim(@cmdS4)))>0 Beginif @@ERROR <>0 begin rollback transaction set @Hata = 'Error !' end Else Beginset @Hata = 'Sucessfully executed :)' End End commit transaction RETURN
I can test it in query builder but when i preview the page and go to do the search i get nothing Here is my statement SELECT Employees.Last_Name, Employees.First_Name, Job_Transaction.Name, Seq_Descript.Seq_DescriptionFROM Call_List INNER JOIN Call_Group ON Call_List.Group_ID = Call_Group.Group_ID AND Call_List.Group_ID = Call_Group.Group_ID INNER JOIN Employees ON Call_List.Clock = Employees.Clock INNER JOIN Job_Transaction ON Call_Group.Group_ID = Job_Transaction.Group_ID INNER JOIN Seq_Descript ON Call_List.Sequence = Seq_Descript.SequenceWHERE (Call_List.Clock = @Clock) Mike
Hi all.I have used a SqlDataSource in my page with this delete command:DELETE FROM tblPersonnel WHERE (ID = @original_ID)and the "OldValueParameterFormatSring" property of the datasource is "original_{0}".and i also have a GridView and a button for delete in rows.(it's CommandName is "Delete"). But when i try to delete a record, the record does not get deleted and this button only makes a PostBack on the page! Why doesn't it work? Thanks in advance.
hi all, i have created a gridview with the select,delete and insert commands working properly. but the update command does not work. when i edit a column and click the update button, it generates no errors but does not save the changes. it just brings back the original values. i dont know wats missing. can anyone help me? this is part of my code:UpdateCommand="UPDATE [test101] SET Surname=@Surname, Names=@Names,Registration=@Registration,[Course code]=@Course_code,Grade=@Grade WHERE ID=@ID1 "<UpdateParameters> <asp:Parameter Name="Surname" Type=String /> <asp:Parameter Name="Names" Type=String /> <asp:Parameter Name="Registration" Type=String /> <asp:Parameter Name="Course_code" Type=String /> <asp:Parameter Name="Grade" Type=Int32 /> <asp:Parameter Name="ID1" Type=Int32 /> </UpdateParameters>
Hi,I am having trouble with a WHERE clause:WHERE (([A] = @A) AND ([B] >= @B) AND (([C] < [D])) OR ([C] = 0 AND [D] = 0)) It's meant to only select data where A = @A, and B is more than or equal to @B, and one of the next two are true: C is less than D or C and D are both 0 Thanks,Jon
I am trying to get the id of last entered record and I used select ID command but it doesn't give me the ID of last record enteredProtected Sub Submit_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Submit.Click Dim name, address, nice As String Dim sConnString, sSQL, sID As String Dim u As MembershipUsername = Me.name.Textaddress = Me.address.Text
u = Membership.GetUser(User.Identity.Name) nice = u.ToString()sSQL = "INSERT into test ([Address],[Name], [username]) values ('" & _ address & "', '" & _name & "', '" & _ nice & "'); SELECT ID FROM test WHERE (ID = SCOPE_IDENTITY())" MsgBox(sID) MsgBox(sSQL) sConnString = ConfigurationManager.ConnectionStrings("aspnetdbConnectionString1").ConnectionString.ToString()Using MyConnection As New System.Data.SqlClient.SqlConnection(sConnString)Dim MyCmd As New System.Data.SqlClient.SqlCommand(sSQL, MyConnection) MyConnection.Open() MyCmd.ExecuteNonQuery() MyConnection.Close() End UsingResponse.Redirect("transferred.aspx?value=2") End Sub End Class
I am using the below WHERE clause and it will not filter out records where Companies.CompanyEmail = b@c.com WHERE (Companies.CompanyEmail <> 'a@a.com') this does not work, is the @ causing a problem? also tried 'a@a.com%' with no success. WHERE (tblCompanies.C_CompanyEmail <> 'none') this works fine. Thank you
For the life of me, I cant get this thing to work.. any insight would be greatly appreciated. What I'm trying to do is simply get the RealName field from my aspnetdb.Here the code. SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=aspnetdb;Integrated Security=SSPI"); SqlDataAdapter a = new SqlDataAdapter ("select RealName from dbo.aspnet_Users where UserName='Dude';", conn); DataSet s = new DataSet(); a.Fill(s); foreach (DataRow dr in s.Tables[0].Rows) { getusersrealname.Text = (dr[0].ToString());
