I dont know the exact position of '/',',','--' or any special character.I have to fetch first name of the country.
create table abc
(country nvarchar(200))
go
INSERT INTO abc VALUES ('Tennessee/Virginia,USA')
GO
INSERT INTO abc VALUES ('Kansas,USA')
[code]...
I'm trying to find the previous / next values of a field when it is having null values. I'm not able to use lag / lead as they don't ignore nulls.
declare @t table (v1 int , v int, v2 datetime)
insert into @t values(4,10, '2014-01-01 10:00:00')
insert into @t values(5, 20,'2014-01-01 10:05:00')
insert into @t values(6, null, '2014-01-01 10:10:00')
insert into @t values(7, null,'2014-01-01 10:15:00')
[code]....
The next values are having nulls. i'm trying to get next value of 'v' ignoring nulls.
How to fetch records which throws error?
For example
I'm inserting records from a table to another table, having multiple columns
SampleQuery :
insert into tableB (col1,col2....col10)
select col1,col2....col10 from tableA
* while executing this query im getting error. (like varchar cant convert to numberic)
* Here I have no clue in which column it is
* also dont know which row causes it
I have a folder with 1000 text(.txt) files in it.
I want to insert the filenames into a table in sql server.
Is it possible in SQL Server 2008..?
I have a small project to be done in which I need to fetch the pdf file from a my system and save it in database and also fetch the name of it and save it in the database.
View 9 Replies View RelatedI have 15 Dealers files .With the Files name as follows
1. ''ACTEST00001_20141112_0408_INV.TXT''
2. ''ACTEST00002_20141112_0408_INV.TXT''
I will get these files through FTP on Daily basis with changes on date alone.
I will have 4 files for each dealers.like INV,SERVICE,SALES,APPOINTMENT.
SO i need to fetch a particular dealer with particular date.
I need to create this scenario in SSIS package. How to create this and what are all the Tasks i need to use to implement this process.
I have a four tables called plandescription, plandetail and analysisdetail. The table plandescription has the columns DetailQuestionID which is the primary and identity column and a QuestionDescription column.
The table plandetail consists of the column PlanDetailID which the primary and identity column, DetailQuestionID which is the foreign key attribute of plandescription table and a planID column.
The third table analysisdetail consists of a analysisID which the primary and identity column, PlanDetailID which is the foreign key attribute of plandetail table and a scenario.
Below is the schema of the three tables
I have a two web form that will insert, update and delete data into these three tables in a two transaction. One web form will perform CRUD operations in plandescription and plandetail table. When the user inserts QuestionDescription and planid in this web form, I will insert the QuestionDescription Value in the plandescription table and will generate a DetailQuestionID value and this value is fed to the plandetail table with the planid. Here I will generate a PlanDetailID.
Once this transaction is done, I will show the second web form in which the user enters the scenario and this will be mapped with the plandescription using the PlanDetailID.
This schema cannot be changes as this is the client requirement. When I insert values I don’t have any problem. However when I update existing data, I need to delete existing PlanDetailID in the plandetail table and recreate PlanDetailID data for that DetailQuestionID and planID. This is because, the user will be adding or deleting a planID associated with the QuestionDescription.
Once I recreate PlanDetailID for that DetailQuestionID and planID, I need to update the old PlanDetailID with the new PlanDetailID in the third table analysisdetail for the associated analysisID.
I created a #Temp table called #DetailTable to insert the values analysisID, planid and old PlanDetailID and new PlanDetailID so that I can have them in update statement once I delete the data from plandetail table for that PlanDetailID.
Then I deleted the plandetailid from the plandetail table and recreate PlanDetailID for that DetailQuestionID. During my recreation I fetched the new PlanDetailID’s created into another temp table called #InsertedRows
After this I am running a while loop to update the temp table #DetailTable with the newly created PlanDetailID for the appropriate planID’s. The problem is here. When I have the same number of planID’s for example 2 planID’s 1,2 I will have only two old PlanDetailID and new PlanDetailID for that planID and analysisID.But When I add a new PlanID or remove a existing planID I am getting null value for that newly added or deleted planID. This is affecting my update statement of analysisdetail table as PlanDetailID cannot be null.
I tried to remove the Null value from the #DetailTable by running the update statement of analysis detail in a while loop however its not working.
DECLARE @categoryid INT = 8
DECLARE @DetailQuestionID INT = 1380
/*------- I need the query to run for the below three data.
Here i'm updating my planids that already exists in my database*/
DECLARE @planids VARCHAR(MAX) = '2,4,5'
[code].....
Hello,
I'm new to the SQL world and want to know if this is the site for a newbie or would you recommend another site more my speed. Thanks for the input.
Clint
US Navy - fueled, armed, and go for launch.
I have the country iso table does anyone have the data for tying contenents to the countries?
You can do anything at www.zombo.com
Hi,
I have created some statistics for my site based on the ip of the visitors. For that reason I have created two tables
1. One table with a column which hold the ip number of the visitor
Table: AccessLog
Column: IP
2. One table with the ip range and the countries
Table: Countries
Columns: ip_to, ip_from, country_name
I would like to create a query which will bring the country name of the visitor IP which IP will be like "ip_to <= ip <= ip_from"
Thanks
Denis
www.tabletennis.gr
Hi!How I can know the "Country version" of SQL Server using VB.NET(perhaps with the SQLDMO)?I mean: if the SQL Server is in "English version" or in "Italianversion" or ...Thank you so much!!Bye
View 1 Replies View RelatedI am having a users table which contains "Mobile" column as well. I want a query to set the country code value by default into the column name so that the column should be updated with the mobile number along with the default country code.
View 10 Replies View RelatedMy company needs to move a 30 Gig SQL Server across the country as soonas possble on July 1. Turns out moving the full db across the networktakes a few hours.I'd like to move a full copy of db a week ahead of time, and then justmove either a differential backup(s) or transaction log with with theweek's new data on July 1.Can anyone suggest the best strategy for doing this? Currently we'redoing a differential backup each hour on the db, and dumping the txnlog each night.Many thanks,Burt
View 1 Replies View RelatedQuick questiom, I need to Import a table with City and State Information
What are your recomendations? Should I create a Unique Key for them?
CityID, CityName, StateID?
StateID, StateName?
Or can I just store them in a table with one column?
CityName, State
Thanks for your help/
I have built a sample table, query, and results for this question. I am using SQL server.
declare @TableX Table
(
Date Date not null,
ID int not null,
Tick varchar(6) not null
)
INSERT @tableX
[Code] ....
Expected results:
DATE(No column name)count
2013-09-02LON1
2013-09-03LON1
2013-09-04LON1
2013-09-05LON1
2013-09-06LON1
2013-09-02USA2
2013-09-03USA2
2013-09-04USA2
2013-09-05USA2
2013-09-06USA2
I want to select unique country - date pairs. It is not even necessary to have the count of each one, just the list of unique country/dates.
My query here uses 'group by' to accomplish this task, but there may be a way to do this with a self join. I believe using a self join would make the query faster.
1) Is this possible to do with a self join?
This Question is pertaining to AND and OR operators.
If we want to retrieve data form a country combined with 3 to 4 cities how do we handle this?
Say for ex:- i want to retrieve all data from Customers table where country is Germany and cities are Berlin, Mannheim,Brandenburg and München.
Any help here would be greatly appreciated...
Unfortunately, data wasn't filtered prior to getting inserted into this table. Now I am stuck with cleaning it up. I have thought about writing a query to update all the values, but there are just too many variations, including spelling mistakes, so I've ruled that out as a possible solution.
I have a table which has a Country field but the values per record vary. For example US, U.S., USA, United States, UK, United Kingdom, Canada, Can, etc. I'm trying to find the percent of records per country.
Sample table data: mytable
Id Name Country
1 John US
2 James UK
3 Jane United States
4 Mary Canada
5 Jack U.S.
6 Tony United Kingdom
7 Jeff US
8 Tom Canada
9 Beth UK
10 Mark USA
I would like to show
US: 50% --> (includes any variation of US ncluding US, U.S., USA, United States)
UK: 30%
CAN: 20%
I've made several attempts myself with no luck. Thanks in advance.
I am searching for the Sql Server Script of all country State and City with the Following type..
For the Country only 2 Columns are there i.e. CountryId and CountryName(CountryId is primary Key)
For the State 3 Columns are there i.e. CountryId, StateId and StateName(StateId is Primary Key and CountryId is Foreign Key)
For the City 3 Columns are there i.e. StateId ,CityId and CityName(CityId is PrimaryKey and StateId is foreign Key).
I need this type of Script with Column name.
hi alli've got two tables called "webusers" (id, name, fk_country) and "countries" (id, name)
at the meantime, i've a search-page where i can fill a form to search users. in the dropdown to select the country i included an option which is called "all countries".
now the problem is: how can i make a stored procedure that makes a restriction to the fk_country depending on the submitted fk_country parameter?it should be something like
SELECT * FROM webusers(if @fk_country > 0, which is the value for "all countries"){ WHERE fk_country = @fk_country}
who has an idea how to solve this problem?
I am using SQL Server 2005 and trying to create a linked server on Oracle 10. I used the commands below:
EXEC sp_addlinkedserver
@server = 'test1',
@srvproduct = 'Oracle',
@provider = 'MSDAORA',
@datasrc = 'testsource'
exec sp_addlinkedsrvlogin
@rmtsrvname = 'test1',
@useself = 'false',
@rmtuser='sp',
@rmtpassword='sp'
When I execute
select * from test1...COUNTRY
I get the error. "The OLE DB provider "MSDAORA" for linked server "...." does not contain the table "COUNTRY". The table either does not exist or the current user does not have permissions on that table."
The 'sp' user I am connecting is the owner of the table. What could be the problem ?
Thanks a lot.
Is it possible to have fetch within a fetch? I am getting this error message "A cursor with the name 'crImgGrp' does not exist." So i separate the process into two stored procedures?
CREATE PROCEDURE TrigSendPreNewIMAlertP2
@REID int
AS
Declare @RRID int
Declare @ITID int
Declare @intIMEmail varchar(300)
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'IA' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID
Declare crImpGrp cursor for
select ITID from RequestRecords where RRID = @RRID
open crImpGrp
fetch next from crImgGrp
into
@ITID
while @@fetch_status = 0
EXEC TrigSendNewIMAlertP2 @ITID
FETCH NEXT FROM crImpGrp
into
@ITID
close crImpGrp
deallocate crImpGrp
while @@fetch_status = 0
FETCH NEXT FROM crReqRec
into
@RRID
close crReqRec
deallocate crReqRec
GO
Hello..
i develope a web projects of horoscope or astrology(http://demo.reallianzbussimart.com/allzodiac.aspx), there is an 12 Zodiaz sign and all the data call on this page through the Database , in this page i there is an one sql query ---- (Select Top 1 col from colour order by newid(),Select Top 1 num from number order by newid() ,Select Top 1 days from day order by newid() )
then all data call one by one change ..
when the refresh the page the value of all zodiac is change that is wrong ,,,
so what is the quary that one time in day the value off all zodiac is same next day the value has been change............................................
so please help me /...........
tell what is the process to not data change into the page refresh
Ashwnai
Let's say i have 5 unique RRID's, column APID and ITID
RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 700
5 15 700
If I run the stored procedure below, I get the results above however, I want my result to be
RRID - APID - ITID
1 13 700
2 13 700
3 13 700
4 14 701
5 15 702
I want my cursor to loop at the same APID then assign one ITID then move to the next APID and so on...
Any help is highly appreciated....
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE InsNewEmployeeImpTaskP2
@REID int,
@LOID int,
@RetValintoutput
AS
Declare @RRID int
Declare @APID int
Declare @intREID varchar(20)
Declare @intIMID varchar(20)
Declare crReqRec cursor for
select RRID from RequestRecords where REID = @REID and RRSTatus = 'AC' and APID is not null
open crReqRec
fetch next from crReqRec
into
@RRID
set @APID = (select APID from RequestRecords where REID = @REID and RRID = @RRID)
set @intIMID = (SELECT ImplementationGroup.IMID
FROM ImplementationGroup_Location INNER JOIN
ImplementationGroup ON ImplementationGroup_Location.IMID = ImplementationGroup.IMID INNER JOIN
Applications_ImplementationGroup ON ImplementationGroup.IMID = Applications_ImplementationGroup.IMID where APID = @APID and ImplementationGroup_Location.LOID = @LOID )
insert into ImplementationTasks
(
IMID,
ITStatus,
ITStatusDate
)
VALUES
(
@intIMID,
'2',
GetDate()
)
SET @RetVal = @@Identity
while @@fetch_status = 0
Begin
Update RequestRecords
set ITID = @RETVal, RRStatus = 'IA'
where REID = @REID and RRID = @RRID
FETCH NEXT FROM crReqRec
into
@RRID
end
close crReqRec
deallocate crReqRec
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
i am having an endless loop after the first record.... anything i missed?
ALTER PROCEDURE IMPGrpEscalationX
AS
Declare @IMID int
Declare @IMID2 int
Declare @FS1 int
Declare @FS2 int
Declare crFirst cursor local for
select IMID from ImplementationGroup where IMSTatus = 'Y'
open crFirst
fetch next from crFirst
into
@IMID
begin
Declare crSecond cursor local for
select IMID from Employees_ImplementationGroup where Employees_ImplementationGroup.IMID = @IMID
open crSecond
fetch next from crSecond
into
@IMID2
set @FS2 = @@fetch_status
if not exists(select IMID from Employees_ImplementationGroup where IMID = @IMID2 and IMType = 'T')
Begin
DECLARE @MsgText varchar(700)
DECLARE @IMGRPNAME varchar(50)
Set @IMGRPNAME = (select IMGrpname from ImplementationGroup where IMID = @IMID2)
--SET @MsgText = 'This implementation group has been without a last resort implementer for the past 24 hours. Please click here to assign a last resort implementer: http://xxxx.com/admin/implementationgrp_emps.aspx?imid=' + @IMID2 + '&imgrpname=' + @IMGrpName
EXEC master.dbo.xp_sendmail
@recipients = cccc@ccc.com',
@Message = @IMGRPNAME,
@Subject = 'needs attention'
end
while @FS2 = 0
fetch next from crSecond
into
@IMID2
end
set @FS1 = @@fetch_status
while @FS1 = 0
FETCH NEXT FROM crFirst
into
@IMID
close crFirst
deallocate crFirst
close crSecond
deallocate crSecond
I need to see the records from two tables where the mobilenumbers are not same.
I have two tables named as messages and subscribers with Id, MobileNumber fields.
Now here I need to see the records where the mobile number exist in one table but not in other table.
Please give me the sql query for this.
with regards
Shaji
I want to fetch all bills (except cancelled bills) for a given month and fetch the previous bill date.
This is to find out the frequency of a vehicle visit to a workshop.
Sample Data
Division,ChasNo,BillDt,Status
D1,C1,01/01/2015,Null
D1,C1,15/01/2015,Null
D1,C1,23/02/2015,Null
D2,C1,26/02/2015,Null
D2,C2,02/01/2015,Null
D2,C2,16/01/2015,Null
D2,C2,21/02/2015,Null
D1,C3,24/01/2015,Cancelled
Result Expected
D1,C1,23/01/2015,15/01/2015
D2,C1,26/02/2015,Null
D2,C2,21/02/2015,16/01/2015
What I tried is
Declare @Mh smallint,@Yr smallint
Select @Mh=2,@Yr=2015
Select Division,ChasNo,BillDt Into #CMBills from Service_Bills Where Month(BillDt)=@Mh and Year(BillDt)=@Yr and Status Is Null
Select SB.Division,SB.ChasNo,CMB.BillDt as CurBillDt,Max(SB.BillDt) as PreBillDt,Datediff(day,Max(SB.BillDt),Max(CMB.BillDt)) as DiffDays from #CMBills CMB
Left Join Service_Bills SB On SB.Division=CMB.Division and SB.ChasNo=CMB.ChasNo and SB.Status Is Null and SB.BillDt<CMB.BillDt
Group By SB.Division,SB.ChasNo,CMB.BillDt
But I'm not getting details for all the bills I fetched.
How can I fetch only Odd or only even records from a table.The odd or even is depends up on Id (Its a primary key/ Integer).
Is there any query
Please help me!
Shaji
Hi Folks,
How do I right queries to fetch first 10 records, then next 10 and so on..
My table structure is as follows:
Columns
--------
MPAD_ID (IDENTITY INCREMENT YES )
MPAD_EmpCode (Employee Code)
MPAD_Date (Date)
MPAD_Status (Attendance Status i.e. P, A, etc)
Sample Records for the same is given below
MPAD_ID MPAD_EmpCode MPAD_Date MPAD_Status
------- ------------ ---------- -----------
1 1001 11/01/2007 P
2 1001 11/02/2007 P
3 1001 11/03/2007 A
... .. .. ..
14 1002 11/01/2007 P
15 1002 11/02/2007 A
16 1002 11/03/2007 P
Please help me out in writing out this query.
Thanks
Jabez.
I have a stored procedure that inserts one row into a table. From aSELECT statement I would like to call the SP on each row in theresults. Is setting up a cursor and using fetch statements the bestway (or even the only way) to do this?
View 1 Replies View RelatedHello everyone, hope someone can help me with this.I have a SQL stored procedure that inserts a record into a table,creates a cursor to fetch the last record that was added to get theunique key that was created and then writes that and other info to aseparate table. This procedure was working fine at our ISP under NT 4and SQL 7.We recently moved to another ISP on servers that are windows 2000 andSQL 2000. Now this code is going kerplooey. It actually worked finein the staging area but now that it was moved into production, it isnot working. also wanted to mention that the production database wasrestored from a backup. below is the code.the first time this is run it is ok, for example the transactionnumber is 1. the next time it is run, a new record is created in thesweep results with a transaction number of 2. but for some reason,when i declare the cursor to fetch the last record, it goes back tothe transaction number 1 record. so the counts from transaction 1don't match counts from transaction 2 and the next step has an errorcondition and doesn't work.thanks in advance for any help you can provideAnn Williams-- update the sweep results tableINSERT tbl_sweepresults (del_wrkfeedback_count,updnull_feedback_count, swp_feedback_count,swp_count_error, del_error, updnull_error, swp_error, init_error,sweep_date)VALUES (@var_del_wrkfeedback_count, @var_updnull_feedback_count,@var_swp_feedback_count,@var_swp_count_error, @var_del_error, @var_updnull_error,@var_swp_error, @var_init_error, GETDATE())-- create cursorDECLARE tbl_sweepresults_cursor SCROLL CURSOR FORSELECT transaction_no, sweep_date, init_error, updnull_feedback_countFROM tbl_sweepresultsOPEN tbl_sweepresults_cursor-- get transaction number, sweep date, init error, feedback sweepcount and pass to tbl_currentTrans for OPAL comparisonFETCH LAST FROM tbl_sweepresults_cursor INTO @var_transaction_no,@var_sweep_date, @var_init_error, @var_swp_countzeroDELETE tbl_currentTransINSERT tbl_currentTrans (current_transaction_no, current_sweep_date,current_init_error, current_swp_countzero)VALUES (@var_transaction_no, @var_sweep_date, @var_init_error,@var_swp_countzero)-- close the cursorCLOSE tbl_sweepresults_cursorDEALLOCATE tbl_sweepresults_cursor
View 1 Replies View RelatedHi,
I'm using a remote SQL Server Express database with a C# app, and to do so as most of you already know, there's no DataSource available, it's all around SQL. This poses a problem as when I want to browse (1 by 1 in my app, with search utility) the contents of a given table, I have to perform a Select command. Well the problem is when I do this it loads all records into a DataSet (or DataTable), which is fine by me, but one of my tables is expected to reach 400 or 500 records in a few months time. This will mean a lot of loading from the db once the app is launched. Is there a way to make this connection more efective? Thanks
hi,
i want to be able to get the columns from my table except for its pk. the long method is to selecting all columns by listing them one by one right? but what if i got 100 columns..
so.. i wanted to use the information_schema..
i have:
use dbase;
select column_name
from information_schema.columns
where table_name = 'table1'
and column_name != 'pkid'
so there i'm listing the columns, but how am i suppose to select those columns from my real table?
thanks in advance!
I'm trying this code but nothing is being displayedSqlConnection conn = new SqlConnection(ConfigurationManager.AppSettings["STRING_CON"]);
SqlDataAdapter da = new SqlDataAdapter("my_sproc", conn);DataTable dt = new DataTable();
DataRow dr;
//Adding the columns to the datatabledt.Columns.Add("CategoryIdIn");
dt.Columns.Add("CategoryNameVc");foreach (DataGridItem item in gd_freq.Items)
{
dr = dt.NewRow();
dr[0] = item.Cells[0].Text;
dr[1] = item.Cells[1].Text;
dt.Rows.Add(dr);
}//ForEach
da.Fill(dt);
gd_freq.DataSource = dt;
gd_freq.DataBind();