I have a web application that uses two databases. DB1 Users perform their CRUD (Create, Read, Update, Delete) operations. Database DB2 is a readonly database on a different server that i use for reporting purposes. Every hour my DB1 saves transaction logs and on DB2 i have a job that restores them on that DB2 to keep it ~up to date. Pr...
There are two things here i want to accomplish. List I want to pass a datetime into my stored procedure in the following format ('2007-05-28 00:00:00'), but that doesn't seem to work unless i use {ts '2007-05-28 00:00:00'} The next thing is I want to increase the @SEAL_DATE by a second so it can be used to check the dates between the e...
I am receiving this error. What I am doing is trying to take data from one table and one db and place it into another db and table. The tables are not exactly the same. I am using a FETCH cursor, so I fetch the first row from db1 table and then place each column value into declared variables. Then I run the insert statement into db2 tabl...
What are the scoping rules for transaction isolation levels in SQL Server 2005? I know what the different levels mean, but not how to properly apply them outside of a manually run script. I can't find a guide for practical use in production-quality code. Obviously, the scope begins when you use a command like this: SET TRANSACTION ISOL...
How does SQL Server 2008's Hierarchy data type perform compared to using the hierarchy implementation described by Joe Celko here: http://www.intelligententerprise.com/001020/celko.jhtml? I've used Celko's method in the past with great results - but don't want to implement it for a new project unless it's better than what Microsoft has ...
Can a fact table have no keys at all? or if it can, is it a good design? If a fact table do not have any dimensions, on what basis is it analyzed? What if a fact table has primary key/s only and no foreign key/s? ...
Hi, I have a script that connects to SQL Server 2005 named instance using osql. But the script throws the following errors: [SQL Native Client]Named Pipes Provider: Could not open a connection to SQL Server [53]. [SQL Native Client]Login timeout expired [SQL Native Client]An error has occurred while establishing a connection to the se...
I'm trying to connect to a MS SQL Server 2005 Express database that is running on the local host from a java program. I have tried the same connect URL (below) that I used on another system (same jave code) that was running MS SQL Server 2000. But that does not work. jdbc:jtds:sqlserver://127.0.0.1:1433/Finance Any ideas? ...
Say a stored procedure on MSSQL Server uses an SQL transaction using BEGIN TRANSACTION/COMMIT TRANSACTION, how does that differ from beginning and commiting one using ADO.NET via SqlConnection.BeginTransaction()? ...
So I've got a situation where I need to pass a bunch of data to the server to create a bunch of records. So I read this article and chose the XML method. So I've got the stored procedure all created in sql server 2005. But now I need to call it from asp.net code. Only all I've got for a database connection is an odbcconnection. I se...
I am trying to connect SQL Server from a Windows service. Now my Windows service is installed in different system. Now if I want to connect to my SQL Server, how can I connect? I am able to connect if I add the respective system userid in the SQL Server login users. I don't think that is the right way..because if i required to install th...
Guys, I need to restrict the development users from creating new schedules for SQL Agent jobs. They should be able to create a job and pick one of the available schedules, but should not be able to create a new schedule. I have a bunch of developers creating SSIS packages that need to be scheduled off business hours. I am now faced wit...
I have to copy data for one student (clone student) that encompass three tables: tblStudent (where PK is studentId) tblStudentSubjects (where PK is StudentsubjectId and FK is StudentId) tblStudentSubjectComments (where PK doesn't exist and FK is StudentSubjectId) To accomplish this I would have to Create Student ...
In code one can specify globally accessible constants/enums/etc once that can then be reused through out the application. This gives the ability to use meaningful names like 'Mazda' instead of numbers like '2'. We would like to do the same with in our SQL Server stored procedures, but are not sure of the best way of implementing this. ...
I am working on an application that has two distinct audiences and as such has two distinct types of data. On the one hand there is very high-read/low-write meta data. These tables will have relatively low row counts and will be mostly read by the other side of the application. The other side of the application is based around very ...
I am pulling the following numbers from MS SQL Server: 345 345.6 345.67 How do I pull them so that they look like the following: 345.00 345.60 345.67 What datatype should they be stored as and what is the magic function to pull them? Thank you. ...
Duplicate Advantages of MS SQL Server 2008 over MS SQL Server 2005? What are the major differences between Sql Server 2005 and Sql Server 2008? How could I convince my organization to invest in Sql Server 2008? For me, one major aspect that I see is that they have a good framework to programmatically generate SSIS packages with the us...
If I want to set a variable to a field in a table I normally use something like SELECT @UserIdToUpdate = userId FROM #NewUsers In this case there will be multiple results and I just want the first one so I tried this but it fails and says invalid syntax top SELECT @UserIdToUpdate = TOP 1 UserId FROM #NewUsers If this is this case c...
At the moment I use DATEPART(yy, MY_DATE) = DATEPART(yy, GETDATE()) to get Year-To-Date details and have to convert it into the revolving last 12 months. How would I do that? ...
Hi, I have a users table in MSSQL and i want to find out the average number of logins for all users per day? I am thinking something with division but not entirely sure the USER table looks like this [ID] ,[APPLICATION_ID] ,[LOGIN_NAME] ,[EMAIL] ,[MOBILE_PHONE1] ,[MOBILE_PHONE2] ,[TITLE] ,[FIRST_NAME] ,[LAST_NAME] ...