InstantForum.NET v4.1.4SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comSat, 21 Nov 2009 03:47:46 GMT20http://www.sqlservercentral.com/Forums/Topic822166-145-1.aspxHi everyone,I have table called products which is having catid field whixh is of type varchar,Where this catid from category table which is of type int,And i want to select products from products based on the category.products table looks like thisPid catid productname1 1,2,3,7,8,11,15,16,17,18,19,20,21, Ceramic Mug2 1,2,3,4,5,7,8,11,13,16,18,19,20,23, t-shirts 3 1,2,3,4,5,7,8,11,20,21,24,25,28, calendars 4 1,2,3,4,5,7,8,10,11,12,15,16,17,18,19, greeting cards4 15,16,17,18,19, pen tableWhen i below this query for selecting whose catid is 1, it gives products whose product id may be like 11,16 are also selected select * from products where Catid like '%1%'but i want to filter based on category id so i am try to convert catid to varchar to int it gives below errorServer: Msg 245, Level 16, State 1, Line 1Conversion failed when converting the varchar value '1,2,3,7,8,11,15,16,17,18,19,20,21,' to data type int.Some body give me solution for this how i can get only products whose catid is 1 or 2 or so on.Thanks in advanceFri, 20 Nov 2009 02:14:57 GMTchandrashekar.2512http://www.sqlservercentral.com/Forums/Topic822761-145-1.aspxI don;t know what the term is for doing the eblow operation but I do know it's valid but for some reason it's not working, at least not consistently.HERE IS A Sample of what Im trying:DECLARE @sMyVal VARCHAR(8000)SELECT @sMyVal = ''SELECT @sMyVal = @sMyVal + IsNull(A.sCol1,'') + ';'FROM TABLE A WHERE A.iRowID Between 1 and 100SELECT @sMyValAssuming there are 100 rows in the TABLE then the above should produce a string of text delimited by the ';' and yet that is not what happens. I end up with only 1 value from the 100 that should appear. If I change the A.sCol1 to a literal value, say the letter 'X' then what I get in th end is a string of 'X', 100 of them to be exact. delimited with the ';'.I would simply ask why is this not working but whats really weird is it did work and then for some reason it stopped just 10 minutes later and I have no idea why. When I first had problems with this I looked an existing UDF that does this same kind of string building in a UDF and I broke it down piece by piece till it was using my table & columns. The thing worked so I assumed I must have just missed something in my orginal code. I then save this working code to a file. I revisit this not 10 minutes later and now when I run the code that worked before I once again get a query that retruns 1 value. I have checked to make sure that I should get a bunch of rows and when I switch back to appending a literal character that is what I get.Any ideas on what may be going on because this is not only highly frustrating since it is something that just stopped working for no apparent reason but also because I know it should work.Fri, 20 Nov 2009 15:49:04 GMTYSLGuruhttp://www.sqlservercentral.com/Forums/Topic821009-145-1.aspxOk I have been experimenting the other day to randomly retrieve files from the varbinary cell vs the same binary from the file system. I had 100k blobs and 100k files created from the same blobs on a file system spread over 1000 folders. The same machine was used with raid 0. First I executed 10 minutes of DB retrieval at random, then file system retrieval, also at random. To my dismay file system fared much slower that is to say that in the same 10 minutes I was able to fetch far fewer files from the file system. I need to feed files (blobs) as varbinary to the application that requests them. So the way I had files on the file system is to have a CLR function that returns varbinary.Are there any tricks to make file system BLOBs strategy work on par with varbinary in terms of speed?Wed, 18 Nov 2009 10:22:13 GMTzaebis 88446http://www.sqlservercentral.com/Forums/Topic822290-145-1.aspxHi My qusetion:i want export Table data to text file usind sqlsmd?but i have to run sqlcmd code from stored proc..i am able to export data using below command at command prompt:sqlcmd -S server1 -U testuse -P test@123 -d citidb -Q "select * from [table1](nolock)" -o "MyData.txt" but how can put the above code in Stored procedureso that i can execute that sprocedure..i dont want to use Xp_cmdshell in Stored procedureFri, 20 Nov 2009 05:48:30 GMTvinodkumar783-709835http://www.sqlservercentral.com/Forums/Topic822271-145-1.aspxHi everyone, I'm trying to connect to an SQL Server instance located on an Internet server. The client is behind ISA Server infrastructure, which, in my opinion, is the only thing that could be causing the problem. My .NET application fails to find the server (SqlConnection.Open throws a regular "network or instance related" exception). However, Management Studio connects immediately without any problems utilizing exactly the same connection string. Please advise as to what I could possibly check to solve this. Thank you.Fri, 20 Nov 2009 05:18:27 GMTtokaplanhttp://www.sqlservercentral.com/Forums/Topic821849-145-1.aspxOk, I have an idea how to do this, but I want to make it efficient.I have four tables in a DB (employees, customer, car, deal)employees = empId(pk)car = carID(pk & identity) custID(FK to customers.custID)customers = custID(pk and identity) empID(FK to employees.empID)deal = dealID(pk/identity) Also has custID, carID, and empID as FK'sWith having the PK's in customers, car and deal as identities, I can have these auto-increase each time something is submitted. How can I have the FK keys update with the same number that is assigned to the PK's(I have separate programs accessing the same database, so having an int in one program will not work). I was thinking having a separate table in the DB and assign a number to it; when the user submits the form, the program will grab this number from the DB, assign it to a local variable, increase by one, then update the number in the DB. Will this work?When the end-user clicks submit, I want the program to do multiple things:1) Take the string from where the employees id is entered, match the number with empid in the database, return material and save it to strings in the program.2) Take the information from the web form and insert into the database.3) Check to see if the empid matches one in the database, if not stop the submission and inform the user to enter a valid number (no idea how to do this)Does all this make sense? If not I can try to explain whatever you need to know.Words surrounded by (()) are omitted code/info from the actual program.The first If statement is commented out because it would not work? [code="vb"] If Me.rfCurrent.IsValid And Me.rfExp.IsValid And Me.rfUserID.IsValid And Me.rfSrvType.IsValid And Me.rfWarExp.IsValid Then Dim myReader As SqlDataReader Try ' If Me.txtCustLast.Text = "" Or Me.txtUserID.Text = "" Or Me.txtVin.Text = "" Or Me.txtWarEnd.Text = "" Or Me.txtExpdate.Text = "" Or Me.txtCustPhone.Text = "" Or Me.txtCustName.Text = "" Or Me.txtCrntMile.Text = "" Or Me.txtComment.Text = "" Then ' Throw New ArgumentException ' End If Dim mySqlCommand As SqlCommand = New SqlCommand("select * from employees where " _ & "empID = " + Me.txtUserID.Text + "", mysqlconnection) 'Open the connection mysqlconnection.Open() myReader = mySqlCommand.ExecuteReader(CommandBehavior.CloseConnection) 'Loop through the rows returned in the SqlDataReader While (myReader.Read()) empname = (myReader("empName").ToString()) empstore = (myReader("empStore").ToString()) empemail = (myReader("empEmail").ToString()) End While ' Catch argNullExc As ArgumentException ' Me.txtError.Text = "please make sure all required fields are filled out" Catch ex As Exception txtError.Text = "error: " + ex.Message Finally If Not myReader Is Nothing Then myReader.Close() End If End Try Dim rdl As String = Me.rdlSrvType.SelectedValue.ToString() obj.SmtpServer = "((server))" Mailmsg.To = "((email)); " + empemail Mailmsg.Bcc = "((email))" Mailmsg.From = empemail Mailmsg.BodyFormat = MailFormat.Html Mailmsg.Subject = "((subject)) " + empname Dim mailCon As String Mailmsg.Body = ((msg)) obj.Send(Mailmsg) End If[/code]Thu, 19 Nov 2009 11:48:31 GMTromallonhttp://www.sqlservercentral.com/Forums/Topic821105-145-1.aspxOk below is my newest task assigned to me by my boss, before you curse me about cusors, the SP in the DBs are already FULL of them and my Boss insist that I continue to use cursors. And I am new to T_SQL and have struggled through only a couple cursors with his help.1.anyway is this really like a [i]select into[/i] script but using a cursor?2. Do you think this task is a little ambiguous about what [i]exactly[/i] he wants this script to do or do I just not understand T-SQL enough to get him from the get go.He wrote: "Adam, write a script that reads the SysWordings table and populates the SysWordings_Optional_EN table with the value form each below column in the EN_Number when it is not empty or null and the Column name goes in to the EN_Description column. This will add a record for each column not null or empty so you will have to use a cursur as in the Delos.Here are the "each column below" he is refering to in his task for to me above:EN_AddInsured,EN_Alarm,EN_AnimalLiability,EN_CovA,EN_CovB,EN_CovC,EN_CovLU,EN_CovLA,EN_Cart,EN_EQ,EN_Flood,EN_InflationGuard,EN_OrdinanceLaw,EN_PersonalInjury,EN_PersonalLiabilty,EN_Rental,EN_ReplacementCost_CovA,EN_ReplacementCost_CovC,EN_Theft,EN_UnderConstruction,EN_WaterDamage,EN_WaterBackUp,EN_WDR,VersionDescription,VersionCommentsBelow is the table I am to select from ,followed by the table to populateCREATE TABLE [dbo].[SysWordings]( [ProgramCode] [varchar](7) NOT NULL, [StateCode] [varchar](2) NOT NULL, [Ins_CompanyCode] [varchar](2) NOT NULL, [ContractCode] [varchar](3) NOT NULL, [EffectiveDate_NB] [smalldatetime] NOT NULL, [ExpirationDate_NB] [smalldatetime] NOT NULL, [EffectiveDate_RN] [smalldatetime] NOT NULL, [ExpirationDate_RN] [smalldatetime] NOT NULL, [ProgramName] [image] NOT NULL, [MinimumEarnedClause] [varchar](255) NULL, [ServiceOfSuit] [varchar](255) NULL, [PrimaryCorrespondent] [varchar](50) NULL, [PrimaryCorrespondentAddress] [varchar](50) NULL, [PrimaryCorrespondentPhone] [varchar](10) NULL, [PrimaryCorrespondentSSN] [varchar](9) NULL, [SecondaryCorrespondent] [varchar](50) NULL, [SecondaryCorrespondentAdd] [varchar](50) NULL, [StateVerbage] [text] NULL, [ProgramEndorsement] [varchar](500) NULL, [DecPageFormNumber] [varchar](30) NULL, [LineCancellationFee] [varchar](255) NULL, [CoInsuranceClause] [varchar](50) NULL, [EN_AddInsured] [varchar](75) NULL, [EN_Alarm] [varchar](75) NULL, [EN_AnimalLiability] [varchar](75) NULL CONSTRAINT [DF_SysWordings_EN_AnimalLiability] DEFAULT (''), [EN_CovA] [varchar](75) NULL, [EN_CovB] [varchar](75) NULL, [EN_CovC] [varchar](75) NULL, [EN_CovLU] [varchar](75) NULL, [EN_CovLA] [varchar](75) NULL, [EN_Cart] [varchar](75) NULL, [EN_EQ] [varchar](75) NULL, [EN_Flood] [varchar](75) NULL, [EN_InflationGuard] [varchar](75) NULL, [EN_OrdinanceLaw] [varchar](75) NULL, [EN_PersonalInjury] [varchar](75) NULL, [EN_PersonalLiabilty] [varchar](250) NULL, [EN_Rental] [varchar](75) NULL, [EN_ReplacementCost_CovA] [varchar](75) NULL, [EN_ReplacementCost_CovC] [varchar](75) NULL, [EN_Theft] [varchar](75) NULL, [EN_UnderConstruction] [varchar](75) NULL, [EN_WaterDamage] [varchar](75) NULL, [EN_WaterBackUp] [varchar](75) NULL, [EN_WDR] [varchar](75) NULL, [VersionDescription] [varchar](75) NULL, [VersionComments] [varchar](100) NULL, CONSTRAINT [PK_SysWordings] PRIMARY KEY CLUSTERED ( [ProgramCode] ASC, [StateCode] ASC, [Ins_CompanyCode] ASC, [ContractCode] ASC, [EffectiveDate_NB] ASC, [EffectiveDate_RN] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]table to populate"CREATE TABLE [dbo].[SysWordings_Optional_EN]( [ProgramCode] [varchar](7) NOT NULL, [StateCode] [varchar](2) NOT NULL, [Ins_CompanyCode] [varchar](2) NOT NULL, [ContractCode] [varchar](3) NOT NULL, [EffectiveDate_NB] [smalldatetime] NOT NULL, [ExpirationDate_NB] [smalldatetime] NOT NULL, [EffectiveDate_RN] [smalldatetime] NOT NULL, [ExpirationDate_RN] [smalldatetime] NOT NULL, [DependentParameter_Name] [varchar](30) NOT NULL, [DependentParameter_Operator] [varchar](10) NOT NULL, [DependentParameter_Value] [varchar](255) NOT NULL, [EN_Description] [varchar](30) NOT NULL, [EN_Number] [varchar](30) NOT NULL) ON [PRIMARY]tanx in Advance!AdamWed, 18 Nov 2009 12:34:46 GMTadam spencerhttp://www.sqlservercentral.com/Forums/Topic821937-145-1.aspxHi,I am new to SQL server 2005. I am trying to get some user data from Active directory using linked server and LDAP query.I did the following:Used this statement for creating a new linked server:EXEC sp_addlinkedserver 'ADSI', 'Active Directory Services 2.5', 'ADSDSOObject', 'adsdatasource' I am not sure about the parameters that i am passing to this SP. Should I give the server name too which is rwdcAfter I execute the above SP I can see a new linked server added in the object explorer.Then I executed the following SQL ldap query:SELECT * FROM OpenQuery(ADSI, 'SELECT * FROM "LDAP://OU=Users,DC=mycompany,DC=com" ') As soon as I execute it the following error shows up:Msg 7321, Level 16, State 2, Line 1An error occurred while preparing the query "SELECT * FROM "LDAP://OU=Users,DC=mycompany,DC=com" " for execution against OLE DB provider "ADSDSOObject" for linked server "ADSI". Could any body help me to figure where the problem is.I have a readonly access to the AD.Also should edit any properties of the created linked server from the editor window.Thank you.Thu, 19 Nov 2009 13:49:42 GMTbhargavi83http://www.sqlservercentral.com/Forums/Topic821885-145-1.aspxHi,I have to tables: table1 (SomeID int) and table2(SomeID int). One column in each table. Please help to write query that will can to compare two tables, so we can know whether all rows (SomeID int) in table1 exists in table2. Query should return true or false.Examples:1. If table1 (SomeID int) has rows with values 1,2, and table2(SomeID int) has rows with values 1,2,3, then the query returns true.2. If table1 (SomeID int) has rows with values 1,4, and table2(SomeID int) has rows with values 1,2,3, then the query returns false.3. If table1 (SomeID int) is empty, and table2(SomeID int) has rows with values 1,2,3, then the query returns trueThanks in advanceThu, 19 Nov 2009 12:37:37 GMTam-883928http://www.sqlservercentral.com/Forums/Topic821717-145-1.aspxif so ,what is the syntax in t-sqlI have package in SSIS that will not run even with cmdshell enabled and proxies set.But I found a secure way to move it from dev to and Depoly live utilizing a job.ALso can U run a package form a VB.Net Visual studios project. I have a windows service appthat waits for a file to be move into there and using the File Watcher component of Visual studios VB.net or C# , it raises the an event based on a new file in a certain directory and kicks off the Exe. , I would like to do the logic in SSIS, becuase its my area of expertise, I googled everything to make make it run in C# as a windows service only to have live deplyment vetod by mi micromanager saying the config file for it should be encrypted when, with my experience, should just use a windows login in lue of sa that needs the password in the config file. Now, it is not recommended to come up with your own decryption algorythm, I learned through research, because then the evil will just search for the documented file on how to decrypt. So to some uo my question here, does anyone have the SYNTAX to run a SSIS package form a VB.Net Visual studios project.Thu, 19 Nov 2009 09:16:36 GMTadam spencerhttp://www.sqlservercentral.com/Forums/Topic821511-145-1.aspxhow to implement Unicode ...In a Database i m having a field with nvarchar (max) datatype when inserting a unicode string from the text box in to the database ...it is saving ???????????? in the database . i am using storeprocedure execution like.......................exec SP_AH_PRE_REQUEST 'SV',null,1,1,101,'polo',101,10,1,'11/1/2009',1,1,'79789798',101,'11/1/2009',N'తెలుగుదేసం','fdsdgs'it is workbut here pass the input parameter @message=N'తెలుగుదేసం'then only save the correct then how to pass N'తెలుగుదేసం'Thu, 19 Nov 2009 05:29:04 GMTpolo.csithttp://www.sqlservercentral.com/Forums/Topic819190-145-1.aspxHi everybody,:-)Can anybody tell me that what function do we use to convert upper or lower case words into mixed case (Initial caps). example: UPPER, lower should be displayed as Upper, Lower.Thanks in advance.Sun, 15 Nov 2009 22:41:19 GMTSoft Developerhttp://www.sqlservercentral.com/Forums/Topic821429-145-1.aspxI have one SP 'A' and another SP 'B'. SP 'B' is returning three tables. I want to call SP 'B' inside SP 'A' and want to fetch all three tables. Please help to achive this.Thu, 19 Nov 2009 03:00:12 GMTNarayan LShttp://www.sqlservercentral.com/Forums/Topic819916-145-1.aspxHi Friends,We are working for warehouse project , now we have to add one image column in one of that table, so we can get the image data from pbulisher and we can consume that.Now the question are ,> use of image datatype degrades the performance> can we use image field in where clause, or can we use image field in any kind of comparision or in binarychecksum(*)>can we bulk insert the image from flatfile, i dnt know is it the valid questions.So If you guys can share your experience or any link whcih can guide us to take the call.Thanks,MithunTue, 17 Nov 2009 03:26:22 GMTmithun.gitehttp://www.sqlservercentral.com/Forums/Topic820568-145-1.aspxHi guys,Is it possible that I can copy a column whose data type is float(8) to a column in another table whose data type is varchar(10)I attempted to update it usingupdate pset p.vphone = left(fp.phone,10)from PMPAXFT P join drkhandb.dbo.finalplans FP on p.VURL = fp.planskey and vphone is not nullBut using this it actually updated value which was in float as '6464737169' to varchar value 6.46474e+0Did tried type casting and conversion too but with no success.Thanks,Waqas.Tue, 17 Nov 2009 22:52:42 GMTrwaqashttp://www.sqlservercentral.com/Forums/Topic820394-145-1.aspxWe write a lot of stored procedures for reporting purposes and in those stored procedures we create a lot of temp tables. Our DBA insists that we create the temp table first and then do an insert into that temp table instead of doing a select into temp. I Google'd this online and a lot of documentation on this talks about SQL Server 6.5 and the issues that it had with Select into (we are running SQL Server 2005). Does it still create a lock on tempdb? Is there really any performance gains to had from doing it in this manner?Tue, 17 Nov 2009 14:07:39 GMTcivicman4http://www.sqlservercentral.com/Forums/Topic820721-145-1.aspxhi my requirement is i need to check any txt file on drive ----------------------------------------------------------for example , if a file "output.txt" exists at "c:\" Query : declare @command varchar(100)SET @command = 'dir c:\output.txt'EXEC [master].[dbo].[xp_cmdshell] @commandOutput is below on resulkt pane Volume in drive C has no label.Volume Serial Number is 5CE8-8857NULLDirectory of c:\NULLFile Not FoundNULL----------------------------------------------------------but this way i cant update status in controller sql table.as i need only one value (lets say 1\0 ) instead of output getting aboveplease helpBhuvneshWed, 18 Nov 2009 05:07:51 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic820124-145-1.aspxHello experts,I’m trying to write a code to insert data in one of the table ‘TotalTable’. First I’ve created a temp table ‘#TempTotalTable’ and popluate it with all the data I needed. Everything works fine upto here and I’ve been able to pull and hold data into ‘#TempTotalTable’. Now I need to insert this data in‘TotalTable’. I wrote the following query to insert data from ‘#TempTotalTable’ into ‘TotalTables’ Here is the query[Insert into dbo.TotalTableselect TBId, TBName, CapturedDate, DBName, TbCreationData, TbLastModifiedDate, NoOfColfrom #TempTotalTable order by tbid]But end up having the following error.[The INSERT statement conflicted with the FOREIGN KEY constraint "Per_Database". The conflict occurred in database "CIAdeel", table "dbo.TotalDatabase".The statement has been terminated.]I realize that the error is about conflicting fogeign key which I’ve in anyother table ‘TotalDatabase’. But I’ve already made sure all the entries of ‘DBName field’ in ‘#TempTotalTable’ which I’m trying to insert in ‘TotalTable’ are already exist in ‘TotalDatabase’ table. Because ‘DBName’ and ‘CapturedDate’ are the primary keys in ‘TotalDatabase’ and fogeign keys in ‘TotalTable’ table. Now I’m not sure how to resolve this issue.For reference here are the sturctures of ‘#TempTotalTable’, ‘TotalTables’ & ‘TotalDatabase’ tables.Create Table #TempTotalTable(DBname varchar(100),TBId int,TBName varchar(500),CapturedDate datetime,TBCreationData datetime,TBLastModifiedDate datetime,NoOfCol int)CREATE TABLE [dbo].[TotalTable]( [TbId] [int] NOT NULL, [TbName] [varchar](500) NOT NULL, [CapturedDate] [datetime] NOT NULL, [DbName] [varchar](100) NOT NULL, [TbCreationDate] [datetime] NULL, [TbLastModifiedDate] [datetime] NULL, [NoOfCol] [int] NULL, CONSTRAINT [XPKTotalTable] PRIMARY KEY NONCLUSTERED ( [TbId] ASC, [TbName] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]CREATE TABLE [dbo].[TotalDatabase]( [DbName] [varchar](100) NOT NULL, [DbCreationDate] [datetime] NULL, [CapturedDate] [datetime] NOT NULL, [NoOfTable] [int] NULL, CONSTRAINT [XPKTotalDatabase] PRIMARY KEY NONCLUSTERED ( [CapturedDate] ASC, [DbName] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]Can anybody tell what changes I should make and which table to insert data into ‘TotalTable’ table.Thanks a lot in advanceTue, 17 Nov 2009 08:42:41 GMTmr_adeelalisyedhttp://www.sqlservercentral.com/Forums/Topic819345-145-1.aspxhello there i am using sql server 2005 at windows xp sp2 with a windows application i developed. my windows app. connect to sql server and fetches records from table. but i have 6,5x,xx,xxx records at one of my customer. i am checking my app. until 40,00,000 records but now with 6.5 millions records i got sql timeout errori am used sql data adapter with command. code as below-str= 'select querycmd=new sqlcommand(str,conn)cmd.CommandTimeout=0da=cmd.excutequery()please help me in thisMon, 16 Nov 2009 06:44:16 GMTgr8.jainhttp://www.sqlservercentral.com/Forums/Topic820612-145-1.aspxDear all,I would like to create cursor inside the stored procedure which needs to fetch each record and do some calculation, this calculation depends on the other field int table. So based on the condition i need to return the value.But when i try to do the calculation it givesLine 21: Incorrect syntax near '@p1'.Please help me as this is very high priority work for me.Here is the stored procedure how it goesCreate Procedure DummyReport(@txt_StateCode int,@txt_DistrictCode int,@txt_Date2 smalldatetime,@txt_AccountNo varchar(20),@txt_DPCode int)AsDECLARE @TransCode varchar(20)DECLARE @Flt_Amount intDECLARE @p1 intDECLARE Cur_Flt_Amt Cursor FAST_FORWARD FOR SELECT Tbl_Transaction.int_TransCode,Tbl_Transaction.Flt_Amount FROM Tbl_Transaction WHERE txt_StateCode=@txt_StateCode and txt_DistrictCode=@txt_DistrictCode AND dat_Date <= CONVERT(DATETIME, @txt_Date2 , 102) AND (txt_AccountNO = @txt_AccountNo ) and (int_DPCode=@txt_DPCode)OPEN Cur_Flt_AmtFETCH NEXT FROM Cur_Flt_Amt INTO @TransCode, @Flt_AmountWHILE @@FETCH_STATUS = 0 if @TransCode='TR001' @p1 = dbo.AddFltAmount(@p1,@Flt_Amount) --here where i am getting error end if CLOSE Cur_Flt_AmtDEALLOCATE Cur_Flt_Amtreturn @p1 create function AddFltAmount(@p1 int,@fltAmt int)Returns intAsBegin Return @p1+ @fltAmtend Thanks in advanceChandrashekarWed, 18 Nov 2009 00:48:13 GMTchandrashekar.2512http://www.sqlservercentral.com/Forums/Topic820185-145-1.aspxI'm having trouble creating a deterministic function.For a bit of background, I had to add to a table a computed column whose formula was based on a function.But the table had an indexed view on it. When I tried to recreate the indexed view, I got complaints that [b]Cannot create index on view "xxx.dbo.xxx" because function "dbo.fnxxx" referenced by the view performs user or system data access.[/b] I tried commenting out bits of my function to see which bit SQL objected to. But eventually I was left with nothing, and SQL was still complaining. I then looked at OBJECTPROPERTY IsDeterministic to see if my function was - and it wasn't! Trouble is I can't see to make even the simplest function Deterministic now.Can anyone see what I'm doing wrong?Thanks,David.CREATE FUNCTION dbo.fnTest ()RETURNS intASBEGINRETURN 1ENDGOselect OBJECTPROPERTY (object_id('fnTest'),'IsDeterministic')Tue, 17 Nov 2009 09:35:05 GMTDavid McKinneyhttp://www.sqlservercentral.com/Forums/Topic820202-145-1.aspxHiCan anyone advise me if it is possible to run a SQL query when one of the string values contain 2 spaces within the string itself. If not how can I work around this (please see below):order_branchcode = 'XXXXXX XX'Thanks in advance.SeanTue, 17 Nov 2009 09:56:07 GMTsshalloehttp://www.sqlservercentral.com/Forums/Topic820407-145-1.aspxHI All am trying to create a cursor, which will fetch the count of a particular record from different database, in that am getting the following error, must declare the table variable @dbname please see the code belowdeclare @dbname varchar(20) declare E_Count Cursor for select name from sys.databases where name like '%_Voters' open E_Count fetch next from E_Count into @dbname while @@FETCH_STATUS = 0 begin insert into #count(T_Count,dbname) select COUNT(*) as T_Count,@dbname from @dbname +'.'+Prod_Stats Where ethinicity = 'U1' fetch next from E_count End close E_Countam getting error in select statement after insert into from @dbname (must Declare the table variable)please help me with thisTue, 17 Nov 2009 14:23:28 GMTmj_saran2002http://www.sqlservercentral.com/Forums/Topic819965-145-1.aspxHithis is the table scriptCREATE TABLE [dbo].[RequestSubTypes]( [RequestSubTypeID] [int] IDENTITY(1,1) NOT NULL, [RequestTypeID] [int] NOT NULL, [RequestSubTypeName] [varchar](50) NOT NULL, CONSTRAINT [PK_RequestSubTypes] PRIMARY KEY CLUSTERED ( [RequestSubTypeID] ASC)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]) ON [PRIMARY]And the indexes are as follows:-ALTER TABLE [dbo].[RequestSubTypes] ADD CONSTRAINT [PK_RequestSubTypes] PRIMARY KEY CLUSTERED ( [RequestSubTypeID] ASC)WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY][b]CREATE UNIQUE NONCLUSTERED INDEX [IX_RequestSubTypes] ON [dbo].[RequestSubTypes] ( [RequestSubTypeID] ASC)WITH (SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY][/b]Column [b]RequestSubTypeID[/b] is a identity column, then why the index IX_RequestSubTypes( non clustered ) was created, I am not able to understand the same. It appears that it's been created by mistake.Could some one explain the same?thanks in advance.Tue, 17 Nov 2009 05:07:50 GMTRookee DBAhttp://www.sqlservercentral.com/Forums/Topic819980-145-1.aspxHi,How to decide when to have Primary key as clustered or non-clustered on a column which as identity column as its primary key.Tue, 17 Nov 2009 05:50:54 GMTRookee DBAhttp://www.sqlservercentral.com/Forums/Topic819968-145-1.aspxHi allCan anyone help me to create a job that execute once a year....In job creation wizard i find only for months and Days.....Pls help me out....Thanks.Tue, 17 Nov 2009 05:18:08 GMTsaveehttp://www.sqlservercentral.com/Forums/Topic819937-145-1.aspxhi i am getting different output than the inserting values --------------------------------------------------------------------------------------create table a ( abc datetime)insert into a values(cast(('2009-11-16 23:43:57.049') as datetime))select * from aoutput---------------------2009-11-16 23:43:57.049insert into a values('2009-11-16 23:43:57.058')select * from aoutput---------------------2009-11-16 23:43:57.053Tue, 17 Nov 2009 04:14:38 GMTBhuvneshhttp://www.sqlservercentral.com/Forums/Topic819884-145-1.aspxHiI'm fairly new to MS SQL and require some assistance if possible.I have two similar query statements one looking for sum of local orders and the other looking for sum of global orders. However, I need to combine the two queries into one the problem is each of the constructed queries require the Group By clause in order to out put the required detail. (see below)-- Query One select sum(poit_valord),poit_supplierfrom poitem,porder,plsuppwhere poit_order = pord_orderAND poit_qtydel < poit_qtyordAND pord_pondcode = 'Local'AND pord_supplier = poit_supplierAND plsup_ndcode = pord_supndcodeGROUP BY poit_supplier,plsup_minord -- Query Twoselect sum(poit_valord),poit_supplierfrom poitem,porder,plsuppwhere poit_order = pord_orderAND poit_qtydel < poit_qtyordAND pord_pondcode <> 'Local'AND pord_supplier = poit_supplierAND plsup_ndcode = pord_supndcodeGROUP BY poit_supplier,plsup_minordAny help would be very much appreciated.ThanksSeanTue, 17 Nov 2009 01:58:10 GMTsshalloehttp://www.sqlservercentral.com/Forums/Topic818392-145-1.aspxWe are experiencing some dead locks. During testing, I have captured the trace. After some testing, I have analysed the trace in DTA and its suggesting me to create some indexes.Should I go ahead and create all of them or some other things I need to consider?Could any one suggest?cheersFri, 13 Nov 2009 04:50:06 GMTRookee DBAhttp://www.sqlservercentral.com/Forums/Topic796445-145-1.aspxIn a Full Recovery model, after every transaction log is backed up, is it worth shrinking the Log File?If yes, what are the benefits? Alternatively, are there side(ill)-effects to shrinking TL after backing it up?I am thinking not only in terms of disk size it occupies, but also for overall performance.DanThu, 01 Oct 2009 09:50:14 GMTrepent_kog_is_nearhttp://www.sqlservercentral.com/Forums/Topic819194-145-1.aspxHiI have a requirement where col 3 should contain values as in sample below (col1 and col2 are already populated)Col1 col2 col3abc 1 1abc 2 1abc 3 1fgh 1 2dft 1 3fgh 2 2abc 4 1Can someone pls helpI know I can use update statement and group by clause but it will take time for millions of records .. hence i'm looking for some function like rank(), dense rank() but somehow not able to do itSun, 15 Nov 2009 23:15:53 GMTkhushbuhttp://www.sqlservercentral.com/Forums/Topic819303-145-1.aspxHi AllI'm trying to build a query that will filter the top 10 values. Noting that we never know what the values in the list will be. The values below in the Before column should filter to After column. Thanks for you assistance.Asta :-)Before AfterRank Rank3 333 343 357 367 377 387 408 409 4112 4512 4512 4513 4515 461516171919202122232425262728293031323334353637384040414545454546Mon, 16 Nov 2009 05:03:12 GMTkeil.astahttp://www.sqlservercentral.com/Forums/Topic819192-145-1.aspxServer A is 2000 and Server B is 2005, in server A there are about 10 users having different permissions i dont know the password of the users, if i take the backup and restore server A to server B whether all the users will come into server B or how? there are different logins are also there how to get those Logins to Server B(2005)ThanksParthiSun, 15 Nov 2009 22:52:10 GMTparthi-1705http://www.sqlservercentral.com/Forums/Topic819509-145-1.aspxHi,I need to know how much is the size of a particular index on a table.cheersMon, 16 Nov 2009 10:16:30 GMTRookee DBAhttp://www.sqlservercentral.com/Forums/Topic819493-145-1.aspxHello experts,I’m trying to get all the available column’s creation date and also last modified date within database. I’ve tried to use the following without any luck.select * from sys.objectsselect * from sys.columnsselect * from information_schema.columnsDoes anybody know a way how I can get pull this information?Thanks a lot advance.Mon, 16 Nov 2009 09:54:58 GMTmr_adeelalisyedhttp://www.sqlservercentral.com/Forums/Topic819483-145-1.aspxI have a logical model for my new project. In that I see lot of column names mentioned as ..._OID as primary key & then joined to link entities as foreign key ..._OID.I would like to know what is an OID? What does it stand for? Whether OID belongs to any specific database servers in the market?Thanks.Mon, 16 Nov 2009 09:45:44 GMTRJ-476420http://www.sqlservercentral.com/Forums/Topic819284-145-1.aspxI have 2 sql server sql server 2005 --> xsql server 2000 --> yI run one query on both servers , and found that on y execution fast, while so slow on 2005 I displayed the execution plan on both servers and found that on x , the sorting (distinct sort) take 100% cost , while on y it take 1%what's the problem, and how to solve it Mon, 16 Nov 2009 03:48:50 GMTali.m.habibhttp://www.sqlservercentral.com/Forums/Topic819308-145-1.aspxIdentity column and clustered, unique located on PRIMARY what is the Difference?What is the diff b/w Pk and Identity columnThanksParthiMon, 16 Nov 2009 05:17:03 GMTparthi-1705http://www.sqlservercentral.com/Forums/Topic655374-145-1.aspxHi,I need to know the difference between LinQ and NHibernate in the following characterstics:a. Speed for insert/update/select/deleteb. Database support (Like MySQL, Oracle, etc.,) (I know that:SQL joins hand with both)c. Performance with heavy datas.d. OthersThanksWed, 11 Feb 2009 21:13:55 GMTjchandramoulihttp://www.sqlservercentral.com/Forums/Topic819008-145-1.aspxHello Experts,I’m trying to fill in one of my table with the information which I’m pulling from two different places (sys.Object & Information.schema.columns). So far I’ve fetch and stored all the data what I need. My first table structure is as followsCreate Table #TempTotalTable(DBname varchar(100),TBId int,TBName varchar(500),CapturedDate datetime,TBCreationData datetime,TBLastModifiedDate datetime,NoOfCol int)And second table structure is like this.create table #ColumnPerTable(DBName varchar(100),TBName varchar(500),NoOfColumn int)Now keeping in mind at some point I would’ve to update NoOfCol in #TempTotalTable though #ColumnPerTable I kept two fields same on both tables TBName & NoOfColumn. Now my problem is when I execute the following update statementupdate #TempTotalTableset NoOfCol = NoOfColfrom #ColumnPerTablewhere #TempTotalTable.TBName = #ColumnPerTable.TBNameIt get executed with the message 235 rows get effected. However when I go back and do select on #TempTotalTable still NoOfCol shows null as follows. [size="1"]DBName TBId TBName CapturedDate TBCreationDate TBLastMoifiedDate NoOfColMaster 326292222 Account 2009-11-14 08:54:43.217 2009-10-28 14:22:49.813 2009-10-28 14:22:49.817 NULLMaster 662293419 Account 2009-11-14 08:54:43.217 2009-10-28 14:22:49.813 2009-10-28 14:22:49.817 NULLMaster 710293590 Account 2009-11-14 08:54:43.217 2009-10-28 14:22:49.813 2009-10-28 14:22:49.817 NULL[/size]I’m not able to understand why? Can anybody see what’s the problem here?Thanks a lot in advance.Sat, 14 Nov 2009 07:40:11 GMTmr_adeelalisyed