Can anyone tell me how to select the "most recent" date values from a
grouped query? Consider the following:
CREATE TABLE [dbo].[TestQuery] ( [ID] [int] NOT NULL , [ID_Parent] [int] NOT
NULL , [Date] [datetime] NOT NULL ) ON [PRIMARY]
This is a simplified adjacency list. What I want to do is find the highest
valued item by date for each sub-tree. In other words, the single highest
date item when This.ID_Parent = That.ID_Parent. I think I first need to
group by ID_Parent, then select the TOP 1 from this query, but how to
aggregate them so I get the TOP 1 for each ID_Parent?
Hi all, I have built a Disaster Recovery Site for my DB.on periodic basis, trn files from production server reach DR server and DR setup will apply them locally.
If the flow is smooth then no issues, if one of the file does not reach DR the entire setup will halt for the want of the file and I dn't have any means to know which file is missing.
Both my production and DR site located remotely behind firewalls, i.e i can't physically access the servers or remotely login to the server.
Can anyone tell me how to see the contents in a folder in the server using SQL query .
I have a requirement to create a package that takes all files in a given folder and adds them to a single archive (.zip) file. I've tried several methods using 7zip and while I can create archives for every file in the directory I can't seem to get them into a single .zip file.
Hi All,I have come up against a wall which i cannot get over.I have an sql db where the date column is set as a varchar (i know, should have used datetime but this was done before my time and i've got to work with what is there). The majority of values are in the format dd/mm/yyyy. However, some values contain the word 'various'.I'm attempting to compare the date chosen on a c# .net page with the values in the db and also return all the 'various' values as well.I have accomplished casting the varchar to a datetime and then comparing to the selected date on the .net page. However, it errors when it comes across the 'various' entrant.Is there anyway to carry out a select statement comparing the start_date values in the db to the selected date on the .net page and also pull out all 'various' entrants at the same time without it erroring? i thought about replacing the 'various' to a date like '01/01/2010' so it doesn't stumble over the none recognised format, but am unsure of how to do it.This is how far i have got: casting the varchar column to datetime and comparing. SELECT * FROM table1 WHERE Cast(SUBSTRING(Start_Date,4,2) + '/' + SUBSTRING(Start_Date,1,2) + '/' +SUBSTRING(Start_Date,7,4) as datetime) '" + date + "'"Many thanks in advance!
Using Sql Server 2005 Express and Management Studio I need to create a SQL insert statement for the contents of a table (FullDocuments) so that I can run the query on another server with that same table schema (FullDocuments) and the contents will automatically be inserted into the new instance of the FullDocuments table.In Management Studio I have used "Script Table as" for the create table query. The second instance of FullDocuments has been created on the remote server. Now how do I generate an insert query for the contents of FullDocuments so that the contents can be moved/inserted to the new instance of the table?Thanks for any help provided.
I am trying to perform a query in SQL that will merge the contents of various table, using search criteria to narrow down the results. All of the tables have the exact same fields, and the search criteria is the same for all of them. What I am looking for is the most efficient way to perform this. I am using SQL stored procedures, and passing the results into my code. Note that in my real-world scenario there are more than three tables.
1) Merge all the tables into one and then perform the search criteria: SELECT a, b, c FROM (SELECT a, b, c FROM Table1 UNION ALL SELECT a, b, c FROM Table2 UNION ALL SELECT a, b, c FROM Table3) AS MY_MERGE WHERE {SEARCH CRITERIA}
2) Perform the search criteria on each table and merge the results SELECT a, b, c FROM (SELECT a, b, c FROM Table1 WHERE {SEARCH CRITERIA} UNION ALL SELECT a, b, c FROM Table2 WHERE {SEARCH CRITERIA} UNION ALL SELECT a, b, c FROM Table3 AS MY_MERGE WHERE {SEARCH CRITERIA})
Thanks in advance for taking the tiemt o read this post:
I am workingon an application in vb.net 2008 and I have 5 drop down lists on my page. I have code that worked in .net 2005 for my databind but would like to use new features in 08 to do this same thing. Here is my 05 code how would I do this same things in 08? Dim db As New DataIDataContext Dim GlobalSQLstr As String GlobalSQLstr = "select Orig_City, ecckt, typeflag, StrippedEcckt, CleanEcckt, ManualEcckt, Switch, Vendor, FP_ID, order_class, Line_type, id from goode2 where 1=1" If (ddlOrigCity.SelectedValue <> "") Then GlobalSQLstr &= "and Orig_City = '" & ddlOrigCity.SelectedValue & "'" End If If (ddlSwitch.SelectedValue <> "") Then GlobalSQLstr &= "and switch = '" & ddlSwitch.SelectedValue & "'" End If If (ddlType.SelectedValue <> "") Then GlobalSQLstr &= "and Order_Class = '" & ddlType.SelectedValue & "'" End If If (ddlFormatType.SelectedValue <> "9") Then GlobalSQLstr &= "and typeflag = '" & ddlFormatType.SelectedValue & "'" End If If (ddlVendor.SelectedValue <> "") Then GlobalSQLstr &= "and Vendor = '" & ddlVendor.SelectedValue & "'" End IfDim AllSearch = From A In db.GoodEcckts2s If (ddlErrorType.SelectedValue <> "0") Then GlobalSQLstr &= "and ErrorType = '" & ddlErrorType.SelectedValue & "'" End IfDim cmd As New SqlClient.SqlCommand Dim rdr As SqlClient.SqlDataReaderWith cmd.Connection = New SqlClient.SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString1").ConnectionString) .CommandType = Data.CommandType.Text .CommandText = GlobalSQLstr .Connection.Open() rdr = .ExecuteReaderMe.gvResults.DataSource = rdrMe.gvResults.DataBind() .Connection.Close() .Dispose()End With
Hello, I need some help, I am in school right now and I am in a SQL server class. We have been working in the query analizer making a database. Well I have to print out everything that I have typed. But I want to view it first. How do I do that?
Sorry I did a search and couldnt find anything.. Probably cause I dont really know what to search for or look under. Thanks for your guys time.
Is there a way to find out programmatically using T-SQL where the current log folder is located for a SQL insatance? if for example the log folder is located under the C: as
I dont alot about sql server 2005(Express edition). For debugging purposes i want to copy the whole app_data folder(.mdf & .log files) on the production server to another folder on the same machine(or sometimes to a network folder). So when i copy and try to paste this App_data folder to a new location, i get this error message "cannot copy ASPNETDB: it is being used by another person or program. close any programs that might be using the file and try again." After reading the above message, i close visual web developer, stop the website in IIS and stop the SQLExpress service on the server and try again but still get the same message. So how can i make sure that all the programs accessing these database files are closed such that i'm able able to copy them to a different location.
I know a WMI event watcher can be used to watch for a new file being added to a folder. However, I need to check for new folders being added to an existing folder. I haven't been able to find a post on doing this. Is there a way in WQL to check for a new folder being added instead of a new file? I've used SQL for years, but am new to SSIS.
I have a SharePoint (2007) Document Library site called B. Web Client is enabled on the server and B is mapped as a Drive (let's call it Y for this discussion)
I want to move documents in A to B. Easy enough, right? Not so....
I first started by creating a batch file that issues a COPY \A \Y /Y at the command prompt. Viola! Worked Great!
I then moved that command to a SQL Agent job as a CMDExec statement (exact same statement) and attempted to run it.....CRASH! It found the files in A but then said "The system cannot find the path specified"
Ok, so I tried it in SSIS. CRASH! Checked the error log. Same thing...
So I then checked the account under which the SQl Agent was running (special domain account for all our SQL Servers). Thinking it might mater I changed it to run under my name (I'm in Domain admin). I also ensured I had permissions to the SPS 2007 library as well. (I did).
Ran again! CRASH! Same error....
So, I created a batch file , placed thec ommand in the batch file and ran that from the command prompt! Viola! Worked Great.
So, I was thinking of how ingenious I was as I pasted my C:RootCopy.bat into my SQL Agent job. With a big grin on my face I right clicked and picked "Start Job at step".......CRASH! Same error.
Does anyone have any ideas on this ???????????????
I am trying to run a UNION ALL query in SQL SERVER 2014 on multiple large CSV files - the result of which i want to get into a table in SQL Server. below is the query which works in MSAccess but not on SQL Server 2014:
SELECT * INTO tbl_ALLCOMBINED FROM OPENROWSET ( 'Microsoft.JET.OLEDB.4.0' , 'Text;Database=D:DownloadsCSV;HDR=YES', 'SELECT t.*, (substring(t.[week],3,4))*1 as iYEAR, ''SPAIN'' as [sCOUNTRY], ''EURO'' as [sCHAR],
[Code] ....
What i need is:
1] to create the resultant tbl_ALLCOMBINED table
2] transform this table using PIVOT command with following transformation as shown below:
PAGEFIELD: set on Level = 'Item' COLUMNFIELD: Sale_Week (showing 1 to 52 numbers for columns) ROWFIELD: sCOUNTRY, sCHAR, CATEGORY, MANUFACTURER, BRAND, DESCRIPTION, EAN (in this order) DATAFIELD: 'Sale Value with Innovation'
3] Can the transformed form show columnfields >255 columns i.e. if i want to show all KPI values in datafield?
P.S: the CSV's contain the same number of columns and datatype but the columns are >100, so i dont think it will be feasible to use a stored proc to create a table specifying that number of columns.
I have a set of folders with the following structure:
MainFolder
1999
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
2000
SpreadsheetA.xls
SpreadsheetB.xls
SpreadsheetC.xls
etc.
Is there a way that I can use the foreach loop container to loop the sub folders? My plan was to get the folder name and path into a variable, use this to build the connection string for each file in this folder, carry out the upload for that folder, then move onto the next folder and do the same thing, but I cannot see a way to do this.
As SQL Server 2016 category is not created so I am posting this query in SQL Server 2014 window.
I have enabled Query Store in SQL Server 2016 using ALTER DATABASE and later tried with Properties of Database too. But I am not able to see the Query Store subfolder under database.
I am having problem with moving a file from one folder to another folder. Here is the detailed scenario:
I want to move a input.csv file from shared input folder to shared archive folder. i am using the below code to do this. declare @inpath varchar(100) SET @inpath = 'move "\abcdefINPUTinput*.csv" "\abcdefARCHIVEarchive.csv"' EXEC @filestatus = master..xp_cmdshell @inpath but the problem was it was cutting the input.csv file from INPUT folder but not pasting it in the ARCHIVE folder. I really appreciate if anyone can help me to solve this or anyone can tell some workarounds.
Can anyone advise me of a convenient way to make a copy of a database in SQL2005. I need to make a complete copy - including all Stored procedures, functions tables and table contents. I want the place the copy ont he sae SQL server but obviously under a different name.
I have a table with about 2 million records. One of the fields has data seperated by a comma. I need to be able to grap the data for each record and split those items into their own table in seperate rows. This is easy in Asp but the page will timeout before it can process all the records. Any Ideas? Thanks,Rick
Hai. Here i am sending my query.This is urgent to my job.Please give me solution as early as possible in SQL server.
Thanks. My query is.. Check for CD drive on your system.If drive available ,display the name of the CD and read the contents of the CD,and search for given file in CD. I want search this on local machine and on remote system.
Where can I get a reference document that will help me troubleshoot and interpet the MS SQL Server ERROR.LOG file? Something that would describe in detail what the error is and various actions I could take to correct the errors. JLS
Is there a way to mask the contents of a cell or column? I have a table that stores passwords and I would like to mask the password much like Access does with asterisks. I know I can restrict the column based on user but that creates other problems on the frontend.
I have a series of records, that are displayed as contents in a .NET application,i know i can order the items to be dynamic, i.e I set the top item in the contents, However I know this will get quite messy at the application level.
I would like to set up the default if I insert an item, into the table to make it the last in the list, select max(ordernum) + 1, i guess?
How can I set an on insert constraint, or what its called for this in SQL Server?
I am storing data temporarily in a database... and periodically needto update that huge database, so just has to copy that temp DB to theoriginal one.Using sql may take a few hours to finish this operation. I think youcan do "export" from temp database into a file and "import" into thehuge database. That will be binary data exort and import and hencewill be faster. But, I am not sure if all databases will support thatthough.So, is there any other solution?
Hi,I have a SELECT FROM TABLE query and in that table there is a field of typeimage. Result of this select goes throug internet do its destination. But infact I need only to know if in this field is or not an image. Is there anyfunciotn which gives me information about contents of image fields?Regards, Paul
Hello,In my MS SQL SERVER database i have an column with datatype image. Somerecords do have data in this column, some others don't. When i look at thistable in Enterprise Manager then i see in every field '<image>' so no way tosee directly wheter a filed has data or not. Is there a way to changedisplay of this column so i can see the binairy data in it?Marcel
