Compilation / Re-build Issue
Hi,
We are using .Net 2.0 for developing our application, All the file in this application are source safed, Whenever we do modification in the code it take longer time to build approax it takes around 2 min to display the default page (login page).
Please do send out your suggestions to reduce the time take for the build, is there any setting need to be done in IDE to make the build process much faster.
Regards
K.Karthik Doss
View Complete Forum Thread with Replies
Related Forum Messages:
Error On Build Solution, If SSIS Project Is Part Of Build
Hello, we have automated build on every night. In our solution is SSIS project, where each package is encrypted per password. We call build process per command line like this.. C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (c:DevelopmentX3\X3.sln /build Release)' in 'c:DevelopmentProjectsDailyBuild Through build process we get a error: External Program Failed: C:ProgrammeMicrosoft Visual Studio 8Common7IDEdevenv.exe (return code was 1): We think a reason is, that on build of SSIS project must be entered a password. You can wonder for what we need that SSIS packages are part of our build. We hope that on build process is also created Deployment Utility, if so set in dtproject.user. Is it so? Is there any way to create Deployment utility on automated build process? Can be a password provided pre command line? with best regards Anton Kalcik
View Replies !
Compilation Error
I'm trying to connect to an SQL database through my asp.net page and I'm getting an Compiler Error Message: BC30188: Declaration expected for the following codes: DBConn= New OledbConnection("Provider=sqloledb;" _ DBInsert.Commandtext = "Insert Into GuestInfo" _ DBInsert.Connection =DBConn DBInsert.Connection.Open DBInsert ExecuteNonQuery() What I'm trying to do is connect to the SQL database and input new information to the database. This is the entire code for connecting and entering info into the database. The SQL Database's name is HMS. I'm stuck and I can't figure it out. Dim DBConn as oledbConnection Dim DBInsert As New oledbCommand DBConn= New OledbConnection("Provider=sqloledb;" _ & "server=localhost;" _ & "Initial Catalog=HMS;" _ & "User id=sa;" _ & "Password=yourpassword;") DBInsert.Commandtext = "Insert Into GuestInfo" _ & "(FirstName,Lastname,Address,City,State,Zipcode) values ('" _ &"'" & txtFirstName.Text & "', " _ &"'" & txtLastName.Text & "', " _ &"'" & txtAddress.Text & "', " _ &"'" & txtCity.Text &"', " _ &"'" & txtState.Text &"', " _ &"'" & txtZipCode.Text &"', ")" DBInsert.Connection =DBConn DBInsert.Connection.Open DBInsert ExecuteNonQuery()
View Replies !
Prevent SP Compilation
Hi,I'm using SQL Server 2000 MSDE on a laptop running Windows XP.I have a couple of SP's that that quite some time to compile. So I waswondering: is there any way to have the database *not* recompile them everytime after a reboot?BOL says: "As a database is changed by such actions as adding indexes orchanging data in indexed columns, the original query plans used to accessits tables should be optimized again by recompiling them. This optimizationhappens automatically the first time a stored procedure is run afterMicrosoft® SQL ServerT 2000 is restarted."Now the SQL Server is restarted a lot, because laptops don't have endlessbatteries <g>Cheers,Bas
View Replies !
Avoiding Compilation
Using small stored procs or sp_executesql dramatically reduces the number ofrecompiles and increases the reuse of execution plans. This is evident fromboth the usecount in syscacheobjects, perfmon, and profiler. However I'm ata loss to determine what causes a compilation. Under rare circumstances theusecount for Compiled Plan does not increase as statements are run. Seemsto correspond to when there is no execution plan. It would seem to me thatcompilation is a resource intensive task that if possible (data and schemaare not changing) should be held to a minimum.How does one encourage the reuse of compile plans?Is this the same as minimizing compilation?Looks like some of this behavior is changing in SQL 2005....Thanks,Danny
View Replies !
SP Compilation Confirmation Message?
How can we say whether the SP is successfully compiled or not if we are compiling it on the server as a part of the TSQL script since it does not throw any message like ORACLE does. In oracle, system will let you know whether the the procedure is successfully complied or not? Thanks/
View Replies !
SQL Compilation And Execution Plan
Hi all, I€™m having a test regarding to the image data type. The test program is written with sql native api and just update the image data type column, but I looked the SQL Compilations/sec and Batch Requests/sec counters in SQLServer:QL Statistics using Perfmon, both values are almost the same. It seemed whenever the stored procedure is called, SQLServer compiles it and makes execution plan again. But when I had a test without image data type, SQL Compilation/sec was 0. SQL version is Microsoft SQL Server 2005 - 9.00.3054.00 (Intel X86) (Build 2600: Service Pack 2). Is SQL server working the way expected or am I missing something?
View Replies !
Compilation Of Stored Procs
Hi, I would like to know if the execution plans of stored procs also get migrated when we do migration to 2005 from 2000 using attachdetach method or we will need to re-run the stored procs? The thing is when I am running the Stored procs in 2005, its performing really slow in first run. Any help in his regard is highly appreciated. Thanks, Ritesh
View Replies !
Compilation Error On Store Procedure
Hi all,Here is my error: Server: Msg 245, Level 16, State 1, Procedure NewAcctTypeSP, Line 10Syntax error converting the varchar value 'The account type is already exist' to a column of data type int.Here is my procedure:ALTER PROC NewAcctTypeSP(@acctType VARCHAR(20), @message VARCHAR (40) OUT)ASBEGIN --checks if the new account type is already exist IF EXISTS (SELECT * FROM AcctTypeCatalog WHERE acctType = @acctType) BEGIN SET @message = 'The account type is already exist' RETURN @message END BEGIN TRANSACTION INSERT INTO AcctTypeCatalog (acctType) VALUES (@acctType) --if there is an error on the insertion, rolls back the transaction; otherwise, commits the transaction IF @@error <> 0 OR @@rowcount <> 1 BEGIN ROLLBACK TRANSACTION SET @message = 'Insertion failure on AcctTypeCatalog table.' RETURN @message END ELSE BEGIN COMMIT TRANSACTION END RETURN @@ROWCOUNTENDGO --execute the procedureDECLARE @message VARCHAR (40);EXEC NewAcctTypeSP 'CDs', @message;I am not quite sure where I got a type converting error in my code and anyone can help me solve it???(p.s. I want to return the @message value to my .aspx page)Thanks.
View Replies !
C++ Ole DB Stack Overflow During Sql Server Compilation
hi,when i execute :CCommand<CManualAccessor, CBulkRowset, CNoMultipleResults> rs;rs.SetRows(100);HRESULT code_resultat = rs.Open(session, requete, &propset, NULL,DBGUID_DBSQL, FALSE);with a requete with length = 13000, it works perfectlybut when my requete length is 200000 (example : SELECT * FROM myTABLEWHERE id_table IN("lot of number : more then 30000 number"))i have code_resultat = DB_E_ERRORSINCOMMAND (= 0x80040e14)and when i explore the IErrorInfo message, i have :minor = 565 and the message issource :Microsoft OLE DB Provider for SQL Serverserveur has made a stack overflow during compilation...Is there a solution to extract to data ?in a fast way ...thanks in advance ...Mike
View Replies !
Function Returning Error During Compilation.....
Hi , I am creating a function which is going to return a table. The Code ofr the function is as follows... =============================== Create function udf_qcard (@cg1 varchar(25)) returns @rec_card table (t_cusip varchar(10),t_data varchar(70)) AS begin declare @t1_sys char(10),@t1_all varchar(11) declare @temp_qcard table (tdata varchar(11) collate SQL_Latin1_General_CP1_CS_AS) if (substring(@cg1,1,2)='Q$') set @cg1 = (select substring(@cg1,3,len(@cg1)) where substring(@cg1,1,2)='Q$') DECLARE c1 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @cg1 and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY insert into @temp_qcard values(@cg1) OPEN C1 FETCH NEXT FROM c1 INTO @t1_sys,@t1_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t1_all) declare @t2_sys char(10),@t2_all varchar(10) DECLARE c2 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t1_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY begin OPEN C2 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t2_all) declare @t3_sys char(10),@t3_all varchar(10) DECLARE c3 SCROLL CURSOR FOR select groups_system, substring(groups_alldata,3,10) from tbl_groups where groups_system = @t2_all and groups_alldata like 'Q$%' and groups_seq>=1 FOR READ ONLY begin OPEN C3 FETCH NEXT FROM c3 INTO @t3_sys,@t3_all WHILE @@FETCH_STATUS = 0 BEGIN insert into @temp_qcard values(@t3_all) FETCH NEXT FROM c3 INTO @t3_sys,@t3_all end end close c3 deallocate c3 FETCH NEXT FROM c2 INTO @t2_sys,@t2_all end end close c2 DEALLOCATE c2 FETCH NEXT FROM c1 INTO @t1_sys,@t1_all END CLOSE c1 DEALLOCATE c1 Insert @rec_card select groups_q+groups_cusip,groups_data from tbl_groups where groups_system in (select tdata from @temp_qcard) and groups_seq>=1 and groups_alldata not like 'Q$%' order by groups_alldata RETURN END ========================== While compiling this I am getting the Below error .... ================== Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 10 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 23 Mixing old and new syntax to specify cursor options is not allowed. Server: Msg 1049, Level 15, State 1, Procedure udf_qcard, Line 35 Mixing old and new syntax to specify cursor options is not allowed. ================= Can Anyone please help me how to resolve this issue... Thanks with Regards. -Mohit.
View Replies !
SSIS Package Compilation And Execution
I am wondering something, once we've created a job that executes a package at a given time interval, does that package get recompiled each time the job spins up and executes the package? Or is the package compiled once and then that compiled code is executed each run after the first run? What I'm seein is this; I have a package that reads data from flat text files and then dumps that data into the database. The package will take 3 minutes to execute when executing on a single file, but when it's looping through ~50 files, it will take ~30 minutes to execute, that is less than a minute per file. Why is this? Hopefully I'm just forgetting something and not setting a checkbox or radio button somewhere. The job is set up as an SSIS job, not as a command line job. Thanks in advance for any help you can give me. Wayne E. Pfeffer Sr. Systems Analyst Hutchinson Technolgy Inc.
View Replies !
Newbie With An Easy Compilation Error Question.
I've been looking over this and can't see anything wrong. Can anyone shed some light on this for me? ------------------ Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: CS0117: 'System.Data.SqlClient.SqlConnection' does not contain a definition for 'ExecuteReader' Source Error: Line 16: SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn); Line 17: myConn.Open(); Line 18: SqlDataReader myReader = myConn.ExecuteReader(); Line 19: do Line 20: { Source File: D:Inetpubhoteladvisor estLogin.aspx Line: 18 void Login(string username, string password) { SqlConnection myConn = new SqlConnection ("server = client1; uid = dbadmin; pwd = dbadmin; database = hotels"); SqlCommand myComm = new SqlCommand("SELECT users, password FROM users WHERE username='" + username + "' AND password='" + password + "'", myConn); myConn.Open(); SqlDataReader myReader = myConn.ExecuteReader(); do { while (reader.Read()) { if (username == myReader.GetString(1) && password == myReader.GetString(2)) { messages.Text = "Your login was successful!"; } else { messages.Text = " Your login was unsuccessful!"; } } } while (reader.NextResult()); myReader.Close(); myConn.Close(); } void Submit_Click(Object sender, EventArgs e) { Login(username.Text, password.Text); } Edit by moderator - NetProfit: Added < code>< /code> tags.
View Replies !
Ignore Compilation Errors For Creation Of Stored Procedures
I have an application that is moving from an home made full text search engine to using the full text indexing engine of SQL 2005. I have a stored procedure that I want to behave as: check documents table to determine whether a full text index for SQL's full text engine has been created. If it has not, query the documentText table (which is the table for my in-house full text search) If it has, use the full text indexing engine My problem is that compilation of the TSQL to create the stored procedure fails when the full text index has not already been created with the followign error: Msg 7601, Level 16, State 2, Procedure My_FullTextSearch, Line 0 Cannot use a CONTAINS or FREETEXT predicate on table or indexed view 'Documents' because it is not full-text indexed. In my test lab, I tried: 1. creating the full text index 2. creating the stored procedure 3. deleting the ful text index which gets me to the desired end result of having a stored procedure that can determine whether or not the full text index has been created yet (the procedure works in this state). But I creating this index as part of this stored procedure creation in production is not an option. My question - Can I somehow tell SQL to ignore the compilation errors it encounters while creating this stored procedure? If not, is there some other way to create this "smart" stored procedure? Here's a code snippet stripped down to the bare minimum to generate the error: CREATE PROCEDURE [My_FullTextSearch] @Term VarChar(1000) AS BEGIN SET NOCOUNT ON; IF NOT OBJECTPROPERTY(OBJECT_ID('Documents'), 'TableHasActiveFulltextIndex')=1 BEGIN Select [DocumentID] from [DocumentText] where [Term] like '%' + LTRIM(@Term) + '%' END ELSE BEGIN Select [key] from FREETEXTTABLE(Documents, Contents, @Term) END END
View Replies !
Stored Procedure Compilation Question: Doing Disparate Things In Aproc
To minimize the very large number of stored procedures typicallyassociated with an application, I have gotten in the habit ofcombining a select, insert, update, and delete all in one procedure,and passing an argument to indicate which to use. (I use defaultvalues for all input params to avoid having to declare them forselects and deletes.) So I'll have just one PersonAdmin proc insteadof PersonGet, PersonInsert, PersonUpdate, and PersonDelete procsWhile this is nice for housekeeping, I wonder what the compiler doeswith such an architecture,and I fear the worst. The select returns arecordset; the others don't.Is this a bad idea?If it is, I really wish SQL would permit some sort of user folderstructure in the proc list.
View Replies !
SqlDataSource, DataView, CType Function && Page_Load-Compilation ErrorBC30451: Name 'SqlDataSource3' Is Not Declared.
Hi all, In my VWD 2005 Express, I created a website "AverageTCE" that had Default.aspx, Default.aspx.vb and App_Code (see the attached code) for configurating a direct SqlDataSource connection to the dbo.Table "LabData" of my SQL Server 2005 Express "SQLEXPRESS" via SqlDataSource, DataView, CType Function and the Page_Load procedure. I executed the website "AverageTCE" and I got Compilation ErrorBC30451: Name 'SqlDataSource3' is not declared: Server Error in '/AverageTCE' Application. Compilation Error Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately. Compiler Error Message: BC30451: Name 'SqlDataSource3' is not declared.Source Error: Line 8: <DataObjectMethod(DataObjectMethodType.Select)> _ Line 9: Public Shared Function SelectedConcentration() As ConcDB Line 10: Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) Line 11: dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Line 12: Source File: C:Documents and Settingse1enxshcMy DocumentsVisual Studio 2005WebSitesAverageTCEApp_CodeConcDB.vb Line: 10 //////////--Default.aspx--////////////////////////// <%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default.aspx.vb" Inherits="_Default" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml" > <head runat="server"> <title>SQL DataSource</title> </head> <body> <form id="form1" runat="server"> <div> Average TCE<br /> <asp:DropDownList ID="DropDownList1" runat="server" AutoPostBack="True" DataSourceID="SqlDataSource2" DataTextField="SampleID" DataValueField="SampleID"> </asp:DropDownList><asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString2 %>" SelectCommand="SELECT [SampleID] FROM [LabData]"></asp:SqlDataSource> <br /> <br /> <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SampleID" DataSourceID="SqlDataSource1"> <Columns> <asp:BoundField DataField="SampleID" HeaderText="SampleID" ReadOnly="True" SortExpression="SampleID" /> <asp:BoundField DataField="SampleName" HeaderText="SampleName" SortExpression="SampleName" /> <asp:BoundField DataField="AnalyteName" HeaderText="AnalyteName" SortExpression="AnalyteName" /> <asp:BoundField DataField="Concentration" HeaderText="Concentration" SortExpression="Concentration" /> </Columns> </asp:GridView> <asp:SqlDataSource ID="ddlLabData" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString %>" SelectCommand="SELECT * FROM [LabData] WHERE ([SampleID] = @SampleID)"> <SelectParameters> <asp:ControlParameter ControlID="DropDownList1" DefaultValue="3" Name="SampleID" PropertyName="SelectedValue" Type="Int32" /> </SelectParameters> </asp:SqlDataSource> <br /> <asp:SqlDataSource ID="SqlDataSource3" runat="server" ConnectionString="<%$ ConnectionStrings:ChemDatabaseConnectionString3 %>" SelectCommand="SELECT * FROM [LabData]"></asp:SqlDataSource> <br /> <br /> LabData-Analyte: <asp:TextBox ID="TextBox1" runat="server"></asp:TextBox><br /> <br /> LabData-Conc: <asp:TextBox ID="TextBox2" runat="server"></asp:TextBox><br /> <br /> Average values: <asp:Label ID="Label1" runat="server" Text="lblAverageValue"></asp:Label><br /> <br /> <br /> <br /> </div> </form> </body> </html> ///////////--Default.aspx.vb--//////////////////////////////// Partial Class _Default Inherits System.Web.UI.Page End Class ////////////////--App_Code/ConcDB.vb--////////////////////// Imports Microsoft.VisualBasic Imports System.ComponentModel Imports System.Data Imports System.Data.SqlClient Imports System.Data.SqlTypes <DataObject(True)> Public Class ConcDB <DataObjectMethod(DataObjectMethodType.Select)> _ Public Shared Function SelectedConcentration() As ConcDB Dim dv As DataView = CType(SqlDataSource3.Select(DataSourceSelectArguments.Empty), DataView) dvConcDB.RowFilter = "Concentration = '" & ddlLabData.SelectedValue & "'" Dim dvRow As DataRowView = dvConcDB(0) Dim ConcDB As New ConcDB ConcDB.SelectedConcentration = CDec(0)("Concentration") Return ConcDB End Function Call AverageValue (Conc1) Public Shared Function AverageValue(ByVal Conc1 As Decimal) Dim AverageConc As Decimal AverageConc = (Conc1 + 22.0) / 2 Return AverageConc End Function End Class ************************************************************** I have 2 questions to ask: 1) How can I fix this Compilation Error BC30451: Name 'SqlDataSource3' is not declared? 2) I just read MSDN Visual Studio 2005 Technical Article "Data Access in ASP.NET 2.0" and I saw the following thing: Types of Data Sources: SqlDataSouirce: The configuration of a SqlDataSoure is more complex then that of the AccessDataSource, and is intended for enterprise applications that require the features provided by a true database management system (DBMS). I am using the website application in VWD 2005 Express to do the task of extracting data values from the Tables of SQL Server 2005 Express via .NET Framwork, ASP.NET 2.0 and VB 2005 programming. Can VWD 2005 Express be configured to SQL Server 2005 Express (SQLEXPESS) for the SqlDataSource connection and do the data-extraction task via DataView, CType Function and the Page-Load procedure? Please help, respond and answer the above-mentiopned 2 questions. Many Thanks, Scott Chang
View Replies !
New SQL 7 Build
Anyone know what these are? ftp://ftp.microsoft.com/bussys/sql/xfer/ It appears new builds for beta are going out. However, the password "Sphinx" is no longer valid. KMT
View Replies !
SQL To Build SQL
I know how to use sql to write a block of sql statements by cutting and pasting the results in to the execution window. Now my question is this... is there a way to create the sql and execute the results. i.e. Automatically cut and paste the results into the execution window and run it?? Something to munch on if your bored. Ideas appreciated.
View Replies !
What Build Is This?
Hey folks, I just inherited a machine where select @@version returns this: Microsoft SQL Server 7.00 - 7.00.710 (Intel X86) Okay, I know that: 623 is orig sql 699 is sp 1 842 is sp 2 961 is sp 3 -- but what's 710 ? going to http://search.support.microsoft.com/kb/c.asp?ln=en-us gets me exactly 0 <zero> hits. Is this a hotfix someone's aware of? Any thoughts/documentation/whatever would be appreciated. Thanks, Tom
View Replies !
RS Build
The common way that we use to delpoy reports in production server is using rsbuild.config file and running the command to execute it. Is this a Microsoft product ? does it have any licensing issue if i need to use this approach in my production environment to deploy the reports?
View Replies !
I Need Help To Build This Query.
Hi, I have two tables called Actcodes and a another table called FundBalances... The Act codes have the following format.. PlanId int, ClientId int, StartDate datetime, EndDate datetime, ClientActCode char(2) CodeDescription char(15) so based on what the user has selected i am getting the names of each codes and they have the following Rows. PlanId ClientId Startdate EndDate ClientActCode CodeDescription 111 1 1/1/2008 3/31/2008 01 Begininng balance 111 1 1/1/2008 3/31/2008 02 Contributions 111 1 1/1/2008 3/31/2008 03 something 111 1 1/1/2008 3/31/2008 04 sdkfjdkf 111 1 1/1/2008 3/31/2008 05 dfdfd 111 1 1/1/2008 3/31/2008 06 dfddfs 111 1 1/1/2008 3/31/2008 09 dfdf 111 1 1/1/2008 3/31/2008 15 dfdkfdlfk and my fund balance i have the following rows.. PlanId int Participantid int StartDAte datetime, end date datetime, FundId int Loans Act1 char(2) TotAct1 money . Act20 char(2) TotAct20 money and the data is as follows PlanId ParticipantId StartDate EndDate fundId Loans Act1 TotAct1 Act2 totact2 ----- Act20 TotAct20 111 1212 1/1/2008 3/31/2008 15 NULL 01 15.15 02 15.48 20 12.4561 111 1212 1/1/2008 3/31/2008 45 0 01 45.12 02 453.123 20 54.00 so on and so so forth tht Act1 matches the ClientActCode in the Act.. So is there a way i can get those column in my select which have actCodes present in the Act code table. for in... in my act code table for this plan i have 01,02,03,04,05,06, 09, 15 clientAct codes so can i get the its respective Acts and Totacts column as Act1, Act, Act3, Act Act5, Act6 based on the ClientAct codes. Any help will be appreciated.. Thanks Karen
View Replies !
Need Help To Build Querry
I have two tables linked with the colunm name systemId and I need to get all the systems responding to the condition (t1.software & 0000000001) = 1 and if they have any contacts I would like to see them. Here is what I have for the moment however my querry do not return the system info if no contact exist and that is wrong. select customers.customerId,customers.systemId,customers.country from customers,contacts where (customers.software & 0000000001) = 1 and customers.SystemId = contacts.SystemId order by customerid Systems -SystemId -CustomerId -Name ... Contacts -ContactId -SystemId -Name -Tel ...
View Replies !
Need Help To Build A Select
Hi there, I have 3 columns that contain the same kind of information and I would like to do a select that would return all the distinct records of all 3 colums. Any help woulb be appreciated. Thanks
View Replies !
Trying To Build CASE
I'm trying to add a CASE to my statement, I keep getting a syntax error. Can anyone see the issue? Should this CASE work? Thanks for your thoughts, Select tmp.Alternate_Name,dbo.tblClient.Salutation CASE WHEN (tmp.Alternate_Name > '') Then tmp.Alternate_Name As Card_Name Else dbo.tblClient.Salutation AS Card_Name END From dbo.tblClient INNER JOIN dbo.tblCards tmp ON dbo.tblClient.ClientID = tmp.ClientID
View Replies !
How To Build This Query
Hi, Please Help me to build this query. I have got a "User" Table --------------------- UserID UserName --------------------- 1 Tuffy Another Table "Groups" Table --------------------- GroupID GroupName --------------------- 1 Manager 2 Employee 3 Sales I have got a "UserGroup" Table HOLDING ID'S as Foreign key. The data in the TABLE is like this --------------------- UserID GroupID --------------------- 1 1(Manager from "Group" Table 1 2(Employee) 1 3(Sales) 2 2(Employee) 2 3(Sales) --------------------- Now when a user logged in The Groups have to be returned as a string that contains pipe separated Group names for example "Manager|Employee|Sales|" So if User 1 log in I need something like that UserID (1)-->"Manager|Employee|Sales|" Please help me how to write this query. Regards
View Replies !
How To Build My SQL Querry.
Hi. I know this querry: "SELECT TOP 10 * FROM MyTable ORDERBY ..." will give the top 10 rows. But how can I build a querry that can get the rows from 40-50 (ie). Thanks.
View Replies !
Need Programmer To Build A Dts...
I need to get a dts package built in sql 2000. Basic tasks are:* Users login to site and upload excel files to their own directory* Need to validate the data within the excel files* Then import that data into an existing database* If a record already exists in db, then update it with new data* original files to be zipped and moved to another folder after import* exceptions logged or emailedI haven't built a dts before and am looking to hire someone to build itout. Usually go to elance but thought I could get some direction here...
View Replies !
How To Build SQL Statement
these are my databases 1. project project_code| project_name | 20201 |Tanaman Padi | 2. account_name an_account_code | an_account_name |an_project_code | K6101 | PADI 1 |022020105 | K6102 |PADI 2 |022020105 | K6103 |PADI 3 |022020103 | 3. account_record ar_account_record | ar_current_debit | ar_current_credit| K6101 | 0 | 1000 | K6102 | 0 | 2000 | K6103 | 0 | 3000 | the agregate query bussiness volume = ar_current_credit - ar_current_debit i would like to get this output no| name | bussiness_volume| 1. | Tanaman Padi | 6000 | this is what ive done 1. 'get name sSQL = "select project_name, an_project_code from project, account_name where project_code = substring(an_project_code,3,5) and substring(an_project_code,3,2) = '20' " 2. 'get bussiness volume select sum(ar_current_creddit-ar_current_debit) as bussiness_volume from account_record where ar_account_code = rs1("an_acc_code") this is the results no | name | bussiness volume 1. |Tanaman Padi | 1000 2. | Tanaman Padi | 2000 3. | Tanaman Padi | 3000 what should i do?
View Replies !
VERSION / Build # ??
I swear I saw a stored procedure that would tell me what version service pack and what build my sql servers are... Does anybody know how to find out? I think its a transact query... Also, I think I saw a stored procedure that would associate that cryptic string of numbers into text; ex: "SQL 6.5, Service Pack 5"
View Replies !
Build A Calender
Whats the quickest way of building a calender split by day,month,year,day name of week. l want populate the table with that information.
View Replies !
Build Into One Procedure @@@@@
l would like to intergrate the the two procedures listed below into one procedure or function. l'm doing it in two steps.This report will have each loan listed and a final line summed on each of the amounts? enddate and startdate being my parameters and print the results to screen.And l'm failing to do that thats why its in two steps.Hope this makes it clear enough. -- Exec New '2001-04-01 00:00:00.000','2002-05-29 23:59:59.000' -- Exec New1 '2001-04-01 00:00:00.000','2002-05-29 23:59:59.000' Alter procedure new (@startdate datetime,@enddate datetime) as begin SELECT Distinct Loan_no AS Loan_no, Date_Issued AS Date_Issued, Store AS Store, Product AS Product, Capital_Amount AS Capital_Amount, Interest_Amount AS Interest_Amount, Total_Amount AS Total_Amount, Insurance_amount As Insurance_Amount, Admin_Fee AS Admin_Fee, User_Issued AS User_Issued, LoanBook AS Company, Status FROM Loan Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000' End Alter procedure new1(@startdate datetime,@enddate datetime) As Begin SELECT Sum(Capital_Amount) AS Capital_Amount, Sum(Interest_Amount) AS Interest_Amount, Sum(Total_Amount) AS Total_Amount, Sum(Insurance_amount) As Insurance_Amount, Sum(Admin_Fee) AS Admin_Fee FROM Loan Where Date_Issued BETWEEN '2001-04-01 00:00:00.000' And '2002-05-29 23:59:59.000' End
View Replies !
Better Way To Build A Report?
The Accounting Department needed some reports, and this was back when I was first learning SQL, so I wrote a batch file that used osql to run a procedure. This procedure created a second batch file with several hundred lines to run another stored procedure and output to .rpt files complete with page breaks. Now I'm revisiting this process having learned alot since then, and would like some input on how to handle this better. The batch file works, but I've never been one of those "if it's not broke" type of people. I'm one of those "If it's not broke, give me 5 minutes and it will be" kind of people I'm specifically interested in something to replace this CURSOR that builds the batch file: Code: DECLARE @Dealer Varchar(20) DECLARE Dealer_Cursor CURSOR FOR SELECT Dealer_id FROM Dealer WHERE (Dealer_id != '-1') AND (isnull(Category,'-1') != 'NOBILLING') OPEN Dealer_Cursor FETCH NEXT FROM Dealer_Cursor INTO @Dealer WHILE @@Fetch_Status = 0 BEGIN /* @Folder is passed as a fully qualified path to the billing folder */ /* Logging and Command Window Notification, to be placed into DealerBilling.bat */ PRINT 'ECHO Creating report for Dealer: ' + @Dealer + ' ...' PRINT 'ECHO Creating report for Dealer: ' + @Dealer + ' ... >> "' + Substring(@Folder,1,3) + 'Reportlog.txt"' PRINT '"osql" -SMYSERVER -UMYUSER -PMYPASS -w 103 -Q "EXEC MYSERVER.MYDB.dbo.Proc_Dealer_Listing ''' + @Dealer + '''" -o "' + @Folder + '' + @Dealer + '.rpt"' FETCH NEXT FROM Dealer_Cursor INTO @Dealer END PRINT ' ' CLOSE Dealer_Cursor DEALLOCATE Dealer_Cursor
View Replies !
Query Build Help
I have a table with 2 columns: Col1 Col2 stat1,stat2 AV,AD stat3 TD I need to build a query like this: SELECT * FROM table3 WHERE stat1=AV and stat2=AD and stat3=TD How do I do this?
View Replies !
How To Build This Expression?
Greetings friends, I have the following T-SQL CASE statement. I've spent the last 10 minutes trying to convert it to an expression in my derived column component but to no avail. case when f.etypeid < 10 then '000' + cast(f.etypeid as varchar) when f.etypeid > 10 and f.etypeid < 100 then '00' + cast(f.etypeid as varchar) else '0' + cast(f.etypeid as varchar) end Many thanks for your help in advance.
View Replies !
How To Build Properly
Hi, I'm very new in SSIS. I've created 3 packages in the project. sometimes when i modified the project and save/save-all it, when i tried to build (isn't this used to deploy?), I am being asked by this: Package 1 has been modified outside the source editor. Do you want to reload it? When I press on yes, all my modifications were not save. If i answered no, the build process stops. i dunno if this is because the build process is already finished or it was terminated because i chose 'NO'. When i tried to rebuild it again, it will ask me the same question. What is the proper way to save and build the project? When it says ' do you want to reload it?', does it mean reloading the old copy before modification? Thanks! cherrie
View Replies !
Build 3200 CU4
I am at a site where they are having problems seeing the backup data in the reports feature and they cannot delete old backups using theie scheduled maintenance job. They were wondering if the above mentioned build would resolve this issue. They do not see the "Include 1st level folders" check box in the backup wizard. They have a copy of the above build, but know instructions were sent. MS will not resend. If anyone knows whether this build will help or has the hotfix.txt file that includes the instructions, I and they would certainly appreciate it. Thanks. Tom.
View Replies !
Build Question
Hi: I am confused with the build versions in SQL server. When I install in my XP local machine...when I say select @@version i get the following... Microsoft SQL Server 2005 - 9.00.1399.06 (Intel X86) Oct 14 2005 00:33:37 Copyright (c) 1988-2005 Microsoft Corporation Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2) In my Enterprise 2003 server I get this... Microsoft SQL Server 2005 - 9.00.1399.06 (X64) Oct 14 2005 00:35:21 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1) I am concerned with the compatibility when I am using service broker between two sql servers...why do i get 2 different build versions...?
View Replies !
Report Build
When you build a report and run the report if you click on a field it will drilldown into detail. How do you choose what detail you want it to bring back?
View Replies !
How To Creating A Build
HI, I have developed one application in sql server 2005. Once i generate the scripts for all the objects, i want to give them to the client as a build. so that he can execute that build by giving parametres like server name,error log path,build source path and db name. Please let me know how to create that. Please do the needful.
View Replies !
Build Datbase From Xsd/xml ??
Hello all. I have an .xsd file and a .xml file that goes with it. Are there any tools available that will build a database structure in SQL Server from either of these two files? I can't see the .xml file being of much use for building the structure, but how about the .xsd file?
View Replies !
Build WHERE Clause
I have a stored procedure which expects one parameter @City Let’s say @City holds pipe delimited value: "New York|London|Paris" In my stored procedure I need to replace all pipes with OR operator? In other words my select statement needs to look like this : SELECT * FROM City WHERE CityName= 'New York' OR CityName= 'London' OR CityName= 'Paris' Thank you
View Replies !
How To Build A Workflow Using A SQL DB?
I would like to build a workflow system where 100 processes are requesting an item from a ~1.000.000 items large DB, process that item and move it to the next state. The problem with the current implementation I tried is that I get deadlocks.... The DB table looks like: CREATE TABLE Transactions( itemid CHAR(32), status TINYINT not null default 0, result INT not null default 0, lockby TINYINT not null default 0, .... (etc.) PRIMARY KEY(refno) ); CREATE INDEX IxStatus on transactions (status) Each process (with his own ID) is doing 4 step: 1) update transactions set status=1, lockby=<ID> from (select top 1 itemid from transactions where status=0) as t1 where t1.itemid=transactions.itemid 2) select itemid from transactions where status=1 and lockby=<ID> 3) process item 4) update transactions set status=2,result=<RESULT> where itemid=<ITEMID> Does anyone has a suggestion on how to do this?
View Replies !
Help To Build Query
I need to build following query: Each item has one or more manufacturers: Is any way to get all the items with all manufacturers where one of manufacturers fit required expression. I will try to explain in example: items | manufacturers ------------------------------ ABC1 | golf ABC1 | toyota ABC1 | citroen ABC2 | skoda ABC2 | subaru ABC2 | lada ABC3 | peugeot ABC3 | renault ABC3 | dodge Example for Input: *olf* Output: ABC1 | golf ABC1 | toyota ABC1 | citroen Example for Input: *aul* Output: ABC3 | peugeot ABC3 | renault ABC3 | dodge Thanks
View Replies !
Build A New Sql Query
Here is my problem. I need to build a new SQL query and im almost new with all the sql language. Im searching a lot and I have 2 book but I cant found what I'm looking for. I need to update the Site and Department in the table 1 with the site/department in the table 3 using the table 2 as a join. The table one is an item owned by the person in the table 3 and I need to set the Site and Department as the same. Table 1 Asset Name | InstanceID | Site | Department Table 2 InstanceID | User Name | Table 3 User Name | Site | Department Help will be really appreciated! Thanks
View Replies !
How Can I Build A Table Using A SP In A For Each Loop
I have a table of CategoryIDs and I want to increment through it passing each categoryID as a parameter to a stored procedure that returns the TOP 1 row, and build a new table from the the result. I do not know how to pass the categoryID into the stored procedure. I do not know how to begin building the new table. public DataTable GetContractorCats() { // Generates table of all Contractor Category IDs no parameters SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings ["ConnectionString2"].ConnectionString); SqlCommand cmd = new SqlCommand("GetContractorCatIDs", con); cmd.CommandType = CommandType.StoredProcedure; SqlDataAdapter da = new SqlDataAdapter(); da.SelectCommand = cmd; DataSet ds = new DataSet(); try { da.Fill(ds, "ContractorCats"); return ds.Tables["ContractorCats"]; } catch { throw new ApplicationException("Data error"); } }protected void Top1EachCategory(){ // need to build a new table row by row using a stored procedure that finds the top result from a database query int RowIncrement; RowIncrement = 0; DataTable dt1 = GetContractorCats(); foreach (DataRow row in dt1.Rows) { I need to pass value of dt1 table ["CG_ID"] as a parameter to Stored Procedure called "GetTop1" Run the stored procedure which returns a single row if the CG_ID is found and use its field values to build each row in a new table. The stored procedure returns C_Name, Category_Name, C_Email, C_RCode and C_City RowIncrement++; } }
View Replies !
How Can Build Two Tabels Together With Ms Sql Query
Hello to all, I have now two tabels ( Ta and Tb). the tabel includes difference attributte. I want to build this two tables together. I used this query: select * from Ta where Ta.Id = @ID union all select * from Tb where Tb.IdOfa = @ID but it doesn't work and the following error message comes "All inquiries in an SQL application, which contain a union operator, must contain directly many expressions in their goal lists "
View Replies !
How To Build An String Alias In T-SQL
I have the following stored procedure: SELECT SERVER_NAME, SERVICE_PORT, SERVER_NAME + ',' + SERVICE_PORT AS SERVER_AND_PORT FROM DEF_SERVICE_SETTINGS I want the 3rd column to be in format: SERVER_NAME + "," + SERVICE_PORT But SQL gives error. Sees "," as column. How can I fix this?
View Replies !
|
TRACKER
