We are trying to read a table name Order through command line SQLcmd.
But it is giving us an error. Order is an SQl2005 reserver word and app programmer created that table who left. No we have problem to read that table through sqlcmd. any ide a how we can do that.
C:>sqlcmd
1> use ACCMedford
2> select * from 'Order'
3> go
Msg 102, Level 15, State 1, Server ACCMedford, Line 2
Incorrect syntax near 'Order'.
1> select * from "order"
2> go
Msg 102, Level 15, State 1, Server ACCMedford,, Line 2
Incorrect syntax near 'order'.
1> select * from order
2> go
Msg 156, Level 15, State 1, Server ACCMedford,, Line 1
Incorrect syntax near the keyword 'order'.
I need to calculate changes in reserve from the initial reserve calculation as seen in the diagram below. The initial reserve is derived from 1000-100 = 900. Next 900-50 = 850, 850-150 = 700 and so on.
I have gone through the table partitioning in MSDN, like Designing Partitions to Manage Subsets of Data . But how to do this in actual world, since some db need to partition for 7 days, then archive these days records on the 8th day, while other prefer 14 days/monthly, and run this repetitively for many years? If this is running weekly, how can i generate the scheme and function dynamically? What if ID for row count is not viable?
Sure this will not work:
Code Snippet CREATE PARTITION FUNCTION [TransactionRangePF1] (datetime) AS RANGE RIGHT FOR VALUES ('10/01/2003', '10/02/2003', '10/03/2003', '10/04/2004', '10/05/2004', '10/06/2004', '10/07/2004'); GO
On an SQL2000 Enterprise Edition with SP3 and 3.2 GB memory, I have restore around 700 databases from backup .bak files with no errors. Now, while in the process to restore (proc to restore with move statement) another 150 databases, following errors are encountered: ------------------------- There is insufficient system memory to run this query.
WARNING: failed to reserve contiguous memory of size = 196608.
Query Memory Manager: Grants=0 Waiting=0 Maximum=150384 Available=150384 "Buffer Distribution: stolen=6699 Free=280 Procedures=4821 Inram=0 Dirty=1221 Kept=0 I/O=0, Latched=1169, Other=194482" ------------------------- Is ther memory leaking and/or hardware related? the sql used only 1.8 gb mem and the whole system is using 2.4 gb mem. After reboot, same error appears in the middle of restoring 150 databases.
Hey All,I think this is something like locking, but not quite. I need to select a record in a database and display the information to the end user. The end user has a few minutes to review it before giving the ok at which point I will update the record.However, while the user is reviewing the record, i dont want anyone else to be able to grab that record. I also want to make sure that if the user doesnt submit the page, that the record will be freed up and not indefinitely marked as taken.What would be the best way to do this? This is with .net 2.0 and sql2000
Hello, Anybody has came to solution across this error? We are getting this error repeatedly. I appreciate if anybody give suitable solution for this. Thanks, Ravi
Hi everybody, This is propably very silly question... I've used SQL Server for sometime now, but always through tools supplied by my hosts (my Little Admin, SQL Server Web Admin). I always wanted to try running the real thing, so I've installed SQL Server 2K developer edition on my machine. I also have IIS and .NET framework running. What would be the next step to see those 3 components in action? I have noticed Northwind database in the Enterprise Manager. How do I make the connection? I have the SQL Server Service Manager running fine, but when I open Query Analyzer it comes up with the window "Connect to SQL Server" and this little dropdown, where you can choose the server is actually empty. What do I do? Thank you all for your pointers.
Say you have users who would be accessing a SQL Server database over the web from around the world (mainly the US, Japan, and UK), and you wanted to make the access as fast as possible.
Say also you have a couple of million dollars for hardware and software.
Hi, I have a table ABC. It has 2 columns, A and B. At column A i have 3 rows, Week,Goal,Used. Let say for column B at row Week is WW15 now, after 7 days i want the row to be updated to WW16. How do i do that? Advice please.
I've used SQL Server for sometime now, but always through tools supplied by my hosts (my Little Admin, SQL Server Web Admin).
I always wanted to try running the real thing, so I've installed SQL Server 2K developer edition on my machine. I also have IIS and .NET framework running.
What would be the next step to see those 3 components in action? I have noticed Northwind database in the Enterprise Manager. How do I make the connection?
I have the SQL Server Service Manager running fine, but when I open Query Analyzer it comes up with the window "Connect to SQL Server" and this little dropdown, where you can choose the server is actually empty. What do I do?
Hi! I want to get hole through to Analysis Services 2005 from MS SQL Server 2005 with openquery, so I created linked server (MSOLAP). How to test that I can access an "database" on Analysis Services Server?
Something like select * from openquery(linked_olap, getdate()) or openquery(linked_olap, 'print hello world') or openquery(linked_olap, select results from dataminingmodelCreatedWithVstudio) ? Thanks Bjørn
I hope someone can answer this, I'm not even sure where to start looking for documentation on this. The SQL query I'm referencing is included at the bottom of this post.
I have a query with 3 select statements joined together like tables. It works great, except for the fact that I need to declare a variable and make it a table within two of those 3. The example is below. You'll see that I have three select statements made into tables A, B, and C, and that table A has a variable @years, which is a table.
This works when I just run table A by itself, but when I execute the entire query, I get an error about the "declare" keyword, and then some other errors near the word "as" and the ")" character. These are some of those errors that I find pretty meaningless that just mean I've really thrown something off.
So, am I not allowed to declare a variable within these SELECT tables that I'm creating and joining?
Thanks in advance, Andy
Select * from
(
declare @years table (years int);
insert into @years
select
CASE
WHEN month(getdate()) in (1) THEN year(getdate())-1
WHEN month(getdate()) in (2, 3, 4, 5, 6, 7, 8, 9, 10, 11, 12) THEN year(getdate())
END
select
u.fullname
, sum(tx.Dm_Time) LastMonthBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) lasmosbillingpercentage
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
Month(tx.Dm_Date) = Month(getdate())-1
and
year(dm_date) = (select years from @years)
and tx.dm_billable = 1
group by u.fullname
) as A
left outer join
(select
u.FullName
, sum(tx.Dm_Time) Billhours
, ((sum(tx.Dm_Time))
/
((day(getdate()) * ((5.0)/(7.0))) * 8)) perc
from
Dm_TimeEntry tx
join
systemuserbase u
on
(tx.owninguser = u.systemuserid)
where
tx.Dm_Billable = '1'
and
month(tx.Dm_Date) = month(GetDate())
and
year(tx.Dm_Date) = year(GetDate())
group by u.fullname) as B
on
A.Fullname = B.Fullname
Left Outer Join
(
select
u.fullname
, sum(tx.Dm_Time) TwomosagoBillhours
, sum(tx.Dm_Time)/((select dm_billabledays from dm_billabledays where Dm_Month = Month(GetDate()))*8) twomosagobillingpercentage
I was told I can use them with SQLDataSources, but I have no clue how to do it. I believe I have managed to set up the querry in the datasource correctly, but what do I need to do to actually use it in my VB code?
Any help is appreciated, and any tutorials you've found to be usefull on the subject are sure to help. Thanks
Hi all, New to replication I need suggestions on how go about keeping 2 sql servers from different parts of the world to be in sync all times, some latency may be accepted.
Here is the senario, we have website hosted in NA and customers from all parts of the world log in to oursystem and does what ever they have to do which will update the sql server databases. as our company is expanding we want to have a same website hosted in eu, so when ever a person logs from eu he will be hitting the webservers in eu and anything he do will go to the SQL database servers that are in eu. Now the problem i have is when one site goes down say NA site goes down, we need to route all the traffic to site in EU that means sql servers in EU. This brings the challenge of both the servers need to be in sync all times.
I did some research on merge replication, this might not work because in this concept we have to make 1 server as the master and the others as slaves. Also i need to think in a way that if they want to expand more that means to asia, i will be having another server, these all need to be in Sync.
We are using SQL Server 2005 standard edition. I was reading that this can be achived easily with peer-to-peer replication,but is only available in Enterprise edition which is 4times the cost of standard edition.My company is not going to agree for this, so got to do the research and propose a solution
Real World: Backup Strategy and implementation, how? A quote:
€œReal World:
Whether you back up to tape or disk drive, you should use the tape rotation technique. Create multiple sets, and then write to these sets on a rotating basis. With a disk drive, for example, you could create these back files on different network drives and use them as follows:
//servername/data1drive/backups/AWorks_Set1.bak. Used in week 1, 3, 5 and so on for full and differential backups. //servername/data2drive/backups/AWorks_Set2.bak. Used in week 2, 4, 6 and so on for full and differential backups. //servername/data3drive/backups/AWorks_Set3.bak. Used in the first week of the month for full and differential backups. //servername/data4drive/backups/AWorks_Set4.bak. Used in the first week of the quarter for full and differential backups.
Do not forget that each time you start a new rotation on a tape set, you should overwrite the existing media. For example, you would append all backups in week 1. Then, when starting the next rotation in week 3, you would overwrite the existing media for the first backup and then append the remaining backups for the week.€?
I understand these concepts, however in €˜the real world€™ how do you go about implementing these jobs in SQL2K and how on earth do you schedule the tasks to overwrite, for example, week 1, when on week 3€™s rotation.
Could I have real world examples or scripts for the jobs that would carry out this task? It appears that whatever course you do, it does not fully cover the above, and I have only worked on my own and never with a DBA, so I have never seen this implemented in any environment.
I would like full details on this please, as I need to get my head around it.
I have traditionally done web app and client server programming and am have been playing around with a new tool (Win forms) app that I eventually want to distribute to other developers and a couple of business people in our organization. I have done apps in the past that all connect to a central server for data access. The I'm working on now will have an individual DB per user and should be available locally for a desktop version of the software.
I am looking for more resources on things to consider when deploying an app with either MSDE or SQL 2005 Express. More specifically, items like long term maintenance of the db once it's installed with the user, etc. (DB bloat, transaction files, auto maintenance routines I may want to build in, etc)
I have seen all of the msdn docs on what you need to deploy (and how to do it), but I'm looking fro input from people that have deployed it and any significant pitfalls they have run into with that sort of deployment.
Any links or book references would be appreciated, thank you,
I am unable to the access on table even after providing the SELECT permission on table.
Used Query by me :
Here Test is schema ; Card is table ; User is Satish
To grant select on Table
GRANT SELECT ON TEST.Card TO satish Even after this it is not working, So provided select on schema also. used query : GRANT SELECT ON SCHEMA::TEST TO Satish.
For reasons that are not relevant (though I explain them below *), Iwant, for all my users whatever privelige level, an SP which createsand inserts into a temporary table and then another SP which reads anddrops the same temporary table.My users are not able to create dbo tables (eg dbo.tblTest), but arepermitted to create tables under their own user (eg MyUser.tblTest). Ihave found that I can achieve my aim by using code like this . . .SET @SQL = 'CREATE TABLE ' + @MyUserName + '.' + 'tblTest(tstIDDATETIME)'EXEC (@SQL)SET @SQL = 'INSERT INTO ' + @MyUserName + '.' + 'tblTest(tstID) VALUES(GETDATE())'EXEC (@SQL)This becomes exceptionally cumbersome for the complex INSERT & SELECTcode. I'm looking for a simpler way.Simplified down, I am looking for something like this . . .CREATE PROCEDURE dbo.TestInsert ASCREATE TABLE tblTest(tstID DATETIME)INSERT INTO tblTest(tstID) VALUES(GETDATE())GOCREATE PROCEDURE dbo.TestSelect ASSELECT * FROM tblTestDROP TABLE tblTestIn the above example, if the SPs are owned by dbo (as above), CREATETABLE & DROP TABLE use MyUser.tblTest while INSERT & SELECT usedbo.tblTest.If the SPs are owned by the user (eg MyUser.TestInsert), it workscorrectly (MyUser.tblTest is used throughout) but I would have to havea pair of SPs for each user.* I have MS Access ADP front end linked to a SQL Server database. Forreports with complex datasets, it times out. Therefore it suit mypurposes to create a temporary table first and then to open the reportbased on that temporary table.
Dear All I need to cerate a SP that SELECTS all the records from a table WHERE the first letter of each records starts with 'A' or 'B' or 'C' and so on. The letter is passed via a parameter from a aspx web page, I was wondering that someone can help me in the what TSQL to use I am not looking for a solution just a poin in the right direction. Can you help.
I have an unusual problem. I am using VB.Net 2003 and sqlexpress using .NET dataset to insert records into an timecards table. After inserting several records I tried a 'Select * from timecards' and the inserted records where not selected. if I 'select * from timecards order by employee' ( or any other field) the inserted records are selected! The table was created by an Access Upsize command.
I have set up a 'Parameters' table that solely stores all pre-assigned selection values for a webform. I customized a stored query to Select the values from the Parameters table. I set up the webform. The result is that the form1.apsx automatically populates each DropDownList task with the pre-assigned values from the 'Parameters' table (for example, the stored values in the 'Parameters' table 'Home', 'Business', and 'Other' populate the drop down list for 'Type'). The programming to move the selected data from form1.aspx to a new table in the SQL database perplexes me. If possible, I would like to use the form1.aspx to Postback (or Insert) the "selected" data to a *new* column in a *new* table (such as writing the data to the 'CustomerType' column in the 'Customers' table; I clearly do not want to write back to the 'Parameters' table). Any help to get over this hurdle would be deeply appreciated.
