I know this is a very simple problem however I've literally just begun learning about SQL and this is the first ever code i've created using the language.
The error is:
Msg 102, Level 15, State 1, Line 30
Incorrect syntax near ')'. Located on line containing "create table Trailer_Type"
I have dataadapter and dataset that reads/writes to SQL tables. I can read. I can create "new" records. However, I have not been able to master the "updating" of an existing row. Can someone provide me specific code for doing this please or tell me what I doing wrong in the code below. The code I using is below. I don't get error, but changes do not get written to SQL dbase. For starters, I think I "not" supposed to use the 2nd line(....NewRow). I think this is only for new row, not updating of existing row - but I don't know any other way to get schema of row. thanks to any who can help
etc duration is in seconds what i want to be able to do is add the start time from a row to the duration from the same row and sutract it from the next rows startime.
Hi everyone, I'm working with VWD express 2005 (VB): I have a simple database with UserId (int), EmployeeName(Varchar), and 7 fields presenting days of the week.(bit/Boolean). It's function is to keep track of which Employees are available at what day(s) of the week. What I want is for the user to select 1 of the 7 days in a dropdownlist after which a datagridview shows all employees that have that particular day set "true" so... SELECT * FROM [EmployeeTable] WHERE [value selected index dropdownlist] = true Question is how do I place the value of the selected day of the dropdownlist in this query? Or is there a better way to write a Select query? thanks beforehand for your reply ! Sean
I am new to SQL but I have jumped into it fairly deep. I need to write a query to generate a report that counts systems by the first two characters in the system name and gives a count by the first two characters and a total of all the systems. The first part I have but I cannot think of how to get an overall total. Here is the code I am using: select substring(dm.[Name],1,2) as 'Location', count(distinct dm.[Name]) as '# Discovered', count(distinct w.[Name]) as '# Managed', count(distinct w.[Name])*100 / count(distinct dm.[Name]) as '% Managed' from DiscoveredMachines dm, Wrksta w
where datediff(dd,dm.[DiscoveryDate],getdate()) < 90 and dm.[Name] *= w.[Name] and (dm.[Name] like 'AB%' or dm.[Name]like 'CD%' or dm.[Name] like 'AD%' or dm.[Name]like 'CB%' or dm.[Name] like 'SX%' or dm.[Name] like 'EX%')
I am writing a query to list all users in 2 countries but exclude users which their name starts with _inactive_5#4$66899, holder_....
I wrote
Select position, subsidiaryName from position where subsidiaryname = 'country one' OR subsidiaryname= 'country 2'
I get a result but there are some names which are fake accounts
eg. names start with inactive_#@$%&*ijkgfhg
or with holder_uhgfjgj
how can I do write into the query so that only the proper names without the rows that are fake showing up. In other words i want all results for those 2 countries except ones starting with inactive or with holder......?
First off sorry for my complete lack of experience with SQL, but I've just started a new position which involved learning SQL. I'm currently looking for a course but in the mean time I just have to try and fumble by doing basic things.
I'm trying to write my first basic select / join statement, and I just can't seem to get it working.
Any help would really be appreciated. It's T-SQL we use btw.
Thanks :)
USE MLSDHeat GO
select * from dbo.CallLog where callstatus between 'open'and 'pending' right outer join dbo.Asgnmnt on callid = callid order by callid
--the above returns error Msg 156, Level 15, State 1, Line 4 Incorrect syntax near the keyword 'right'.
I'm struggling with the problem which feels like it shouldn't be taking me this long! Any help would be gratefully received. Simply, there are two tables: Users: userid | username Links: sourceUserId | destUserId sourceUserId and destUserId are both in the users table. I'm trying to write a SP which will output the names of the linked users. eg: Bob | Alice Alice | Geoffrey Peter | Bob Any help gratefully received! Thanks in advance -- Chris
I am bulk-loading a large amount of data into a group of related tables. Each table has an identity column as its primary key.
A problem is introduced because I need to break up the bulk-loads into multiple sessions, due to performance and memory constraints. I can get all the foreign key references correct within the scope of a single bulk-load session. However, there are many duplicate rows when you look at the job as a whole.
So, I need to find all the duplicate rows, collapse them down to a single row, and update all the foreign key references in other tables to reference this single row.
I can find all the duplicate rows and generate a table consisting of the primary key ids and the duplicated values, ie:
ID Value ---+----- 1 | Fred 2 | Fred 5 | Ethel 7 | Ethel 9 | Ethel
Now, all I need to do is DELETE all the duplicate rows (in the example above, rows 2, 7 and 9) and generate UPDATE statements for the other tables that reference this one which replace all the duplicate keys with the chosen one (ie. replace all references to foreign key 2 with 1, and all references to foreign key 7 and 9 with 5).
But this is where I'm getting stuck with SQL. I haven't been able to figure out a way to extract just the first row for each group of Values, which contains the primary key I will use to substitute, then generate the correct subquery for the UPDATEs.
I know I can do this sort of thing procedurally, but I'm wondering if there is also an elegant way to do this in SQL. Thanks so much for the help!!
So, I have never worked with SQL in my entire life, but have some basic programming skills. I was given a job to run through two tables, sort some of the information and return the two most recent enteries for the job number. I can sort everything but I am having a hard time just returning the two most recent enteries. I have two tables in which to look from and I think a subquery WHERE with the TOP would work.... yet I have tried everything i can think of and after a good 10 hours of research, im hoping to get some help!
The two most recent enteries that i need returned is "start date" with all the other information...
Here is my code that is from designer (which i have played with to no avail)
SELECT Table1.[Inquiry #], Table1.[TTI #], Table1.[Proj Name], Table1.[Customer Name], Table1.[Date In], Table2.Activity, Table2.[Start Date], Table2.[Stop Date], Table2.[Project Lead] FROM Table1 INNER JOIN Table2 ON Table1.[Inquiry #] = Table2.[Inquiry #] GROUP BY Table1.[Inquiry #], Table1.[TTI #], Table1.[Proj Name], Table1.[Customer Name], Table1.[Date In], Table2.Activity, Table2.[Start Date], Table2.[Stop Date], Table2.[Project Lead] ORDER BY Table1.[Inquiry #], Table2.[Start Date] DESC;
Well, all i can do now is pray someone has the opportunity to help.
if the value of a paticular cell in the table has changed since last poll,
then initiate the second task
2. do a select query that picks about 10,000 new rows off another db table,
the 10,000 rows should then be stored in a in-memory dataset.
Every time the poll initiates a new select query, it should insert the new rows to the existing in-memory dataset.
thus if the select runs for 2 times in 2 minutes, the the in-memory dataset would contain a maximum of 20,000 rows.
3. Then I want to apply a set of transformations on the dataset and then finally update some db tables, push some records to the ssas database. (push mode incremental processing)
which sub tasks can be achieved and which cannot.
if not, Is there a workaround?
Please do provide some specific links that accomplish some of these similar tasks.
I have tested some functionality, like
doing a full processing of a ssas database.
reading from a database table and inserting into a flat file.
I tired to use the ExecuteSQLTask, and i also assigned the resultant to an user:variable. the execution completed succesfully but I am not able to see the value of the variable change. also I am not able to use the variable to figure out a change in previous value and thus initiate a sql select. or use the variable to do anything.
Hi there I sorry if I have placed this query in the wrong place. I'm getting to grips with ASP.net 2, slowly but surely! When i try to access my site which uses a Sql Server 2005 express DB i am receiving the following error:
Server Error in '/jarebu/site1' Application.
Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: Database 'd:hostingmemberasangaApp_DataASPNETDB.mdf' already exists.Could not attach file 'd:hostingmemberjarebusite1App_DataASPNETDB.MDF' as database 'ASPNETDB'.Source Error:
An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace:
Version Information: Microsoft .NET Framework Version:2.0.50727.42; ASP.NET Version:2.0.50727.210
This is the connection string that I am using: <connectionStrings> <add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|ASPNETDB.MDF;Integrated Security=True;Initial Catalog=ASPNETDB;User Instance=True" providerName="System.Data.SqlClient"/> </connectionStrings>
The database is definitly in the folder that the error message relates to. What I'm finding confusing is that the connection string seems to be finding "aranga"s database. Is it something daft?
I am a beginner and looking for some help. I have a database with just one column (some names). That is the primary key aswell. Because I want the names to be unique.I used a grid view control to display the data and included the insert functionality in the grid view by using some code and the part of the code that does the insert is 1 public static void Insert(Categories category) 2 { 3 string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; 4 using (IDbConnection cn = new SqlConnection(connectionString)) 5 { 6 cn.Open(); 7 8 IDbCommand cmd = new SqlCommand(); 9 10 cmd.CommandText = "INSERT INTO Categories (CategoryName) VALUES " + 11 "('" + category.CategoryName + "')"; 12 13 cmd.Connection = cn; 14 cmd.ExecuteNonQuery(); 15 } 16 } Question: when someone tried to enter a new name which already exists in the database it throws an error page which is what I want but is there a way to be able to display the user a message sayin g that he/she has entered a name that already exists and hence they need to try a different name? instead of the ugly error page? Thank you in advance,Prasad.
Hi, I need to bind to dropdown list, the conference names from the last one to the first one. I'm using following query, the problem is it returns the conference names the number of dates created, since it's one table with all data I can't change it now. Is there a way to get the names one time for each name without repeating?
string Sql = "SELECT DISTINCT conference_name,creation_date FROM Conference ORDER BY creation_date DESC"; SqlDataAdapter da = new SqlDataAdapter(Sql, myConn); dataSet = new DataSet(); da.Fill(dataSet); ddlConfName.DataTextField = "conference_name"; ddlConfName.DataSource = dataSet.Tables[0]; ddlConfName.DataBind();
Forgive me if i'm being really stupid but... we have an accounts package (Exchequer) using sql. I would like to use the tables in sql to interrogate the db further. However my numeric fields e.g. Credit Limit are split into 2 fields. One is a smallint type and one is an integer. How do I combine these fields to get an accurate value? Thanks
I keep getting the error "Invalid attempt to read when no data is present" when trying to query a table in my SQL DB. I have checked and rechecked and the table name and column names within it are spelled correctly. There are only three records in the database, they all have data in them, and the code in Country.Text precisely matches the data in the Country field in one of the records.
It's worth mentioning that when I use Visual Studio 2005's little direct SQL query tool to build and run the following SQL statement that it works properly:
I am perplexed. Any ideas anybody...here is the code...?
Dim SelectSQL_Countries As String SelectSQL_Countries = "SELECT * FROM data_Countries " SelectSQL_Countries &= "WHERE Country='" & Country.Text & "'"Dim con_Countries As New SqlConnection(ConfigurationManager.ConnectionStrings("MySiteMainDB").ConnectionString) Dim cmd_Countries As New SqlCommand(SelectSQL_Countries, con_Countries)Dim reader_Countries As SqlDataReader Try con_Countries.Open() reader_Countries = cmd_Countries.ExecuteReader()StateID.Text = reader_Countries("WordForState") reader_Countries.Close()Catch err As Exception lblResults.Text = err.Message Finally con_Countries.Close() End Try
I have a database and would like to retrieve specific data via queries. This database is also connected to an ASP .Net 2.0 application to be the front end. Ive created the query in the database. Would you recommend i use parameter names to retrieve the data via code or should i have the query within my code to retrieve the data? Thanks
HI all, I got a tsql that needs to be simplified. Select * from Table1 where condition1 and id not in ( Select id from table1 where condition2 and id in ( Select id from Table1 where condition1 ) ) basicly all records thats in condition1 but that doesnt have condition2 but limited to condition1. I'm probably maken this to complicated. but im tired and im losing time just on one stupid query. Thanks for the help.
Ok, so im pretty much finished writing my forum web page. However to display things like how many replies each thread has and who replied last, i need to perform a query in the code file. Im guessing its simple enough but i cant get the syntax for actually performing any query. I already know the sql syntax like select * from all that stuff but how do i get do something like: Dim x as integer = sqlQuery("Select count(*) FROM ...") Currently i have it all working by creating a table and making it invisible and just pulling data from the table but thats sloppy and pretty ineffecient if i databind a table for every single topic name.
I am writing a query now where I only want to get participants in the 112 service code only but this query I have is giving me other service codes as well when I have it set to 112. How can I fix this just so i can get this 112 service code only.
declare @bgDte smalldatetime declare @enDte smalldatetime declare @rgn char(2) set @bgDte = '2013-01-01' set @enDte = '2013-04-30' set @rgn='05'
I've created a stored procedure which inserts values into a table and upon successful execution the RC column gets returned along with the Identity value (I'm using SELECT SCOPE_IDENTITY()), but I don't want to RC column, I only want to get the Id number of the current row. I'm doing this using the Query Analyzer.
Is there a way to suppress the RC column? I have run the same query on different servers and the RC col doesn't show up. I only want the ID value to put into an ASP.NET page.
While testing different query methods from ASP.NET (Output parameter, Return_Value, etc) did I set a flag within SQL Server?
i have a number of business programs. Each program is started anew at the beginning of each fiscal year. each program has a number of goals and customers subscribe to the goals.
i have to pull all this info out of the database.
i have a cursor that gets the first program, inside this program i have a cursor that gets the first period, and inside that i have a cursor that gets the info on each goal.
program cursor
{
period cursor
{
goal cursor
}
}
}
}
this takes ages ( hours and hours ) to run. is there any way i could have designed this using joins and simple selects to make it more efficient??
I have a custom code that successfully query my database in SSRS 2005 report. However, I am hard coding its connection string (in the custom code) and I want it to be able to use the same connection string as the ones that the report use. This information is available in my registry setting, but if it is possible, I want to avoid that, since I aim for a quite simple solution. If I am able to get the connection string details at the report level or from within the custom code, it would be great.
Anybody have a suggestion? Anything would be really appreciated.
I've been reading a million and one posts on replication
My scenario is that i have a live SQL 2000 server. In a DR invokation, i'e i've lost my live sever, i want to be able to access the same data at the DR (SQL 2000) site and have it accessable to the users. DR server has a different name to the live box.
Replicate
Master CRMDatabase
Data changes all the time but can have hourly replication of transaction logs for this example. I've currently researched a sp called update logons but this has to be fed each account name to enable them on the new server. There must be a way to activate all CRMDatabase logons with the new server?
Could someone be kind enough to lead me through a step by step guide on the best solution.
Hi All,First of all its very very urgent. Secondly I am not an expert. Now here is my problem.I want to loop through my html files and want to save the data in my table column. here is the Code which need correction.CODE:USE [AdventureWorks]DECLARE @cmd varchar(1000);DECLARE @FileName varchar(100);DECLARE @FilePath varchar(100);SET @FileName = 'C: est'+@FilePath+'html';SET @cmd = 'INSERT INTO myTable(FileName, FileType, Document)' + 'SELECT ' + @FilePath + ', FileType, * FROM OPENROWSET(BULK @FileName, SINGLE_CLOB) AS Document'EXEC (@cmd);Select * from myTableJust for practice I am using Adventureworks db. I am using SQL Express 2005.My html files are named something like this:AC0234.htmlDB9803.htmlCG4571.htmlI cannot change my file names.Thanks in advance.Regards;
select * from bigtable where product_code in ('abc','def','hij','klm')
I know that each of those product_codes will return a large data set. What I'd like to do is to run the same query but constrain the result set to a max rownum of 200.
I have some custom code in my report <Code> element that I use to query a database. I have hardcoded the connectionstring in this code but I would like to use the same datasource that the report uses. Is there some report property that allows me to access this datasource?