I am migrating data that has to be inserted using stored procedures which already exist. The stored procedures have parameters and a return value (from a select statement) of an id for the row inserted. Within an OLE DB Command in SSIS I can call the stored procedure passing column values as the parameters and I usually use output paramt...
Would 2 tier be a valid choice for this scenario: sql server database the business would never require more than a couple of hundred simultaneous connections to the shared database over a LAN the fact that (I believe) it is a lot less effort to develop a 2 tier application Client program updates automated over the LAN ...
I have to queries: select id, name, num from pending id, name, num 1, first, 1 3, third, 12 select id, name, num from completed id, name, num 1, first, 100 2, second, 20 I want output to combine these, maybe like a pivot table, using T-SQL. id, name, pending, completed, total 1, first, 1, 100, 101 2, second, null, 20, 20 ...
I have two MS SQL servers at different locations on the WAN. At the moment I use LINKED servers to copy table data from one to the other, but performance is toilet. It takes about 10 minutes to copy a 40 field table with 800 rows. What is the best 'simple' method of copying/replacing table data on a different SQL server. This solution m...
I want to implement paging in a gridview or in an html table which I will fill using ajax. How should I write queries to support paging? For example if pagesize is 20 and when the user clicks page 3, rows between 41 and 60 must be shown on table. At first I can get all records and put them into cache but I think this is the wrong way. Be...
Is there any way to get the benefits of SSIS 2008 (including the performance improvements) while still working with a SQL Server 2005 database? It seems like it still pulls the data from OLE, so I don't see how it would be a problem. I'm running the package from the dtproj file, not loaded onto the server. ...
This table gets hit with this query the most, so I want add a index to speed things up, this table will have 5 million rows it in. My query looks like this: SELECT someID FROM someTable WHERE myVarChar = @myVarChar AND MyBit = 0 AND MyBit2 = 1 AND MyBit3 = 0 myVarChar is unique also. What would the best index be for this t...
Is there any way where I can check if the field is numeric in derived column/Conditional Split of the data Flow task in Sql Server Integration Services 2005. Functionality should be like ISNUMERIC() function. Currently I'm using ((DT_NUMERIC,12,0)fieldname= (DT_NUMERIC,12,0)fieldname) in the derived column expression to check if th...
I don't want to touch-off a religious war here, but there seem to be two schools of thoughts in how to represent boolean values in a database. Some say bit is the appropriate data type, while others argue tinyint is better. The only differences I'm aware of are these: bit: storage size is 1 bit, possible values are 0 or 1 tinyint: sto...
Hi all. The current project that I'm working on have table with 126 columns and the least that i saw is at least 50 columns. Should a table hold less columns per table or separate them out into a new table and use relationship? In your experience what is the max columns per table? Does it affect the database with such design? Jack ...
I need to do something like this but SQL Server 2008 doesn't like it. My query is actually more complex than this and I realize this isn't the best way to accomplish what I'm doing but my focus is on the functionality of the WITH statement and not the select and where statements. WITH stuff1 AS ( select name, startdate, id from...
Are there any common reasons why upgrading a database from SQL Server 2000 to SQL Server 2005 would result in slower queries? This is coming from an ASP.NET 1.1 application with hundred of tables, everything is indexed and seems to run well on the older version. ...
We have a multitude of databases whose files are stored on their own individual mount points in a drive (ex. Z:\dbname_db\dbname_db.mdf and Z:\dbname_log\dbname_log.ldf) What I'm looking for is a way to find the available free space of the mount point. EXEC xp_cmdshell 'fsutil volume diskfree Z:\dbname_db' But the service is...
Hi, we are trying to create C# CLR Stored Procedure on a SQL Server 2005 machine. The C# code connects to an external datasource via an OLEDB driver to get a datatable. It has been tested on a separate console application, and it runs without problems. (The OLEDEB Driver is a proprietary driver connecting to a proprietary DB...) On SQL...
Can anyone think of a reason to do this: SELECT * FROM TableA ORDER BY cast(cast(RealColumnA as nvarchar(50))as float) --where RealColumnA is defined as real in the table A former developer of mine insisted this was necessary to get reals to sort correctly. Can anyone think of a reason that may be true? The cast in the orderby clause...
If you had a DBA who was responsible for deploying databases in a live environment, what would you choose to give him? A database creation script or a backup which he could restore onto an existing database? What kinds of advantages/disadvantages are there? (We're using MSSQL2000 and MSSQL2005) ...
What is the syntax for reading until the end of file in SSIS VBScript? Dim readFile As FileInfo = New FileInfo(logHourlyName) If readFile.Exists() Then Dim textStream As StreamReader = readFile.OpenText() Dim strLine As String Do While Not EOF <--- what goes here? curLine = textStream.ReadLine() Loop textStream....
I have an XMLA query which returns the State and Last Processed date of an Analysis Services cube as XML, like so: Query: <Discover xmlns="urn:schemas-microsoft-com:xml-analysis"> <RequestType>DISCOVER_XML_METADATA</RequestType> <Restrictions > <RestrictionList xmlns="urn:schemas-microsoft-com:xml-analysis"> <DatabaseID>S...
Is Dynamic SQL more vulnerable to SQL Injection/hacking? If yes, how to prevent? ...
We have scheduled a number of jobs in SQL Server 2000. We want these jobs to be executed in a sequential order i.e. the failure of one job should prevent the next job from running. Can someone help me on doing this or creating dependency between scheduled jobs. ...