Hello SQL Gurus,
I want to dynamically add a column to a select statement who must contain the line number.
Example :
Some table has 3 columns (id,field1,field2)
SELECT top 3 *,[lineNumber] as lineOrder FROM someTable
must produce
id | field1 | field 2 | lineOrder
-----------------------------------
1 abc def 1
23 def ghi 2
7 ghi jkl 3
and so on... Possible ?
Hi all,
I need few clarifications from you experts regarding SQL server 2005 & CLR integration ( my questions might be simple and silly, please bear with me).
A web service should be invoked from the SQL Server , Is CLR stored procedure only way to do that ?
Does SQL Server uses the CLR only when the CLR support is enabled ? OR SQL Server itself runs on top of the CLR no matter it is enabled or not ?
What are the major disadvantages of using CLR stored procedures instead of T-SQL?
Thanks in advance,
DBLearner
What's the MS SQL Server SQL for making rows
xxx yyy 1 zzz
xxx yyy 2 zzz
xxx yyy 3 zzz
into a row
xxx yyy 1,2,3 zzz
Or is there another way
Hello all, I have a puzzle that needs solving please. I have a Stored Procedure and it works fine and produces what I want to see. I have two servers replicating and when the stored procedure is ran on the other server, it produces nothing at all but the data is verified the same. So I go to query analyzer and copy the following sql statement from the stored procedures and put in the variables it asks for and I get nothing on either server. Here is the statement:
CREATE PROCEDURE sp_Transfer (
@Weekending datetime,
@Transfer datetime)
AS
select jobs.jobnumber, activities.weekending,
activities.material,
case employees.type when '1099' then case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft1099) else
(lineitems.quantity * lineitems.labor1099) end else
case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft) else
(lineitems.quantity * lineitems.labor) end end end end end end end end as labor,
(lineitems.quantity * lineitems.vehicle) as Vehicle,
(activities.meals + activities.lodging) as PerDiem,
(employees.state) as FState, (states.state) as TState
from jobs
inner join activities on jobs.jobid = activities.jobid
left outer join employees on activities.employeeid = employees.employeeid
left outer join lineitems on activities.activityid = lineitems.activityid
inner join states on jobs.billingstateid = states.stateid
where (activities.weekending = @Weekending or activities.transferdate = @Transfer or jobs.transferdate = @Transfer)
and jobs.billingstateid <> employees.stateid and lineitems.quantity <> null
GO
Can anyone help tell me why this happens or has anyone ever experienced this before?
Thank you in advance!!!!!!
Can anyone solve this puzzle with SQL only - no temp tables and no cursors
(sent to me by a friend)?
I would have done it with a cursor on SQL Server but the rules stipulate not using a cursor. Can this be done with just SQL?
You have a table:
First Name Last Name
John Doe
Jeff Smith
etc..
Is it possible to write a query in SQL which assigns serial numbers 1; 2; 3; to the rows(in any sequence)? So the output should look like this:
Number First Name Last Name
1 John Doe
2 Jeff Smith
etc..
Note that your query must work with any data for the input table. You cannot rely on the fact that there are only two rows. Number cannot be a IDENTITY column.
Thanks!
Hello all, I have a puzzle that needs solving please. I have a Stored Procedure and it works fine and produces what I want to see. I have two servers replicating and when the stored procedure is ran on the other server, it produces nothing at all but the data is verified the same. So I go to query analyzer and copy the following sql statement from the stored procedures and put in the variables it asks for and I get nothing on either server. Here is the statement:
CREATE PROCEDURE sp_Transfer (
@Weekending datetime,
@Transfer datetime)
AS
select jobs.jobnumber, activities.weekending,
activities.material,
case employees.type when '1099' then case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft1099) end else
case employees.type when '1099' then case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft1099) else
(lineitems.quantity * lineitems.labor1099) end else
case left(lineitems.item,4) when '2392' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,4) when '2391' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,4) when '3172' then (lineitems.quantity * lineitems.draft) else
case left(lineitems.item,5) when 'ECAD2' then (lineitems.quantity * lineitems.draft) else
(lineitems.quantity * lineitems.labor) end end end end end end end end as labor,
(lineitems.quantity * lineitems.vehicle) as Vehicle,
(activities.meals + activities.lodging) as PerDiem,
(employees.state) as FState, (states.state) as TState
from jobs
inner join activities on jobs.jobid = activities.jobid
left outer join employees on activities.employeeid = employees.employeeid
left outer join lineitems on activities.activityid = lineitems.activityid
inner join states on jobs.billingstateid = states.stateid
where (activities.weekending = @Weekending or activities.transferdate = @Transfer or jobs.transferdate = @Transfer)
and jobs.billingstateid <> employees.stateid and lineitems.quantity <> null
GO
Can anyone help tell me why this happens or has anyone ever experienced this before?
Thank you in advance!!!!!!
Hi all,I have a sql puzzle (which I've solved) but seems very unwieldy. Isthere a more efficient way of doing this?It reads like so:SELECT a.*FROM axindex a INNER JOINaxindex b ON a.id4 = b.id4 AND a.id1 = b.id1 ANDa.id5 = b.id5 AND a.conflict_type = b.conflict_type INNER JOINaxindex c ON b.id4 = c.id4 AND b.id1 = c.id1 ANDb.id5 = c.id5 AND b.conflict_type = c.conflict_type INNER JOINaxindex d ON c.id4 = d.id4 AND c.id1 = d.id1 ANDc.id5 = d.id5 AND c.conflict_type = d.conflict_typeWHERE (a.stext = 'land') AND (b.stext = 'reg') AND (c.stext ='fees') AND (a.conflict_type = 5) AND (d.stext = 'hamilton')Many thanks in advance.Sam
View 1 Replies View RelatedHi,
I managed to get things broken and I can't solve it:
Scenario:
SQL Server
--->Databases
--->TestDB
---> Users
------> dbo mapped to user1 (user1 = SQL Server user)
------> user1(database user) mapped to nothing (no SQL user)
---->Security
-------> Logins
-------> user1
Problem: I don't want SQL Server user 'user1' to be dbo but I can't remove dbo rights because it owns objects nor can I drop database user 'user1' or delete SQL Server user 'user1' because it's mapped to dbo.
I'm running in circles..
First and last time I use the auto_fix option to fix orphaned users.
At this point, the only solution to me is dropping the database.
Anyone with a better idea?
Thank you!
Best Regards,
Worf
Here's a fun little puzzle that I thought up based on some rather gnarly problems I faced today. It might brighten up your morning. See if you can figure out what it does and how it does it.
How does it work? When will it fail as written (this example does not fail)? Why will it fail? How can you change it to increae its capacity?-- ptp 20080117 SQL puzzle
DECLARE @iINT
, @cVARCHAR(360)
SET @i = 90
SET @c = ''
WHILE 64 < @i
BEGIN
SET @c = Char(@i) + Space(@i - 64) + @c
SET @i = @i - 1
END
SELECT Replace(Replace(Replace(Replace(@c
, ' ', ' '), ' ', ' '), ' ', ' '), ' ', ' ')
UNION SELECT @c-PatP
I've been running my simulations against our SQL Server 2005 database all day today and have been noticing a lot of deadlocking. Almost every transaction was deadlocking and retrying.
About an hour ago I started SQL Profiler and enabled trace flags using DBCC TRACEON(3605, 1204). However, even though Performance Monitor was indicating high Deadlocks/sec ratio I could not find any evidence of them in SQL Profiler or in SQL Server error log. I restarted my simulations several times, but nothing changed.
So, then a miracle happened and the last time I started my simulations all deadlocks disappeared. I did not change any queries and I am puzzled as to what could have happened. The only things I did were starting SQL Profiler and executing DBCC TRACEON commands.
Since queries and transactions did not change, could it be that they started executing (and hence releasing locks) faster because of lower network traffic at the end of the day or because the transaction log was written to disk?
Could it be anything that SQL Server 2005 does to optimize its own performance?
What else could have caused such a drastic change?
Thanks.
Alec
Hi,
Can I find out the user who has executed the job?
The result of query Q1) gives this information in the message column of the output.
Q1) select * FROMMsdb..sysjobhistory
Do we have any other table or SP from this information can be captured directly?
Regards,
Ashish Johri
Hi,Probable there is a simple solution for this, hopefully someone candirect me in the right direction.I have a table with a persons firstname, lastname, birthdate andaddress. However, I want to select only one person per address, namelythe eldest of all persons living on the same address.Can anyone provide me a solution?Thanks in advance.Duncan
View 2 Replies View RelatedHello -I have three feeds from sources around the world, each coming in at aseparate time.These feeds move into a large table (3GB) that is queried by managers.The feeds are loaded sequentially, and then the previous day's feedrows are deleted from the table (this is done so that the user'sapplication is never without data).The issue is that the import takes a lot of time, as do the deletes.These is hurting performance significantly. I attempted to fix theproblem by creating separate tables for each feed. I then created aview with the original table's name and used UNION ALL's. My intentionwas that as each feed came in, I'd alter the view with the new table'sname, and then truncate the older table. This met both goals ofconcurrency and import/delete speed.Unfortunately, this view seems to ignore the indexes on the underlyingtables, which devastates performance. I can't index the view, sincealtering it makes the index less useful.I'm looking for a different strategy for loading and deleting thedata, all without disruption to the applications. I'd appreciate anysuggestions....
View 3 Replies View RelatedHello folks,
I have probably a dumb newbie question but I can't find the answer anywhere.
On my Control Flow design pane I have two objects: a SQL task object and a Data flow task object. The first 'points' to the second. From my digging I believed that by indicating with the arrow from 1 to 2 that 1 would execute to finish before 2 was started.
My SQL taks is to truncate a table to receive the new data coming from the data flow task object. Instead 2 executes first and then 1. You can imagine producing an empty table was not my goal for this package.
Can anybody give me a clue?
Thanks....
Jim
I Imported a table over to sql 2000 server(through the DTS), then I exported the table to access 2000. After creating my forms my users were trying ot add data but couldnt, they kept getting an error message saying these records are not updateable. After some research I found that I never appointed a primary key, so after doing so the records became updateable. constant learning process adn I love it :)
View 7 Replies View RelatedI have a view based on few tables okay
I have a query that gives me document details of various revisions
Now i also get the latest rev of those if i use a MAX() in my Query
Now a case where i have say
Docu Numb - Revi - Received Date
Document1 - rev0 - 10/2004
Document1 - rev1 - 11/2004
Document1 - rev2 - 12/2004
Document1 - rev3 - 01/2005
Document1 - rev4 - 02/2005
Document1 - rev5 - 03/2005
Document1 - rev6 - 04/2005
Document1 - rev7 - 05/2005
If i query for latest it gives Document1 - rev7
And if i filter for a month of 12/2004
If i query for latest it should give Document1 - rev2
Now i need to get a filter with latest as rev2,
plus i also need as a indication of actual latest as rev 7
using a query
How do i do that ...
Coz when i use filter for a month and Max then it
does not consider rev7 for that filter and skip the record
and if i try to filter only as per month .. i get a wrong info
as latest rev2 which is not a true info as per database records.
May i possibly get both ... filtered info as per specific month and
also a rev info as what is the latest for a spec document.
Just i need to know is that possible .. in SQl using a query
Any more info needed please mail me on
neeraj.jariwala@gmail.com
Life is beautiful ... When you smile ...
Let me try to put this down in words...
If I log into the SQL server with my credentials and connect to SSIS through SQL Management Studio, I'm able to execute my test SSIS package successfully.
If I go to my machine and try to connect to SSIS "Integration Services..." I get "Access Denied" message.
If I execute the same package via DTEXECUI, the package errors out also.
My account is member of the group "Security_IT_Data", this group has sysadmin privileges on the SQL Server level.
The account used to run the SSIS Service is part of the Local Admin group on the box and also a sysadmin on the SQL Server.
What am I missing?
I have the following data (very simplified version)TransactionId Agent_Code------------- ----------191462 95328C205427 000024C205427 75547CAgent Code 75547C is a corporate agent. The others are not. I have alist of corporate codes so I can query against it, BUT what I want todo is...Return a unique TransactionId and max of the AgentCode, but if theAgent is a corporate agent, I need to return max of the corporate agentcodes. We can have multiple agents against the transaction andsometimes have a mix of corporate and none corporate agents. What weneed to do is see the corporate adviser if there is one. I only want 1record per TransactionId.We derive more data (sales hierarchy) from this, so are not interestedin anything other than the maximum, but need to know if it wascorporate which therefore gives me a different hierarchy later.Ideally I want to do this in a view and not use an SP. I can then usethis in my main view. If I have to resort to an SP, then so be it, butI would appreciate any helpful comments (or even better, the answer)ThanksRyan
View 4 Replies View RelatedIs there a way to restore just the data.mdf file and accept an existing log?
Current state: A client has a backup where the data file is 400mb and the log is 4.99gb. The avaiable hhd space is 6gb.
Restoring the backup to a test database fails.
Objective: Check the calendar file for missing data, and if found, update the live calendar with the missing items.
We don't need the database to be fully operational, we only need a particular table. I'll write a cursor if needed to update the live cal from the restored backup.
I suspect that the available diskspace is causing the retore to fail.
TIA
After being put in a position where I had to deal with a SQL problem without very little SQL knowledge I have screwed a clients main database up.
I backed up the transaction log and then deleted the log. The database is shown as suspect and won't let me restore.
Am I F&%ked?
Any help in sorting this out will be rewarded with much kudos and thanks
I wanted to create a table with an exsisting table, then create a relationship between the two. The table being created needs a DocID autonumbered primary key, is that possible to create through sql. An autonumber like access has, through a query or something. should I just have a insert into query or something?? how would I go about doing that.
Goodmorning Guys :)
I can execute a stored procedure I created in the Enterprise manager cant I??
View 8 Replies View RelatedYes this is a silly question, but I don't know the answer!
I have developed a database using SQL Server 2K. I am now upgrading to SQL Server 2005. Can I still use my current database files in SQL Server 2005? If I can, do u have any idea how I can make SQL Server 2005 load up the old files and start working?
Also my hosting provider has NOT upgraded to SQL Server 2005. He will only accept the old SQL Server 2K files. Can SQL Server 2005 save files that will work on a SQL Server 2K server?
Thank you!!!
Hi all. I think its a silly question to ask. What is better to use? I mean in terms of performance.
c.* or c.field1, c.field2, c.field3, c.lastfield..... ?
thanks
-Ron-
HiI've not used SQL Server for a while, and I've forgotten how you hide allthose system procedures (beginning with dt_) in Enterprise Manager.Could some kind person please refresh my memory?ThanksCaptain Nemo
View 2 Replies View RelatedIn a situation when you have a power cut, and then sometime later 'most' of your sql servers come back on line, is it better to leave them all down unless they all come back online, or is it better to let some of them come back up knowing that the ones that do come up will have job failure issues with the ones that are down. I pose this question purely from the perspective of scheduled job problem as we do not have people on site when we have intermittent power cuts at weekends. What would scheduled jobs which are due to run , but miss their run time as we leave the servers down after a power cut till we get back in on Mondays do when we do actually re-power them up, would they just resume from their next scheduled point, or would they try to run as often as they should have run?
Dave
Hi everyone,
Inside a Script Task I€™ve got this line:
Dts.Variables("Var1").Value = Dts.Variables("Var2").ToString
After that, I get this value for Var1:
"Microsoft.SqlServer.Dts.Runtime.Variable" {String}
String: "Microsoft.SqlServer.Dts.Runtime.Variable"
Does anyone have any idea about the hell is happening here?
Both of them has been defined at the same scope and own String as data type
Thanks in advance and regards,
I will give you the simplest version of this I know if.
I have 3 tables.
Person Table
PersonID, Forename, Surname
Event Table
EventID, EventName
Involvment Table
PersonID, EventID
In this, the Person table's primary key is PersonID, the Event table's primary key is EventID and the Involvment table's primary key is PersonID, EventID.
There is also a foreign key constrant between Person.PersonID and Involvment.PersonID and a foreign key constraint between Event.EventID and Involvment.EventID.
The sql to create this would be
CREATE TABLE [dbo].[Person](
[PersonID] [int] NOT NULL,
[Forename] [nchar](30) NOT NULL,
[Surname] [nchar](30) NOT NULL,
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Event](
[EventID] [int] NOT NULL,
[EventName] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Event] PRIMARY KEY CLUSTERED
(
[EventID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
CREATE TABLE [dbo].[Involvment](
[PersonID] [int] NOT NULL,
[EventID] [int] NOT NULL,
CONSTRAINT [PK_Involvment] PRIMARY KEY CLUSTERED
(
[PersonID] ASC,
[EventID] 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
ALTER TABLE [dbo].[Involvment] WITH CHECK ADD CONSTRAINT [FK_Involvment_Event] FOREIGN KEY([EventID])
REFERENCES [dbo].[Event] ([EventID])
GO
ALTER TABLE [dbo].[Involvment] CHECK CONSTRAINT [FK_Involvment_Event]
GO
ALTER TABLE [dbo].[Involvment] WITH CHECK ADD CONSTRAINT [FK_Involvment_Person] FOREIGN KEY([PersonID])
REFERENCES [dbo].[Person] ([PersonID])
GO
ALTER TABLE [dbo].[Involvment] CHECK CONSTRAINT [FK_Involvment_Person]
As so obviously stolen from SSMS.
Now what I am having problems with is if someone is involved with more than one event, then I only want them to get listed once.
If we have the following in the Person table.
0
John
Doe
1
Jane
Doe
the following in the events table
0
This
1
That
and this in the Involvment table
0
0
0
1
1
1
then when doing any select using the Involvment to get name and event information from their respective tables, there will be two entries for John Doe. I don't want this if it is possible. Although the same event multiple times is ok in this case.
So, if someone could help with this then it will be greatly appreciated. I'm still not that great with SQL so this is a problem which has been annoying me.
I have typically done any ETL style manipulations I needed to do to data stored in SQL Server in VB.NET. I would use the IMPORT?EXPORT DTS wizard to import flat files, or mabe something from ACCESS every now and then.
I am looking at a situation in my current contract where I will be pulling flat files from a mainframe and quasi relational stuff from a DB2 instance via an ODBC connection. I will be using this stuff to build a datawarehouse for a manufacturing client.
My question is this. Is there really enough good stuff in SSIS for what I will be doing to justify my learning it or if I'm comfortable doing the manipulations in VB.NET will that work just as well for my client? After all, I can schedule VB.NET apps to write results to log files and to run at specified times, etc. It just sort of always seemed to me that DTS was for people that needed to manipulate data without necessarily having to know a lot about programming per se.
I'm looking for opinions from people that know SSIS well. Is there enough meat to make cooking the SSIS meal worth the trouble?
Thanks in advance for any info.
As i am inserting data from textfile to sql server database i happened to have space Like a square appears in front of data for certain columns.How do i remove that????I use Trim(column) for all incoming columns but it does not work.
View 3 Replies View RelatedWhat is the function of SQL Server Manager Studio Express?
Create Database and tables?
It seems that Visual Studio is already including the functions.
We can create database and table in Visual Studio 2005.
I am a new to SQL, sorry for the silly question.
Hi:
in sp_add_jobschedule
@freq_type = 8 for weekly @freq_interval = 1 for Sunday.
I need to script to create a job which will run every Sunday 6 pm, to backup one prod database to a new backup device. EX. database name is DBTest, and the 7/20/2003 (Sunday) running result should be backup_DBTest_2003_07_20.bak.
and next Sunday's should be backup_DBTest_2003_07_27.bak.
(if this way, the backup device will be created at the begining of the job running...)
this way the backup would not be overwritten. Is there a way to dynamically generate 54 weeks job script from this coming Sunday?
Or to make the backup result as backup_DBTest_2003_week27.bak, backup_DBTest_2003_week28.bak....
thanks
David
SQL 2000 pricing and licensing is quite confusing, and even more pain including the upgrading pricing from sql6.5/sql7.0.
I got very clear Windows2000 pricing and upgrading from one Microsoft web address. But just could not find a ONE Microsoft web sit explicitely describe the $ pricing for each of SQL2000 edition (per processor vs. CAL..., including upgrading from SQL 6.5, SQL 7.0). May be I missed that site?
Thanks
David