I'm wondering if an idea I'm playing with is feasible and if so, how you would recommend implementing it.
Let's say I have a Dictionary table, 2 columns:
Word | Definition
And I have a string - "The cat sat on the dog"
If there's a definition for "cat" in the dictionary table, I want to alter the string so it becomes "The >>cat<< sat on the dog"
At the same time, if there's also a definition for "dog" then my string now becomes "The >>Cat<< sat on the >>Dog<<"
The idea being that when I manipulate the data in my ASP I can replace() the >><< with specific HTML code. (I'm trying to recreate the "in text" advertising thing that lots of people seem to be using - but not doing adverts, just information for our users - Someone hovers over a highlighted word, and with a little bit of Ajax, I can pull the definition out...
I'm not sure (but I'm suspecting) that it would make more sense to do this as I'm storing the string in a table, rather than as I'm pulling it out ready for use (don't want to be slowing my end users down )
Can anyone help? I currently have this query that imports distinct data into a prices table with a couple of contraints. This table became to large so I have now split this down into yearly tables (dbo.price --> dbo.price2001, dbo.price2002 etc). I get the data each day in another table that may contain data for different years so I need to be able to look at this data and insert into the right yearly tables.
E.g. present query:
Insert Prices select distinct M.ids, C.zdateT, 1.0,0 from mapid as M,datacorrect as C where M.asset = 'money' and C.zDate not in (select zdate from price where sid >=15)
So I need to run this query for each yearly table with a date listed in the datacorrect table (insert prices(yr) yr = 2002,2003,2004 etc)
I am writing a query to return some production data. Basically i need to insert either 1 or 2 rows into a Table variable based on a decision as to does the production part make 1 or 2 items ( The Raw data does not allow for this it comes from a look up in my database)
I can retrieve all the source data i need easily but when i come to insert it into the table variable i need to insert 1 record if its a single part or 2 records if its a twin part. I know could use a cursor but im sure there has to be an easier way !
Below is the code i have at the moment
declare @startdate as datetime declare @enddate as datetime declare @Line as Integer DECLARE @count INT
set @startdate = '2015-01-01' set @enddate = '2015-01-31'
SQL Server 7.0 (SP1) Error: ------ OLE DB provider 'SQLOLEDB' supplied inconsistent metadata. An extra column was supplied during execution that was not found at compile time.
A column was deleted from the a table on the linked server and now this message appears when using the linked server definition to access the table. Deleting/Recreating the Linked Server has no effect. I found an earlier note on this...but it just kind of ended with no resolution. Anyone have any thoughts on this now.
Hello!I have a problem that I really could use some help to solve. I have a tablewhich looks like this:id1, id2, id3, rate, ratenrExamples of a select * from this table would be:1047336399 21000 1 617 11047336399 21000 1 624B 11047336399 21000 1 621D 11047336399 21000 2 624B 11047336399 21000 2 612A 11047336399 21000 2 621D 11047336399 21000 3 617 11047336399 21000 3 624B 11047336399 21000 3 621D 1I would like to transform this table into something like this:1047336399 21000 1 617 1 624B 1 621D 11047336399 21000 2 624B 1 612A 1 621D 11047336399 21000 3 617 1 624B 1 621D 1the three first columns should be the primary key.Any help is appreciatedGunnar
Hi, How to alter a table with default value? I am using the below statement, But, it is not working..Any pointers? Thx.. ------------------------------- alter table action_item ALTER COLUMN STATUS default 0
Hi! I've added a column to a published table in the Publisher using SP_REPLADDCOLUMN. After doing this the replication triggers for that table(i.e. del_%,upd_%,ins_%) are doubled both at the Publisher and at the Subscriber. If i update anyone of the column in the table at the Subscriber; i get the following error:
Server: Msg 208, Level 16, State 1, Procedure upd_3E6DE124B82D42A5AEB169557C0D757C, Line 60 Invalid object name 'ctsv_3E6DE124B82D42A5AEB169557C0D757C'.
Any ideas, what has gone wrong????
I have the following SQL setup. Publisher: Enterprise Edition, SP3. Subscriber: Standard-Edition, SP3. The error is at the Subscriber.
Hisuppose that I have a set named 'my_set' wich contain'aaa','bbb','ccc'and I want to alter with an sql command the structure to get 'ddd' inaddition. How can I do that?
I'm trying to do a simple alteration to the table design of one of ourSQL 2k tables, simply changing an identity row so that its not 'notfor replication', and its taking absolutely ages to do so, and stopsthe sql server from working.Whilst it's attempting the update, no one can access the database, thesqlservr.exe memory usage shoots up and enterprise manager reports anot responding status. Eventually after about 10 minutes, it bombs outreporting,Unable to modify tableCould not allocate space for object 'Tmp_TableName' in database'DBNAME' because the 'PRIMARY' filegroup is full.The table i'm attempting to change has only about 4000 records sothere's not a huge amount of data.Any ideas what's causing this and how i can get around it?A similar thing happens when i attempt to change the length of avarchar too.Thanks in advance for any suggestionsDan Williams.
Altering a table which is having more than 100 million rows. Would like to know the best possible way to add a new column to this table without impacting the performance much.
need help please on update only if . i need to update only the field "val_holiday " (in table B from table A) and olso to check on table B the "ID" + "new_date" only if exist and update the field "val_holiday " (in table B)
and at the end of update change the field "field_check_update_if _ok" from 0 to 1 only the row that update in table B
Code Snippet select from table A update table B WHERE ..........................HOW ?
----------------------------------------------------------------- table A ID fname new_date val_holiday field_check_update_if _ok ---------------------------------------------------------------------------------------------------
----------------------------------------------------------------- table B ID fname new_date val_holiday ----------------------------------------------------
I have a db1.tPersonJobHist where I have two columns
[WorkflowCoordinatorFlag] [dbo].[shrsFlag] NOT NULL, [HRBusinessPartnerCode] [dbo].[shrsCode] NULL,
Our developer mistakenly made HRbusinesspartnercode field as nullable. He now wants to change it to Not Nullable.
So there were 4 rows where the values were not null(not sure how). We edited those rows and changed those values to NUll. Now we have no Nulls in that column.
So we brought up the table designer and made the change but got the following error
'tPersonJobHist' table - Unable to modify table.
Cannot insert the value NULL into column 'HRBusinessPartnerCode', table 'BD6578.dbo.Tmp_tPersonJobHist'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Question is why wouldn't it let me alter the table design. and what is tmp_tpersonjobhist.?
Hello.I have one serious problem with COUNT in TSQL.I use MS SQL Server 2000.I would like to count rows depending on data in it.What I meat is:I have fields like:region | month | year | some_count |-------|-------|------|-------------|LA | 1 | 2003| 4 |LA | 2 | 2003| 2 |LA | 3 | 2003| 1 |LA | 4 | 2003| 6 |VV | 1 | 2003| 3 |VV | 2 | 2003| 7 |VV | 4 | 2003| 20 |VV | 6 | 2003| 3 |BB | 2 | 2002| 1 |etc...And what I would like to get from it is:region | jan2003 | feb2003 | mar2003 | apr2003 | may2003 | june2003 | etc...-------|----------|---------|-----------|----------|----------|-----------|--LA | 4 | 2 | 1 | 6 |0 | 0 | 0VV | 3 | 7 | 0 | 20 | 0| 3 | 0BB | 0 | 1 | 0 | 0 |0 | 0 | 0etc...Is it possible to do it in views? if yes then how?I could use temp tables for each month but it is not solution when I have 3yearsand more later then it will be round 40 temporary tables.Thanks for any response.Regards,Casper
Hello all,I have two tables - Projects and ProjectStructTable Projects contains master records of the projects, ProjectStructallows to define a project herarchie and contains the fieldsPrjStructId, ProjectId, PrjStructName, ..., ParentIdPrjStructParent contains a reference to the parent or to itselves ifrecord is top-level-record for a project.I try to create a trigger on table Projects (INSERT) whichautomatically creates the top-level-entry in ProjectStruct but Ididn't succed.Tried to use (several variations similar to)INSERT INTO ProjectStruct (ProjectId, PrjStructName, ParentId)SELECT prjProjectId, 'top-level',IDENT_CURRENT('ProjectStruct'))FROM INSERTEDbut this inserts a reference to the last inserted record. Why thishappens is pretty clear to me, but I found no way to get the referenceto the identity column of the record currently inserted.Is there a way to do this?
In a package we have statements pointing to 2(or more) different databases on the server. When moving between environments, is there an easy way to change statements like:
UPDATE t1 SET t1.name = t2.name, t1.age = t2.age FROM DB1..Person t1 INNER JOIN DB2..PersonToo t2 ON t1.PersonID = t2.PersonID
I can think only of building the statements replacing the database names with variables, but that's not an easy way. I do not know how to use package variables in this situation. 1 way of doing this may be by using a Lookup or Conditional Split and use the resulting dataflow in a SP or such to update, using parameters... It all sounds very messy, and I still don't know how Any ideas?
I know it sounds very simple with the subject line but for me it's not.
BACK GROUND
My Client is using Crystal Reports 8.5 and SQL Server. a View is bound to this report and they use Select Expert of CR 8.5 IDE to generate Reports manually.
now there are 3 tables in Question Billing_Revenue, Billing_Revenue_History and Billing_Revenue_Revision and the Fields in Question are Interstate_Revenue and International_Revenue.
Every end of the Quarter the Billing_Revenue data is moved to Billing_Revenue_History. Billing_Revenue_Revision has the revised data if the "Filer" has submited the changed/Revised Data again to the Company.
Bill Run = is the Process they call when they generate the Reports for a perticular Billing Period. they do it every month. But sometimes they need to generate Invoices for the OLD Periods.
if we put the problem in simple words,
they want to get the Revenue Columns from Billing_Revenu if they run the "Bill Run" in the Current Quarter (Billing Cycle), else if the Billing Cycle is older than current Quarter (cycle), then they want to first check in the Revision Table and if that Filer has submitted the revised data in that Cycle then get it from there else look into History Table and if the filer is found there for that cycle than get the data from there
here's the requirement given to me by my system Analyst.
User enters list of filer ID=USER_FILER_ID and cycle ID=USER_CYCLE_ID
Get CURRENT_CYCLE= Max CYCLE_ID from Billing_Cycle Get USER_PERIOD_ID = Period ID from Billing_Cycle using USER_CYCLE_ID Get PERIOD_FIRST_CYCLE= Min Cycle ID for USER_PERIOD_ID from Billing_Cycle
For each USER_FILER_ID If USER_CYCLE_ID=CURRENT_CYCLE Pull revenue from BILLING_REVENUE /*Process stops here*/ ELSE { If USER_PERIOD_ID exists in BILLING_REVENUE_REVISIONS BRR for USER_FILER_ID
Get the max (cycle_id) from BRR to pull revenue columns with most recent filing of USER_PERIOD_ID from BRR /*one filer may have more than one revision for the same period id*/
ELSE
IF PERIOD_FIRST_CYCLE exists in BILLING_REVENUE_HISTORY for USER_FILER_ID Pull revenue information }
here's the View being used which is the First Case which is happening currently
WHERE (dbo.BILLING_DETAILS.CYCLE_ID = dbo.BILLING_CYCLE.CYCLE_ID) AND (BILLING_DETAILS.LINE_ITEM_TYPE IN ('SLDADJ', 'SLDC',
'RHCDC', 'RHCDADJ'))))
Current Situation. 1. They use CR 8.5 IDE to enter the parameters and this version of CR doesn't allow using Stored PRocedures. 2. I'm not very good with the Database side and Complex Queries always leave me in the middle of nowhere. But i really really need to do this 3. please give me some clue.. some idea how to resolve this.
here's the primary database diagram http://deepak.palkar.googlepages.com/Billing2.jpg
Hi, Everyone. i am wondering if there is some way to hide a table row depending on the page number. I have tried to find a way to access the global page number to use it in an expression for the visibility property on the table row, but i havent found anything useful.
I have a matrix report where I drill down from a business' divisional level to an office level. A subreport is generated by clicking on the relevant data field. However, when at divisional level, clicking on the data field simply generates the data from the first office row. I want it so that when the column is collapsed to divisional level, it shows the data for the entire division.
So... is there any way I can write an expression that creates different subreports based on whether a matrix column is expanded or collapsed?
I have a table called acc1152 with the field accno. depending on what the value of this field is, i need to replace it with a new value. These are the values i need to update
old value new value 7007 4007 7008 4008 4008 7 7009 4009 7011 4011 4011 ' ' 7010 4010 4010 1 7016 4016 4016 1 4506 4006 4512 4012
how do I write one query that will accomplish this?
I am loading files with same format from two different locations in to one database. First, my SSIS connects to location 1.
Lets Say Location1 : C:LoadFilesImport Location2 : D:LoadFilesImport
Location one has three different set files starting with
First Set: AP_1, AP_2,AP_3, Second Set: VD_1, VD_2, VD_3, Third Set: BK_1,BK_2,BK_3,
This SSIS set to run every 3 hours, if it finds files of any set load them. While loading it has to insert into a derivedcolumn called CustID. If the file name Starts with AP_ , custiD values should be as 101
AP_1 goes to Table1 AP_2 goes to Table2 AP_3 goes to Table3
If the file name Starts with VD_ , custiD values should be as 201 If the file name Starts with BK_ , custiD values should be as 301
after processing all these files in first location, the SSIS looks for files in second location and does the same?
--How to achieve CustID depending upon file name ?
Suppose we have the following table in our database;
CREATE TABLE [dbo].[PERMISSION]( [ID] [int] IDENTITY(1,1) NOT NULL, [USERID] [int] NOT NULL, [STARTTIME] [smalldatetime] NOT NULL, [ENDTIME] [smalldatetime] NOT NULL, [REASON] [nvarchar](250) NULL, [PERMISSIONTYPEID] [int] NOT NULL,
[code]....
This code works pretty well. But I don't want to do this with "select" since there is OUTPUT clause in T-SQL. So the CommandText property will be changed into this;
well, not only this statement gives an error while executing; but also, no such usage defined in the
documentation of OUTPUT Clause. Actually the documentation tell us to use Temporary Tables for that. So I have to change CommandText into this; command.CommandText = @"DECLARE @MyTableVar table(ID int, CREATETIME smalldatetime); insert PERMISSION output INSERTED.ID, INSERTED.CREATETIME into @MyTableVar
code]....
No temporary tables required; thus, no "type spesific" things required. This way is easier to create dynamic queries though. Only,the RETURNING INTO clause.So, I was wondering; why MS-SQL (T-SQL) forces users to "declare a temporary table (type specific)" and "execute select on the temporary table in order to assign values to output parameters" while using "the OUTPUT Clause". Comparing to the Oracle's sample I'm just using "the RETURNING INTO Clause in order to assign values to output parameters" and that's it; very easy and dynamic. To Summarize, Oracle's RETURNING INTO Clause is better than MS-SQL's OUTPUT Clause, but in fact they're doing the same job.
Disable logins on access expiry date(Not windows password expiry). we grant access to users on databases only for 60 days. So access is only valid for 60 days. Then the user should again request access to the database going thru security clearance. Thn the DBA's enable the login. Maintaining all these logins of users manually is causing more confusion. As we know, we dont have any inbuilt functionality to automatically disable logins in SQL Server.
I have a table where the logins and expirydate were recorded in a DB.
Using this table and SQL Server agent. Can i achieve this process automated ? CREATE TABLE [dbo].[LoginsExpiry]( [LoginName] [varchar](50) NULL, [ExpiryDate] [date] NULL, [Roles] [varchar](500) ) ON [PRIMARY] GO
Hi friends,please help me in selecting values from the tablethe record is as follows:Id HomePhone WorkPhone Mobile Email20 2323223 323232232 Join Bytes!i have to select values as follows.Id DeviceType DeviceInfo20 HomePhone, Mobile, Email 2323223, 323232232, Join Bytes!the one solution is:select'HomePhone, Mobile, Email' AS DeviceTypeHomePhone + ',' + WorkPhone + ',' + MobilePhone + ',' + Email ASDeviceInfofrom table where Id = 20but here the work phone number is not available so that informationhas to be truncated...Thanks in AdvanceArunkumar.D
