I have a xml file downloaded from webAPI which i want to import into my database.There are several sub childnodes and need to combine data between nodes.
<?xml version="1.0" encoding="UTF-8"?>
<classes type="array">
<class>
<class_id>3S</class_id>
<class_pk type="integer">3900</class_pk>
[code]....
We currently have an SQL db running on a web server.One of these fields is a large(ish) amount of text data – up to 400characters – and has been cast variously as varchar, nchar and texttype to overcome a problem. The problem appears to be in retrievingthe data via ASP. I understand that ASP can handle string data of thissize so I am okay there.When the records are retrieved from the db, the data string length =0.I know the data is there because I have written a Delphi data managerwhich interrogates the db and shows all records and their contents.So if ASP can handle strings this size and the data is there, why do Iget a data length of zero bytes returned when I interrogate the recordset?Whichever way I cast this field I get the same result.I know the code is sound as it works locally through a MS SQL serveron my PS.Anyone have this problem or know what's causing it? I have logged asupport call with my hosting company, but they haven't replied as yetand I am stuck on an urgent project.Any suggestions?CheersGrant
View 1 Replies View RelatedI have four tables: Customer (CustomerId INT, CountyId INT), County (CountyId INT), Search(SearchId INT), and SearchCriteria (SearchCriteriaId INT, SearchId INT, CountyId INT, [others not related to this]).
I want to search Customer based off of the Search record, which could have multiple SearchCriteria records. However, if there aren't any SearchCriteria records with CountyId populated for a given Search, I want it to assume to get all Customer records, regardless of CountyId.
Right now, I'm doing it this way.
DECLARE @SearchId INT = 100
SELECT * FROM Customer WHERE
CountyId IN
(
SELECT CASE WHEN EXISTS(SELECT CountyId FROM SearchCriteria WHERE SearchId = @SearchId)
THEN SearchCriteria.CountyId
[Code] .....
This works; it just seems cludgy. Is there a more elegant way to do this?
Hai Friends,
Actually i want to import excel sheet in to sql server tables. I am writing the following query SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;Database=C:Book1.xls','SELECT * FROM [Sheet1$]') and able to retireve the data but the problem is the data present in the excel sheet is
Username
password
hello
12345
12345
hello
hai-123
haihai
haihello
hello-123
Once i run this query i am getting it as
Username Password hello NULLNULL hellohai-123 haihaihaihello hello-123can anybody please help me out in this case and how to solve this problem and if you have any piece of code please pass it on to me and i am in very urgent need waiting for your replies thanks in advance.
We have a daily process, which copies millions of rows of data from one DB to another over Linked Server. Just checking on the best practise, are there more efficient ways than the Linked server to copy millions of rows of data from one DB to another? I checked bulk insert but that transfers the data from the file to DB not DB to DB.Â
View 6 Replies View RelatedHi!
View 36 Replies View RelatedI have the following stored procedure that is returning nothing can anyone please help?
SELECT job_id, line_num, cust_id, cust_po_id, product_desc, form_num, revision, flat_size, new_art_reprint, order_qty, po_recieved_date, ord_ent_date, customer_due_date, scheduled_ship_date, act_ship_date, act_ship_qty, ship_from, ship_to, price_per_m, misc_charges, commentsFROM tblOrderWHERE (cust_id = @Cust_Id) AND (po_recieved_date BETWEEN @Start AND @End)
When I input parameters I make sure my start date is before the first po_recieved_date and the end date is after it yet it is returning nothing. I also made sure that I am putting the correct @Cust_Id
Does anyone out there have a good example of a VB Application for importing data from an Access DB into a SQL Server DB using DTS. Any help with this would be greatly appreciated.
Thanks,
Dana
LD
Hey guys,I am trying to import data from an Excel spreadsheet into my SQLdatabase. I am running SQL 2005.I following Microsoft's instructions for creating a linked server, andit appeared to work. However when I run this query:SELECT * INTO test FROM OPENQUERY(EFORMS,'SELECT * FROM [Form Tracker Baseline$]')I get the following error:The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server"EFORMS" reported an error. The provider did not give any informationabout the error.Msg 7303, Level 16, State 1, Line 1Cannot initialize the data source object of OLE DB provider"Microsoft.Jet.OLEDB.4.0" for linked server "EFORMS".Test is the table that will receive the imported data. Eforms is thename of my linked server, and Form Tracker Baseline is the worksheetname.The error sounds like it can not locate my spreadsheet. Any ideas whyI am getting this message? Is there an easier way to do this import?Thanks,
View 4 Replies View RelatedHow do I get my data to show starting at the first row instead of skipping down?
Refer to the attachment.
Code:
CREATE PROCEDURE [dbo].[uspReportData]
-- Add the parameters for the stored procedure here
@Metric1 as varchar(50) = NULL, @Metric2 as varchar(50) = NULL, @Metric3 as varchar(50) = NULL, @Metric4 as varchar(50) = NULL,
@Metric5 as varchar(50) = NULL, @Metric6 as varchar(50) = NULL, @Metric7 as varchar(50) = NULL, @Metric8 as varchar(50) = NULL,
[code].....
I have null fields in one of the column and want to return "N/A" when the column is null. How can I do that ?
View 2 Replies View RelatedThanks to everyone in advance
The code below returns the members who have accounts that are inactive, and works correctly.
It returns the memberid, but returns null values for the barcode values.
I am explicitly looking for unmatched rows, i.e. the LEFT OUTER JOIN will return joined member-address
rows which do not have a matching account, according to the ON conditions, and these are the ones I want
because of that WHERE condition.
Therefore account.barcode will always be NULL too.
Is there to perform the same query and return account details?
I have tried running a query to return just the memberid values and then perform a second query to match the account
details, but this is very ineffecient and slow.
select member.memberid, account.barcode
FROM member
INNER
JOIN address
ON address.MemberID = member.Memberid
LEFT OUTER
JOIN account
ON account.Memberid = member.MemberID
AND account.enddate >= current_date
AND account.closed = 0
WHERE account.Memberid is null AND member.isDeleted = 0
Special thanks to r937 for help.
James
I've got the following query in SQL 2000:select a.SSN, a.MonthName, a.IMClinicDay,b.IMClinicDay as SecondDayfrom tblResidentRotations ainner join view7 bon a.SSN = b.SSNwhere a.AcademicYear = '2004-2005' and a.SSN = '999999999' anddatename(month, a.IMClinicDateFirst) = b.MonthNameThis query returns a resultset like this:<SSN> <Month> <a.IMClinicDay> <SecondDay>999999999 July Friday PM Tuesday PM999999999 September Tuesday PM Friday PM999999999 October None Friday PM999999999 November Friday PM Tuesday PM999999999 January Tuesday PM Friday PM999999999 April Friday PM Monday PM....and so onFor some of the months, there is a null value for "b.IMClinicDay". Forexample, it's null for August, December, and February. I want myresultset to look like this:<SSN> <Month> <a.IMClinicDay> <SecondDay>999999999 July Friday PM Tuesday PM999999999 August Tuesday PM null999999999 September Tuesday PM Friday PM999999999 October None Friday PM999999999 November Friday PM Tuesday PM999999999 December Tuesday PM null999999999 January Tuesday PM Friday PM999999999 February Friday PM null999999999 April Friday PM Monday PM....and so onHow can I return a null for these days? Thanks for any help oradvice.
View 4 Replies View RelatedI need to import csv data into a SQL Server 2005 database using SQL Server Management Studio Express. I can't find any menu options for accomplishing this. Is this a limitation of the Express edition, or am I missing something when I try to find this feature?Thanks for any help provided.
View 7 Replies View RelatedHi,
I am working on a project which involves migration of data from a Rdb database (which is remote )
to a SQL server.
I did a telnet to that machine and took the details of the schema and found there are 12 tables to be migrated,
and some of the tables have as many as 3 million records in it.
I wanted to try the ODBC to migrate. But I didnt find the Rdb option in the list of databases it has.
or can I write it to a flat file and then improt from there.
Please give me some idea on this
Thank You
geetha
Hello all:
I have gone through most of the question posed by people, about importing data from EXCEL sheet to some table using SQL server database. I have a slight variation of this problem.
My excel file contains some information apart from the normal data.
Lets say some 5-6 lines always gives me some info about the data, like its purpose, client info, date etc...
After this INFO my actually data start, which I want to load into table.
I have found some wizard for the same, "EMS SQL MANAGER 2005", which supports most of the file formats, and load data into the database.
But we are planning to not use this tool, instead everything should be done using TSQL.
If somebody can please gives me some idea how this problem can be tackle, it would be a great help. We won't be using any Third party tools, like scripting etc...
We want to stick with just TSQL for this problem.
Thanks a bunch,
sabegh
Hi everybody,
View 13 Replies View Related
I hope I am giving the right information here to get help with this.
I am trying to get information from two tables that are in an access database over to sql 2005. I did this via dts and saved the import options as a file, but when I run this as a job I get the following error: Date 2/27/2008 11:14:08 AM
Log Job History (CombinationTable1)
Step ID 0
Server D86J0PD1
Job Name CombinationTable1
Step Name (Job outcome)
Duration 00:00:00
Sql Severity 0
Sql Message ID 0
Operator Emailed
Operator Net sent
Operator Paged
Retries Attempted 0
Message
The job failed. The Job was invoked by User BIZCHAIRDarleneMurphy. The last step to run was step 1 (CombinationTable1).
I have also tried to run this in the following way:
USE Combination_Table
CREATE TABLE GG Products
AS
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'W:MASTER DOCUMENTSDatabasesChannel_Advisor_Feed.mdb';'admin';'', GG Products)
go
Here I get errors regarding nulls and if I pointed to the correct database, which I have.
Any help with this problem and I hope I have given good information in order to get help.
Thank you
Dee
I am/have been having an issue with Data Access from a Sql Server database. I have a class that contains a method called "GetDataReader" which takes in a string for the query. Occasionally, the DataReader returned has completely different columns thus resulting in an error when trying to read the data. Below is a small section of code that actually creates the datareader, opens the connection and executes the reader.SqlCommand cmdReader = new SqlCommand();
SqlDataReader drReturn = null;
try {
// Set the connection for the command object
cmdReader.Connection = new SqlConnection(this._csbGlobal.ConnectionString);
cmdReader.Connection.Open();
// Set the command type
cmdReader.CommandType = Type;
// Set the command text
cmdReader.CommandText = Sql;
// Set the command timeout
cmdReader.CommandTimeout = _iTimeout;
if (Parameters != null) {
// Set the parameters
for (int i = 0; i < Parameters.Count; i++) {
cmdReader.Parameters.AddWithValue("@" + Parameters.GetKey(i), Parameters[i]);
}
}
// Get the return value
drReturn = cmdReader.ExecuteReader(CommandBehavior.CloseConnection);
// Dispose of the command object
cmdReader.Dispose();
cmdReader = null;
} catch (SqlException e) {
this.HandleError(e);
} catch (Exception e) {
this.HandleError(e);
}
// Return the reader
return drReturn;The data access class is created on each page and only used for that page and any usercontrols on that page. I have checked all the datareaders and they are all being closed. I have been fighting with this issue for about a month and a half now. It only seems to happen when there are a lot of people on the site.If anyone has experienced this or konws how to fix it please let me know. Thanks-Jason
Hi,I'm creating a stored procedure that pulls information from 4 tables based on 1 parameter. This should be very straightforward, but for some reason it doesn't work.Given below are the relevant tables: SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Project](
[ProjID] [varchar](300) NOT NULL,
[ProjType] [varchar](20) NULL,
[ProjectTitle] [varchar](max) NULL,
[ProjectDetails] [varchar](max) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](max) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK__tbl_Project__0B91BA14] PRIMARY KEY CLUSTERED
(
[ProjID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Project] WITH CHECK ADD CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Project] CHECK CONSTRAINT [FK_tbl_Project_tbl_ProjectStatus]
-----------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_Report](
[ReportName] [varchar](50) NOT NULL,
[ProjID] [varchar](300) NULL,
[DeptCode] [varchar](50) NULL,
[ProjType] [varchar](50) NULL,
[ProjectTitle] [varchar](500) NULL,
[ProjectDetails] [varchar](3000) NULL,
[ProjectManagerID] [int] NULL,
[RequestedBy] [varchar](50) NULL,
[DateRequested] [datetime] NULL,
[DueDate] [datetime] NULL,
[ProjectStatusID] [int] NULL,
CONSTRAINT [PK_tbl_Report] PRIMARY KEY CLUSTERED
(
[ReportName] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectManager] FOREIGN KEY([ProjectManagerID])
REFERENCES [dbo].[tbl_ProjectManager] ([ProjectManagerID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectManager]
GO
ALTER TABLE [dbo].[tbl_Report] WITH CHECK ADD CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus] FOREIGN KEY([ProjectStatusID])
REFERENCES [dbo].[tbl_ProjectStatus] ([ProjectStatusID])
GO
ALTER TABLE [dbo].[tbl_Report] CHECK CONSTRAINT [FK_tbl_Report_tbl_ProjectStatus]
--------------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectStatus](
[ProjectStatusID] [int] IDENTITY(1,1) NOT NULL,
[ProjectStatus] [varchar](max) NULL,
CONSTRAINT [PK__tbl_ProjectStatu__023D5A04] PRIMARY KEY CLUSTERED
(
[ProjectStatusID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
-----------------------------------------------------------
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tbl_ProjectManager](
[ProjectManagerID] [int] IDENTITY(1,1) NOT NULL,
[FName] [varchar](50) NULL,
[LName] [varchar](50) NULL,
[Inactive] [int] NULL,
CONSTRAINT [PK__tbl_ProjectManag__7D78A4E7] PRIMARY KEY CLUSTERED
(
[ProjectManagerID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
And here is the stored procedure that I wrote (doesn't return results): SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[GetReportQuery]
(
@ReportName varchar(100)
)
AS
BEGIN
SET
NOCOUNT ON
IF @ReportName IS NULL
BEGIN
RETURN -1
END
ELSE
BEGIN
DECLARE @DeptCode varchar(50), @ProjID varchar(50)
SELECT @DeptCode = DeptCode FROM tbl_Report WHERE ReportName = @ReportName
SET @ProjID = @DeptCode + '-' + '%'
SELECT P.ProjID, P.ProjType, P.ProjectTitle, P.ProjectDetails, M.FName, M.LName, P.DateRequested, P.DueDate, S.ProjectStatus
FROM tbl_Project P, tbl_ProjectManager M, tbl_ProjectStatus S
WHERE ((P.ProjID = (SELECT ProjID FROM tbl_Report WHERE ((ReportName = @ReportName))))
AND (P.ProjectDetails = (SELECT ProjectDetails FROM tbl_Report WHERE ReportName = @ReportName) OR P.ProjectDetails IS NULL)
AND (M.FName = (SELECT FName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.FName IS NULL)
AND (M.LName = (SELECT LName FROM tbl_ProjectManager WHERE (ProjectManagerID = (SELECT ProjectManagerID FROM tbl_Report WHERE ReportName = @ReportName))) OR M.LName IS NULL)
AND (P.DateRequested = (SELECT DateRequested FROM tbl_Report WHERE ReportName = @ReportName) OR P.DateRequested IS NULL)
AND (P.DueDate = (SELECT DueDate FROM tbl_Report WHERE ReportName = @ReportName) OR P.DueDate IS NULL)
AND (S.ProjectStatus = (SELECT ProjectStatusID FROM tbl_Report WHERE ReportName = @ReportName) OR S.ProjectStatus IS NULL)
)
END
END Can someone see what's wrong? Thanks.
Hi all,I have a SQL job where I do the following -I check for new rows in my Table "DumpResults", every now and then and get the new rows to be inserted into table "CleanTable". I use OPENXML() to get the new data to be inserted but for some reason I don't get the right data through OPENXML() - DECLARE @intDoc INTDECLARE @xmlDoc VARCHAR(8000)IF(SELECT COUNT(*) FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable)) > 0BEGINSET @xmlDoc = (SELECT * FROM DumpResults WHERE DumpResults.C1 NOT IN (SELECT CleanTable.C1 FROM CleanTable) FOR XML RAW) SET @xmlDoc = '<TABLE>' + @xmlDoc + '</TABLE>'
PRINT @xmlDocEXEC sp_xml_preparedocument @intDoc OUTPUT, @xmlDoc--INSERT INTO CleanTable(C1, C2, C3, C4, C5)
SELECT C1, C2, C3, C4, C5, C6 FROM OPENXML(@intDoc,'/row',1)WITH (C1 INT, C2 CHAR(3), C3 CHAR(3) , C4 FLOAT, C5 INT)EXEC sp_xml_removedocument @intDoc
END
ELSE Output that I get is - <TABLE><row C1="1" C2="AAA" C3="BBB" C4="1.000000000000000e+000" C5="2"/></TABLE>(0 row(s) affected) SO "PRINT @xmlDoc" is returning back the xml data (new results) it collected from the "DumpResults" table which isn't there in "CleanTable" but the "Select... FROM OPENXML(...)" doesn't return any result. why so? If anyone knows please reply If anyone has any better method to do it, inputs are welcome. Thanks
Hello....
I am in the process of building a search function for an application (VS2005, ASP.net, VB code, and AJAX functionality).The user's form has a textbox (for an input string) and two drop-downs. The user would then click on a "Search Now" button, and the data should populatein a Gridview to the left of the search form. I'm trying to search over an SQL database and need to know how to exactly implement what I have so far:
<div id="mcb" style="background-color:#2e4b5b; width:195px; height:410px; float:left"> <div id="ht" style="background-color:#1b2c36;padding:10px; border-bottom:solid 1px #fffdff; width:175px;float:left;"> Quick Search </div><div style="clear:both;"></div>
<div style="padding:10px; background-color:#2e4b5b"> <div id="tfta" style="padding:5px 0px 0px 0px"> <b>Vendor Name</b> <asp:TextBox ID="txtVendorFilter" runat="server"></asp:TextBox> <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br /> <b>Contract Type:</b> <asp:dropdownlist ID="ddlContractTypeSearch" runat="server" Width="155px"> <asp:ListItem Value="0">Select...</asp:ListItem> <asp:ListItem Value="1">Vendor Maintenance</asp:ListItem> <asp:ListItem Value="2">Consulting Contract</asp:ListItem> <asp:ListItem Value="3">Hardware License</asp:ListItem> <asp:ListItem Value="4">Software License</asp:ListItem> <asp:ListItem Value="5">Statement of Work</asp:ListItem> <asp:ListItem Value="6">Admin Svcs. Agreement</asp:ListItem> <asp:ListItem Value="7">Master Service Agreement</asp:ListItem> </asp:dropdownlist>
<div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br /> <b>Sort By:</b> <asp:DropDownList ID="ddlSortBy" runat="server" Width="155px"> <asp:ListItem Value="0">Select...</asp:ListItem> <asp:ListItem Value="1">Contract Name</asp:ListItem> <asp:ListItem Value="2">Contract Type</asp:ListItem> </asp:DropDownList> <div style="font-size:1px; width:150px; height:1px; background-color:#8FA71F"></div><br /> </div> <br /> <asp:Button ID="btnUpdateFilter" runat="server" Text="Search" width="120px"/> <asp:Button ID="btnReset" runat="server" Text="Reset" /></div>
</div>
Here's my select statement used to initially populate the Gridview (set up using the configuration of the Gridview):SELECT [ContractID], [ContractName], [ContractType], [EffectiveDate], [TerminationDate] FROM [Contract]Here's my btnUpdateFilter codebehind (VB):
Protected Sub btnUpdateFilter_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpdateFilter.Click Dim strVendorName As String Dim strContractType As String Dim strSortBy As String If txtVendorFilter IsNot "" Then strVendorName = txtVendorFilter.Text End If
''''''''''''''''''''''''''''If ddlContractTypeSearch.SelectedValue = 1 Then strContractType = "1"ElseIf ddlContractTypeSearch.SelectedValue = 2 Then strContractType = "2"ElseIf ddlContractTypeSearch.SelectedValue = 3 Then strContractType = "3"ElseIf ddlContractTypeSearch.SelectedValue = 4 Then strContractType = "4"ElseIf ddlContractTypeSearch.SelectedValue = 5 Then strContractType = "5"ElseIf ddlContractTypeSearch.SelectedValue = 6 Then strContractType = "6"ElseIf ddlContractTypeSearch.SelectedValue = 7 Then strContractType = "7"Else strContractType = "0"End If
''''''''''''''''''''''''''''''If ddlSortBy.SelectedValue = 1 Then strSortBy = "ContractName"ElseIf ddlSortBy.SelectedValue = 2 Then strSortBy = "ContractType"Else strSortBy = "ContractName"End If
End Sub
My question is, "how can I form the proper SQL statement", and then implement it so that my gridview will populate using my generated SQL Statement?
Thank you for the help!!!
-S
I need some help. I have a function that used to use ADO.NET to return a dataview. Now I'm using linq, and I don't know how to make this work anymore.my original function looked like this: public DataView getIssue() { //do some ADO, return the dataview return ds.Tables["Articles"].DefaultView; } With this I could write Gridview1.dataSource = getIssue();I want to do the same thing with Linq, but I'm running into some trouble:Here's my function now:public DataView getIssue() { var query = from a in db.Articles join i in db.Issues on a.IssueID equals i.IssueID select a; DataView dv = new DataView(); dv = query.asdatatable(); return query; }OK, first off I can't use asdatatable since I am using a join, so I can't make the results of my query a dataview. I can't return the results of my query in this function, something that seemed simple to do in Linq now seems like something that may only be possible in ADO.Thanks for your time
View 5 Replies View RelatedYou know how osql can return the results of executing a .sql script to a window? Is there any way to achieve that same behavior in .NET? I am in the process of working on an application that needs to execute a .SQL script (which is easy to do) and then return the actual SQL output from the execution to a window. How do I get that output? None of the sql data objects seem to support this.
View 1 Replies View Relatedselect count(orgcode)
from orglevel
where orglvl = 1 and exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)
26 rows
select count(orgcode)
from orglevel
where orglvl = 1 and orgcode in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')
26 rows
select count(orgcode)
from orglevel
where orglvl = 1 and not exists (select 'x' from empcomp where eecemplstatus = 'A' and eecorglvl1 = orgcode)
6 rows
select count(orgcode)
from orglevel
where orglvl = 1 and orgcode not in (select distinct eecorglvl1 from empcomp where eecemplstatus = 'A')
zero rows
Confusing the hell out of me .....
I have this script that captures metrics and sizes using T-SQL. When running in development I have no issues. I can run under the context of any database and get results back. When deploying to production servers no results are returning. I'm trying to figure out what permissions are needed for this to return results.
No error message is being returned either. My access is limited for triage against Production? I need to be able to have Production DBA set the right permissions so this job can run correctly.
DECLARE @allocation_table table
(
dbname sysname,
reservedpages bigint,
usedpages bigint,
pages bigint
[Code] ....
I have a query running without issue on around 100,000 results however i have a couple where my MAX aggregate is returning two results for a MAX(date time) and i cannot figure out why. I have paired down the query to try and work it out but still don't know.
My query now looks like this (i have remove most columns)
SELECT STATUS, RunOn, sort, T_ID
FROM dbo.Results
WHERE (RunOn IN
(SELECT MAX(RunOn)
FROM dbo.Results
group BY T_ID) AND T_ID = 21405)
ORDER BY sort
I added the T_ID = 21405 to restrict the results to the problematic results, if i set T_ID to any other test result i get one value based on the latest RunOn, but for some reason with this T_ID i get two values, the latest one and one slightly older... The date time looks ok, i just can't work out why the latest and one older result are returned !
The original query was grouping by more items but i removed those to try and work out what is going one, as mentioned for 99.9% of results it works perfectly, but for this one i don't know.
I have a query with a nested query that is used as an in line view. When I run the whole query I keep getting slightly different results each time (10000, 10002, 10001, 9999 etc.), nothing is being changed, no jobs are running on the DB to affect the tables etc.
When I run the inline view query with the nested query, or nested query on its own, it returns same number each time.
What could potentially be the cause of this?
Using SQL Server 2008 R2 Express Edition on local W7 PC
Hello.
I am new at SQL and am using SQL server express edition and im a bit stuck! I am using ASP.NET and C# in my website which is using sql database back end.
String SQLroom = "SELECT DISTINCT RoomName FROM Room INNER JOIN RoomCalendar ON Room.RoomID = RoomCalendar.RoomID WHERE Capacity = '" + reqCapacity + "' " + " AND NOT ('" + newRoomEnd + "' <= roomStartDateTime OR '" + newRoomStart + "' >= roomEndDateTime) AND (OHP = '" + ohpYesNo + "' AND AV = '" + avYesNo + "') ";
This is my SQL string... what it is trying to do is:
find the room
where the capacity is the reqcapacity entered by user
and the startdatetime and enddatetime entered by the user are not present in the table for that room capacity
and then look at whether the user requires OHP or AV facilities, which are stored in the database as either yes or no values.
The problem i am having is with the condition in the sql query... because the user may require an OHP and not AV, but then the room returned "`could" have AV facilities, as it wouldnt make a difference to them if it was there or not, basically, the yes condition has to be satisfied.
Not sure whether i should be using AND, or OR? or a combination.
Any ideas gratefully appreciated....
Sandy
I havea 2 part issue with a query I'm trying to run for a report.
I have an incident report that needs to show results based on dates from the week before. In addition, This report should run every Monday morning, with the exception of a Monday holiday, where in that case, the report will run the next business day. I have an idea to use a case statement, but it doesn't seem to work for me. Any suggestions?
Also,part of the query I have is pulling back the right data, as long as I don’t include parameters. When I do add values to the parameters the query includes all the dates. I need to see either/or - not both. I just want to either see dates without the param values or see only dates that I ask for in a parameter. Does that make sense?
Here’s the query (thanks!):
SELECT incident.incid_id,
incident.incid_short_desc,
incident.incid_received_date_time,
incident.incid_closed_date_time,
security_users.description,
incident.incid_assigned_to,
incident.tagged_delete_flag,
activity_result_master.result_desc,
incident_priority_master.priority_desc,
activities.result_id
FROM activities AS activities INNER JOIN
incident AS incident ON
activities.incid_id = incident.incid_id INNER JOIN
activity_result_master AS activity_result_master ON
activities.result_id =
activity_result_master.result_id
INNER JOIN security_users AS security_users ON incident.incid_assigned_to = security_users.name INNER JOIN
incident_priority_master AS incident_priority_master ON incident.priority_id = incident_priority_master.priority_id
WHERE incident.tagged_delete_flag = 'N' AND
activities.result_id = '6' AND
(incid_received_date_time >= DATEADD (d,-7,GETDATE()) AND
incid_closed_date_time <=DATEADD (d,-3,GETDATE())) OR
CONVERT(varchar,incident.incid_received_date_time,101) >= @StartDate AND CONVERT(varchar,incident.incid_closed_date_time,101) <= @EndDate
ORDER BY incident.incid_assigned_to, incident.incid_id
Hi,I wanted to know if this is possible and if so, how do Ido it. Say, I have a query "SELECT * FROM Table WHEREColumn="some_value". This executes on a very large dataset and I would like to return the results as they queryexecutes rather than wait for the whole query to execute.Basically, I want to get the results as they are preparedby the database. Any way to do this?Regards,San
View 8 Replies View Related
I want to write a stored procedure that takes two three paramters based on a case statement determining which values are null something like this
Select InformationDate From thisTAble
Where
If @dateValue IsNot @Null
Informationdate = @dateValue
Else
Where
In this second where cluase I want to be able to pull out all the results based on a date range and i am not sure how the syntaz would go
InfomationDate IsBettween @daterangeFrom @dateRangeTwo
Any help on this i hope i was clear...thank you!
Does anyone lese get the same results that I do?
tblTest has an id field and a testName field....table has 2 records:
1 kevin test 1
2 kevin test 3
SELECT *
FROM tblTest t
INNER JOIN CONTAINSTABLE(tblTest,*,'"kevin test 1"') as A ON t.id = A.[KEY]
Result is both records returned, but since the search is in quotes as an exact phrase, should not just the first record be returned?